Describe the proposed approach to designing a star schema in the Dimensional Modelling stage of Kimball's Business Dimensional Lifecycle. Include the creation of a simple (maximum of four dimension tables and one fact table with maximum of six attributes per table) star schema to illustrate each step.

Question

Quizplus · Accepted Answer

The Answer is Dimensional Modeling Stage
This stage can create a dimensional model (DM) for a data warehouse or to 'dimensionalize' the relational schema of an OLTP database.
A high-level (simplified) version of the dimension model is first created, which progressively gains more detail.

The DM is created using a two phased approach -
Part I Create a high-level DM - a graphical representation of the dimension, fact and utility tables involved in representing the business process. The DM is created using a four-step process.
Part II Identify all dimension and fact attributes for the DM.
Part I Create a high-level DM (4 step process)
Step 1 Select Business Process
Enterprise-level business requirements should identify business process (subject area) to be modelled.

The first business process should:
o Deliver significant value to the organisation,
o Built in reasonable time,
o Establish data foundations for the enterprise view by creating reusable or conformed dimensions.

Step 2 Declare the Grain
Decide on level of detail or the grain needed for the selected business process. In other words, what a row of the fact table represents.
Choosing the level of grain is determined by finding a balance between meeting business requirements and what is possible given the data source. Recommendation is to build the DM using the lowest level of detail available.

Step 3 Choose the Dimensions
Determine the most useful dimensions that apply to each fact row and how to best represent each dimension.

Step 4 Identify the Facts
Identify numeric facts used to measure the business process. This step should also identify calculations derived from the facts, which are used to monitor the business.

Part II - Identify Dimension and Fact Attributes
This part involves identifying the attributes needed by the business to analyse the selected business process.
The usefulness of a DM is determined by the attributes associated with each dimension as this governs how the data will be viewed for analysis.

Describe the Proposed Approach to Designing a Star Schema in the Dimensional