Introduction to the Unified Dimensional Model (UDM)

I was talking to a colleague about Analysis Services and how awesome the Unified Dimensional Model (UDM) is. I then realized I have not blogged in ages about anything useful. So I have pulled together some of my thoughts about the UDM.

I was really introduced to the UDM on the Yukon Ascend program last year in Sydney. At the time I was still trying to find out what the UDM was in Analysis Services 2005 (AS2005). The simple answer is “The UDM is a Cube in AS2005”. Ok that may be glossing over the UDM a bit, but you will not find the UDM in AS 2005. There is no CREATE UDM function in AS2005. An AS2005 cube ties all the UDM features together.

The UDM, I mean cubes provide the ability for reports (OLAP, Reporting Services) to be generated against standard OLAP cubes as well as Relational data sources. Queries can be optimized to either run against aggregates from OLAP storage or directly against the relational data sources offering a high performance reporting environment. Reporting services has been extended too with a UI to create MDX queries (something that was sorely missing in SQL 2000).

The feature list for the UDM is huge. Data mining has been extended in AS2005 with seven algorithms. New KPI functionality has also been introduced in this release. The list goes on. I have put together some Useful links to UDM and Analysis Services 2005 resources.

Introduction to the UDM
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/IntrotoUDM.asp

Analysis Services 2005 Processing Architecture
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

Enabling Drillthrough in Analysis Services 2005
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp

Introduction to Data Mining
http://www.microsoft.com/technet/prodtechnol/sql/2005/intro2dm.mspx

SQL Server 2005 Reporting Services (SSRS)
http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx


I have started my research in earnest into how I am going to use the UDM on Business Intelligence projects. I hope this is a good start for any one looking at the UDM.

\Myles.

TechEd 2005 Auckland NZ Session slides

Hello,

My session slides have now been posted on to ww.microsoft.com

For my session on Analysis Services 2005 migration tips see:

http://download.microsoft.com/download/0/9/b/09bb6510-535a-4fe6-be02-8af8762b0acc/SQL312.ppt

For Ad-hoc reporting with report builder see:

http://download.microsoft.com/download/e/2/6/e2631eea-9a8d-484c-ba43-ac2e5be86396/BIN316.ppt

\Myles

TechED Photos

Hello All,

Mark who attended my morning session has sent me some pics of me getting ready for the migration to AS 2005.



Thanks Mark.

\Myles

What I got out of TechED

Wow TechED is over, three days of sessions and two presentations of my own. It was a full on event for NZ IT scene.

Besides presenting at TechED I did get to a couple of sessions. Looking back at them I took something from each session, listed below are my thoughts.

BIN301- Cube Design in Analysis Services 2005 - Best Practice for Performance and Functionality - Richard Lees.

This was the only session on AS 2005 that I was able to attend this year. Richard covered the UDM in detail and gave us a run of all the client tools for AS 2005. The best was the review of the proactive caching for loading cubes and the use notification services for automation of cube processing.

The UDM really does have a large amount of features around query performance. Something I am going to look into in more detail on this blog at some stage.

BIN310 - SQL Server 2005: End-to-End Part 3 (Analyze and Act) - Thierry D'Hers

It was great to get to meet Thierry. I spent a bit of time with him on is brief say here in NZ. His insight into the direction of the MS BI platform was very informative. Thierry’s session reviewed all the SQL 2005 features with a take on MS direction for the product range.

DBA313 - Architecting a Large Scale Data Warehouse with SQL Server 2005 - Doug Barrett

Doug took us threw some of the key DW features for creating a DW on SQL 2005. The session was more of a traditional approach to DW architecture. He cover table partitions and loading with SSIS packages.

I realy feel that any new BI projects on the MS platfrom will be based around the UDM.

Microsoft is going to post all TechED presentations on their NZ site latter this month. I will post a link to these sessions on blog.

Till next time

Myles

Fuzzy Lookups and Groupings Provide Powerful Data Cleansing Capabilities

Hello All,

I came across this neat MSDN Article on Data Cleaning using SSIS. It was raises some great points on how to use the new Fuzzy transformations.

Fuzzy Lookups and Groupings Provide Powerful Data Cleansing Capabilities

http://msdn.microsoft.com/sql/default.aspx?pull=/msdnmag/issues/05/09/SQLServer2005/default.aspx

\Myles

Beginners Guide to Business Intelligence: What is Business Intelligence About?

I have decided to do a beginners guide to BI on my blog. I thought this would be a great way to tie all the resources I use and give you a good idea for an approach to BI projects. I am starting off with a question that needs to be answered.


What is Business Intelligence About? Decisions of Course

I often find myself in situations where I have to try and explain Business Intelligence. It’s not that easy to define BI in a universal definition that everyone agrees with. BI is as much a collection of technologies and principles as anything else.

So here it goes,

‘Business Intelligence is about taking the guess work out of the decision making process.’

That’s right it’s not about technology or a wonderful new process. It’s about the management decision making process that occurs in every business. To demonstrate this in more detail lets review what the Management Decision Making Process (MDMP) is about.

The MDMP was introduced to me on my first data warehousing project by Peter Nolan the architect of the project. He took me through the concept of why you want to start a BI project in the first place. He posed a simple question, ‘How is Business?’

