Limitations in Drill-through in excel 2007 (TM1 9.1 SP3)

[vijay srinivasan]

Hi,

I have a 64-bit version of TM1 9.1 SP3 version installed on a 64-bit windows server. Microsoft Excel 2007 is installed in the server machine. I have configured a "Drill-through" option in one of my cubes which fetches detailed data from an Oracle DB.

When I slice the cube to an excel and do a drill-through, sometimes the result set contains more than 65536 rows. Since excel 2007 doesn't have the limitation of 65536 rows, I expect the resultant excel sheet to contain all the rows. However, the drill through still returns only 65536 rows. So this is making it impossible for the users to get the complete result. It seems that the limit 65536 is hard coded in the TM1 9.1 SP3 version. Is this true? Is there a way to "tell" TM1 server that the current version of excel can take more than 65536 rows ?

I did find a work around. If I do a "In cube" drill through, the Relational drill through window gives me the complete result set. I can then copy the complete result and paste it in an excel sheet. However, this solution is tedious since there is no short cut to go to the end of the result set other than by doing a "Page-down" till i get to the end. If my result set contains 250K rows then I need to keep the "Page down" key pressed for a long time ! Also the copy takes considerable amount of time and RAM.

So it would be really helpful if someone can let me know if there is a way to configure the TM1 9.1 SP3 server to return more than 65536 rows to the excel sheet from a drill through operation. Thanks.

Regards,
Vijay.

[lotsaram]

This has been said before but maybe it's worth saying again ...

Why
- would you want to design a drill through that would return more than 65,536 rows?
- would a user want to execute such a drill-through?
- and what on earth would any sane person who understands what they are doing and knows how to use a BI tool possibly want to do with a result set that large?

[Steve Rowe]

Hi Vijay,
I don't think there is a way to correct this.

A possible workaround off the top of my head, would be v challenging I think.
Intercept the call to the drill through (there's a post by Paul Simon on how to do this).
Do a count in SQL to see how many records would be returned.
Run the query to return the first 65k records and then the next 65k records.. (is this even possible in SQL?).
Somehow get the results back into Excel.

Not sure if the above approach would be possible, it's probably easier to use the arguements of the start cell as the parameters for a SQL query that you run direct from Excel using VBA. This would give you far greater control.

As LotsaRam says it's unusal to need to do this but sometimes you just have to do as your told I suppose!

Cheers,

[kpk]

A)
In a drill through rule you can define more than one drill through processes for the same cell (separating them with comma).
It pop-ups the list of predefined processes to the end-users.
If you segment your data into (less than 65k rows) parts then you can let the user to select the most desired tons of records.
B)
If you need that amount of raw(?) data then why don't use
Excel ODBC connection to Oracle via Data>PivotTable menu or directly via the Data>ImportExternalData menu?

Regards,
Peter

[vijay srinivasan]

@kpk,

Thanks, your solution worked !.. I had to design the drill through to get the data for a complete month. Now I split it into groups of 5 days each and provide the user the option of choosing the range of days of the month.

@lotsaram,

I agree with you that transactional level data for a BI user is not ideal.. but in this particular situation the user not only does reporting but also does reconciliation of data with another system. So the drill to get >65K records was unavoidable.

@Steve,

I haven't tried the solution that you had specified. But would like to see if it works as it would be ideal for my situation. I will update this thread if i am able to successfully try that solution.

However I have a question. Is the limitation still present in the latest versions of TM1 (9.4 and 9.5) ? Would an upgrade be a possible solution to overcome this problem ?

Regards,
Vijay.