Conceptual Modelling of Data Warehouse
Last Updated on by Karina Shakya
The conceptual data model is a structured business view of data required to support business, processes,record business events and track related performance measures. This model focuses on identifying data used in business but not its processing flow or physical characteristics. It is a concise description of the user’s data requirements without taking into account implementation details.
Conventional database are generally designed at conceptual level using some variation of well-known ER model although the UML is being increasingly used.
Conceptual Schema can be easily translated to relational model by applying set of mapping rules.
A data warehouse conceptual data model is nothing but highest level relationship between different entities in the data model.
Feature of Data Warehouse Conceptual Data Model
Following are the feature of conceptual data model:
- This is initial or high-level relation between different entities in the data model .Conceptual model includes the important entities and relationship among them.
- In the data warehouse conceptual data model we will not specify any attributes to the entities.
- We also have not define any primary key yet.
The figure below is an example of a conceptual data model.
From the figure above, we can see that the only information shown via the conceptual data model is the entities that describe the data and the relationships between those entities. No other information is shown through the conceptual data model.
Schema of Conceptual Data Model
Star Schema:
- Consists of a central fact table and a set of surrounding dimension tables on which the facts depend.
- a central fact table contains the keys to each dimensions.
- The fact table also contains the attributes, e.g., Rs. sold and units sold.
- Each dimension in a star schema is represented with only one dimension table.
- This dimension table contains the set of attributes.
- The following diagram shows the sales data of a company with respect to the four dimensions, namely time, item, branch, and location.
Snowflake Schema:
- Star schemas may be refined into snowflake schemas if we wish to provide support for dimension hierarchies by allowing the dimension tables to have sub-tables to represent the hierarchies.
- For example, the item dimension table in star schema is split into two dimension tables, namely item and supplier table as shown in figure below
Fact Constellation:
- Sophisticated applications may require multiple fact tables to share dimension tables.
- This kind of schema can be viewed as a collection of stars, and hence is called a galaxy schema or a fact constellation.
- The following illustration shows two fact tables, namely Sales and shipping. Time, item, and location dimension tables are shared between the sales and shipping fact table.
