- 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
.
- 資本的眼睛
- Magento 2 Cookbook
- 讓財報說話:世界500強CFO帶你輕松讀財報(鮮讀版)
- 博弈論及其在經濟管理中的應用
- 國家治理能力視角的國家審計功能理論研究
- Metabase Up and Running
- Big Data Visualization
- AO2011實用手冊
- 2016年度注冊會計師全國統一考試專用輔導教材(圖解版):審計
- 成功通過PMP(第3版)
- Getting Started with Oracle Tuxedo
- 計量經濟學理論與應用:基于Eviews的應用分析
- Microsoft SharePoint 2010 Developer’s Compendium:The Best of Packt for Extending SharePoint
- 審計實務
- Oracle Enterprise Manager 12c Administration Cookbook