Fact table design for data ware house

Fact table design for data ware house

1. Software/Web/Mobile/Database Architect, Engineer, and Developer Toronto, Canada http://sayed.justetc.net http://www.justetc.net Sayed Ahmed Data Warehouse Fact Table Design Logical Design of a Data Warehouse

2. This is a video just for the sake of a video I will not explain anything Just will walk through on some presentation slides on Data Warehouse

3. Our Services Free Training and Educational Services Training and Education in Bangla :  Bangla.SaLearningSchool.com Training and Education in English: www.SaLearningSchool.com English.SaLearningSchool.com http://sitestree.com Ask a question and get answers:  Ask.JustEtc.net

4. Fact Table Design Fact Table Design Topics Define fact table column types Understand the additivity of a measure i.e. for measure column type Handle many-to-many relationships in a Star schema

5. Fact Table Column Types Fact Table Column Types Foreign keys Measures Lineage columns (optional) Business key columns from the primary source table (optional ) Surrogate keys

6. Fact Table Columns - Measure Measure Column Type Measure columns help with measurements useful for a specific business process Measures columns are usually numeric And can be aggregated Measure columns store values that are of interest to businesses such as sales amount, order quantity, and discount amount

7. Measures Fact tables - Measures Collection of measurements on a specific aspects of business Measure columns sales amount, order quantity, and discount amount.

8. Fact Table Columns – Foreign Key Foreign Key – Column Type These are the columns as coming from Dimension Tables Are the primary keys of Dimension Tables

9. Fact Table Columns – Surrogate Keys Surrogate Key Usually will comes from the primary dimension table (primary for the current fact table) Usually one or two columns in a fact table are surrogate keys

10. Surrogate keys for fact tables   OrderId  and  LineItemId are the surrogate keys as coming from the primary Source Order details table OrderId  and  LineItemId  columns will help For quick comparisons with source data Surrogate keys are not a must in fact tables; however, they help Must read: http://www.kimballgroup.com/2006/07/design-tip-81-fact-table-surrogate-key/

11. Lineage columns in fact tables Lineage columns Just as with dimension tables, these are strictly for auditing purposes References: https://upsearch.com/implementing-a-data-warehouse-fact-tables /

12. Additivity of Measures The primary purpose of Data warehouse is reporting, and forecasting Many times reports are aggregations such as sum Example: sales by quarter, by region, by product type Many reports are usually aggregations Hence, fact tables will have some columns to assist with that measures and aggregations for reporting These are the measures columns as we discussed before The measures that you add will help in how you want to do the measures and reporting

13. Types of Additivity of measures Types of Additivity of Measures additive measures Semi-additive measures   non-additive measures  

14. Additive, Semi-Additive Measures Additive If a measure can be summed across all dimensions Semi-additive Sometimes, we can sum a measure across all dimensions except for time such as account balance We can’t sum the account balance across the time dimension   We would need to do something like take the average instead or simply use the last value   Measures like this are called  semi-additive measures

15. Non-additive measures Some measures can’t ever be summed These are called non-additive measures Such as discount percentages and prices These are the non-additive measures

16. Additivity of measures in SSAS SSAS has support for semi-additive and non-additive measures  The SSAS database model Business Intelligence Semantic Model (BISM) . BISM includes way more additional metadata than RDBMS SSAS has two types of storage dimensional  and  tabular

17. SSAS and additivity of measures Tabular storage works through tables like a data warehouse does quicker to develop The dimensional model more properly represents a cube T he dimensional model includes even more metadata than the tabular model

18. SSAS and additivity of measures In BISM dimensional processing SSAS offers semi-additive aggregate functions out of the box For example, SSAS offers the LastNonEmpty aggregate function which properly uses the SUM aggregate function across all dimensions but time and defines the last known value as the aggregate over time

19. SSAS and additivity of measures In the BISM tabular model you use the Data Analysis Expression (DAX) language The DAX language includes functions that let you build semi-additive expressions quite quickly as well

20. Many to many relationships in data warehouse RDBMS Many to Many Relationship

21. Many to many in RDBMS and DW RDBMS Model as above image is ok, Because Every internet sales will have multiple reasons Every reasons will have multiple internet sales However, data warehouse has issues with such modeling because when using tools to build reporting the fact table is expected to be on the many side In the above picture, fact table is on the one side In SSAS reporting, many times, we use tools that build MDX queries that expect the schema to be perfectly in star or snow-flake schema

22. How can we resolve ... In SSAS with a BISM dimensional model you can solve the problem by creating an intermediate dimension between both fact tables Picture as below

23. Many to Many Relationship

24. DW Many to Many You create it from the primary key of the FactInternetSales   table DimFactInternetSales . put it on the “one” side of the relationships with both fact tables Now, both fact tables are always on the “many” side of any relationship However , the relationship between the FactInternetSales  and the new  DimFactInternetSales  dimension is de facto one to one

25. Thank You ? http://ask.justetc.net

26. Our Services Free Training and Educational Services Training and Education in Bangla :  Bangla.SaLearningSchool.com Training and Education in English: www.SaLearningSchool.com English.SaLearningSchool.com http://sitestree.com Ask a question and get answers:  Ask.JustEtc.net