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?
SQL code choice in TI process
-
- 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
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.