Drill Through Efficiency

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.

TM1 9.1 SP2

The software downloads section of the Applix website has been updated with the latest release of TM1 9.1 SP2 for the 32bit and x64 installs, as of 17/7/2007.

TM1 Copy Pate DBRW

If you want to copy and paste values into TM1 DBRW cells then historically you needed to use SPF (Beachware).

In recent versions of TM1 you are able to copy and paste a single value into a DBRW cell but not a range of cells.

Ben Hill recently posted his vba module that replicates the SPF functionality and I can vouch for its use having used it myself for a good while now.

Check out his download from the TM1 forum:

http://forums.applix.com/forum/tm.asp?m=12606

Zero Out and load last 2 months of data

I recently needed to dynamically load the last 2 months worth of data into TM1.

If anyone was interested I could go into detail of the sql but within TI I had to make sure I deleted the current month and the previous month. The best way to do this was to define to seperate areas of the cube to zero out, the code is shown below. This code is based on a fiscal year of Jul to Jun and will need adjusting to your own fiscal year.

#***********************************************
# Zero out first month
#***********************************************
pYear1 = CAPIT(LOWER(TIMST(Now, '\Y')));
pMonthNo = StringToNumber(TIMST(Now, '\m'));
IF(pMonthNo > 6);
pYear1 = NumberToString(StringToNumber(pYear1) + 1);
ENDIF;
pMonth1 = TIMST(Now, '\M');

vCube = 'Revenue';
vDimName1 = 'Scenario';
vDimName2 = 'Fiscal_Year';
vDimName3 = 'Period';
vTemp = 'SystemUseOnly_Load' | STR(RAND, 10, 8);
vViewName = vTemp;
vSubName = vTemp;

SubsetCreate(vDimName1, vSubName);
SubsetCreate(vDimName2, vSubName);
SubsetCreate(vDimName3, vSubName);
SubsetElementInsert(vDimName1, vSubName, 'ACTUAL', 1);
SubsetElementInsert(vDimName2, vSubName, pYear1, 1);
SubsetElementInsert(vDimName3, vSubName, pMonth1, 1);
ViewCreate(vCube, vViewName);
ViewSubsetAssign(vCube, vViewName, vDimName1, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName2, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName3, vSubName);
ViewZeroOut(vCube, vViewName);
ViewDestroy(vCube, vViewName);
SubsetDestroy(vDimName1, vSubName);
SubsetDestroy(vDimName2, vSubName);
SubsetDestroy(vDimName3, vSubName);

#***********************************************
# Zero out second month
#***********************************************
IF(CAPIT(pMonth1) @= 'JUL');
pYear2 = DIMNM('Fiscal_Year', DIMIX('Fiscal_Year', pYear1) - 1);
pMonth2 = 'JUN';
ELSE;
pYear2 = pYear1;
pMonth2 = DIMNM('Period', DIMIX('Period', pMonth1) - 1);
ENDIF;

vCube = 'Revenue';
vDimName1 = 'Scenario';
vDimName2 = 'Fiscal_Year';
vDimName3 = 'Period';
vTemp = 'SystemUseOnly_Load' | STR(RAND, 10, 8);
vViewName = vTemp;
vSubName = vTemp;

SubsetCreate(vDimName1, vSubName);
SubsetCreate(vDimName2, vSubName);
SubsetCreate(vDimName3, vSubName);
SubsetElementInsert(vDimName1, vSubName, 'ACTUAL', 1);
SubsetElementInsert(vDimName2, vSubName, pYear2, 1);
SubsetElementInsert(vDimName3, vSubName, pMonth2, 1);
ViewCreate(vCube, vViewName);
ViewSubsetAssign(vCube, vViewName, vDimName1, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName2, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName3, vSubName);
ViewZeroOut(vCube, vViewName);
ViewDestroy(vCube, vViewName);
SubsetDestroy(vDimName1, vSubName);
SubsetDestroy(vDimName2, vSubName);
SubsetDestroy(vDimName3, vSubName);