BookmarkSubscribeRSS Feed

Designing CAS Tables for VA 8.1 Graph Objects

Started ‎08-18-2017 by
Modified ‎08-18-2017 by
Views 1,439

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

  1. Measures for counting, summing, averaging, ...

  2. 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:
 
1.png
 
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. 

2.png

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. 

3.png
 
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:

4.png

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.

 

 

Version history
Last update:
‎08-18-2017 02:56 PM
Updated by:
Contributors

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Labels
Article Tags