Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

2

Dimensional Modeling

Dimensional modeling is the process you use to convert your existing OLTP data model to

a model that is more business-centric and easier for Business Intelligence tools to work with.

Tools like SSIS, Analysis Services, and the others you’ll learn about in this book are geared

specifically toward variations of this type of model. In this lesson you will learn what makes

a dimensional model different and then have the opportunity to convert a simple model

yourself.

As seen in Figure 2-1, the OLTP model is highly normalized. This is to enhance the quick

insertion and retrieval of data. The goal in designing a data warehouse or star schema is to

denormalize the model in order to simplify it and to provide wider, more straightforward

tables for joining and data-retrieval speed. This denormalization allows you to “model” the

database in a business-focused way that users can understand, and dramatically increases

performance of the types of analytical queries that we’re performing.

Why do you need to do this denormalization in order to report on your data, you may ask?

The largest reason is that you need to consolidate some of the redundancy between tables.

Consolidating redundancy will put the database into a star schema layout, which has a cen-

tral fact table surrounded by a layer of dimension tables, as shown in Figure 2-2.

As you can see in Figure 2-2, we have ...