You probably already know what a measure is, but there are a number of points that need to be made when we talk about them and make decisions on how to define them. If we can get on the same page, we’re more likely to be successful with our work.
First off, we can define a measure as being whatever it is you want to measure. That’s all. Here is an example: Amount Sold.
If we are working with a data warehouse, a measure comes from a fact. I prefer we don’t think of them as being synonymous — facts and measures, that is. Let’s take the example of the Amount Sold.
An invoice is made up of line items. Each line item has a total value for that line, probably quantity times price, for example two units at $500 each. This line will total $1000, and because this belongs to an actual transaction that is recorded in our application we call it a FACT and it’s not changeable (unless it’s changed by someone who is using the application).
Here is the example (the highlighted line):
It’s necessary to instantiate our measure, Amount Sold by defining it in code. This type of measure is called a “base measure” because it’s virtually the same as the fact.
It might seem like this adds an extra layer of complexity, but this is actually an important practice. There are a couple reasons this is done:
- All measures are reference-able simply by their name, without qualifying what table they originate from.
- Derived measures should be made up only from measures (not a combination of measures and facts).
As a practice, I like to keep base measures pure, made from a single fact. There is a more technical reason to do this, in the context of a Tabular Model:
That is an extremely important distinction when using the DAX Language.
Let’s move on from that stuff — scalar vs. tabular — and return to the idea of base measures and derived measures. This is a concept more easily understood, and it is a topic that comes up during design.
Base Measures: think of base measures as nearly the same as the facts, but they are renamed for reporting, and can be used to build derived measures. Examples of base measures would be Amount Sold, Number of Sales.
Derived Measures: think of derived measures as being measures made of measures. An example of a derived measure would be Average Sale Amount. It uses the two base measures above, dividing the Amount Sold by the Number of Sales.
Here is another technical point about measures. In an SSAS Tabular database, measures are not stored. They are calculated when needed. This is because the Tabular model stores all of data in memory. This is one of the ways that it performs so well.
Measures are not KPI
Here is one last statement on measures. They are not key performance indicators (KPI). This is very often confused in business. The intent is to identify the “key metrics” to determine whether an effort is successful or not. For example, a business leader might say, “We need to measure the length of time our customers wait for service by department.” It is then incorrectly believed that is a KPI. It is not.
KPI are based on measures. First, a measure gets defined, such as Wait Time. Then, a KPI can be created that provides a judgement about that measure’s value (good, bad, acceptable, etc.).