Cubegeek

Design for Analysis: Measure By Measure

Good business and data analysis should be done well in advance of a detailed SOW. A good design can be fully compromised by an inability to deliver good data. We all know the GIGO theory, but everything between garbage and gold is not necessarily good.

My approach to to sourcing multidimenal data is by the Measure by Measure process. This means that I go down to the individual measure and find out which system(s) has the data available that will fit nicely into my multidimensional model. Let's try an example.

At my company, I know that my end-users which are many will have to deal with Wholesale Volume. Right now, in the Vehicle Operations department, I have at least two upstream systems which has WV. Which should we use? In this particular situation, I have recently discovered that while most of the end users get these numbers for a report named Carflow, this is not actually a system. There are at least two systems upstream of the Carflow report. They are NDSN and VPipe. In addition, there is at least one other system that various systems analysts use to create the Carflow report, depending upon which set of numbers you are talking about. These individuals will mix data from those various sources and use their understanding of the business to guide their judgement about which numbers to use. Those processes should be understood if we are to source 'Carflow' from their sources. What will help us is the Measure by Measure approach.

In the Carflow report itself the dimensionality is implicity expressed. It needs to be explicitly expressed as a valid data source. For example, the WV figures are expressed in 12 periods for an Model over the past three Model Years. We need to examine each of those three dimensions for the WV measure. On the same report, Fleet Volume is expressed for the same Model but Model Year is not broken out. This indicates that some aggregation of FV is done elsewhere. Some systems analysis proves this to be true and it was confirmed that the Fleet numbers were generated by another individual who contributes to the report.

Our requirements for the larger application into which 'Carflow' numbers must be input therefore require some of the detail behind the actual Carflow report. These systems are VPipe, NDSN and others. Our requirements are relatively simple but they must be consistent across all measures so that all of them will fit in the same multidimensional model.

Measures: (Wholesale Volume, Fleet Volume)

Models
The Model dimension presents the first challenge. In the Carflow report, each spreadsheet tab represents a Model. 'Sentra' is a model. However in our requirements, we need Model at the 'P4' level. Additionally, we know that P4 code is a function of 'Model Line' and 'Model Year'. This makes things a bit more complicated with regard to explicit expression. For example, the P4 code for XTerra is WD22, but only in Model Years before 2005. In 2005 and beyond, XTerra becomes N50. However in the Carflow report, this distinction is not explicitly expressed. We therefore assume that the analysts who assemble the Carflow report understand this for Wholesale Volume and use a combination of P4 codes appropriate to the Model Year.

Model Year
Model Year is not so problematic vis a vis what is explicitly needed in the targe and what is implicit in the source. There are, in the Carflow report, several lines which indicate 'Current Model Year' which need to be derived from common sense. There are also some lines which have no listing of mdel year.

Month
There are obviously 12 months in a calendar fiscal year, but there may be some measures, like BOM inventories, that need more than 12 months of source data to be properly calculated. There may also be accrual type calculations which are necessary for the creation of a certain number. Or there may be rolling averages involved.

So for each Measure we seek to include in the multidimensional model, we must evaluate the various sources and determine if the dimensions work. So we can ask questions and make requirements like - "I need NA CV by Model, Model Year" as raw data and not get confused by what shows up in reports vs source systems.

Dataflow Modeling

1. Refresh Periodicity
How often is this data file refreshed? On what basis do I get it? Is it going to be monthly on the first Tuesday? Is it going to be every other Wednesday at 1pm? Can I get it on demand?

2. Data Periodicity
The data contents of the file. Are they monthly, weekly, daily data?

3. Fallback & Dependencies
Is there an alternative source for this data? If this file does not arrive, what processes are affected? If this data needs to be resubmitted what is the guideline? How often can it be resubmitted? Does the target need to be reset?

4. Data Owner
Who is responsible for this data? Who understands what it means?

5. Volume
How big is the file? What is a typical size for the file?

6. Data Slice
What is the cut of this data? Is it in an archiveable form? Why is the data cut in this way? Is it along the dimensionality of the data? Is it a complete dataset? How does the physical cut of the data related to the logical cut of the data? Is it consumed in the same grain as it is produced?

7. Data Target
What is the consumer of this data? Does the target need to be reset or is this incremental data?

8. Format
Is this CSV? Fixed length or delimited fields? How many lines of header? Field names on top? Record length? Delimiter? What is the file spec & naming convention for this file?

9. Notification
How do I know about the existence of the file? What will I know about the file before I get it? What can I know about the file before I read it?

10. Data Increment
Is data restated? Should it overwrite the target data or be added incrementally. Are these balances or transactions?

The Object Analytical Model

As I play with Ruby on Rails, I am percieving that the thing that makes it so powerful is the formalization of the ORM, the Object-Relational Model. I can't speak directly to the specifics of what it is, but the reason prototyping seems to be a breeze is because that work is done. So now there is a kind of algorithmic representation of the data to be used and the possible relations between it. Having an object oriented shell (Ruby) around query generation thus seems to be a no-brainer. That is to say things that were once done painstakingly are now easy. I say this having done it the hard way back in 91.

In 91 I was building star schemas, before the term had been formalized. Or put another way, I built my first star schema in 1990 or 89 thereabouts just a few weeks after having learned SQL. In those days, client-server was brand new in the corporate IT and what we were doing was considered unique. Mind you, these were the days before Powerbuilder, when DB2 was still king and Sybase hadn't quite become dominant.

By the time I was on my third big multidimensional-relational project, I had a good sense of what made sense and what didn't - to the point at which I was confident that I could replace systems build by Liddle's old company Metaphor, which at the time was in the process of devolution being sold to IBM. My concept was a 'navigation set'. Having scoped out the application quite well, (in those days there was no such thing as a legal ad-hoc query), we understood every implication to every click on the screen. And since I was working in a 4GL-based front-end (this was in the days just prior to visual programming), I had to write code to dynamically generate SQL. The result was a big hairy array with 100 elements which described the current state of the application in terms of all the dimensions. For all I know, this is what's at the core of all such general purpose OLAP front-ends. I'd have to ask my boys at Pentaho.

The Navigation Set, once established by the operations (click, pivot, drill, select, etc) of the front-end operator would then, at the press of a 'Go' button, send it's parameters to the Query Module which would assemble fragments of SQL. My navset was smart enough to know when to to subselects as well. The Query Module would then have an interface to the DB2 on the back side and through a dynamic bind, query the appropriate fact table(s) and bring back a screenful of data.

This might be the basis of an object-analytical model, and an OAM would be the touchstone of development of a new set of tools that can make analytical applications easier to build. Of course people would have to understand the fundamental performance characteristics of multidimensional databases (right now there just seems to be Essbase and MSAS) but this could be a good thing. The issue is, of course, the rapidity with which repetitive questions can be asked - the fundamental stuff of OLAP.