There are four primary drivers that make up a cycle that form the MDMP. Consider each factor as deliverables forming a BI platform. The diagram below shows four primary drivers for the MDMP.

Made an Investment – Every organization makes an investment either in a product or a service. The tracking of the investment is the first driver in the MDMP. For example a product stock keeping or a resource schedule report allows a compony to track its resources.

What Happened? – The second driver covers the result of the investment. Did the investment payoff, did we make a sale? For example a sales report allows business to track the success of the investments.

Why did it happen? – The third driver is to analyse the success of the investment. Organizations use OLAP analysis Ad-hoc reporting to drill down to the reasons for the performance.

What if? – The fourth and final driver and the most difficult to implement. Organizations are constantly looking for new opportunities to invest, questions like; what is the impact of further investment? What if we gave 10% further discount? Looking for new opportunities with data mining and forecasting techniques helps answer this question.

So how do you deliver MDMP?

When starting a BI project you must set firm goals around the end deliverables. A great place to start is taking a single business process and mapping to the MDMP. (Selecting a business process is an entirely different subject for a blog.)

Once a process has been selected a solid business case been derived using the MDMP. The four primary drivers of MDMP will allow you to set clear goals for delivery that the business can understand.

SQL Server 2005 vs. MDMP

Reviewing the SQL Server 2005 product against MDMP shows how much BI functionality Microsoft has release in the version. Below is a table showing the reporting functionality SQL Server 2005 has built into support the MDMP.

Conclusion

Basing your BI project on the MDMP you have a common point of view across the business and technology requirements are key to delivering a successful project. For further information on the MDMP check out the resources below.

Resources

Getting Started and Finishing Well By Peter Nolan
http://www.intelligententerprise.com/010507/print/webhouse.htm

Where is Peter?
http://www.peternolan.com/

Beginners Guide to Business Intelligence: What is a Data Warehouse?

Download SQL Server 2005 now!

Hello All,

Again I am completely snowed under with work so I have not written any blogs since Monday.

I am currently working on feature for this blog and I will have it out soon.

Now is a good time as any to get started with SQL Server 2005. Download the current BETA CTP from http://www.microsoft.com/sql/2005/productinfo/ctp.mspx

Good Luck

Myles

What I am going to see at TechEd 05

Hello All,

Well its 17 days out to New Zealand TechEd 05 and I can’t wait.

I know I have not blogged for a long while. This is due to work commitments and TechEd prep. I am still in the middle of a large Data warehouse project. It’s been running for the last 8 months and nearing completion.

There are a few TechEd sessions I am really looking forward to seeing. I thought it would be a good idea to share them with you. Below are the key sessions I don’t want to miss.

• BIN303 - Exploiting Data Mining in SQL Server 2005 - Richard Lees
• BIN304 - SQL Server Integration Services: Deploying, Managing and Loading a Data Warehouse - Tony Bain
• BIN310 - SQL Server 2005: End-to-End Part 3 (Analyze and Act) - Thierry D'Hers
• DBA313 - Architecting a Large Scale Data Warehouse with SQL Server 2005 - Doug Barrett

I haven’t listed the times here as the schedule may change for the latest TechEd schedule see: http://nigelp.members.winisp.net/schedule.htm

Bye for now.

Myles

TechEd 05 Preparation – Project Real

Hello All,

While I am doing prep for TechED 05, I came across a couple of really good public links to project Real. For those of us that are going to be converting a lot of SQL 2000 Clients to 2005 it's on the 'must read list'.

Check Out:

Project REAL: Technical Overview
http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx

Project REAL—Business Intelligence in Practice
http://www.microsoft.com/sql/bi/projectreal/default.mspx

Happy Reading

Myles

News Group Post: Slowly Changing Dimensions Type 2

Note: This is a response to a news group posting. Due to formatting problems I have reposted the response on my Blog. Don't worry I don’t intend this again.


Rico asks how I can implement type 2 changes in a dimension.

Hello Rico,

Type 2 is relatively easy to implement, but it can be a pain for users to understand and use.

Implementing Type 2

Generally I use a set of standard flags to track the changes in the dimension. Each flag is used to determine the state of the record. I am assuming that you are using Surrogate keys in your dimensions and Fact tables.

Date From – The date the record arrived in the dimension
Date To – The date the record is deemed to be changed
Current Flag – The State of the record Y or N

With these flags you can track changes of the dimension at the lowest level such as employee changes.
e.g.

Employee Dimension



You will have to come up with at change capture process. If you have type 1 deployed already it would be the same logic for identifying the changes, with the exception of updating old records and a creating new records in the dimension. Remember you will have to update your surrogate key lookup in you fact table build to load with the current dimension record. I.E. “WHERE Current Flag =’Y’ ”

Implementing Type 2 without Flags

To be honest I have not tried this method but it worth considering if your Business users have problems reporting using the Type 2 Flags. Rather than having one Employee Dimension create a series of mini dimensions against the fact table. This would allow you to track changes against the fact record instead of the Dimension.

E.G.

Employee Dim is broken into three new dimensions (Dim Manager, Dim Employee, and Dim Job Title) as shown in the diagrams below




There is a HUGE draw back to this you end up a large amount of dimensions and a really wide fact table if you have to include a lot of them.


Hope this gets you started.


Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/