Business Intelligence with MicroStrategy Cookbook
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

Creating a simple counter fact and metric

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?

Getting ready

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:\Windows\System32\cmd.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.

How to do it...

Open the desktop application and create the counter fact:

  1. Click on the Schema Objects | Facts folder. Right-click on the right pane and select New | Fact.
  2. 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.
  3. This time instead of choosing a field we simply type 1 into the Fact Expression textbox. This 1 will be our first fact.
  4. Set Mapping method to Manual and click on OK.
  5. Check FactInternetSales in the Source tables list.
  6. Click on Save and Close.
  7. Name it One from FactInternetSales and click on Save.
  8. Now go to Public Objects | Metrics folder; right-click on the right pane and select New | Metric.
  9. Leave Empty Metric selected and click on OK.
  10. 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.
  11. Now in the text area select the function Sum and change it to Count, so that the resulting formula is: 
    Count([One from FactInternetSales]) {~}
  12. Click on Save and Close and name it Count One from FactInternetSales.
  13. Update the schema.
  14. 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.
  15. Leave Blank Report selected and click on OK.
  16. When the Report Editor opens, go to the left My Shortcut pane and click on Public Objects.
  17. The Object Browser list will display a series of folders: double-click on Metrics.
  18. Double-click on Count One from FactInternetSales, the Report View area will show the header of the metric in the grid.
  19. Run the report with the View | Grid View menu.
  20. After few moments the report will show the result.
  21. If it matches the number of rows we measured at the beginning, then we're ok, else go to step 1.
  22. Click on Save and Close and name it 03 FactInternetSales row Count.

How it works...

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.

There's more...

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.

Note

You can watch a screencast of this operation at:

Exercise 12

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.

Exercise 13

Did you remember to update the schema?

In a new report, drag the metric you just created and view the result. Is it what you expected? Don't worry about the decimals, we'll deal with them later.

Save the report as 04 FactInternetSales Total SalesAmount.

本周热推：
审计学Minitab Cookbook统计分析与数据解读非线性经济时间序列建模内部审计与高校治理
上一章目录下一章