Business Intelligence Process Grain of the Fact Table

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

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

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

BI Benefits in Modern Organizations n Improvement of Operational Performance n Improvement in Customer Service n Identification of New Opportunities

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

BI for Competitive Advantages? n 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?

Reading Assignment n n Predicts 2014: Business Intelligence and Analytics will remain CIO’s Top Technology Priority Analytics 3. 0

BI Tools Personal BI Team BI Organizational BI

Power. Pivot for EXCEL (Personal BI)

Example: Sales Data in DB

The Star Schema Years Customers Year _ID Year Sales Month _ID Month Customer_ID Salesman_ID Year_ID Month_ID Day_ID Amount Customer _ID Customer_Name Sales. Men Salesman _ID Salesman_Name Day. Of. Week Day _ID Day

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

What is a Cube? n n 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.

Grain of the Fact Table n 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

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

Size of Fact Table n n 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 n Example: assume the following for Figure 1: n Total rows calculated as follows (assuming only half the products record sales for a given month):

Size of Fact Table (2) n n 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 = ? n 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 = ?

Advantages of a Star Schema n n 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

Class Exercise – Size of a Fact Table

Power. Pivot SAP Business Object Explorer Exercises