1 / 30

Contents of this slideshow :

Contents of this slideshow :. What is a data warehouse? Multi-dimensional data modeling. A star shema datawarehouse has a central table ( the Fact table ) surrouded by dimension tables with on-to-many relationships towards the fact table. An example of a Datawarehouse:.

kieu
Download Presentation

Contents of this slideshow :

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. Contents of this slideshow: • What is a data warehouse? • Multi-dimensional data modeling

  2. A star shema datawarehouse has a central table (theFact table) surrouded by dimension tables with on-to-many relationships towards the fact table. An example of a Datawarehouse: The fixed data base structure implies that application programs (drilling functions/aggregates) can be generated automatically!

  3. A dimension hierarchy is a set of tables connected by one-to-many relationships towards the fact table: Dimension hierarchies: In a dimension hierarchiy it is possible to aggregate data from the fact table to the different levels of the hierachy. Drill-down= “de-aggregate” = break an aggregate into its constituents. Roll-up= aggregate along one or more dimensions.

  4. Two different types of drilling: • Drilling in dimension hierarchies. • Drilling between dimensions.

  5. Which star schemas or data marts can be build by using the illustrated integrated E-commerce/ERP data model?Which star schema would you recommend to be implemented first?

  6. Data marts = Kimball uses the word for any multidimensional database/star schema. A galaxy is a set of multidimensional databases with conformed (fælles tilpassede) dimensions: The value chain Suppose an entreprise has a datamart for Purchase and another datamart for Sale as illustrated above. Is it possible to calculate the revenue per month for the last year by using such a galaxy?

  7. Conformed dimensions = dimensions designed to be common for different data marts in order to make drill across operations possible.Conformed facts = measures with common units of measurement and granularities that make it possible to integrate measures from different fact tables. The value chain Is it possible to calculate the revenue per month for the last year if the datamart for Purchase and the datamart for Sale do not have conformed dimensions or facts?

  8. Contents of this slideshow: • What is a datawarehouse? • Multi-dimensional data modelling

  9. SELECT Product#, SUM(Qty*Price) AS omsætningFROM Orderdetails JOIN ProductsGROUP BY Product# Datawarehouse aggregating to the product level:

  10. SELECT Product#, Salesman#, SUM(Qty*Price) AS omsætningFROM Orderdetails JOIN Products JOIN Salesmen GROUP BY Product#, Salesman#; Drill down to the Product per Salesman level: Where should the Price be stored?

  11. A dimension hierarchi is a set of tables connected by one-to-many relationships towards the fact table: Dimension hierarchies: A Snowflake schema may in contrast to star schemas have dimension hierarchies. Describe advantage and disadvantage by using dimension hierarchies/Snowflake schema?

  12. A Snowflake schema may have branches in the dimension hierarchies: Snowflake schema with branches: Are Customers related to the regions?

  13. The aggregation level is the argument to the GROUP BY statement.

  14. Drilling in dimension hierarchies:

  15. Drilling between dimension hierarchies:

  16. Roll up can be executed by removing one or more argument to the GROUP BY statement. Roll up to the top level: Roll up to the product level. Roll up to the top level.

  17. Non-linear dimensions as e.g. the Date Dimension: • The granularity is day. • Many different hierarchies. • Two major problems: • Calender Week do not aggregate to year. • Type of Day distinguish between working day and holiday. However, they are idependent of the other dimensions (e.g. Easter). Fiscal Year Calendar Year Calendar Fiscal Quarter Quarter Calendar Fiscal Month Month Fiscal Week What aggregation level would you use to calculate the average sale in non-hollyday mondays per month? Calendar Type of Day Week Day of Week Day

  18. The time dimension: • The granularity is minute. • The top level is a hole day. Day Part AM/PM Flag Hour Why do you think Kimball recommends to separate the date and time dimensions? Minute

  19. Degenerated dimension = A dimension that is not created because nobody want to aggregate data to the degenerated level. Example: The Order dimension should be deleted while the Time and Customer attributes should be created as new dimensions to which it is meaningful to aggregate data.

  20. Exercise: The figure illustrates an ER-diagram of a car rental company like Hertz or Avis. Design a snowflake shema, star shema or Galaxy for the car rental company!

  21. Major problems in data warehouse design: Drilling in many-to-many relationships and tree structures. Inconsistensies caused by ”slowly changing dimensions”.

  22. 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? Slowly Changing Dimensions (SCD)

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

  24. Exercise: Is the region of the customer a dynamic attribute of the customer? Does it make sense to aggregate the rental revenue to the region of the customers?

  25. It is possible to cheat the application generator. That is, special very complicated data structures may function as many-to-many or networt relationships when they are dealt with as 1-to-many relationships.How would you recommend to design a datawarehouse where it is possible to aggregate Sale to the Stock locations used for the sale?

  26. Exercise.Design a datawarehouse for a travel agency.

  27. Design a data warehouse (or galaxy) for an ERP system with as many meaningful dimensions as possible:

  28. End of session Thank you !!!

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

  30. Where do the responses of SCDs store historic information? • Response 1 does not store historic information. • Response 2 store historic information in a new record version. • Response 3 store at one historic value in a new dimension attribute. • Response 4 store historic information in a new dimension relationship. • Response 5 store historic information in a new fact attribute. • Response 6 can sometimes deminish the aggregation error of response 1 as finer granularity in a state fact more acurately can be related to the right dimension record. • Response 7 store historic information in a new fact table.

More Related