The pizza was hot and the beer was cold….which is better than the other way around!
There was mention of SQL Server 2005 Service Pack 2 being released this week with some extra in adding data mining and profile via excel!
Apart from the long waited fix for Reporting Services, this should be a welcome update.
Warren Thornthwaite (co-author The Microsoft Data Warehouse Toolkit)
spoke about Dimension Modelling and gave us some good tips
and a very quick walkthrough what is normally a 4 day course.
He suggested for those into Business Intelligence/Data Warehousing to get on their Design Tips email list.
Go to the Kimball Group Web site http://www.kimballgroup.com/
His emphasis was good and the approach I like…
He said that most of the Data Warehousing and Business Intelligence effort should be based on finding out what the users do.
Not necessarily asking what they want as they most often don’t know!
Warren also did not believe in doing Business Intelligence demos, as the users will only see a small picture of what is possible.
Data Warehousing and Business Intelligence can greatly improve a company’s reporting which in turn affects their bottom line. This is hard to demo!
Basic approach is to de-normalise dimensions for usability and normalise facts for performance.
Modelling is best at the lowest level of detail then everything is more flexible.
Dimensions come from asking who,what,when,where,why and how. E.g. Product, Date, Client..
He normally uses a star schema.
Biggest bottleneck can be standard naming across an organisation.
e.g. Area versus region. But if this are not the same, then there is a case of having both fields available.
The problem comes when different divisions talk about their report by area verus the other divisions report by region!
Or maybe the same name is used for different reasons. E.g., Region there might have to be a Sales- Region versus a Marketing-Region.
As he stated, very few problems are technical, most are political.
Warren said he has seen Data Warehousing/Business Intelligence work on every platform and also fail on every platform.
His formula is to deliver value to the business as quick as possible rather than do the “easy” things first…
He normally draws up a Bus Matrix (Business Matrix) and mentioned a spreadsheet tool in his book, which they use to document and even start create the Data Warehouse/ Dimensions!
This matrix has processes/value chain items down the left and the dimensions across the top as multiple divisions will want to access the same dimensions.
The one tricky item but most important is slowly changing dimensions and how there are catered for.
Whenever someone did not cater for this initially, it was a nightmare to incorporate, so he always does these at the beginning.
E.g. A customer changes state so the previous sales figures per state will change unless this is catered for…
There were several approaches including do nothing, create a new dimension when a attribute changes etc.
He discouraged snowflaking and normalising the model and that with SQL 2005 the case for snowflaking is no longer there.
Other fancy terms were thrown around such as Junk dimensions for putting all those little lookups into one dimension – but be careful how many are added!
Also many to many dimensions – bridging tables or in SQL termes – intermediate fact tables.
A lot of value in Data Warehousing/Business Intelligence is the ETL Process – (Extraction, Transformation and Loading Process)
In addition, initially you mirror the Relational Model in Analysis services.
All in all his book sounds compelling although still have to finish and build all the projects in Practical Business Intelligence with SQL 2005 which is a great book to get you up to speed on Data Warehousing/Business Intelligence. It included the same concept of surrogate keys as Warren suggested….
Regards,
Tom Bizannes
Databases and Business Intelligence
http://www.smartbiz.com.au
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment