Question

You are the data design specialist on the data warehouse project team for a retail company. Design a STAR schema to track the sales units and sales dollars with three-dimension tables. Explain how you will decide to select and build four two-way aggregates

Answer 1

Star Schema

*Star schema is the fundamental schema among the data mart schema and it is simplest.
*This schema is widely used to develop or build a data warehouse and dimensional data marts.
*It includes one or more fact tables indexing any number of dimensional tables.
*The star schema is a necessary cause of the snowflake schema. It is also efficient in handling basic queries.

STAR schema to track the sales units and sales dollars with three-dimension tables

*This diagram shows the sales data of a company with respect to the four dimensions, namely time, item, branch, and location.

*There is a fact table at the center. It contains the keys to each of the four dimensions.
*The fact table also contains the attributes, namely dollars sold and units sold.

Selecting and building four two-way aggregates

*Aggregates are to be considered for use when the number of detailed records to be processed is large and/or the processing of the customer queries begins to impact the performance of the BI application.
*Customers of BI applications expect an application that is very responsive to their queries. *Aggregates are the way to meet this implied requirement.

*When detailed records are needed the base fact table is available to answer those queries.
*The performance should still be good here because you should be limiting the number of records you are requesting and your indexing strategy should be supporting the use of the detailed fact star for these requests.