Handling Count Metrics For Aggregates

Here’s a quick tip for a scenario that I encountered in a project today.  Say that you’ve got a schema where one of the metrics will be a Count(*) from the Fact Table.  This is a common occurrence when the Fact Table contains Events that may or may not have traditional facts, such as Revenue/Cost, but also the existence of the record is a fact itself.

For example, say you’ve got the table FactCases, where each record is a customer support case, with fact values like TalkSeconds and ReplacementCost.  While the Sum of TalkSeconds and Sum of ReplacementCost are valuable metrics, another metric you’ll definitely want is the Number of Cases.  This would be accomplished by creating a Count(CaseKey) metric.  But say you have a lot of cases, and you want to introduce an Aggregate Table in the future (or even now).  How will MicroStrategy handle an Aggregate Table with a Count?  Today, I’ll show you one possible solution.
For further details on the role of Aggregate Tables, check out yesterday’s post on the subject.

The basic issue is that when you define the metric Count(CaseKey), the metric is non-aggregatable.  This is due to the fact that if you were to introduce an Aggregate Table, say at the Month level, you’d have a column called Cases that would have the total.  But at this level, the metric for Number of Cases would be defined as Sum(Cases).  Since the aggregation function changes between the levels, that would make it impossible for the same metric to use both the detail and aggregate tables.  This would impact your flexibility and make drilling impossible.

One possible solution is to convert the metric Count(CaseKey) to it’s equivalent Sum(1).  There are a couple of assumptions here of course.  The first assumption is that every record in the table is equal and have the same “weight”.  Second is that there aren’t any duplicates in the table, so Count(distinct) isn’t required.  The advantage to using Sum(1) is that now you can use Sum(Cases) from the Aggregate Table (or multiple Aggregate Tables) since they use the same aggregation function.

To create the Sum(1) metric for our Support Cases scenario, use the following steps:

  1. Create a Fact, called “Case Event” with a Fact Expression of simply 1 and set it’s mapping mode to Manual.
  2. The Fact will list every table in the system as a possible source, since the constant “1” isn’t related to anything.  Choose only the FactCases table, and save.
  3. Click on New to add a second Form Expression, and this time choose the Cases column from the AggregateCases table.
  4. Create a metric called “Cases” with the definition of Sum(Cast Event).
Now, when the metric “Cases” hits the FactCases table, it will resolve as Sum(1) in the SQL.  But when it hits the AggregateCases table, it will resolve as Sum(Cases).  This provides you with the flexibility of introducing Aggregate Tables now or later, as well as making this metric available for Dynamic Sourcing with Intelligent Cubes (another feature you’d lose with a traditional Count metric).

You may also like...