(YTD) transformations without using transformation tables in MicroStrategy

How to create Year to Date (YTD) transformations without using transformation tables in MicroStrategy SQL Generation Engine

Topics:Analytics - U225Schema Objects - U067Metrics - U060SQL Generation - U080

This article describes how to create transformations without using database transformation tables in MicroStrategy

Apr 6, 2017•KB Article

Content

In MicroStrategy SQL Generation Engine, when simple Period to Date (PTD) reports are needed, there is no need to create a transformation table. Using a combination of filters and metric dimensionality, the same results can be achieved.

 

The following example uses MicroStrategy Tutorial to explain the construction of a Year-To-Date metric.

 

OBJECTS:

Name:	DT
Type:	Prompt on value (date)
Definition:

Name:	F1
Type:	Filter
Definition:	Part 1: Date@ID = DT AND Part 2: <;DATE@ID=?DT> {YEAR}

Name:	Sales
Type:	Metric
Definition:	Sum(dollar sales) {~+}

Name:	R1
Type:	Report
Definition:

IMPORTANT NOTE: No YTD transformation is used in this solution.

 

The Engine generates the following Structured Query Language (SQL):

select a14.CUST_REGION_ID CUST_REGION_ID,
    max(a16.CUST_REGION_NAME) CUST_REGION_NAME,
    sum(a11.ORDER_AMT) SALES
from ORDER_FACT a11,
    LU_ORDER a12,
    LU_CUSTOMER a13,
    LU_CUST_CITY a14,
    LU_DATE a15,
    LU_CUST_REGION a16
where a11.ORDER_ID = a12.ORDER_ID and
    a12.CUSTOMER_ID = a13.CUSTOMER_ID and
    a13.CUST_CITY_ID = a14.CUST_CITY_ID and
    a11.ORDER_DATE = a15.DATE_ID and
    a14.CUST_REGION_ID = a16.CUST_REGION_ID
  and (a11.ORDER_DATE <= '09/23/1999'
  and ((a15.YEAR_ID)
  in (select r11.YEAR_ID
    from LU_DATE r11
    where r11.DATE_ID = '09/23/1999')))
group by a14.CUST_REGION_ID

 

This is a very simplified SQL that will aggregate the sales for all the Dates in the year that are less or equal to the selected Date. These filters can be placed into the metric if a report is desired that has some metrics with this date range filter and some without.

 

Note: Because, in this example, the query must look up the year corresponding to the date chosen in the prompt, the date lookup tables must include the chosen date. If users will expect to choose dates in the future, date lookups must be populated into the future.