Filters on metrics
In Chapter 3, Schema Objects – Attributes, we learned how to build filters on attributes to restrict the data returned from the data warehouse using a list of elements. Similarly, we can create filters using values of metrics. The difference—as said—is how the SQL sentence will be generated when applying the filter: attribute filters appear in the WHERE clause, while metric filters appear in the HAVING clause. For those of you SQL superstars, I do not need to explain that WHERE restricts before aggregation, while HAVING restricts (of course…) after aggregation.
Getting ready
In the previous recipe we added a new fact table. Before we go on, we should specify the Product FK so that MicroStrategy can JOIN the FactResellerSales table with the Product dimensions. We see from the database ER diagram that FactResellerSales has a ProductKey column that points to the DimProduct table.
So, open the Product attribute (double-click) and modify the ID form to include FactResellerSales as a source table (as in the following screen capture):
Save the attribute and then go to Schema Objects | Tables and open the FactResellerSales table and uncheck The key specified is the true key for the warehouse table. Then click on Save and Close and update the schema.
To introduce this scenario, we first create a basic report with Sum ProductMargin from FactResellerSales grouped by Product Category. No need to save it.
Uh-oh: we're losing money on Bikes…, this won't please the board. This is a typical case where we need to be sure of the numbers and be able to give an explanation to the Product Manager whose temperature is rising.
How to do it...
We decide to create another report showing only the products that have negative profit margin. First, we need a filter on Sum ProductMargin from FactResellerSales:
- We begin by opening the Product attribute to remove the LDESC form from the default display, go to Schema Objects | Attributes, double-click on Product and click on the Display tab. Remove LDESC from Report display forms. Then click on Save and Close.
- Open the folder Public Objects | Filters and right-click on the right pane. Select New | Filter from the context menu. Leave Empty Filter selected and click on OK.
- In the Filter Editor, click on Public Objects shortcut in My shortcut panel, and double-click on the Metrics folder from the list that appears.
- You will see a list of metrics; double-click on Sum ProductMargin from FactResellerSales.
- The Filter definition area is automatically populated with metric qualification. See the Parameters group: Operator is set by default to Exactly. Since we want to see the products with negative profit, we need to select Less than in the Operator combobox and type
0(zero) in the Value textbox. - That's it, click on OK and then on Save and Close. Name the filter
ProductMargin less than zero. - Update the schema (we modified the Product attribute).
- Go to My Personal objects | My Reports, create a new blank report with the Product attribute on rows and Sum ProductMargin from FactResellerSales on columns, and then click on the Public Objects shortcut, double-click on Filters, and right-click on the ProductMargin less than zero filter.
- From the context menu, select Add to Report Filter and switch to Grid View to see the results.
- Here's the list of the 121 products losing money, better do something about it; save the report as
09 Non profitable Products.
How it works...
If you look at the SQL view, you will notice the HAVING sum((a11.SalesAmount - a11.TotalProductCost)) < 0.0 clause. This is the part introduced by the filter on the metric. During the SQL generation process, the filters on attributes and metrics are appended to the SELECT statement sent to the data warehouse.
There's more...
If you right-click on the Sum ProductMargin from FactResellerSales header and select Sort rows by this column | Ascending, you will get an ordered list starting with the worst product Touring-1000 Yellow, 60.