- Business Intelligence with MicroStrategy Cookbook
- Davide Moraschi
- 434字
- 2021-07-23 15:32:05
Adding more facts
Now we want to complicate things a little more so we need more data: OrderQuantity
, TotalProductCost
, SalesAmount
, TaxAmt
, and Freight
from FactResellerSales
. It is another fact table that we did not include in previous recipes, so we are adding it now.
Getting ready
From the Schema menu, open the Warehouse Catalog window and add the FactResellerSales table from the left list to the right Table being used in the project list. Then click on Save and Close.
How to do it...
- Go to Schema Objects | Facts and create a new fact.
- In the Create New Fact Expression dialog, select FactResellerSales from the Source table dropdown.
- Drag OrderQuantity from the Available columns list to the Fact expression text area.
- Very important: set Mapping method to Manual and click on OK.
- In the Fact Editor, check FactResellerSales in the Source tables list.
- Click on Save and Close and name it
OrderQuantity from FactResellerSales
. - Repeat steps 1 to 6 and create similar facts with the following columns:
TotalProductCost
SalesAmount
TaxAmt
Freight
- Name every fact with the column name +
from FactResellerSales
. - Create one last new fact, select FactResellerSales as table, but this time in the Fact expression type:
SalesAmount + TaxAmt + Freight
- Set Mapping method to Manual and click on OK.
- Save it as
TotalPaid from FactResellerSales
. - Update the schema.
How it works...
We can include calculations inside facts, for example we add the values of three columns to compute how much the customer paid for a specific product, including taxes and shipping.
There's more...
You can also use functions with columns, like Round2(DiscountAmount, 4)
to return a specified number of digits after the decimal separator.
Exercise 16
Create a fact named ProductMargin from FactResellerSales
using this formula:
SalesAmount - TotalProductCost
And now update the schema.
Exercise 17
Create the following metrics:
Sum OrderQuantity from FactResellerSales
Sum TotalProductCost from FactResellerSales
Sum SalesAmount from FactResellerSales
Sum TaxAmt from FactResellerSales
Sum Freight from FactResellerSales
Sum TotalPaid from FactResellerSales
Sum ProductMargin from FactResellerSales
Since metrics are not schema objects there is no need to update the schema.
Exercise 18
Create a report with all the metrics you just created, go to SQL View and verify the SQL sentence. It should look like:
select sum(a11.OrderQuantity) WJXBFS1, sum(a11.TotalProductCost) WJXBFS2, sum(a11.SalesAmount) WJXBFS3, sum(a11.TaxAmt) WJXBFS4, sum(a11.Freight) WJXBFS5, sum(((a11.SalesAmount + a11.TaxAmt) + a11.Freight)) WJXBFS6, sum((a11.SalesAmount - a11.TotalProductCost)) WJXBFS7 from FactResellerSales a11
And the numbers should look like:

Hint: right-click on the header cell named Metrics, select Move | To Rows to pivot.
Save this report as 08 Multiple Metrics from FactResellerSales
.
- 自愿審計動機與質量研究:基于我國中期財務報告審計的經驗證據
- 審計學基礎
- 國有企業經濟責任審計實務指南
- 審計學
- 金融保險集團內部審計創新與實踐
- 注冊會計師全國統一考試專用教材:審計
- Learning Microsoft Azure
- 大數據搜索與挖掘及可視化管理方案 :Elastic Stack 5:Elasticsearch、Logstash、Kibana、X-Pack、Beats (第3版)
- 非線性經濟關系的建模
- OAuth 2.0 Identity and Access Management Patterns
- Salesforce Essentials for Administrators
- 新編統計學
- Microsoft Dynamics NAV
- 陜西國家統計調查市、縣優秀報告集萃(2006—2015)(上下)
- 內部控制審計功能與質量