Conceptual data modeling with business cases

There are three main types of data models: conceptual, logical, and physical. Each model serves a distinct purpose, primarily determined by the level of operational detail it presents.

Conceptual data models are created during the initial stage of the data modeling process. They offer a high-level overview, focusing on essential information while excluding finer details for easier comprehension.

For many people, a conceptual data model sounds very abstract, and they don't know how to implement conceptual data modeling in their business cases. In this article, I will describe how to create a conceptual data model based on a business case.

Business Case - Northwind Traders

Northwind Traders is a fictitious organization that manages orders, products, customers, suppliers, and many other aspects of a small business.

Northwind Traders has an online transaction processing(OLTP) database which serves both operational activities and analytical purposes. The OLTP database is not optimized for analytical usage so Northwind Traders would like to modernize their existing structure by creating an online analytical processing(OLAP) database.

The purposes of the structure modernization includes:

• Better scalability

• Improve the performance of operational system

• Improve the report loading speed

• Improve the security control of the data

OLTP Database Architecture

The entity relationship diagram(ERD) of the OLTP system is as follows. There are some main tables in the system, including customers, suppliers, employees, orders, products, etc.

This ERD can help us understand what tables we have in the system and the relationship among those tables.

OLTP ERD sourced from https://www.udemy.com/course/analytics-engineering-bootcamp/

Business Requirements

Before starting to create an OLAP database, we need to understand the reporting requirements so we can design a structure which suits the business requirements well.

The business requirements include:

• Sales overview - overall sells report to better understand the customers about what is being sold, the best selling products, the least selling products, etc.

• Sales representative performance tracking - track sales and performance of each representative to adjust commissions, reward high achiever and empower low achiever.

• Inventory monitor - understand the current inventory level, stock status, how many products are being sold of each supplier. This report will allow the business to improve stock management and potentially enhance the bargaining power.

• Customer reporting - allow customers to track the status of their purchase orders, how many and when they are buying. This report can empower the customers to make data driven decision.

Bus Matrix

After understanding the business requirements, we can start linking requirements with related tables in the OLTP database. Like the following example, this is called Bus Matrix, a data warehouse planning tool and model created by Ralph Kimball.

In the bus matrix, we can list all the requirements in the business process column and put some main tables as other column headers. Then we need to check if a requirement is related to each table.

For example, the product table is related to Sales Overview so we can give it a check. Sometimes we may find a table is missing in the bus matrix then we can just simply add it in in the process.

Conceptual Data Model

Based on the bus matrix and the ERD of the OLTP database, we can design a conceptual data model like the following picture.

The first schema(blue colored one) is for sales overview and sales representatives performance tracking.

By utilizing the star schema methodology, we know the orders table is a fact table, so we can put it in the center.

To easily distinguish between the orders table (customer orders) and the purchase orders table (orders placed to suppliers), we can refer to the orders table as the sales table, which is the fact_sales in the first schema.

The second schema (yellow one) is for inventory monitoring. We only need supplier and product information, so there are fewer tables in the schema compared to the first one.

The third schema was designed by following the same process, so I just skipped the description here.

Review

After understanding the business case, the OLTP database architecture, business requirements, we utilize Bus Matrix and Star Schema to create the conceptual data model for the Northwind Traders case.

Hope this article can help you understand how to create a conceptual data model for your business cases. Please feel free to share your thoughts and ask any questions.