KB484788: How to create transformation metrics using data wrangling when importing data

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Description

Transformation metrics are useful for calculating metric values for different time periods (e.g., Last Year, Last Month, and Last Week).
When importing data, the transformation objects are not available. However, it is possible to replicate the same behavior of a transformation object using data wrangling.
Here is the general principle:

• Import your table with a date attribute. You get all the attributes and metrics as usual.
• Re-import your table again in the same MTDI cube.
• Wrangle the new table to
  
• Change the metric names (e.g., append “_LY”).
• Modify the date to add 1 year (as to simulate the Last Year value).
• Save and create a dossier.
• You should have Day and your metrics (e.g., Revenue, Revenue_LY) so that you can do all the necessary calculations.

The “add 1 year” step is not something that is directly available out of the box. However, it can be achieved by importing the correct script in the wrangling interface.
The following are a few example scripts:

• Add Months to a date
• Add Days to a date
• Add Value to a number
• Create a new column with static text
• Create a new column with static number
• Create a new column with row index
• Create a new column to have the HTML image tag from a column having the file name
• Create a column with simple IF test

The Data Wrangling engine is based on GREL Open Refine. For more information on generating scripts, see KB484386: Data wrangling using GREL generator.

Steps for creating transformation metrics

• Import your file containing a date attribute.
• Click on the Add new table icon. Re-import the same table.
  

• Change the name of the new table to a meaningful name, e.g., Fact LY.
  

• Click on the Wrangle button.
• Modify the names of the metrics to meaningful names, e.g., Revenue LY.
  

• Import the correct script, e.g, MSTR Grel Add Month.txt.
  

• Modify the script to match you data. Update the columnName for the date and change the value to 12 months.
  

• Click Apply.
• Perform the same steps above if you need other periods, e.g., Last Week or Last Month.
  

• Go and create a dossier. You can easily manipulate the different metrics or create calculations.