The “avg” function is MicroStrategy has caused me problems in the past. It’s not that “avg” does anything wrong…it just doesn’t necessarily provide the correct subtotals to the end user, depending on the end user requirements.
Here’s the report output that examine all of the different ways average can function.
Now we’ll examine these metrics left to right.
This first one is your true “Avg” metric. Here’s the SQL that is sent through when you query the Warehouse.
SELECT
- ID,
- AVG(VALUE)
FROM FACT_VALUE
GROUP BY ID
Simple enough…but now say you want the “total” average as related to these groups. In the metric definition if you select “avg” as the aggregation type, you’ll notice that the average in the subtotal, is simply the average of the averages (following me?). Because you’re returning the data already at the aggregated level, the system doesn’t know what’s driving t in the background.
The second metric is actually the “weighted average” for our purposes. This metric is actually defined as the SUM(VALUE)/COUNT(VALUE), with the Smart Metric Enabled on your metric creation. The subtotal of this metric is actually the true average of all of the values, not the average of the average.
The columns that follow prove the logic out. You can see that the sum/count is the “more” correct way to calculate average. Keep this in mind when designing your metrics.
Who is your top customer? Your client probably knows. But can they name the top ten…in order? Some of your clients will be able to, but most won’t. What about the bottom 10…there’s a big no go, but in Business Intelligence, this is all important. How do Sales Reps know who to spend money on to court, and who just isn’t paying off…whether that be revenue, margin, ad count etc?
The Rank function takes care of all of this. The power isn’t that you can rank everything necessarily, but that you can decide at what point the rankings are broken up. Who was your best customer in 2008? How is your worst customer now?
The Rank function is the easiest way to tackle this. When creating a metrics, simply use Rank([Fact]) or Rank([Metric]). That’s the easy part, the next part is what requires a little bit of critical thinking. Rank is only valuable when you’ve broken down some sort of parameters. To set the parameters, click on the word “Rank” with your mouse and select Rank Parameters from the Tools option in your metrics editor, or right click on the word “Rank” and select parameters. What you will see is this:
You set the specific attributes you want to break by in the parameter screen. In this case, I’m interested in seeing the top customers by Year and Business Unit. You can add as many metrics as you want in this screen, keeping in mind that you have to build the reports with those specifications in mind…rank metrics can act funny if you’re not very aware of how you’re aggregating them and you just start throwing them into any old report.
Recent Comments