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.
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.
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:
0
(zero) in the Value textbox.ProductMargin less than zero
.09 Non profitable Products
.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.
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.