Before we play with sales figures, we need to know some basic information: how many records are we talking about? How many rows should I expect to be returned from a fact table?
I think it's useful to create a shortcut on the Windows desktop to the sqlcmd utility, so that we can easily open the command-line SQL interface to verify the correctness of the statements generated by MicroStrategy.
This is the one liner that I use (should be written on a single line):
C:WindowsSystem32cmd.exe /c sqlcmd -S (localdb)v11.0 -d AdventureWorksDW2008R2
So, whenever I click on the shortcut I jump directly into the data warehouse.
Once you get in please run this:
select count(1) from FactInternetSales go
This will show the real number of rows contained in the fact table (FactInternetSales) that we are going to use. And this:
select sum(SalesAmount) from FactInternetSales go
This will return the total sales of all-time on the Internet channel. It's important to note down these numbers, 60398 and 29358677.2207 respectively, as we will use them as a confirmation that everything is going well.
Open the desktop application and create the counter fact:
- Click on the Schema Objects | Facts folder. Right-click on the right pane and select New | Fact.
- You are presented with the Fact Editor, which is very similar but simpler than the Attribute Editor. We select FactInternetSales from the Source table dropdown on the left and see all the columns in that table.
- This time instead of choosing a field we simply type
1into the Fact Expression textbox. This 1 will be our first fact. - Set Mapping method to Manual and click on OK.
- Check FactInternetSales in the Source tables list.
- Click on Save and Close.
- Name it
One from FactInternetSalesand click on Save. - Now go to Public Objects | Metrics folder; right-click on the right pane and select New | Metric.
- Leave Empty Metric selected and click on OK.
- You're now inside the Metric Editor. We have an Object Browser part on the left and the metric definition on the right. In Object Browser there is only one fact: One from FactInternetSales, which we just created. Click-and-drag it to the text area where it says Enter your formula here.
- Now in the text area select the function
Sumand change it toCount, so that the resulting formula is:Count([One from FactInternetSales]) {~} - Click on Save and Close and name it
Count One from FactInternetSales. - Update the schema.
- Go to My Personal Objects | My Reports. Create a new report by right-clicking on the right pane and selecting New | Report from the context menu.
- Leave Blank Report selected and click on OK.
- When the Report Editor opens, go to the left My Shortcut pane and click on Public Objects.
- The Object Browser list will display a series of folders: double-click on Metrics.
- Double-click on Count One from FactInternetSales, the Report View area will show the header of the metric in the grid.
- Run the report with the View | Grid View menu.
- After few moments the report will show the result.
- If it matches the number of rows we measured at the beginning, then we're ok, else go to step 1.
- Click on Save and Close and name it
03 FactInternetSales row Count.
In our first example the fact is a constant: the number 1. This way we are telling MicroStrategy to SELECT 1 from the FactInternetSales table. Because the table has 60398 rows we would retrieve 60398 times the number 1; but we said that we cannot put a fact directly into a report, we need to create a metric to aggregate that number to a meaningful total.
So, we create a metric using the fact and the aggregate function Count(), in order to get a total number of rows from the FactInternetSales table. This is a very simple query, useful to verify the size of the table, and to familiarize with the way the SQL syntax is generated.
In this particular case, the aggregate functions Sum() and Count() would have returned the same result:
select count(1) from FactInternetSales
would be the same as:
select sum(1) from FactInternetSales
I personally prefer using Count() because it is conceptually more appropriate.
Now do the same with a real fact. Pick the column SalesAmount and create the fact named SalesAmount from FactInternetSales first, then build a metric with the SalesAmount from FactInternetSales fact aggregated using the function Sum(). Name the metric Sum SalesAmount from FactInternetSales.