1 / 39

How to build your own… Super Model

How to build your own… Super Model. Dimensional Modelling for Analysis Services. Darren Gosbell Principal Consultant - James & Monroe http://geekswithblogs.net/darrengosbell. Agenda. Why build a Dimensional Model? What is a Dimensional Model? Overview of some modelling techniques.

crete
Download Presentation

How to build your own… Super Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. How to build your own…Super Model Dimensional Modelling for Analysis Services Darren GosbellPrincipal Consultant - James & Monroehttp://geekswithblogs.net/darrengosbell

  2. Agenda • Why build a Dimensional Model? • What is a Dimensional Model? • Overview of some modelling techniques. • What functionality does Analysis Services provide to help us?

  3. Further Reading • “The Data Warehouse Toolkit” by Ralph Kimball & Margy Ross • “The Data Warehouse Lifecycle Toolkit” by Ralph Kimball & Margy Ross

  4. Why Build a Dimensional Model

  5. What is a Dimensional Model? • A De-normalized database. • Designed for ease of querying, not for transactional updates. • Built to support aggregate queries • Modelled around business subject areas.

  6. Facts & Dimensions • There are two main types of objects in a dimensional model • Facts are quantitative measures that we wish to analyse and report on. • Dimensions contain textual descriptors of the business. They provide context for the facts.

  7. A Transactional Database

  8. A Dimensional Model

  9. Star Schema factSales dimProduct dimCustomer dimTime … … ProductID TimeID CustomerID SalesAmount ProductID ProductName SubCategoryName CategoryName

  10. Snowflake Schema factSales dimProduct dimSubCategory dimCategory SubcategoryID CategoryID Description CategoryID Description ProductID SubcategoryID Description ProductID TimeID CustomerID SalesAmount

  11. Building a Model - Facts • You have to talk to the “business”. • Identify Facts by looking for quantitative values that are reported. • Make sure the granularity is “right”.

  12. Building a Model - Dimensions • Identify Dimensions by listening for “by” words. • Look for related attributes that should be part of a single dimension. • Pay attention to how “Dimensions” change over time and in relation to each other.

  13. Slowly Changing Dimensions -Handling Changes over time

  14. If you don’t consider changes over time yourmodel will start out like this…

  15. … but ending up like this!

  16. Type 1 Slowly Changing Dimension • The simplest form • Only updates existing records • Overwrites history

  17. Type 1 Slowly Changing Dimension

  18. Type 2 Slowly Changing Dimension • Allows the recording of changes of state over time • Generates a new record each time the state changes • Usually requires the use of effective dates when joining to facts.

  19. Type 2 Slowly Changing Dimension 23/2/09

  20. Type 3 Slowly Changing Dimension • De-normalized change tracking • Only keeps a limited history • Stores changes in separate columns

  21. Type 3 Slowly Changing Dimension NSW VIC

  22. Relationships between facts and dimensions

  23. Regular Relationships • Most Common relationship • Works like an inner join between the fact and dimension

  24. Regular Relationships DEMO

  25. Many to Many Relationships • Allows for the situation where you want to associate more than one member from a dimension with a single fact.

  26. Scenario • Bank Account Transactions - each one has an Account - Accounts have one or more Customers - Each Customer has one or more Accounts

  27. Many-to-Many Relationships Demo

  28. Bank Accounts Albert Betty $3,020

  29. Bank Accounts • The relational schema

  30. Referenced Relationships • Joins a dimension to a fact table through another “intermediate” dimension

  31. Reference Relationships Demo

  32. Reference Relationships Geography SELECT {[Measures].[Amount]} ON Columns {[Geography].[City].&[1]} ON ROWS FROM [Balances] CityID CityName 1 Adelaide Customer CustomerID FullName CityID 100 Albert 1 101 Betty 1 TimeID CustomerID Amount 200801 100 $1000 200801 101 $2000

  33. Materialized Reference Relationships CityID CityName 1 Adelaide CustomerID FullName CityID 100 Albert 1 TimeID CustomerID Amount CityID 200801 100 $1000 1

  34. Fact Relationships • Used when a table plays both the role of a dimension and a fact. • Sometimes also known as a degenerate dimension.

  35. Fact Relationships DEMO

  36. No Relationship • Used for controlling calculations when you want to influence the context of the calculation without changing the context of the data.

  37. No Relationship DEMO

  38. Key Take Aways • Why to build a dimensional model. • What makes up a dimensional model. • How implement various modelling techniques in Analysis Services (2005 & 2008).

  39. Thank You Darren Gosbell http://geekswithblogs.net/darrengosbell

More Related