Designing CAS Tables for VA 8.1 Graph Objects

Did you know that Ralph Kimball developed many of his star schema design constructs while constrained by a product that required a star schema? Granted, he helped design that product but, maybe, just maybe, some of his ideas on surrogate keys, conformed dimensions, and the like came from fitting data to the product and not from preconceived notions about how data should be modeled. The point is that the products you are using influence how you model your data.

So how does VA 8.1 influence data design? Designing tables for VA 8.1 graph objects is fairly straightforward. Graph objects summarize data visually into line graphs, bar charts, bubble plots, histograms, etc.. To produce these, you need

Measures for counting, summing, averaging, ...

Categorical variables for grouping, classifying, slicing, ....

But how should we model these measure and categorical fields? .

The Multiple Measure Approach

The simplest way to model these fields, of course, is to string the fields together creating a wide record. Our table might look something like this:

This approach works great for most scenarios. Report designers simply pick the fields they need for their reports. However, it can be limiting when you want to display multiple measures in the same report. Consider the waterfall chart object. It displays one measure by one categorical variable. The example below shows fixed costs by region.

This is fine but what if we want to show all categories of costs and revenues together -- product revenue, services revenue, variable costs, and fixed costs -- for a true "waterfall" affect? We can't do that with all of the measures strung out on the same record. .

The Single Measure Approach

Ironically, to show multiple measures on a VA 8.1 graph object (waterfall chart included) we actually need to construct our table with a single measure field (a generic measure) along with a measure type field. Consider the waterfall chart along with the table from which it was built below.

Here we see a single amount field, amt, graphed by a type field, accountNm. This gives us the full waterfall chart effect of positive and negative sums affecting the total.

As with the multiple-measure approach, you still need to attribute the CAS table with a full set of categorical variables. So a single measure CAS table might look like this:

Next Steps

In future articles, we'll look at using views to satisfy both multiple-measure and single-measure configurations from a single CAS table as well as using more complex structures like star schemas and joins.