开元财神棋牌

書名： 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.

Note

You can watch a screencast of this operation at:

http://at5.us/Ch4V4

官术网_书友最值得收藏!

Business Intelligence with MicroStrategy Cookbook