Business Intelligence Process Grain of the Fact Table Dr. Chang Liu

1. Business Intelligence ProcessGrain of the Fact TableDr. Chang Liu

2. Business Intelligence Process Analysis Services Multidimensional Cube Data Operational Data Sources (Normalized) Staging Database Data Warehouse (Denormalized) Client Distribution Data Data Mart Data Mart * Cubes are normally created as part of a Business Intelligence process.

3. Business Intelligence Process Demo Create a dimensional model for a BI application Use Excel as a front end tool to analyze data

4. BI Benefits in Modern Organizations • Improvement of Operational Performance • Improvement in Customer Service • Identification of New Opportunities

5. BI Obstacles/Challenges • BI requires large initial investment • BI requires substantial ongoing costs • BI return-on-investment is difficult to justify • Organizations lack of preparation for BI: • Business events are not consistently defined throughout the enterprise • BI Tools may be difficult to use for certain users

6. BI for Competitive Advantages? • IT and Business together must tackle their data issues by answering the following questions: • Data Relevance – what data is needed to complete on analytics? • Data Sourcing – where can this data be obtained? • Data Quantity – How much data is needed? • Data Quality – How can the data be made more accurate and valuable for analysis? • Data Governance – What rules and processes are needed to manage data from its creation through its retirement?

7. Reading Assignment Predicts 2014: Business Intelligence and Analytics will remain CIO’s Top Technology Priority Analytics 3.0

8. BI Tools Organizational BI Team BI Personal BI

9. PowerPivot for EXCEL(Personal BI)

10. Example: Sales Data in DB

11. The Star Schema Years Year _ID Year Customers Customer _ID Customer_Name Sales Customer_ID Salesman_ID Year_ID Month_ID Day_ID Amount Months Month _ID Month SalesMen Salesman _ID Salesman_Name DayOfWeek Day _ID Day

12. The Star Schema (2) Periods Period_ID Date Customers Customer _ID Customer_Name Sales Customer_ID Salesman_ID Period_ID Amount SalesMen Salesman _ID Salesman_Name

13. What is a Cube? A cube can be thought of as a multidimensional pivot or crosstab. It stores numeric values for all combinations of values of the business dimensions.

14. Grain of the Fact Table • Granularity of Fact Table–what level of detail do you want? • Finer grains  better market basket analysis capability • Finer grain  more dimension tables, more rows in fact table • In Web-based commerce, finest granularity is a click

15. Star schema example Fact table provides statistics for sales broken down by product, period and store dimensions

16. Size of Fact Table • Depends on the number of dimensions and the grain of the fact table • Number of rows = product of number of possible values for each dimension associated with the fact table • Example: assume the following for Figure 1: • Total rows calculated as follows (assuming only half the products record sales for a given month):

17. Size of Fact Table (2) • The size of the fact table is many times larger than the dimension tables! • Estimate the size (in bytes) of the fact table: • Each of the above 6 fields average about 4 bytes in length • Total Size = ? • The size of the fact table depends on the number of the dimensions and the grain of the fact table. • Suppose we’d like to request the daily totals be accumulated in the fact table (assuming 20% of all products record sales on a given day) • Number of rows in the fact table? • Total Size = ?

18. Advantages of a Star Schema • The star schema is a denormalized schema • The star schema has several benefits: • Simplified the database structure • Easy to query because there is only one level of joins • Queries run much faster compared to the normalized structure • Easy to maintain • Modeled around business entities

19. Class Exercise – Size of a Fact Table

20. PowerPivotSAP Business Object Explorer Exercises