Data Modelling - How to ?

The Data Modelling is the most important activity in any BI project. A well planned Data Model helps to analyze the business performance in many perspectives.

The Data Modelling procedure stands the same for building Transactional (OLTP) and Analysis (OLAP) Database systems.

The Data Modelling for a Transactional databases systems are termed as Relational Data Modelling and for Analysis database systems as Dimensional Modelling.

It becomes important to understand that we need to add additional information in a Dimensional Model to accommodate aggregated relational data (also called as Facts) and group description data (also called as Attributes)

The relational data model is well normalized for effective read and write operations and designed in the 3rd Normal Form.

The dimensional data model is de-normalized for quick read only operations.

Both Normalized or De normalized  database systems require an effective Data Model for future maintenance and enhancements.

The following are the steps involved in an effective Data Model Process which is carried out sequentially.

1. Data Modelling Overview

2. Elements used in Logical Data Models

3. Physical Elements of Data Models

4. Normalizing a Data Model

5. Requirements Gathering

6. Interpreting Requirements

7. Creating a Logical Model

8. Common Data Modelling Problems

9. Creating the Physical Model in a Database System.

10. Indexing Considerations

11. Creating an Abstraction Layer in Database System.

Setting up Adventureworks[AW] Datawarehouse[DW] Database as a Project Source for Microstrategy Projects.

The next step is to import the AW-DW Database into Microstrategy.

Pre-requisite 

1. Microsoft SQL Server 2008 R2 is installed
2. Microstrategy is installed and configured
3. Have Admin privileges on SQL Server and Microstrategy Boxes.

Installing Microsoft Adventureworks datawarehouse database.

Step 1: - Download Microsoft AdventureworksDW2008R2_Data.mdf file from 
below link

http://msftdbprodsamples.codeplex.com/downloads/get/363848

Step 2:- Login to MS SQL Server with Admin privileges and attach the mdf file
without the log file by placing the downloaded mdf file in the location path
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
and run this below script in the SQL Server and Execute it.

EXEC sp_attach_single_file_db @dbname='AdventureWorksDW2008R2_Data',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2008R2_Data.mdf'
GO

This should attach the database without the log file.


Now Microsoft Adventure works database is ready as a source database for Microstrategy Projects.

Adventure Works Datawarehouse Data Model 

Download the Datamodel for Adventureworks Datawarehouse Database by Right click and save the below picture.

This can be used as a reference to create a Logical Data Model for our Microstrategy Project.