This post is the first in direct response to a request from Twitter, submitted by @praneethgoparaj, asking if I could spend a little bit of time on the broad topic of Metrics. Metrics are of course the lifeblood of the system and drive the engine of the report by determining which tables your report will source itself from. There are lots of tricks you can do with Metrics as well as some straight forward uses. I don’t want to rehash documentation here, but I’ll touch on the different kinds of things you can do.
Metrics in General
Basically speaking, Metrics are an aggregation function that surrounds a Fact, which directly relates to a measurable column in a table or tables. The Metric can take on other properties that can help guide how the SQL is written, such as a Filter (see Conditional Metrics) or controlling it’s Dimensionality (see Level Metrics).
The Metric (and more specifically, the Fact it contains), determines what the base table for the report will be, and then dimension tables are chose from there based on the Attribute needs. You can do some fancy things by manipulating the Facts that will open up options for creative solutions.
In my history, these are the most common types of Metrics. Whereas a normal Metric takes it’s filters from the Report, a Conditional Metric will take it’s filter first from itself, and then the Report if they don’t conflict (and that conflict resolution is of course customizable). The advantage here is that you can build Metrics that contain filters that don’t apply to the rest of the report which can be particularly useful when you need to apply a flag to a Metric.
Unfortunately, relying on this type of Metric often can create some real headaches for Developers. The number of objects can quickly balloon, and needing to make modifications can require changes in lots of places. Although I don’t really have any tips to help alleviate that pain, I can help you create the pain faster =]
You can right click a Metric or group of Metrics and choose Create Advanced Metrics and add a Filter or Transformation to all of them. This is incredibly useful if you need to apply a filter to a bunch of Metrics as it saves you some steps.
A Conditional Metric can also only have a single Filter, so if you want something like “South Books Revenue”, you have to create a filter that contains both “South” and “Books”, as opposed to being able to put 2 individual filters into a single Metric. This just adds even more to the clutter of objects, and I’ve never understood why that’s not supported.
Level (Dimensional) Metrics
These are commonly considered the most complicated Metrics, as they deviate from the rules of normal aggregation and filtering. I’m not even going to attempt to talk about the different implications of the Filtering and Grouping Dimensionality settings (you can look those up in the Advanced Reporting Guide), but it’s worth mentioning that the most common technique for building these types of Metrics is trial and error. 😉
These are Metrics that are made up of other Metrics, such as a Profit Metric may be defined as Revenue – Cost. One of the unfortunate disadvantages of this kind of Metric is that it can’t carry it’s own Dimensionality or Filter, meaning if you need a “South Profit” Metric, you have to first build “South Revenue” and “South Cost”. Like the limitation of a single Filter in a Conditional Metric, I don’t see why the engine can’t take a Filter at this level and supersede it over the lower components. That would cut down on object clutter and make these faster to build.
In cases of Quotients, the “Smart Metric” setting is the highlight of this type, which tells the SQL Engine if it should do a straight aggregation or recalculate the formula based on individual totals. This setting is off by default, but you almost always want it on if the setting is available.
A nice feature of Compound Metrics is that you can click on them in Web (or double click in Desktop) and have their components broken out. This provides some Metric Drilling options for the user to see the components of the calculation broken out.
Database Passthrough Metrics
In addition to tricks you can do with ApplySimple for Attributes and ApplyComparison for Filters, there’s also ApplyAgg for Metrics. It’s basically the same concept as the other Apply functions, which allows you to customize the SQL generated by the Metric. A really cool use case is building an Adaptive Metric, which is one that can change aggregation functions.
And although I didn’t actually use ApplyAgg in the example, the Adaptive Metric is similar to the solution for aggregating Count metrics but creating static Metrics such as Sum(1).
It’s also possible to simplify some Metrics down stream by putting some logic in the Fact definition itself. In that Profit example before, I could define a Profit Fact with “Revenue-Cost” directly. This saves me a few objects and could simplify some definitions if I’m going to be using a lot of Dimensional and Conditional Metrics for Profit. This does mess with your ability to aggregate, since the SQL Engine won’t be able to break the Revenue and Cost out separately. In this example it should be fine since it’s just a subtraction, but if the formula included a quotient, you wouldn’t be able to do it using this method.
If you have a question or idea for a Blog Post, hit me up on Twitter @BryanBrandow!