Data Warehousing Part 1—Topics

1. Data Warehousing Part 1—Topics • Doing vs. Deciding—OLTP vs. OLAP • Data Warehouses • Fact tables, Dimension tables, Granularity • DW in an integrated Business Intelligence system • Design Steps • Designing Fact Tables • Designing Dimension Tables • The Time dimension • Fact Table Exercises

2. "With uncertainty present…" With the introduction of uncertainty—the fact of ignorance and necessity of acting upon opinion rather than knowledge—into this Eden-like situation, its character is completely changed. With uncertainty absent, man's energies are devoted altogether to doing things; it is doubtful whether intelligence itself would exist in such a situation; in a world so built that perfect knowledge was theoretically possible, it seems likely that all organic readjustments would become mechanical, all organisms automata. With uncertainty present, doing things, the actual execution of activity, becomes in a real sense a secondary part of life; the primary problem or function is deciding what to do and how to do it. The two most important characteristics of social organization brought about by the fact of uncertainty have already been noticed. In the first place, goods are produced for a market, on the basis of an entirely impersonal prediction of wants, not for the satisfaction of the wants of the producers themselves. The producer takes the responsibility of forecasting the consumers' wants. In the second place, the work of forecasting and at the same time a large part of the technological direction and control of production are still further concentrated upon a very narrow class of the producers, and we meet with a new economic functionary, the entrepreneur. Frank H. KnightUniversity of Chicago 1921

3. Doing vs. Deciding • Organizations do many things • List thirty transactions that your project organization executes or does • Managers decide things • List thirty decisions that your project organization makes • Identify where in the organizational hierarchy the decision lies • What is the consequence/importance of the decision? • What information influences each decision?

4. Doing vs. Deciding / OLTP vs OLAP • Are systems designed to support the execution of events suitable for the making of decisions? • Event/transaction support requires • High throughput • High reliability • Accuracy • DB structures tuned for storage & performance • Online Transaction Processing (OLTP) systems support events • Provide data or information to support transactions • Record acts → New data

5. OLTP vs. OLAP—Let Me Count the Ways… • Online Analytical Processing (OLAP) or Business Intelligence (BI) systems are oriented at decision making and analysis • What are the problems with using our OLTP databases to support managerial decision making? ?

6. Managers unlikely to understand where the data they need is in the database Managers not skilled at reassembling the component data of a concept recorded in multiple tables Managers need summarized and aggregated data more than they need details Different summaries needed on the same data Retrieval slows down the OLTP systems Historical and current data likely to be useful together Data from multiple systems is likely to be useful External data is equally likely to be useful Economic conditions Competitor actions Demographic information And the granddaddy of all: What-if analysis requires messing with the data Still Counting…

7. The Data Warehouse • The DW is a separate storage structure • Designed to optimize query execution • Not storage efficiency • Not transaction throughput • Expected to be loaded during down times • Supports "readability" • May sacrifice details for summaries • Data and structures anticipate user needs • Recurring decisions • Flexible exploration

8. Steps and Components • Source Systems—provide raw data to the DW • Integration Services—Provide transformation and loading services from source data to DW • Data Warehouse—Customized data store for Business Intelligence • Analysis Services—Tools for data mining and reporting • Reporting Services—Our old friend acting on an enhanced data store

9. Storage Strategies • The DW stores transformed data that • May be accessed directly to support analysis • Supports actions of the Analysis Services to provide enhanced and efficient analysis • Multiple Strategies • We will look at the widely used approach using • Fact tables, • Dimension tables, • Arranged in a Star Schema or Snowflake Schema(or both)

10. Fact Tables Contain Facts (duhhhh) of Interest • No PK designated for fact table • Natural PK is TimeKeyOrdered, ProductKey, CustomerKey • This defines the granularity of the data • CategoryKey FD on ProductKey • SalesTerrKey, SalesRepKey FD on CustomerKey • UnitsSold, TotalDiscounts • Summed from source data • Additive • SalesPrice is not additive • ValueSold is derivable and additive

11. Star Schema & Dimension Tables • Dimension Tables represent concepts (entities) used to group data in the fact tables • Also contain descriptive attributes of the entity represented by the dimension table • Simplest way for nontechnical users to picture the data • Relate to FKs in the fact tables

