In this chapter, we will cover:
- Creating a simple counter fact and metric
- Using the SQL view to inspect SELECT statements
- Understanding the GROUP BY clause
- Adding more facts
- Filters on metrics
- Creating ranking metrics
- Grouping at a different level (level metrics)
- Embedding filters inside metrics
- Using Metric Join Type in reports
- Creating a previous month metric (transformation)
It's time to produce some numbers. In our BI journey, we have walked through the installation, configuration, and creation of a project, and there was not a single number involved. I appreciate your patience reading the first few chapters; now we start looking at what the reports look like and where the results come from. Because, after all, everything we do ends up in a bottom line total.
I don't need to remind you that not-so-stupid numbers are the ultimate goal of a BI project (yes, I know, flashing out iPads is also fun), and it is extremely important to know where numbers come from, why they look good or bad.
Chances are that parts of your deliverables will be PowerPointed in a board meeting; and there will be a few grey-headed very important people getting upset looking at the slides. In those moments, you'd better be 110 percent sure that the total is correct, where the numbers came from and—most of all—be able to demonstrate it. We're not responsible for bad numbers, we are responsible for inaccurate ones.
OK now, before we continue, in order to keep things clear and understand the MicroStrategy terms, I need to answer a question: what is the difference between a fact and a metric?
A fact is a column in a table and a
metric is one or more aggregate functions that we apply to that fact. SalesAmount, being a column, is a fact; SUM(SalesAmount) is a metric. It is actually a little more complicated than this, but in the beginning we stick to this concept:
- Fact – a column (or expression, or function …) from a table
- Metric – an aggregate function of a fact at a certain level
A metric always has a level: when we want to measure numbers like sales for example, we usually consider them as a total per year, per region, per customer, or any other dimension. To put it another way, the list of attributes in a report is called the Report Level. A grid with the product, month, and number of sold items has a Report Level of products and month. If I change the month with year and add customer, I have a Report Level of product, year, and customer. Metrics are computed by default at the Report Level (which is represented, in the metric formula, by {~}); however, we'll see later, specific metrics can be calculated at a level that is different from the default.
Moreover, we can have several metrics based on the same fact with different aggregation functions: MAX(SalesAmount), AVG(SalesAmount), and so on. Facts are schema objects, while metrics are application (public) objects; that is, schema must be updated after we create or change a fact, but it's not necessary to do so after modifications in metrics.
In the following recipes we will create both facts and metrics, and see their effect on reports.
Note
One more thing: we cannot use a fact directly in a report; we always need to wrap the fact into a metric in order to display it on a grid or a graph. Sounds weird? Well, think that we are issuing a SELECT query with a GROUP BY clause, so every field that is not in the GROUP BY should be aggregated someway. It will be easier to see as we read on.