SQL code choice in TI process

Post Reply
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

SQL code choice in TI process

Post by Mark RMBC »

This post is more for curiosity than anything else. It has been a long week!

I have created a TI process whose data source is ODBC. The process itself does a CellIncrementN to a cube. The process actually works fine and takes around 4 minutes to run.

When writing the SQL code I had 2 options:
1. Exclude the Group by clause and return 54,000 rows
2. Include the Group by clause and return 5,000 rows (I have kept with the CellIncrementN method even though I don't need it)

For method 1 the SQL code compiled in seconds but obviously there are 54,000 rows for the process to run over. For method 2 the SQL codes took a few minutes to compile but there are only 5,000 rows to run over.

When testing each method, the first was slightly quicker, maybe by 30 seconds. On balance I decided to go with method 2, because as the data grows I estimate this will turn out to be the most efficient in the end.

My basic query is, has anyone else faced this choice and is there anything I need to consider other than the time it takes?
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: SQL code choice in TI process

Post by tomok »

The question is does TM1 add things up faster than your database server does (because that in effect is what a GROUP BY does). I also have seen instances where TM1 is indeed faster. YMMV.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Post Reply