Aggregate data is what reports are made of; speaking ANSI SQL, this means GROUP BY. MicroStrategy applies GROUP BY to all the attributes present on a report grid. Well, this is not completely correct, but we begin with this simple concept:
Every attribute on a grid is reflected in the GROUP BY clause of the resulting SELECT statement.
Hence, we have:
- Metrics that represent the aggregation (sum, count, and so on) of facts
- Attributes
BYwhich weGROUPthe data
I hope this is clear enough, because this concepts will return every now and then with metrics. In this recipe, anyway, we'll see an example of how it works.
We are using now the SalesAmount metric and the Product attribute. Before going on we need to establish a connection between the fact table and the attribute table, so that MicroStrategy knows how to JOIN them.
The connection is represented by the FK (ProductKey) in FactInternetSales. In other words, the ID form of Product is present in both the fact and the dimension tables, so we need to specify that those two columns are the same ID in order to JOIN them in the SQL statements.
First, we modify the Product attribute then we create a new report:
- Go to Schema Objects | Attributes and right-click on Product. From the context menu select Edit.
- You're in the Attribute Editor and the ID form is already selected, click on the Modify button.
- In the modify Attribute Forms dialog, on the right there is a panel with Source tables where we have selected DimProduct and set it as lookup. Now click to select FactInternetSales too, like in the following screen capture, and click on OK:
- Back in the Attribute Editor, click on Save and Close.
- Now go to the folder Schema Objects | Tables and right-click on FactInternetSales, selecting Edit from the context menu.
- You're in the Table Editor: see in the Logical View tab, that the Product attribute is present together with the two facts we created previously.
- Below the list of objects there is a checkbox that says The key specified is the true key for the warehouse table; be sure to uncheck this because ProductKey is not the PK in this table.
- Click on Save and Close, and update the schema.
- Now go to My Personal Objects | My Reports folder, right-click on the right pane and select New | Report from the context menu.
- Leave default Blank Report selected and click on OK.
- Here we are in the Report Editor. First, click on the Attributes icon in My Shortcut panel then double-click on the Product icon in the list that appears: note that the Product attribute is added to the grid.
- Now click on the Public Objects icon in My Shortcut panel and in the list that appears double-click on Metrics to see the list of metrics that we have in the project
- Double-click on the metric named Sum SalesAmount from FactInternetSales, the metric is added to the grid.
- From the View menu select Grid View, and the report shows the results.
- You see the DESC and LDESC forms of the Product attribute and if you scroll to the right there is the column of Sum SalesAmount from FactInternetSales at the Product level.
- We do not need the LDESC form in this report so we remove it. Scroll to the beginning of the grid until you see the header of the Product attribute. Right-click on the cell containing the Product header and from the context menu select Attribute Forms.
- You see that there are three Attribute Forms here: ID, DESC, and LDESC. Uncheck LDESC and leave only DESC checked.
- The LDESC form has disappeared and we have a SalesAmount report grouped by Product.
- Now select the menu View | SQL View to see SQL sentence. There is a
GROUP BYclause with theProductPK, the factSalesAmountis aggregated with theSum()function and grouped by Product. - Go back to Grid View with View | Grid View and click on Save and Close.
- Name the report
06 SalesAmount by Product.
In this case we put a single attribute and a single metric on the report. The metric is calculated at the level of the single attribute: sum(SalesAmount) from FactInternetSales group by ProductKey. Easy to guess, if we put more than one attribute on the grid the GROUP BY clause would contain more columns.
Refer to Chapter 3, Schema Objects – Attributes, for details about how attribute forms are displayed in reports.
Following the steps in this recipe create a new report with SalesAmount by customer.
Remember to modify the Customer attribute in order to include the CustomerKey column from the FactInternetSales table (and update the schema). The first few rows of the report should look like this image:
Save the report as 07 SalesAmount By Customer.