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...

  1. Go to Schema Objects | Facts and create a new fact.
  2. In the Create New Fact Expression dialog, select FactResellerSales from the Source table dropdown.
  3. Drag OrderQuantity from the Available columns list to the Fact expression text area.
  4. Very important: set Mapping method to Manual and click on OK.
  5. In the Fact Editor, check FactResellerSales in the Source tables list.
  6. Click on Save and Close and name it OrderQuantity from FactResellerSales.
  7. Repeat steps 1 to 6 and create similar facts with the following columns:
    • TotalProductCost
    • SalesAmount
    • TaxAmt
    • Freight
  8. Name every fact with the column name + from FactResellerSales.
  9. Create one last new fact, select FactResellerSales as table, but this time in the Fact expression type:
    SalesAmount + TaxAmt + Freight
  10. Set Mapping method to Manual and click on OK.
  11. Save it as TotalPaid from FactResellerSales.
  12. 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:

Exercise 18

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:

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset