Operations involve multiple discrete events that are represented as documents in the ERP system. For example, our customer selling cycle includes a sales quotation, a sales order, a customer delivery, a sales return, a sales invoice, and a sales credit memo. Our supplier purchasing cycle includes a purchase order, a delivery, a return, a purchase invoice, and a purchase credit memo.

Although we can create a transactional fact table that allows us to analyze each discrete event, we are interested in analyzing the relationship between the events more than the events themselves. For example, we want to know how much time it took to deliver a product after receiving its originating purchase order. It would also be insightful to compare the quantity that we delivered with the quantity of the originating purchase order. We would have to work between multiple rows in a transactional fact table to discover this information; and just like a row-based database, we would find it challenging to work between rows in QlikView.

Therefore, we create a table where one instance of the whole operational process is stored in only one row. Each instance is an evolving event that is updated every time it reaches a milestone in the process. For example, in the beginning a row may only contain a sales order. When this sales order becomes a sales delivery, we update this row with data from the sales delivery. We refer to this fact table as an accumulating snapshot.

We measure the following evolving events in our data model:

We keep the accumulating snapshot table simple by modeling every cycle as a one-way, linear process. In our ERP system, we can begin this process with any discrete event. For example, we may start the selling cycle with a sales quotation, or we could skip this step and start with a sales order. In the script, we assume that a document created from nothing is a new cycle and concatenate a new row to the accumulating snapshot.

When we generate a document from another document, we assume that we are adding on to an existing cycle. We link this new document's data with an existing row that contains the base document's data. For example, if we generate a sales invoice from a customer delivery, we will insert this invoice into the same row as the base delivery document.

Each event contains multiple dates and amounts that correspond to different discrete events in the process. For example, one row can potentially contain the sales order date, the delivery date, and the sales invoice date. As such, accumulating snapshots tend to have more columns and fewer rows than a transactional fact table.

As sales and purchasing cycles have a similar sequence of events, we use the same columns for each cycle. For example, we use a column called [Order Quantity] to hold the quantity of both sales and purchase orders. We differentiate between each order using a field called [Process Type] that holds a text value that is either Sales or Purchasing. Even with this table optimization, the fact table contains almost one hundred columns.

Most of this data model's dimensions are similar to the ones from previous perspectives. The difference is in how it handles dates. Each date has its own master calendar and they all share a common master calendar. We need a bridge table to link this common master calendar to the fact table so that it can handle the multiple date fields that exist in each row. Before we look deeper into this subject in the next section, let's review the 7Ws of our operations data model:

Handling multiple date fields

When we have multiple date fields on one row, we can't just fit every date into one calendar. At the same time that we handle multiple date fields in one common calendar, we also create a master calendar for each important date. The important dates in this case are when we create a new document in a cycle. We use these dates and the same calendar subroutine that we used in previous perspectives to create a separate calendar for each date field for that date. We use calendars that correspond to certain documents in the analysis that we perform in this perspective:

call Qvc.CalendarFromField('_KEY_Quote_Date','QuoteCalendar','Quote'); 

We also create a common calendar that helps the user navigate through the data without having to first think about what calendar to filter. This calendar behaves as shown in the following figure. When we select a process cycle that is identified by _KEY_ProcessID, which is equal to 1399, we notice that it contains multiple dates starting from May 15, 2012, until June 9, 2012. As a result, we can see in the common calendar's Year and Month filters that both May and June 2012 are possible values:

We make this behavior possible by creating a bridge or link table between the fact table and the CommonCalendar dimension. This link table is called DateLink and it stores the date of each cycle's discrete events in individual rows:

The following code is an example of the script to include the dates of the first two discrete events in the DateLink table. The other dates can also be added in the same way:

DateLink:Load _KEY_ProcessID,[Quote Document Date] as _KEY_Date,'Quote' as [Document Type]Resident Operational_ProcessWhere not isnull([Quote Document Date]);

Concatenate (DateLink)
Load 
   _KEY_ProcessID,[Order Document Date] as _KEY_Date,'Order' as [Document Type]Resident Operational_ProcessWhere not isnull([Order Document Date]); 

Now that we've reviewed the operations data model and how we handle multiple date fields, let's start to analyze our operational cycles.