KB31853: How to create a transformation metric with no aggregation in MicroStrategy Developer 9.x

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:

KB7238: Creating transformation, rank and percent to total metrics in MicroStrategy Developer

Take the following Month to Day table as an example:

Transformation table 'MTD_DAY':

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

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

• 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'

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

1. • 
     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...
2. Create a filter to select 'Day' element at the runtime
3. Create a report with attributes 'Day', 'MTD' and metrics 'Day Revenue', 'MTD Revenue', also adding filter into the report
4. Execute the report with grid view, the report displays like this with the prompt answer '1/5/2006'