how to build your own… super model
DESCRIPTION
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. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/1.jpg)
How to build your own…
Super ModelDimensional Modelling
for Analysis Services
Darren GosbellPrincipal Consultant - James & Monroe
http://geekswithblogs.net/darrengosbell
![Page 2: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/2.jpg)
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?
![Page 3: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/3.jpg)
Further Reading
• “The Data Warehouse Toolkit” by Ralph Kimball & Margy Ross
• “The Data Warehouse Lifecycle Toolkit” by Ralph Kimball & Margy Ross
![Page 4: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/4.jpg)
Why Build a Dimensional Model
OLTP System Dimensional ModelProcess Oriented Subject OrientedTransactional AggregateCurrent Historic
![Page 5: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/5.jpg)
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.
![Page 6: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/6.jpg)
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.
![Page 7: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/7.jpg)
A Transactional Database
OrderDetails
OrderHeaderID
ProductID
Amount
OrderHeader
OrderHeaderID
CustomerID
OrderDate
FreightAmount
Products
ProductID
Description
Size
Customers
CustomerID
AddressID
Name
Addresses
AddressID
StateID
Street
States
StateID
CountryID
Desc
Countries
CountryID
Description
![Page 8: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/8.jpg)
A Dimensional Model
FactSales
CustomerID
ProductID
TimeID
SalesAmountProducts
ProductID
Description
Size
Subcategory
Category
Customers
CustomerID
Name
Street
State
Country
Time
TimeID
Date
Month
Quarter
Year
![Page 9: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/9.jpg)
Star Schema
ProductIDTimeID
CustomerIDSalesAmount
factSales
ProductIDProductName
SubCategoryNameCategoryName
dimProduct
…
dimCustomer
…
dimTime
![Page 10: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/10.jpg)
Snowflake Schema
ProductIDTimeID
CustomerIDSalesAmount
factSalesProductID
SubcategoryIDDescription
dimProduct
SubcategoryIDCategoryIDDescription
dimSubCategory
CategoryIDDescription
dimCategory
![Page 11: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/11.jpg)
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”.
![Page 12: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/12.jpg)
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.
![Page 13: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/13.jpg)
Slowly Changing Dimensions -Handling Changes over time
![Page 14: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/14.jpg)
If you don’t consider changes over time yourmodel will start out like this…
![Page 15: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/15.jpg)
… but ending up like this!
![Page 16: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/16.jpg)
Type 1 Slowly Changing Dimension
• The simplest form• Only updates existing records• Overwrites history
![Page 17: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/17.jpg)
Type 1 Slowly Changing Dimension
CustomerID Code Name State Gender
1 K001 Miranda Kerr NSW F
CustomerID Code Name State Gender
1 K001 Miranda Kerr VIC F
![Page 18: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/18.jpg)
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.
![Page 19: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/19.jpg)
Type 2 Slowly Changing Dimension
CustomerID Code Name State Gender Start End
1 K001 Miranda Kerr NSW F 1/1/09 <NULL>23/2/09
CustomerID Code Name State Gender Start End
1 K001 Miranda Kerr NSW F 1/1/09 23/2/09
2 K001 Miranda Kerr VIC F 24/2/09 <NULL>
![Page 20: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/20.jpg)
Type 3 Slowly Changing Dimension
• De-normalized change tracking• Only keeps a limited history• Stores changes in separate columns
![Page 21: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/21.jpg)
Type 3 Slowly Changing Dimension
CustomerID Code Name Current State
Gender Prev State
1 K001 Miranda Kerr F <NULL>VICNSW
![Page 22: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/22.jpg)
Relationships between facts and dimensions
![Page 23: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/23.jpg)
Regular Relationships
• Most Common relationship• Works like an inner join between the
fact and dimension
![Page 24: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/24.jpg)
DEMORegular Relationships
![Page 25: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/25.jpg)
Many to Many Relationships
• Allows for the situation where you want to associate more than one member from a dimension with a single fact.
![Page 26: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/26.jpg)
Scenario
• Bank Account Transactions - each one has an Account - Accounts have one or more Customers - Each Customer has one or more Accounts
![Page 27: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/27.jpg)
DEMOMany-to-Many Relationships
![Page 28: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/28.jpg)
Person Account Amount
Albert #1 $1,010
Albert #2 $2,010
Betty #2 $2,010
TOTAL $5,030$3,020
Bank Accounts
Account #1$1,010
Account #2
$2,010
Albert
Betty
![Page 29: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/29.jpg)
Bank Accounts
• The relational schema
![Page 30: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/30.jpg)
Referenced Relationships
• Joins a dimension to a fact table through another “intermediate” dimension
![Page 31: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/31.jpg)
DEMOReference Relationships
![Page 32: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/32.jpg)
Reference Relationships
CustomerID FullName CityID100 Albert 1
SELECT {[Measures].[Amount]} ON Columns {[Geography].[City].&[1]} ON ROWSFROM [Balances]
TimeID200801
CustomerID100
Amount$1000
200801 101 $2000
CityID1
CityNameAdelaide
Geography
Customer
101 Betty 1
![Page 33: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/33.jpg)
Materialized Reference Relationships
CustomerID100
FullNameAlbert
CityID1
CityID1
CityNameAdelaide
TimeID200801
CustomerID100
Amount$1000
CityID1
![Page 34: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/34.jpg)
Fact Relationships
• Used when a table plays both the role of a dimension and a fact.
• Sometimes also known as a degenerate dimension.
![Page 35: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/35.jpg)
DEMOFact Relationships
![Page 36: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/36.jpg)
No Relationship
• Used for controlling calculations when you want to influence the context of the calculation without changing the context of the data.
![Page 37: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/37.jpg)
DEMONo Relationship
![Page 38: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/38.jpg)
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).
![Page 39: How to build your own… Super Model](https://reader038.vdocuments.us/reader038/viewer/2022102719/56815c94550346895dcaa2b7/html5/thumbnails/39.jpg)
Thank You
Darren Gosbellhttp://geekswithblogs.net/darrengosbell