Final answer:
To design a dimensional model, identify the fact tables and dimension tables for the business process in question. In a retail example, the 'Sales' fact table with units sold and total sales amount interfaces with dimensions like 'Time' and 'Product'. You then choose between a star or snowflake schema for organizing these tables.
Step-by-step explanation:
To design a dimensional model from an existing database schema, you first need to identify the fact tables and dimension tables. A fact table contains measurable, quantitative data about a business process, while dimension tables contain descriptive attributes related to the fact data.
For example, if you are designing a data warehouse for a retail company, a potential fact table could be 'Sales', capturing data such as units sold and total sales amount. The related dimensions could be 'Time', 'Product', 'Customer', and 'Store'.
After identifying the tables, you can opt for a star or snowflake schema. A star schema has a single fact table with multiple dimension tables directly connected to it. In contrast, a snowflake schema normalizes the dimension tables into multiple related tables, which can resemble a snowflake pattern. The choice between these two often depends on the trade-offs between performance and normalization.
The measures are the components of the fact tables that can be aggregated; they are typically numeric. Using the retail example, the measures within the 'Sales' fact table might be 'Total Sales', 'Quantity Sold', and 'Average Sale Price'.