Intelligent Enterprise

Better Insight for Business Decisions

RSS
Webcasts
Whitepapers
Subscribe
Home

May 9, 2002

Temporal Dimensions

Understanding the relationship between age and time will help you build better models for perishable goods

By Erik Thomsen

Continued from Page 1

From a manager or analyst perspective, you need to know such things as how long fish stays in inventory, the average age of fish sent to production, how much fish is in danger of being thrown away, and, assuming the existence of multiple inventory sites, those instances where fish was thrown away from one site while in another site had a shortage of that same fish. For all these types of analytic queries, it is best to treat age as a separate temporal dimension as shown in the schema:


(TIME.  FISH.  AGE.) 
~ QTY_RECEIVED , QTY_SENT , 
QTY_ON_HAND

So how would you modify the formula for QTY_ON_HAND to reflect the addition of the age dimension? How about adding the age dimension to each of the two formula components for QTY_ON_HAND like the following? What do you think?


QTY_ON_HAND, 
FISH. , 
TIME.2-N. , 
AGE. = 
((QTY_ON_HAND + QTY_
RECEIVED - QTY_SENT) , TIME.-1)
QTY_ON_HAND, 
FISH. , 
TIME.1 , 
AGE. = 0

As you've probably noticed, the new definitions as stated have several problems. First, in the age-containing schema, QTY_RECEIVED would have to be input solely at the '1' or initial member of the Age dimension. In other words, fish should only be arriving in inventory at some minimum age, such as one-day old. (You can model the fish's age from the time it was caught but that wouldn't change the essential problem being tackled here.) Thus, it doesn't make sense in the upper expression to refer to QTY_RECEIVED as if it applied to all ages. Neither would it make any sense to restrict QTY_RECEIVED to Age.1 as this would lead to overcounting the QTY_RECEIVED variable. In contrast, QTY_ON_HAND and QTY_SENT do apply to all ages.

But if QTY_RECEIVED, which is the sole measure of input flows, only applies to the first day of age, how do stock additions occur across the Age dimension?

Additions to fish, for ages greater than one, occur through the aging process itself. This implies that QTY_ON_HAND for any age fish needs to be a recursive function in two dimensions, Time and Age, as the following shows:


QTY_ON_HAND, 
FISH., 
TIME.2-N. , 
AGE.2-N. = 
((QTY ON HAND - QTY_SENT), 
TIME.-1 , AGE.-1)

Furthermore, you need to be aware of two separate starting cases: the first day, which happens once for the whole schema, and the first day of age, which happens for every fish as it is received.


QTY_ON_HAND, 
FISH. , 
TIME.1 , 
AGE. = 0
QTY_ON_HAND, 
FISH., 
TIME.2-N , 
AGE.1 = 
(QTY RECEIVED, TIME.1 , 
AGE.0)

Table 3 illustrates a restatement of fish received and fish sent that associates an age with the fish.

Can you say why fish is received at age zero instead of age one, which was defined as the minimum age of fish? The reason is because I'm tracking QTY_ON_HAND at the beginning of the day instead of at the end of the day. With beginning-of-day accounting, in order for the fish received on any day to appear the following day as one-day old fish, they need to arrive at age zero. Some of you may have also noticed that fish is sent at the "all" age. This reflects the fact that production isn't going to specify fish ages, just fish types. The business must decide on an appropriate inventory depletion rule. Can you figure out what rule I used to calculate the QTY_ON_HAND variable in Table 4?

Rate This Article

Comments:

Optional e-mail address:

I used the rule FIFO. This way, when production asks for some amount of fish, inventory uses up the oldest fish first. Think about how you would express the formula for updating the inventory schema based on requests for fish from production.

As you learned in this installment, solving analytic business problems frequently requires using schemas whose variables are recursively defined across two interdependent time-based dimensions. In addition to enabling you to build models for things that age, understanding the relationship between age and time is critical to connecting schemas across the entire enterprise value chain where different events, such as materials shipping, materials inventory, production, product distribution, and so on, are temporally (and spatially), linked.

Erik Thomsen [erik@dimsys.com] is cofounder of Power Thinking Tools, which developed the first OLAP engine with integrated statistics, visualization, text processing, and object management. He is a researcher and consultant for Dimensional Systems and focuses on integrated multitechnology analytic solutions. He is the author of OLAP Solutions (John Wiley & Sons, 1997) and coauthor of Microsoft OLAP Solutions (John Wiley & Sons, 1999).

RESOURCES

Thomsen, Erik. OLAP Solutions, John Wiley & Sons Inc., 2002

Related Article at IntelligentEnterprise.com:

"Marketing Calculation," February 21, 2002: www.intelligententerprise.com/020221/504analytic1_1.jhtml