The Theory of Identification
Imagine a generic data warehouse that has the standard “Amount” and “Quantity”…maybe you’re a retailer, and you want to see how many units of an item you’ve sold in the last fiscal year, or maybe you want to see how much the average price of products sold in “Category A”; this can all be achieved with your standard data warehouse as is. In Microstrategy it’s easy enough of course…just make a metric that based on FACT_AMOUNT or FACT_QUANTITY and then sum it out…throw your filters together and voila…answers. But…what if you want one column, one metric that contains both quantity and amount? Hmmmmm….
Why would you want this? Drillable grid reports! Yes, documents and dashboards are nice, but the slice and dice functionality of OLAP cubes with standard grid reports is where it’s at for the data junkies. Flash driven graphics is one thing, but true drag and drop functionality…that’s a necessity if you’re an analyst.
Tip. Forget the idea that a column is enough to identify your FACT. If you have “Amount”, make sure that somewhere in your data warehouse you have it identified as “amount” just as you’d have something like price or category attached to a transaction. This is a great way of making use of shortcuts in the future…
So what’s the reason for all of this identifying? It’s so that we can make our Consolidations work more effectively…but, to make those consolidations work, first…we need to make a “consolidation friendly” metric; a metric that is a combination of both “amount” and “quantity”, with the knowledge that you’ll be separating them out later.
Now some people might not like the philosophy behind a metric like this, because effectively, you’ve made a metric that only works when used in conjunction with an consolidation that separates them back out again. You can see an example of this below.
Note: You’ll have to make sure your report developers are either aware of it’s limitations so there’s no “accidents”.
So, with the above steps, what you’ve now been able to create is a grid report with ratios that you can drill into that all lie in one column, one number. Of course you can make a metric at the report level that’s dynamic, but now you have 3 columns…Amount, Qty, and Ratio. Maybe you just want to see the ratio of one specific category, to a quantity in another category? You can’t do that easily without creating a new set of filters at the metric level. With this method, for every ratio you need, there’s a consolidation that can me made.
One report, one metric, and you can drill until your fingers bleed.
Recent Comments