Chapter 3 : Inventory

Chapter 3 : Inventory

Semiadditive Facts
We stressed the importance of fact additivity in Chapter 2. When we modeled
the flow of product past a point at the checkout cash register, only the products
that actually sold were measured. Once a product was sold, it couldn’t be
counted again in a subsequent sale. This made most of the measures in the
retail sales schema perfectly additive across all dimensions.
In the inventory snapshot schema, the quantity on hand can be summarized
across products or stores and result in a valid total. Inventory levels, however,
are not additive across dates because they represent snapshots of a level or balance
at one point in time. It is not possible to tell whether yesterday’s inventory
is the same or different from today’s inventory solely by looking at
inventory levels. Because inventory levels (and all forms of financial account
balances) are additive across some dimensions but not all, we refer to them as
semiadditive facts.
The semiadditive nature of inventory balance facts is even more understandable
if we think about our checking account balances. On Monday, let’s presume
that you have $50 in your account. On Tuesday, the balance remains
unchanged. On Wednesday, you deposit another $50 into your account so that
the balance is now $100. The account has no further activity through the end of
the week. On Friday, you can’t merely add up the daily balances during the
week and declare that your balance is $400 (based on $50 + 50 + 100 + 100 +
100). The most useful way to combine account balances and inventory levels
across dates is to average them (resulting in an $80 average balance in the
checking example). We are all familiar with our bank referring to the average
daily balance on our monthly account summary.
All measures that record a static level (inventory levels, financial account balances,
and measures of intensity such as room temperatures) are inherently nonadditive
across the date dimension and possibly other dimensions. In these cases, the measure
may be aggregated usefully across time, for example, by averaging over the
number of time periods.
Drill Across
In Chapters 1 and 2 we modeled data from several processes of the value
chain. While separate fact tables in separate data marts represent the data from
each process, the models share several common business dimensions, namely,
date, product, and store. We’ve logically represented this dimension sharing in
Figure 3.6. Using shared, common dimensions is absolutely critical to designing
data marts that can be integrated. They allow us to combine performance
measurements from different processes in a single report. We use multipass
SQL to query each data mart separately, and then we outer join the query
results based on a common dimension attribute. This linkage, often referred to
as drill across, is straightforward if the dimension table attributes are identical

Buss Architecture

The word bus is an old term from the electrical power industry that is now

used commonly in the computer industry. A bus is a common structure to

which everything connects and from which everything derives power. The bus

in your computer is a standard interface specification that allows you to plug

in a disk drive, CD-ROM, or any number of other specialized cards or devices.

Because of the computer’s bus standard, these peripheral devices work

together and usefully coexist, even though they were manufactured at different

times by different vendors.

By defining a standard bus interface for the data warehouse environment, separate

data marts can be implemented by different groups at different times. The separate

data marts can be plugged together and usefully coexist if they adhere to the standard

The bus architecture is independent of technology and the database platform.

All flavors of relational and online analytical processing (OLAP)-based data

marts can be full participants in the data warehouse bus if they are designed

around conformed dimensions and facts. Data warehouses will inevitably

consist of numerous separate machines with different operating systems and

database management systems (DBMSs). If designed coherently, they will

share a uniform architecture of conformed dimensions and facts that will

allow them to be fused into an integrated whole.

The major responsibility of the centralized dimension authority is to establish, maintain,

and publish the conformed dimensions to all the client data marts.

gross margin return on inventory (GMROI,pronounced “jem-roy”)

Although this formula looks complicated, the idea behind GMROI is simple. By

multiplying the gross margin by the number of turns, we create a measure of the

effectiveness of our inventory investment. A high GMROI means that we are

moving the product through the store quickly (lots of turns) and are making

good money on the sale of the product (high gross margin). Alow GMROI means

that we are moving the product slowly (low turns) and aren’t making very much

money on it (low gross margin). The GMROI is a standard metric used by inventory

analysts to judge a company’s quality of investment in its inventory.

If we want to be more ambitious than our initial design in Figure 3.2, then we

should include the quantity sold, value at cost, and value at the latest selling

price columns in our snapshot fact table, as illustrated in Figure 3.3. Of course,

if some of these metrics exist at different granularity in separate fact tables, a

requesting application would need to retrieve all the components of the

GMROI computation at the same level.

Notice that quantity on hand is semiadditive but that the other measures in

our advanced periodic snapshot are all fully additive across all three dimensions.

The quantity sold amount is summarized to the particular grain of the

fact table, which is daily in this case. The value columns are extended, additive

amounts. We do not store GMROI in the fact table because it is not additive.

We can calculate GMROI from the constituent columns across any number of

fact rows by adding the columns up before performing the calculation, but we

are dead in the water if we try to store GMROI explicitly because we can’t usefully

combine GMROIs across multiple rows.

Remember that there’s more to life than transactions alone. Some form of snapshot

table to give a more cumulative view of a process often accompanies a transaction

fact table.

Let’s assume that the inventory goes through a series of well-defined events or

milestones as it moves through the warehouse, such as receiving, inspection,

bin placement, authorization to sell, picking, boxing, and shipping. The philosophy

behind the accumulating snapshot fact table is to provide an updated

status of the product shipment as it moves through these milestones. Each fact

table row will be updated until the product leaves the warehouse. As illustrated

in Figure 3.5, the inventory accumulating snapshot fact table with its

multitude of dates and facts looks quite different from the transaction or periodic

snapshot schemas.

The rows of the bus matrix correspond to data marts. You should create separate

matrix rows if the sources are different, the processes are different, or if the matrix

row represents more than what can reasonably be tackled in a single implementation

iteration.

Creating the data warehouse bus matrix is one of the most important up-front deliverables

of a data warehouse implementation. It is a hybrid resource that is part technical

design tool, part project management tool, and part communication tool.

It goes without saying that it is unacceptable to build separate data marts that

ignore a framework to tie the data together. Isolated, independent data marts

are worse than simply a lost opportunity for analysis. They deliver access to

irreconcilable views of the organization and further enshrine the reports that

cannot be compared with one another. Independent data marts become legacy

implementations in their own right; by their very existence, they block the

development of a coherent warehouse environment.

Conformed Fact

Revenue, profit, standard prices, standard costs, measures of quality, measures

of customer satisfaction, and other key performance indicators (KPIs) are facts

that must be conformed. In general, fact table data is not duplicated explicitly

in multiple data marts. However, if facts do live in more than one location,

such as in first-level and consolidated marts, the underlying definitions and

equations for these facts must be the same if they are to be called the same

thing. If they are labeled identically, then they need to be defined in the same

dimensional context and with the same units of measure from data mart to

data mart.