TM1 users love drill through to transactions capabilty.
The success of its deployment though will depend upon how fast a drill through will return transactions.
Developers should look to maximise the efficiency of their sql statements and the best way to do this is to utilise the indexes of the tables you are querying.
For example I changed a SQL drill through statement that took 8 minutes sometimes and got it down to 8 seconds, just by making sure the sql queried the indexes of the tables.
Usually transactional journal tables will be indexed on their journal dates, so if you include this in your where clause the sql will work a lot faster.
Normally your TM1 GL cube will only contain balances per month and your transactions will need to be queried by their transaction date. Within your TI drill process you can easily take your month and convert it into a sql date between criteria.
Within the prolog of your TI drill process you will need code similar to this that will take the month, and depending on your financial year, convert to a calendar date string.
IF(NUMBR(AttrS('Period', Period, 'Period No')) <= 6);
vStartDate = '01-' | Period | '-' | STR(NUMBR(Fiscal_Year) - 1,4,0);
ELSE;
vStartDate = '01-' | Period | '-' | Fiscal_Year;
ENDIF;
In the sql of your TI process you can then include this vStartDate parameter within your where clause. Use add_months to add a day and then -1 off it to get the last day of the month.
WHERE a.Journal_Date between to_date('?vStartDate?','DD-MON-YYYY') and add_months(to_date('?vStartDate?','DD-MON-YYYY'), 1) - 1
This means that if a user selected drill on a May 2007 cell, the TI would create a variable vStartDate of '01-May-2007' and then create a sql that includes the where clause of
WHERE a.Journal_Date between to_date('01-May-2007','DD-MON-YYYY') and add_months('01-May-2007','DD-MON-YYYY'), 1) - 1
This sql is based on pl/sql so for Microsoft sql it may be slightly different syntax.
