August 5, 2010
It’s common in most cubes to have a number of different date dimensions, whether role playing, distinct, or a combination of both. Say for example, Entry Date, Posting Date and Accounting Period. There may also be numerous hierarchies in each date dimension, such as calendar and fiscal calendar, leading to a relatively complicated array of dates to worry about when calculating semi-additive measures.
If we create a date related calculation (i.e. total to date) how do we ensure that this calculation works across all date dimensions?
Lets assume we have a stock movement measure, where each record in the fact table is the change in stock (plus or minus). The current stock level is found by using a calculation totaling every record to date.
CREATE MEMBER CURRENTCUBE.[Measures].[Stock Level]
AS
SUM({NULL:[Date].[Calendar].CurrentMember}
, [Measures].[Stock Movement]
);
[Note that {NULL:xxx} just creates a set of everything before the xxx member, i.e. everything to date]
This works just fine, if the user selects the [Date].[Calendar] hierarchy. What if the user selects the [Date].[Fiscal] hierarchy, or the [Period] dimension? Basically the calculation wont work, as the MDX expression is only aware of the [Date].[Calendar] hierarchy.
The simple solution is to use the Aggregate function over all of the dimensions that the calculation needs to be aware of:
CREATE MEMBER CURRENTCUBE.[Measures].[Stock Level]
AS
AGGREGATE(
{NULL:[Date].[Fiscal].CurrentMember}
* {NULL:[Date].[Calendar].CurrentMember}
* {NULL:[Period].[Period].CurrentMember}
, [Measures].[Stock Movement]
);
The calculation will then use whichever date or time hierarchy is selected. It will even cope if multiple dimensions are selected, say the calendar on 0 and the periods on 1, both axis will honor the aggregation as expected.
Frog-Blog out.
Posted in Analysis Services | 
Tags: Analysis Services, Calculated Member, MDX, Semi Additive, SSAS, Time Dimension | 
10 Comments »
August 2, 2010
Anyone involved in the Business Intelligence space has had their head in the sand if they are not aware of the long running, and more often than not misunderstood, debate between the two conceptual models of data warehouse design.
Bill Inmon has recently posted an article on www.b-eye-network.com discussing the matter, and to his credit, has tried to put forward a number of balanced pros and cons of each methodology.
I’ll state now that I’m a big advocate of a hybrid approach, taking elements from both Imnon and Kimball camps and selecting the right approach for each unique project depending on the requirements and purpose of the warehouse. I therefore appreciate both sides of the debate, and am not going to jump to the defence of either side. Having said that, most projects more often than not have a weighting towards Kimball due to the time pressures imposed by clients.
Bill nicely simmarises the key elements of each approach as:
The Kimball approach to database design and development is typified by the star schema design of databases. There are fact tables and dimension tables. In a complex environment, there are snowflake structures, which are merely extended versions of the star schema. In order to resolve differences of granularity between fact tables, conformed dimensions are used. Staging areas are occasionally used to capture raw data before the placement of the data into a Kimball style data mart.
The Inmon approach to data warehousing centers around a relational, non redundant, granular, integrated design of detailed data. From this base of data, data marts are spun off to different departments according to their individual analytical needs. In recent vintage, with DW 2.0, the Inmon approach calls for the recognition of the life cycle of data within the data warehouse, the inclusion and integration of unstructured data within the data warehouse, and the close integration of metadata into the data warehouse infrastructure.
He then proceeds to present a brief comparitive assessment of the pros and cons of each. I don’t entirely agree with the black and white nature of the comparisons as most items are a shade of grey in both camps, but it certainly provides a good starting point for those that are starting out in the BI field and want to know more about whhat this debate is alll about.
Possibly the most interesting item (certainly from a business intelligence consultant’s perspective) is Bill’s renewed call for an open, public debate between Bill and Ralph – It gets my vote!
Thanks to Graham Bradfield at Computer People for pointing me towards this article in his BI newsletter.
View the full article here: Data Warehousing: Kimball vs Inmon
Posted in Business Intelligence | Tags: Architecture, Business Intelligence, Data Warehouse, Inmon, Kimball | No Comments »
July 26, 2010
Leap before you can land…
Research scientists at the Southern Illinois University have concluded that primitive frogs learned to jump before they learned to land. The result being a rather inelegant crash landing. I’m sure the frogs were happy that they achieved their goal of getting from A to B, but I do feel sorry for them having to endure the presumably painful landing, not to mention the indignity of having the rest of the pond witness the proceedings!
What struck me about this story, aside from the obvious frog reference (albeit not purple!), was the parallel to Business Intelligence projects that we encounter time and time again. BI systems that are put in place, but whilst the project teams are basking in the glory of accomplishing their goal, often sporting a couple of bruises picked up along the way, the wider business looks on not really knowing what has just happened or understanding how the company or their department are going to benefit from this leap in technology.
Although BI, as both a business function and technical toolset, has long since reached maturity, its adoption within SMEs is still surprisingly in its youth. Most companies who embark on a BI project seem to do so either because they have been sold on a fantastic vision of the future of information management, or because they have a specific business need to solve. Whatever the reasons for initiating a BI project, there is often very little thought given to how this new information system is likely to impact the wider business, or how the system can be utilised to provide far more benefit than the original project scope called for.
There are two aspects to this impact, the pain and the gain.
The gain
When the company’s data is cleaned, consolidated and remodelled to make reporting and analysis more simple (one of the primary goals of a BI project), the tendency is to use that system to reproduce the company’s old static reports but in a slightly more glossy fashion, only faster and more accurate. This stops far short of what a good data warehouse or cube is capable of. Enter self-service BI.
Self-service BI essentially allows your employees to have a level of (secured) access to the underlying BI data, combined with the right tools to perform their own analysis. This enables them to not just consume their old static reports, but to pro-actively go digging for information, knowledge and a greater understanding of your company. Gone are the days when requiring a change to a report has to be documented, requested, specified, programmed, tested & released. A well designed data warehouse and cube, along with the right client tools (often just Excel), enables a user to just do it themselves with the bare minimum of training (if any), whilst retaining the confidence that the resulting report is accurate; the warehouse/cube take care of ensuring the underlying business logic is right.
The pain
A data warehouse is, by definition, a combined set of data from a variety of source systems. This means that any change to the source systems must be assessed for their impact on the warehouse. After putting a sparkly new BI infrastructure in place, you don’t want it to fall over because someone changed the structure of a source database or extract file! This adds a layer of change control that may not have existed before, and can cause some headaches.
There is usually a moderately large requirement for business users’ time, both to help the BI architect to understand and model the business properly, and also in testing.
Pain but no gain?
So, if the benefits such as self service BI (with dashboards, data mining, KPIs, PowerPivot etc. etc) are not adopted and utilised, the business is left having made an impressive technology leap, but is then stuck with the impact on source systems, a bill, and confusion from staff who don’t see the benefit of getting their old reports via a different method.
Evolution taught the frog that by landing properly, he can quickly jump again to go even further, and avoid the pain in the meantime.
June 9, 2010
I’m excited to be presenting another session to the South Wales SQL Server User Group.
On Thursday 24th June 2010, Eversheds in Cardiff are kindly hosting the event, to run from 18:45 to 21:00.
The event is free, and you’ll even get pizza thrown in – what more can you ask for? Oh yes, some BI content…
My session will cover data warehouse modelling, including a number of hands on business case studies including transactional data, account balances and duration based data.
Please feel free to bring your own data modelling problems along and I’ll try and cover as many as I can.
Register for free here: http://www.sqlserverfaq.com/events/235/Data-warehouse-design-case-studies-Other-BI-related-session-TBC.aspx
Hope to see you there!
Posted in Analysis Services, SQL Server | Tags: Data Warehouse, Modelling, Star Schema | No Comments »
May 18, 2010
Purple Frog spent a very interesting day at Microsoft last week, at one of their many events promoting the launch of SQL Server 2008 R2. Rafal Lukewiecki presented an entertaining (as always!) and informative series of talks covering the release, focusing on the enhanced Business Intelligence tools available.
The primary changes to note are
- Power Pivot – An in memory, client side add-in to Excel, that allows users to create virtual cubes on their desktop and analyse over 100m records of data virtually instantly
- DAX – A new expression language, designed for non-technical (probably more realistically, semi-technical) users to extend pivot tables and power pivot tables without having to learn MDX
- Report Components – In a report consisting of a couple of tables, a chart and a few gauges (gauges, sparklines & maps are all new features of SSRS), you can save each element as a component and re-use it in different reports. This should result in much less duplication of work.
- Report Builder 3 – A thin-client tool allowing end users to create Reporting Services reports. This is a big enhancement over its predecessor s it is finally fully compatible with reports created in the Business Intelligence Development Studio (BIDS), including report components.
- Master Data Services – A centralised tool and database intended to provide governance of your organisation’s master data (centralised list of products, fiscal calendar, regions etc.).
The enhancements to Reporting Services (SSRS) are very welcome, and should be of huge benefit to anyone either currently using SSRS or considering using it. I firmly believe that there are no comparable web based reporting engines that even come close for SME organisations when looking at the whole picture including cost of implementation, ease of use, flexibility and capability.
Master Data Services as a concept has been around for a long time, but there has never been a tool available to organisations to effectively implement it. This is Microsoft’s first proper stab at delivering a workable solution, and although I’m a big fan of the concept, and have no doubt of its benefit to a SME, I’m yet to be convinced that the tool is ready for a large scale corporate environment. Time will tell how scalable and manageable the system is, and credit has to go to Microsoft for starting the ball rolling.
The most impressive addition is without a doubt PowerPivot. In a nutshell, it’s a user defined OLAP cube wrapped up within Excel 2010, running entirely in memory on a user’s workstation. If you’ve not yet played with it or seen a demo, I’ll try and elaborate for you… Think about loading Excel with 1 million rows, and then imagine sorting and filtering a number of those columns [cue going out to lunch whilst waiting for Excel to catch up]. With PowerPivot, you can sort and filter over 100 million rows of data almost in an instant – it’s very impressive indeed!
That’s the snazzy demo bit, but to reduce it to a glorified spreadsheet is very harsh indeed. It allows a user to import multiple data sources and combine them together into a single dimensional data model, PowerPivot will create your own personal cube, without you having to build a warehouse, without knowing anything about MDX, dimension hierarchies, attribute relationships, granularity etc. etc.
Microsoft’s vision and reason for creating this tool is self-service BI, allowing users to create their own cubes, data analysis environments and reporting systems. And this is where I start to have a problem…
I can’t remember the last time I designed a data warehouse, where I did not find significant data quality problems, conflicting data, missing data, duplicated data etc.. I also find it hard to think of a situation where an end user (even a power user) is sufficiently clued up about the intricacies of a source OLTP database to be able to extract the right data and know what to do with it. Or if they are, a dozen other people in different departments have a different idea about how things work, resulting in many different versions of the truth.
I’m therefore (for now!) sticking with the opinion that it is still absolutely vital for an organisation to provide a clean, consistent, dimensionally modelled data warehouse as the basis for their BI/MI infrastructure. Tools like PowerPivot then sit very nicely on top to provide an incredibly powerful and beneficial user experience, but to try and use the emergence of self-service BI tools to usher in a new ‘non-data warehouse’ era is a very dangerous route which I hope people will avoid.
In summary – this release brings with it a fantastic host of new tools, but with great power comes great responsibility…
Posted in Analysis Services, Integration Services, Reporting Services, SQL Server | Tags: DAX, Master Data Services, PowerPivot, SQL Server | 2 Comments »
April 15, 2010
Following the launch of SQL Server 2008 R2 this week, the ever dedicated SQL Bits team are putting on the next installment tomorrow with SQL Bits VI – ‘the Sixth Sets’. The Purple Froggers will of course be there, hopefully in time for a pre-conference bacon butty!
Also worth mentioning is ‘Microsoft Solutions for Business Intelligence’, a seminar that Microsoft are holding at their Reading campus on May 12 2010. Rafal Lukewiecki is speaking, anyone who has seen him before will know what an entertaining and knowlegable presenter he is. Whether you are currently implementing a BI solution or are just ‘BI curious’, a technical developer or a business manager, the day will provide an insight into the direction BI is heading in, and what we can expect in the years to come.
We hope to see you at either/both!
April 7, 2010
When developing SQL Server Reporting Services (SSRS) reports, BIDS caches the query results when you preview the report. This cache is then used next time you run a preview. This has the benefit of speeding up report development, but it does cause a problem when you want to test changing data.
A simple way of forcing the cache to refresh is to open the folder containing the .rdl report files, and delete the corresponding .rdl.data files. The next time you preview the report SSRS will be forced to requery the source.
To save time, I use the following macro to take care of it.
Press ALT+F8 to open the Macro Explorer, and add a new module called “RemoveRDLDataFiles” under MyMacros. Edit the file and add the following code to the file. (This is for SQL Server 2008, you may need to tweak the references for 2005).
Imports System Imports EnvDTE Imports EnvDTE80 Imports EnvDTE90 mports System.Diagnostics Imports System.IO Public Module RemoveRDLDataFiles Sub RemoveRDLDataFiles() Dim project As Project Dim Folder As String project = DTE.ActiveSolutionProjects(0) Dim fi As New FileInfo(project.FullName.ToString) Folder = fi.DirectoryName For Each FileFound As String In Directory.GetFiles(Folder, "*.rdl.data") File.Delete(FileFound) Next End Sub End Module
You can then run it by either double clicking on the macro, or assigning a keyboard shortcut to it (via Tools, Customize, Keyboard).
March 25, 2010
When browsing a cube using Excel 2007, you can drillthrough the measures to display up to 1000 rows of the transaction level source data.
I often get asked whether this limit of 1000 rows is configurable – well the good news is yes it is.
There is an option in the actions tab of the BIDS cube designer which allows you to specify the maximum rows, but helpfully this is ignored by Excel. Instead, you have to set it in Excel when you create a pivot.
Just click “Options” on the “PivotTable Tools” ribon, then in the “Change Data Source” dropdown click on “Connection Properties“. In this screen, just change the “Maximum number of records to retrieve” property.
March 17, 2010
Whilst designing a data warehouse for a banking client recently, I needed to calculate projected future loan payments (including breaking this down by interest and capital payments) for every customer throughout the life of the loan.
In Excel this is a pretty simple job, as Microsoft helpfully provide a number of functions to do just that (namely PMT, FV, IPMT and PPMT). In SQL Server however we do not have the luxury of having ready made functions, so I set about making my own.
Initially I coded them up as SQL Server functions, only to find that the internal rounding that SQL performs renders the results too inacurate to be usable. It was therefore necessary to write the functions in a C#, and wrap them up in a CLR library. SQL Server can then import them as scalar functions, to be used by any query that requires them.
To overcome this, the Purple Frog team have written a .Net CLR library which add four loan amortisation functions to SQL Server, which can be called from within a query as scalar functions, such as:
SELECT dbo.PMT(@APR/12.0, @Term, @LoanValue, 0, 0)
The functions provided are:
- PMT (The monthly payment of a loan)
- FV (The future value of a loan at a given month)
- IPMT (The interest portion of the monthly payment at a given month)
- PPMT (The capital portion of the monthly payment at a given month)
These are designed to mirror the parameters and results of the Excel functions, and have been written in C# using Visual Studio 2008, and tested against SQL Server 2008 Enterprise.
The functions perform surprisingly well; in tests I was able to calculate over 3 million monthly payments per minute, and that was on a relatively underpowered development server.
I must thank Kevin/MWVisa1 for writing a superb article explaining the finer points of the calculation process in his post here, on which the bulk of this code is derived.
You can download the C# code, or the pre-compiled binary from the Frog-Blog download section.
March 17, 2010
For anyone interested in SQL Server, the unmissable SQL Bits conference is returning for round six. “SQL Bits – The 6th Sets” is being held in central London for the first time, and unlike previous conferences it will be for one day only on Friday 16th April 2010.
It’s being held as part of the SQL Server 2008 R2 release, also happening that week in London.
Registration is now open, you can find out more at the SQL Bits website.
We highly recommend the event, hope to see you there…
I'm Alex Whittles.
I specialise in designing and implementing SQL Server business intelligence solutions,
and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.
