Dynamic TI queries

  • Jun. 13, 2011

Introduction

In TI processes, there are several ways to use dynamic queries. With this I mean parameterizing the query that attacks a database like SQL Server, Oracle, even Access files. After listing the 3 most important and relatively easy ways, I discuss them.

  1. Make the query larger than needed, encompassing all needed ‘scenario‘s’. The Data tab will have Itemskip statements.
  2. Using parameters in the query in the Data source tab
  3. Setting up the query in the Data source tab

Pros and cons

Solution 1 is obviously the simplest of the 3 but not the most efficient. Say that your TI process needs to load monthly data for 2011. You could have a query to import all records for 2011, and in the Advanced > Data tab you Itemskip lines that do not match the chosen month.

Solution 2 is more interesting. The parameter containing the month to be loaded, can be used in the query specification:

ParameterTypeDefault ValuePrompt Question
pMonthString2011-05Which month do you want to import ?

The query could look like this in its simplest form: 

SELECT *
FROM DWH.ACTUALS
WHERE month_code='?pMonth?'

You observe that we substitute the (String) parameter pMonth into the SQL command. We enclosed it with question marks. This approach is not complicated yet it can reduce the returned number of records in a great way. We attack the relational tables less, we need to transfer less records, and the Metadata and Data tabs of the process will be finished earlier.

I must admit, to many TM1 users the above is not very new. Maybe what comes now is new, because it is supported only lately. Solution 3 starts from the fact that data sources in TM1 are only dealt with AFTER the Advanced > Prolog tab and BEFORE the Advanced > Metadata tab. Therefore, the Prolog tab can still be used to set up the query using information from the user (parameters) and/or TM1 (all TI and rules functions). Now that‘s interesting!

Extending the solution

For instance, we want to have data only for elements in a certain dimension (for instance, years, or the children of a user-specified consolidated value, or data specified in a lookup cube): 

# Wim Gielis
# https://www.wimgielis.com


#####
# TI code to piece together a dynamic SQL query
# 13/06/11
#####

sSQL = 'SELECT * FROM DWH.ACTUALS WHERE type IN (';

i=1;
WHILE(i<=DIMSIZ('Type'));
   sSQL = sSQL | '''' | DIMNM('Type',i) | ''',';
   i=i+1;
END;

sSQL = SUBST(sSQL,1,LONG(sSQL)-1) | ')';

DatasourceQuery=sSQL;

The query statement will now be pieced together at run-time: elements from the dimension Type will be used to filter in the SQL statement. The query is truely dynamic: the TI local variable DatasourceQuery sets the SQL query at run-time. This has the added advantage that the SQL query can be kept very small in terms of generated records (to not ask more resources than necessary and to ensure the TI process opens and saves fast). In the end, this query will be different from the one that is gathering the data.

The bottom line

The basic take-away from this solution 3 is that the Prolog tab of a TI process can be used to alter or set the Data source in a dynamic way. We could do it for other data sources, but lately SQL statements were added to that list (around TM1 9.4.1 FP3 I think). Make sure that all times the Variables tab is not compromised! Other useful information can be found in this article.

I am sure the reader will be able to find other uses of the techniques in this article.




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links