January 10, 2011

Analysis Services 101

Here is intro course to BI Analytics...

1. Facts(Measures) is a summable numeric value
2. Only events tracking in Fact table known as fasctless fact tables, in that case add column with value 1 to track them.
3. Grain is the lowest possible level of information in fact, make it as lowest as possible. Atomic level.
4. Facts are verb in Business process where as Dimesnions are nouns.
5. Dimension shared across more than 1 business process is known as Conformed Dimension
6. Surrogate keys (Meaningless , non-natural, artificial, ) is int value associayrf with each rown in dimesnion
7. Dimensions with Changeable attribute is called Slowly Changing Dimesnions (SCDs)
Type 1 SCDs overwrite old value
Type 2 SCDs have new row for every new change
Type 3 SCDs provide option of columns (not commonly followed and not scalable)
8. Data Dimension classic example for role Playing Dimension... better to have surrogate keys in year-month0date format since 20050221(would help for partitioning)
9.Dimension that doesn't have attributes are known as Degenerated Dimensions (e.g TransactionID)
10.Discourage Snowflake schema, Analysis 2000 has little inclination towards it but not 2005
11. Bridge table required when we have many-many dimensions, called Intermediate Fact Tables in Analysis Services 2005
12. Fact Table types, Periodic Snapshot, transaction and accumulating snapshot

No comments: