QlikView Your Business

Chapter 9 Data Modeling for Profitability Analysis

In Chapter 5, we described how to create a dimensional star schema around a single set of business transactions (invoices). We created a single Fact table by loading the rows from the [Invoice Details] table and adding fields from the [Invoice Header] table. Dimension tables were created for Customers, ShipTo (Customers), Warehouses, Products, and [Sales Persons], as well as a [Master Calendar] table of dates. However, to support the more in-depth analysis of profitability, you need more than just invoices. To calculate the profit margins for particular products, a more accurate measure of sales and the cost of goods is needed. In this chapter, we’ll introduce several new concepts and discuss data modeling alternatives.

On the revenue side, adding the credit-memo transactions to your model allows you to see not just sales, but also any returns or adjustments. To accommodate the new transactions, we’ll compare two modeling techniques: concatenating the Fact table with the new transactions and linking two separate Fact tables together.

On the costing side, we’ll bring in labor, material, and estimated overhead costs for each product, by year. This introduces the concept of the slowly changing dimension into the model and the modeling challenges that go with it.

The precise way in which you incorporate these new data elements impacts the memory footprint and performance of the application. To better inform your modeling decisions, ...