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
[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, ...