SQL Server Business Intelligence Data Modeling

By: Siddharth Mehta

Overview

Some of the excerpts from the business scenario are as follows - "Each business unit from every geographical location would access the online system and manage sales and order related data that would be stored in a central data repository. A team of data analysts would be accessing the data to create reports on-demand, as required by the CXOs of the company. They need to be able to slice and dice the data for detailed analysis." This would translate into a unique system requirement where we would be required to develop an OLTP data model to store data from an online front-end application. The aspect which makes it unique is that we would also have to keep the reporting and analytics requirement as this data model would act as one of the source systems for the rest of the solution. We would derive and develop the database model for this requirement.

Explanation

Development of a data model generally starts with development of a conceptual data model. During this phase of modeling, business entities and their relationships are defined. Let's consider a few points to derive the conceptual model.

1. The first entity to start with is Products. Products can be classified into categories and further into sub-categories.
2. Clients place orders using an online system which generates sales.
3. Sales is managed at a geographic level and categorized by Continent, Country, State and City.
4. Sales is operated and supported by sales professionals and managers.
5. Performance of employees is managed at a territory level. The address of employees are maintained by the organization.
6. Address can be of two types - Work Address and Residential Address.
7. Products are sold out from stores to different customers who may be loyal to stores near to them.
8. Stores may have special offers or regular offers under which products prices are regulated.

There can be many business scenarios which one would need to consider while deriving the conceptual data model. In case you are interested in considering a more elaborate business scenario, consider reading the Adventure Works Cycles Business Scenarios. This would help you understand a near real time business scenario in more detail.

Business entities and relationships are represented using an Entity-Relationship diagram. ER Diagram is one of the standard methods of developing the conceptual data model. Try to create a entity-relationship diagram yourself. You can even use PowerPoint and just create few objects to represent entities and show the relationship to represent the conceptual model at the least. You can learn more about ER diagrams from this video.

Once the conceptual data model has been developed it is used as the input to develop the logical data model. A logical data model contains normalized data entities with all its attributes. This data model is independent of the underlying database management system that would be used to implement the data model.

We can start with the identification of an initial list of tables from the conceptual model - Products, Orders, Country, Address. As we know that Products would have categories and sub-categories, we can normalize the Products table into Product Categories -> Product Sub-categories -> Products. Similarly geographic information can be categorized and normalized into Country and State. You may use PowerPoint or Excel if you do not have any other modeling tool at your disposal, and try to create a list of entities along with the attributes that you think should be a part of the tables.

Generally before adding attributes to the logical entities, a data dictionary is created which explains the business meaning of the attributes. For example, ProductNumber is a unique identifier of the product that would be formed by 25 digits of the bar-code, which originates from the SAP system. Similarly all such attributes are listed and explained the data dictionary. To get a better idea about the attributes and the tables that we can have as a part of our business scenario, consider reading AdventureWorks Data Dictionary as a point of reference. Once the logical data model is developed, it's implemented using a database management system which is called the physical data model. A physical data model consists of all the database objects like tables, views, stored procedures, triggers, etc. The AdventureWorks OLTP database is the output of a physical data model. The AdventureWorks data model contains the tables related to our requirement as shown in the logical design. Consider reading the database development tutorial to learn more about OLTP database development, which would help you to understand the physical design of any database in more detail. You may find a number of database objects in the AdventureWorks OLTP database. To understand the same, read the documentation of SQL Server Objects in AdventureWorks.

Below is a small representation of a logical data model from AdventureWorks corresponding to our business case, in the form of a database diagram. Most of the entities that may be required for our business scenario is represented here in the physical model. Compare the logical model that you have developed to the below logical diagram. If you have developed the logical / physical data model to this level of detail or even more, then you are ready for the next chapter. Else consider investing more time in thinking through the conceptual and logical data model and try to understand the points that you may have missed that are included in the below data model.

In real-world scenario there would be a large number of business entities and even more complex relationships. We are using a small section of AdventureWorks to limit the complexity and focus more on the concepts of developing the solution.

Additional Information

• The database diagram can help you understand the tables and relationships in a data model. Add tables and related tables one by one to understand each table and its relationships. Read this article to learn about database diagrams.
• Read more database design tips to learn about database design concepts and tools.