In BI, when we analyze numbers at a deeper level or at a higher level, we speak about drilling. Going deeper is referred to as drill down and vice versa is called drill up. Sometimes, you may also find the term drill across, which refers to a change in the point of view by moving the perspective to a different dimension (or hierarchy).
MicroStrategy uses hierarchies to drill into data. In Chapter 3, Schema Objects – Attributes, we learned how to create a hierarchy from several related attributes: these are called user hierarchies . In addition to those, there is a default system hierarchy that is created automatically with all the attributes in a project.
So, for example, using the time hierarchy we created before, users can drill down from year to month and month to date. It is a very useful feature and users generally like it; as always, a little training can be useful to not get lost in the map.
Let's start by creating a new report with the Product Category attribute and the Sum SalesAmount from FactResellerSales metric.
Once you have the report in Grid View, follow these steps to drill down:
- Position the cursor on the Product Category header; you'll see it changing shape to a cross-arrow pointer.
- Right-click and select Drill from the context menu that appears, then select Down from the nested Drill menu, now click on Product Subcategory.
- A new report is created automatically with the same metric and the Product Subcategory attribute.
- Now repeat the steps by right-clicking on the Product Subcategory header, Drill | Down | Product.
- Another report opens with the Metric and the Product attribute.
- Close the two new reports without saving them and go back to the original one with Product Category.
- Now click on the category element named Components, see that the entire row is selected.
- Right-click on Components and, from the context menu, drill down to Product Subcategory.
- This new report now shows fewer rows than before: only the subcategories belonging to Components are displayed.
- Now right-click on Handlebars and drill down to Product.
- Only those products that belong to handlebars subcategory are displayed.
- Close the two new reports without saving them.
- In the original Product Category report, select Bikes.
- From the Data menu, click on Drill and the Drill dialog box appears.
- Unfold Down in the Drilling options tree, and click on Product Subcategory.
- In the Keep parent combobox, select Yes and hit OK.
- In the new report that appears, both Product Category and Product Subcategory are shown. Select Mountain Bikes.
- From the Data | Drill menu, select Down to Product and set Keep parent combobox to Yes.
- In the resulting report, there are now three attributes. You can close all those reports without saving them.
When drilling, it is very important which element is selected: if we drill from an attribute header, we drill to a new complete report, whereas, if we drill from a specific element of the attribute (row), we are silently applying a filter on that element so that only children of the selected row will be displayed.
By enabling the Keep parent feature, the new report will maintain the original drilled-from attribute plus the drilled-to one.
Similarly, you can drill up from a lower-level attribute to its parent. Be careful: the filters are kept from one report to the other when moving up and down repeatedly and you may end up with inconsistent numbers.
Try the drill across:
- From the same report with the Product Category attribute and the Sum SalesAmount from FactResellerSales metric, select the Bikes row and right-click on it.
- From the context menu, select Drill | Other directions | Time | Year, as shown in the following screen capture:
- You will get a new report with the sales amount by year filtered by the Bikes category:
