MSTR Transformation Metric with no aggreagation

Transformation Metric with no aggreagation in MSTR

 Edit 0 1…

Content
A transformation table is a type of relation table, normally used to create a transformation metric. Refer to the following MicroStrategy Knowledge Base technical note for more information on transformation metrics:

TN 7238 "Creating transformation, rank and percent to total metrics in MicroStrategy Desktop 8.x"


Take the following Month to Day table as an example:

Transformation table 'MTD_DAY':

DAY_DATE	MTD_DAY_DATE
1/1/2006	1/1/2006
1/2/2006	1/1/2006
1/2/2006	1/2/2006
1/3/2006	1/1/2006
1/3/2006	1/2/2006
1/3/2006	1/3/2006
...	...

By default the transformation metric with transformation 'Month to Date' will display like this

Day	MTD_metric
1/1/2006	1/1/2006 metric
1/2/2006	1/1/2006 + 1/2/2006 metric
1/3/2006	1/1/2006 + 1/2/2006 + 1/3/2006 metric

But sometimes, users may want to compare with Day level metric and it's corresponding transformation metric as shown in the following chart:

Day	MTD	Day_metric	MTD_metric
1/1/2006	1/1/2006	1/1/2006 metric	1/1/2006 metric
1/2/2006	1/1/2006	1/2/2006 metric	1/1/2006 metric
	1/2/2006	1/2/2006 metric	1/2/2006 metric
1/3/2006	1/1/2006	1/3/2006 metric	1/1/2006 metric
	1/2/2006	1/3/2006 metric	1/2/2006 metric
	1/3/2006	1/3/2006 metric	1/3/2006 metric

How to display transformation metric without aggregation:

The following steps may be used as a reference. This example uses the MicroStrategy Tutorial project:

1. In MicroStrategy Developer 9.x, create a new attribute 'MTD'
   • Map column 'MTD_DAY_DATE' in the transformation table 'MTD_DAY' and the column 'ORDER_DATE' in the 'ORDER_FACT' and 'ORDER_DETAIL' fact tables
   
   
   
   
   • Add the 'Day' attribute as parent and set the relationship as 'Many to Many'
   
   
   
   
   • Update the schema
2. Create a metric 'Day Revenue' with the 'Revenue' fact
   • Add the 'Day' attribute as level with Filtering and Grouping set to 'Standard' and remove the default 'Report Level' target
3. Create a metric 'MTD Revenue' with fact 'Revenue'
   • Add the 'Month to Date' transformation
   
   
   
   
   • Change the metric join type to 'Outer'
     • Metric Editor -> Tools -> Metric Joint Type...
   • Change the formula join type to 'Outer'
     • Metric Editor -> Advanced Settings -> Fomula Joint Type...
4. Create a filter to select 'Day' element at the runtime
5. Create a report with attributes 'Day', 'MTD' and metrics 'Day Revenue', 'MTD Revenue', also adding filter into the report
6. Execute the report with grid view, the report displays like this with the prompt answer '1/5/2006'

https://community.microstrategy.com/s/article/KB31853-How-to-create-a-transformation-metric-with-no