12. Snowflake Schema & Dimension Tables • Fewer direct links from dimension tables to fact table • Dimension tables relate to each other • Natural hierarchical relationships in data are preserved • Implications for drilldown reports • Increases complexity of data retrieval for nontechnical users

13. Granularity • The granularity of the fact tables is critical • There are alternative levels of granularity • Finer granularity → more detail, more recordsUse SalesDate instead of Month • Coaser granularity → less detail, fewer recordsUse SalesMonth instead of SalesDate • Finer granularity can be aggregated in the DW to find the coarser granularity values • Coarse granularity cannot be decomposed • Granularity decisions are made for each of the FKs from the dimension tables

14. Fact Tables (Part 2) • Identifying Fact Tables and their facts is an art • No obvious mapping from OLTP tables to Fact or Dimension Tables • The same DB table can contribute to multiple fact tables • Requires analysis to discover central concepts that will become fact tables • Decision maker interviews • Reporting requirements

15. Fact Tables (Part 2—cont.) • Look for a logical concept or event which measures of interest are about • A sale (invoice) • An order (purchase order) • An enrollment (college DB) • The concept/event should support the requirements • The event is likely to be based on an OLTP table • Not every OLTP table will become a fact table • This concept/event will form the foundation for a fact table

16. Fact Tables--Measures • Measures are the facts to be recorded for each row in the fact table • Measures are often additive • UnitsSold, TotalDiscounts, ValueSold • Some are not additive • SalesPrice • Sometimes nonadditive measures are transformed into additive measures • ValueSold = (UnitsSold * SalesPrice) - TotalDiscounts

17. Fact Tables—Measures (cont.) • Measures may come from several sources—often not just values from a single OLTP source table • Other candidates in our example • COGS • CurrentInterestRate – CompetitorPrice • GrossMargin – NetMargin • ShippingCost – ShippingWeight

18. Fact Tables--Dimensions • Dimensions are ways of looking at the data • Users may indicate they look at {fact table subject}"by" {dimension name} • Sales by week • Sales by customer • Sales by product category • Dimensions lead us to Dimension Tables • Descriptive attributes about the dimension • Foreign key to the fact table

19. Dimension Tables • Dimension tables are often basedon an OLTP entity • Denormalized to include descriptiveattributes from other tables • Product might include • SupplierName • CategoryName • SubCategoryName • SupplierCountry • In Snowflake dimension tables related hierarchical information may be retained in the hierarchical tables

20. Dimension Tables—Primary Keys • Dimension tables should alwaysbe given an artificial identity PK—even if there is a suitable OLTP table PK • If tables are ever loaded from multiple sources the natural PK may become invalid • E.g., merging sales data from two business units with different databases • Retain the business PK as an attribute in the dimension table • Possibly include source system identifier for the row

21. Dimension Tables—Time • Time is a hugely common"by" dimension • Decide on time granularity • Daily, Weekly, Hourly? • You might consider two timedimensions • Daily for grossest categorization • Hour for additional precision

22. Dimensions—Time (cont.) • The time dimension tablemaps from the measured timeattribute associated with thefact table record to variouslabels and aggregationsassociated with that value • Facilitates summarizing byvarious aggregates with asingle time dimension measure • TimeKey PK is often a datetime data type to the date level of precision

23. Fact Tables--Granularity • In the olden days granularity decisions were made at the DW DB design stage • Granularity decisions traded off • Number of records and computational overhead associated with more detailed granularity • Lack of precision with coarser granularity • Modern computational power supports finer granularity • Analysis services provides support for fast computation over large data sets • Just don't go overboard

24. Fact Table Exercise #2 • Expand entities around the core of our University ERD • See next slide • Consider two business goals • Understand real credit hour revenue • Understand classroom utilization • Identify and design Fact and Dimension Tables

25. Fact Table Exercise #2 (Cont.)

26. External Data • What external data might you want to have in a sales-oriented DW?

27. Next Time • Transformations to load the DW from the source OLTP (and other) data sources • Automated support • Do it yourself • Analysis Services—putting our DW to work