Building a Dimensional Data Model For a Data Warehouse

Summary: A dimensional data model is often considered as a better option for data warehouse as compared to standard data model. This is because a dimensional model has a number of data warehouse advantages. In order to build and create a dimensional data model, a data modeler should follow a methodology that outlines the decisions he needs to make to complete the database design. The methodology first identifies the major processes in an organization where data is stored. A data modeler or a database designer should start this process with the existing sources of data that an organization uses. Once the sources are identified one or more tables are created from each business process. Let's have a look at the proper steps involved in this methodology:

●Choosing the business processes that an organization wants to use to analyze the subject area to be modeled.

●Determining the granularity of the fact tables.

●Identifying dimensions and hierarchies for each fact table.

●Identifying measures for the fact tables.

●Determining the attributes for each dimension table.

●Getting users to verify the data model.

Advantages of using a dimensional data model for data warehouse include:

1.The dimensional model has many standard types of joins and framework. All the dimensions can be thought of as symmetrically equal entry points into the fact table. The logical design can be done independent of expected query patterns. The user interfaces, the query strategies and the SQL generated against the dimensional model are all symmetrical. In other words, attributes cannot be found in fact tables and facts in dimension tables. If there is a non-fact field in the fact table, it can be assumed as a key to a dimension table.

2.The dimensional data model can be extended to accommodate new data elements and new design decisions. Its existing tables, both fact and dimension can be changed in place by adding new data rows in the table. Also the data is not required to reload. Moreover, no query tool or reporting tool needs to be reprogrammed in order to accommodate the change. Here are a few changes that could be made with dimensional modeling:

➢Adding new unanticipated facts if they are consistent with the existing fact table.

➢Adding completely new dimensions, as long as there is a single value of that dimension defined for each existing fact record.

➢Adding new, unanticipated dimensional attributes.

➢Breaking existing dimension records down to a lower level of granularity from a certain point in time forward.

3. With the dimensional model there is a set of standard approaches for handling common modeling situations, each having a set of alternatives that can be specifically programmed in report writers, query tools and other user interfaces. These modeling situations include:

➢Dimensional modeling provides specific techniques for handling slowly changing dimensions, depending on the business environment.

➢Heterogeneous products where a business needs to:

1. Track a number of different lines of business together within a single common set of attributes and facts at the same time and

2. Describe and measure the individual lines of business in highly idiosyncratic ways using incompatible measures.