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

• Strategy

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

In Strategy 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 Strategy Tutorial to explain the construction of a Year-To-Date metric.

OBJECTS:

Unable to connect to the service using a secure TLS channel.The HTTPS connection between Strategy Library and the Modeling service is not configured correctly. For steps on configuring HTTPS connection, please refer to Configure HTTPS Connection Between Library Server and Modeling Service. The TLS/SSL between the Strategy Modeling service and Strategy Intelligence server is not configured correctly due to the invalid trust store path: The path provided was not valid, please double-check that it exists. The TLS/SSL connection between the Modeling service and the Intelligence server is not configured correctly. For steps on configuring TLS enabled between the Modeling service and the Intelligence server, please refer to Configure Modeling Service When Intelligence Server is TLS Enabled. An error occurs when opening a fact object via the Workstation Fact editor: "Model Server internal error: Invalid argument: The requested fact does not contain the requested expression." Or "Model Server internal error: No value present."This is a known defect that is being investigated by the Technology team. Please use Strategy Developer as a workaround. See KB484768. sales_rep_idsales_rep_namedistrict_ideff_dtend_dt1Jones371/1/190012/31/20032Smith371/1/190012/31/20993Kelly381/1/19006/30/20044Madison381/1/190012/31/20991Jones391/1/200412/31/20993Kelly397/1/200412/31/2099sales_rep_idtrans_dtsales19/1/200310029/10/200320039/15/200315013/1/200420023/10/200425033/15/200430029/5/200412539/15/200427549/20/2004150Sales Rep@ID = sales_rep_id; @Desc = sales_rep_name Tables: LU_SALES_REP (lookup), LVW_CURRENT_ORG, FACT_TABLECurrent District@ID = district_id; @Desc = district_name Tables: LU_CURRENT_DISTRICT (lookup), LVW_CURRENT_ORG Child: Sales RepHistorical District@ID = district_id; @Desc = district_name Tables: LU_DISTRICT (lookup) , LU_SALES_REP, LVW_HIST_DISTRICT_SALES Child: Sales RepDate@ID = date_id, trans_dt Tables: LU_TIME (lookup) , FACT_TABLE, LVW_HIST_DISTRICT_SALESMonth@ID = MONTH_ID Tables: LU_TIME (lookup) Child: DateSalesExpr: sales Tables: FACT_TABLE, LVW_HIST_DISTRICT_SALESSalesSales: SUM(sales)sales_rep_cdsales_rep_idsales_rep_namedistrict_idcurrent_flag11Jones37022Smith37133Kelly38044Madison38151Jones39163Kelly391sales_rep_cdSales110022003150520022503300212562754150Sales Rep Surrogate@ID = sales_rep_cd Tables: LU_SALES_REP (lookup), FACT_TABLESales Rep@ID = sales_rep_id; @Desc = sales_rep_name Tables: LU_SALES_REP (lookup), LVW_CURRENT_ORG Child: Sales Rep SurrogateCurrent District@ID = district_id; @Desc = district_name Tables: LU_CURRENT_DISTRICT (lookup), LVW_CURRENT_ORG Child: Sales RepHistorical District@ID = district_id; @Desc = district_name Tables: LU_DISTRICT (lookup), LU_SALES_REP Child: Sales RepDate@ID = date_id, trans_dt Tables: LU_TIME (lookup) , FACT_TABLEMonth@ID = MONTH_ID Tables: LU_TIME (lookup) Child: DateSalesExpr: sales Tables: FACT_TABLE, LVW_HIST_DISTRICT_SALESSalesSUM(sales)Name:DTType:Prompt on value (date)Definition:	Unable to connect to the service using a secure TLS channel.The HTTPS connection between Strategy Library and the Modeling service is not configured correctly. For steps on configuring HTTPS connection, please refer to Configure HTTPS Connection Between Library Server and Modeling Service. The TLS/SSL between the Strategy Modeling service and Strategy Intelligence server is not configured correctly due to the invalid trust store path: The path provided was not valid, please double-check that it exists. The TLS/SSL connection between the Modeling service and the Intelligence server is not configured correctly. For steps on configuring TLS enabled between the Modeling service and the Intelligence server, please refer to Configure Modeling Service When Intelligence Server is TLS Enabled. An error occurs when opening a fact object via the Workstation Fact editor: "Model Server internal error: Invalid argument: The requested fact does not contain the requested expression." Or "Model Server internal error: No value present."This is a known defect that is being investigated by the Technology team. Please use Strategy Developer as a workaround. See KB484768. sales_rep_idsales_rep_namedistrict_ideff_dtend_dt1Jones371/1/190012/31/20032Smith371/1/190012/31/20993Kelly381/1/19006/30/20044Madison381/1/190012/31/20991Jones391/1/200412/31/20993Kelly397/1/200412/31/2099sales_rep_idtrans_dtsales19/1/200310029/10/200320039/15/200315013/1/200420023/10/200425033/15/200430029/5/200412539/15/200427549/20/2004150Sales Rep@ID = sales_rep_id; @Desc = sales_rep_name Tables: LU_SALES_REP (lookup), LVW_CURRENT_ORG, FACT_TABLECurrent District@ID = district_id; @Desc = district_name Tables: LU_CURRENT_DISTRICT (lookup), LVW_CURRENT_ORG Child: Sales RepHistorical District@ID = district_id; @Desc = district_name Tables: LU_DISTRICT (lookup) , LU_SALES_REP, LVW_HIST_DISTRICT_SALES Child: Sales RepDate@ID = date_id, trans_dt Tables: LU_TIME (lookup) , FACT_TABLE, LVW_HIST_DISTRICT_SALESMonth@ID = MONTH_ID Tables: LU_TIME (lookup) Child: DateSalesExpr: sales Tables: FACT_TABLE, LVW_HIST_DISTRICT_SALESSalesSales: SUM(sales)sales_rep_cdsales_rep_idsales_rep_namedistrict_idcurrent_flag11Jones37022Smith37133Kelly38044Madison38151Jones39163Kelly391sales_rep_cdSales110022003150520022503300212562754150Sales Rep Surrogate@ID = sales_rep_cd Tables: LU_SALES_REP (lookup), FACT_TABLESales Rep@ID = sales_rep_id; @Desc = sales_rep_name Tables: LU_SALES_REP (lookup), LVW_CURRENT_ORG Child: Sales Rep SurrogateCurrent District@ID = district_id; @Desc = district_name Tables: LU_CURRENT_DISTRICT (lookup), LVW_CURRENT_ORG Child: Sales RepHistorical District@ID = district_id; @Desc = district_name Tables: LU_DISTRICT (lookup), LU_SALES_REP Child: Sales RepDate@ID = date_id, trans_dt Tables: LU_TIME (lookup) , FACT_TABLEMonth@ID = MONTH_ID Tables: LU_TIME (lookup) Child: DateSalesExpr: sales Tables: FACT_TABLE, LVW_HIST_DISTRICT_SALESSalesSUM(sales)Name:DTType:Prompt on value (date)Definition:
Unable to connect to the service using a secure TLS channel.The HTTPS connection between Strategy Library and the Modeling service is not configured correctly. For steps on configuring HTTPS connection, please refer to Configure HTTPS Connection Between Library Server and Modeling Service. The TLS/SSL between the Strategy Modeling service and Strategy Intelligence server is not configured correctly due to the invalid trust store path: The path provided was not valid, please double-check that it exists. The TLS/SSL connection between the Modeling service and the Intelligence server is not configured correctly. For steps on configuring TLS enabled between the Modeling service and the Intelligence server, please refer to Configure Modeling Service When Intelligence Server is TLS Enabled. An error occurs when opening a fact object via the Workstation Fact editor: "Model Server internal error: Invalid argument: The requested fact does not contain the requested expression." Or "Model Server internal error: No value present."This is a known defect that is being investigated by the Technology team. Please use Strategy Developer as a workaround. See KB484768. sales_rep_idsales_rep_namedistrict_ideff_dtend_dt1Jones371/1/190012/31/20032Smith371/1/190012/31/20993Kelly381/1/19006/30/20044Madison381/1/190012/31/20991Jones391/1/200412/31/20993Kelly397/1/200412/31/2099sales_rep_idtrans_dtsales19/1/200310029/10/200320039/15/200315013/1/200420023/10/200425033/15/200430029/5/200412539/15/200427549/20/2004150Sales Rep@ID = sales_rep_id; @Desc = sales_rep_name Tables: LU_SALES_REP (lookup), LVW_CURRENT_ORG, FACT_TABLECurrent District@ID = district_id; @Desc = district_name Tables: LU_CURRENT_DISTRICT (lookup), LVW_CURRENT_ORG Child: Sales RepHistorical District@ID = district_id; @Desc = district_name Tables: LU_DISTRICT (lookup) , LU_SALES_REP, LVW_HIST_DISTRICT_SALES Child: Sales RepDate@ID = date_id, trans_dt Tables: LU_TIME (lookup) , FACT_TABLE, LVW_HIST_DISTRICT_SALESMonth@ID = MONTH_ID Tables: LU_TIME (lookup) Child: DateSalesExpr: sales Tables: FACT_TABLE, LVW_HIST_DISTRICT_SALESSalesSales: SUM(sales)sales_rep_cdsales_rep_idsales_rep_namedistrict_idcurrent_flag11Jones37022Smith37133Kelly38044Madison38151Jones39163Kelly391sales_rep_cdSales110022003150520022503300212562754150Sales Rep Surrogate@ID = sales_rep_cd Tables: LU_SALES_REP (lookup), FACT_TABLESales Rep@ID = sales_rep_id; @Desc = sales_rep_name Tables: LU_SALES_REP (lookup), LVW_CURRENT_ORG Child: Sales Rep SurrogateCurrent District@ID = district_id; @Desc = district_name Tables: LU_CURRENT_DISTRICT (lookup), LVW_CURRENT_ORG Child: Sales RepHistorical District@ID = district_id; @Desc = district_name Tables: LU_DISTRICT (lookup), LU_SALES_REP Child: Sales RepDate@ID = date_id, trans_dt Tables: LU_TIME (lookup) , FACT_TABLEMonth@ID = MONTH_ID Tables: LU_TIME (lookup) Child: DateSalesExpr: sales Tables: FACT_TABLE, LVW_HIST_DISTRICT_SALESSalesSUM(sales)Name:DTType:Prompt on value (date)Definition:	Unable to connect to the service using a secure TLS channel.The HTTPS connection between Strategy Library and the Modeling service is not configured correctly. For steps on configuring HTTPS connection, please refer to Configure HTTPS Connection Between Library Server and Modeling Service. The TLS/SSL between the Strategy Modeling service and Strategy Intelligence server is not configured correctly due to the invalid trust store path: The path provided was not valid, please double-check that it exists. The TLS/SSL connection between the Modeling service and the Intelligence server is not configured correctly. For steps on configuring TLS enabled between the Modeling service and the Intelligence server, please refer to Configure Modeling Service When Intelligence Server is TLS Enabled. An error occurs when opening a fact object via the Workstation Fact editor: "Model Server internal error: Invalid argument: The requested fact does not contain the requested expression." Or "Model Server internal error: No value present."This is a known defect that is being investigated by the Technology team. Please use Strategy Developer as a workaround. See KB484768. sales_rep_idsales_rep_namedistrict_ideff_dtend_dt1Jones371/1/190012/31/20032Smith371/1/190012/31/20993Kelly381/1/19006/30/20044Madison381/1/190012/31/20991Jones391/1/200412/31/20993Kelly397/1/200412/31/2099sales_rep_idtrans_dtsales19/1/200310029/10/200320039/15/200315013/1/200420023/10/200425033/15/200430029/5/200412539/15/200427549/20/2004150Sales Rep@ID = sales_rep_id; @Desc = sales_rep_name Tables: LU_SALES_REP (lookup), LVW_CURRENT_ORG, FACT_TABLECurrent District@ID = district_id; @Desc = district_name Tables: LU_CURRENT_DISTRICT (lookup), LVW_CURRENT_ORG Child: Sales RepHistorical District@ID = district_id; @Desc = district_name Tables: LU_DISTRICT (lookup) , LU_SALES_REP, LVW_HIST_DISTRICT_SALES Child: Sales RepDate@ID = date_id, trans_dt Tables: LU_TIME (lookup) , FACT_TABLE, LVW_HIST_DISTRICT_SALESMonth@ID = MONTH_ID Tables: LU_TIME (lookup) Child: DateSalesExpr: sales Tables: FACT_TABLE, LVW_HIST_DISTRICT_SALESSalesSales: SUM(sales)sales_rep_cdsales_rep_idsales_rep_namedistrict_idcurrent_flag11Jones37022Smith37133Kelly38044Madison38151Jones39163Kelly391sales_rep_cdSales110022003150520022503300212562754150Sales Rep Surrogate@ID = sales_rep_cd Tables: LU_SALES_REP (lookup), FACT_TABLESales Rep@ID = sales_rep_id; @Desc = sales_rep_name Tables: LU_SALES_REP (lookup), LVW_CURRENT_ORG Child: Sales Rep SurrogateCurrent District@ID = district_id; @Desc = district_name Tables: LU_CURRENT_DISTRICT (lookup), LVW_CURRENT_ORG Child: Sales RepHistorical District@ID = district_id; @Desc = district_name Tables: LU_DISTRICT (lookup), LU_SALES_REP Child: Sales RepDate@ID = date_id, trans_dt Tables: LU_TIME (lookup) , FACT_TABLEMonth@ID = MONTH_ID Tables: LU_TIME (lookup) Child: DateSalesExpr: sales Tables: FACT_TABLE, LVW_HIST_DISTRICT_SALESSalesSUM(sales)Name:DTType:Prompt on value (date)Definition:
Unable to connect to the service using a secure TLS channel.The HTTPS connection between Strategy Library and the Modeling service is not configured correctly. For steps on configuring HTTPS connection, please refer to Configure HTTPS Connection Between Library Server and Modeling Service. The TLS/SSL between the Strategy Modeling service and Strategy Intelligence server is not configured correctly due to the invalid trust store path: The path provided was not valid, please double-check that it exists. The TLS/SSL connection between the Modeling service and the Intelligence server is not configured correctly. For steps on configuring TLS enabled between the Modeling service and the Intelligence server, please refer to Configure Modeling Service When Intelligence Server is TLS Enabled. An error occurs when opening a fact object via the Workstation Fact editor: "Model Server internal error: Invalid argument: The requested fact does not contain the requested expression." Or "Model Server internal error: No value present."This is a known defect that is being investigated by the Technology team. Please use Strategy Developer as a workaround. See KB484768. sales_rep_idsales_rep_namedistrict_ideff_dtend_dt1Jones371/1/190012/31/20032Smith371/1/190012/31/20993Kelly381/1/19006/30/20044Madison381/1/190012/31/20991Jones391/1/200412/31/20993Kelly397/1/200412/31/2099sales_rep_idtrans_dtsales19/1/200310029/10/200320039/15/200315013/1/200420023/10/200425033/15/200430029/5/200412539/15/200427549/20/2004150Sales Rep@ID = sales_rep_id; @Desc = sales_rep_name Tables: LU_SALES_REP (lookup), LVW_CURRENT_ORG, FACT_TABLECurrent District@ID = district_id; @Desc = district_name Tables: LU_CURRENT_DISTRICT (lookup), LVW_CURRENT_ORG Child: Sales RepHistorical District@ID = district_id; @Desc = district_name Tables: LU_DISTRICT (lookup) , LU_SALES_REP, LVW_HIST_DISTRICT_SALES Child: Sales RepDate@ID = date_id, trans_dt Tables: LU_TIME (lookup) , FACT_TABLE, LVW_HIST_DISTRICT_SALESMonth@ID = MONTH_ID Tables: LU_TIME (lookup) Child: DateSalesExpr: sales Tables: FACT_TABLE, LVW_HIST_DISTRICT_SALESSalesSales: SUM(sales)sales_rep_cdsales_rep_idsales_rep_namedistrict_idcurrent_flag11Jones37022Smith37133Kelly38044Madison38151Jones39163Kelly391sales_rep_cdSales110022003150520022503300212562754150Sales Rep Surrogate@ID = sales_rep_cd Tables: LU_SALES_REP (lookup), FACT_TABLESales Rep@ID = sales_rep_id; @Desc = sales_rep_name Tables: LU_SALES_REP (lookup), LVW_CURRENT_ORG Child: Sales Rep SurrogateCurrent District@ID = district_id; @Desc = district_name Tables: LU_CURRENT_DISTRICT (lookup), LVW_CURRENT_ORG Child: Sales RepHistorical District@ID = district_id; @Desc = district_name Tables: LU_DISTRICT (lookup), LU_SALES_REP Child: Sales RepDate@ID = date_id, trans_dt Tables: LU_TIME (lookup) , FACT_TABLEMonth@ID = MONTH_ID Tables: LU_TIME (lookup) Child: DateSalesExpr: sales Tables: FACT_TABLE, LVW_HIST_DISTRICT_SALESSalesSUM(sales)Name:DTType:Prompt on value (date)Definition:	Name:F1Type:FilterDefinition:Part 1: Date@ID = DT

Name:F1Type:FilterDefinition:Part 1: Date@ID = DT	Name:F1Type:FilterDefinition:Part 1: Date@ID = DT
Name:F1Type:FilterDefinition:Part 1: Date@ID = DT	Name:F1Type:FilterDefinition:Part 1: Date@ID = DT
Name:F1Type:FilterDefinition:Part 1: Date@ID = DT	Part 2: <;DATE@ID=?DT> {YEAR}

Name:SalesType:MetricDefinition:	Name:SalesType:MetricDefinition:
Name:SalesType:MetricDefinition:	Name:SalesType:MetricDefinition:
Name:SalesType:MetricDefinition:	Sum(dollar sales) {~+}

Name:R1Type:ReportDefinition:	Name:R1Type:ReportDefinition:
Name:R1Type:ReportDefinition:	Name:R1Type:ReportDefinition:
Name:R1Type:ReportDefinition:

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.

Comment

0 comments

Details

Knowledge Article

Published:

April 6, 2017

Last Updated:

April 6, 2017

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