1 / 37

Book of Lars Frank, Chapter 10, SCD (Slowly Changing Dimensions) :

Book of Lars Frank, Chapter 10, SCD (Slowly Changing Dimensions) :. The hidden slides of this slideshow may be important. However, I will focus on leaning by exercises and therefore, rattling off new concepts are often done in hidden slides.

kim-park
Download Presentation

Book of Lars Frank, Chapter 10, SCD (Slowly Changing Dimensions) :

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. Book of Lars Frank, Chapter 10, SCD (Slowly Changing Dimensions): The hidden slides of this slideshow may be important. However, I will focus on leaning by exercises and therefore, rattling off new concepts are often done in hidden slides.

  2. If the attributes of a dimension is dynamic (e.i. they may be updated) we say that they are slowly changing. May the Branch-size of a Branch-office change after e.g. a renovation?May the Branch-name of a Branch-office change? Introduction to Slowly Changing Dimensions (SCD)

  3. Soppose the attribute Branch-size is dynamic and aggregations is made to the level (Branch-size, Year) or (Branch-size, Month) . Does this aggregation make sense and how would you solve possible problems? Exercise in SCD:

  4. Soppose the attribute Branch-name is dynamic and aggregations is made to the level (Branch-name, Year). Does this aggregation make sense and how would you solve possible problems? Exercise in SCD:

  5. Problems with slowly changing dimensions: • If you do not update a dynamic attribute the datawarehouse is stale. • If you update a dynamic attribute the old measures may be aggregated to a wrong attribute level value as e.g. the Branch office size! Which dimension attributes and relationships may be slowly changing and which of these give aggregation problems?

  6. Table 1[S1] [S1]Prøv evt. At få tabelnavnet op… Denne side er helt blank

  7. Owerwrite the old value: Kimball’s type 1 response:

  8. Response 1 used with dimension attribute change: Bran-ID … Quantity … Bran-ID … Br-Name … 001 2000 001 Centre Bran-ID … Quantity … ButikID … Br-Name … 001 2000 001 West Bran-ID … Quantity … ButikID … Br-Name … 001 2000 001 West 001 3500 Sales fact table Branch office dimension

  9. In response 2 you create a new version of the changed record: Sales fact table Branch office dimension How is it possible to aggregate to the fhysical Branch office level?

  10. Soppose the attribute Branch-name and Branch-size use response type 1 and 2, respectively and are changed at the same time. How is it in this situation possible not to preserve the historic Branch-name information as the this gives wrong name level aggregations? Exercise in SCD:

  11. Exercise: What SCD responces will you recommend for the datawarehouses designed in the car rentel case of slideshow 1.

  12. Kimball’s 3 responces toslowly changing dimensions : • Owerwrite the old value. • Create a new dimension record with the new value. • Create an extra attribute for the changed dimension value.

  13. Create an extra attribute for the changed dimension relationship. Kimball’s type 3 response: Suppose the product group of a product may be changed.Does this solution make meaningful aggregations to the two group levels?

  14. In response 3, you create a new version attribute: Order-line fact table Branch office dimension Does this solution make meaningful aggregations to the two Size levels?

  15. Response 3 should only be used for a new grouping criteria: Order-line fact table Product dimension What is the difference between the Grouping update and the previous Branch size update as the Grouping aggregations functions well while the Branch-size aggregations does not give any meening?

  16. Suppose the product group of a product may be changed. How would you implement SCD response 2 in this example? Will SCD response 2 make meaningful aggregations if you want to compare product group sale over time?Will SCD response 3 make meaningful aggregations?

  17. Exercise in when to preserve historic information. Exchange the Product dimension with a Branch office dimension and the Productgroup dimension with a Branch-Size dimension in the following example! Notice!It may be both attribute and business dependent whether you want to preserve historic information or not. Will SCD response 2 make meaningful aggregations if you want to compare the sale of the Branch-Size over time?Will SCD response 3 make meaningful aggregations?

  18. Suppose the product group of a product may be updated. Will the response type 1 give correct aggregations to the group level if you want to compare product group sale over time?

  19. Suppose the product group of a product may be changed. Will the solution below give correct aggregations to the group level if you want to compare product group sale over time?

  20. SCD Type 4 may be used in dynamic dimension hierarchies: Suppose both salary group and product group are dynamic. Does this make SCD problems?

  21. The Type 4 Responce:Dynamic relationships in a dimension hierarchy may be related directly to the fact table

  22. SCD Type 5 store dynamic attributes in the fact table:

  23. Use fine granularity: SCD Type 6 Responce:

  24. The Type 7 Response: Store the Dynamic Dimension Data as Static Facts in another Mart. Example Let us suppose a fact table stores the sale of products in a department store. In this example the department records may have an attribute with the number of salesmen as well as well as an attribute with the monthly costs of the departments. These attributes are dynamic! Which response type would you recommend?

  25. Exercise:Select responses to SCD for theAirline DW.

  26. Exercise:Select responses to SCD for the Hotel DW.

  27. Exercise. Select responses to SCD for the travel agency.

  28. Exercise.Design a datawarehouse for a promotion company. How is it possible to measure the results of promotions and where should these measures be stored in the data warehouse?

  29. Design a DW for a commercial TV channel Exercise:

  30. Make some requerements for a HRM system and try to group them in OLTP and OLAP requerements. Make an ER diagram for an OLTP database and one or more OLAP datamarts that can fulfill the requerements. HRM exercise:

  31. Design a datawarehouse for a bank: It should be possible to analyze both costs and revenye for customers, households, branch offices, regions, account managers etc.

  32. Exercise: Design a datawarehouse for a housing association that let out flats, shops and office areas. It is possible to sign up on vaiting lists for these.

  33. Exercise: Design et datawarehouse for DSB in order to deminish train delays.

  34. Exercise: Design a datawarehouse for stock exchange dealers in a bank.

  35. Suppose an account shifts Branch relationship in the middle of the month. Will the aggregations be correct and how will you solve possible problems? Kimball’s type 2 response: Can you find more solutions?

  36. Suppose both the Branch relationship and the Branch-size are dynamic.How can aggregations be correct? Kimball’s type 2 response:

  37. End of session Thank you !!!

More Related