Very slow DBRW performance when a cube is used as data source

[akarimov]

Hello everyone!

I'm not a TM1 professional, however have some experience that includes several areas of IT. I tried to search for any tips regarding the issue we are facing right now from this forum but was not successful. So, I decided to post it here, hoping to get any suggestions from pros.
We are switching from our on-premises IBM TM1 9.5.2 to the on-cloud IBM P&A 2.0. We have encountered an issue related to DBRW formulas that use one of the cubes with 16 dimensions in order to retrieve values from it. The original Excel template has a data worksheet where formulas are copied from the top row and pasted into the column (e.g. A2:A40000) then they are refreshed, copied and pasted as values to the same range. There are about 150 columns and 35-40 thousand rows overall. All this runs with a simple VBA macro and once one column is done it moves to the next one and so on. Our current TM1 9.5.2 and TM1 Perspectives takes about 10 minutes to run this process, and with IBM P&A and PAX - almost infinity. When we apply this formula to one column (with around 40K rows) it takes about 30-40 seconds to refresh. If we let the entire process run, then after 10-15 minutes (until this problem formula starts) it becomes really slow and the Excel file size starts consuming up to 5 GB memory and 25-40% CPU. We tried with about 2000 rows of data, and it took about 40 minutes to finish the task. Using DBR and tweaking Excel's Auto calculation did not help. Can you please share your knowledge or advise where to look at?
Thank you!

[burnstripe]

I believe there's 2 factors causing this.
1) the cloud environment will have a higher ping (latency) , result in slower response time
2) pax will be recalculating after every change to the worksheet

Some options to address this are:
1) change existing method.
A) Consider outputting the content with a process using asciioutput (recommended for truly large outputs)
B) turn off automatic recalc in the vba whilst building dbrw statements and only refresh content once the whole workbook is compiled. Depending on size you may hit workbookmaxcellcount parameter which could be altered or you could break it up into batches such that you only have x amount of dbrw calculating at once, the hardcoding the results before doing the next batch

2) There could be other factors at play given the version difference and possible different server/model configuration. But I'd say the higher ping count, latency is likely your main issue.

The higher ping, latency is simply the result of the server being on cloud vs on premise. And different approaches will be required to reduce the number of packets sent, received. Pax is optimised for cloud environments but the method your using is forcing excel to send loads of individual packets rather than one big packet

[Steve Rowe]

You are also moving from the Perspectives client to the PAfE client as part of this transition (as well as moving from v9 to v11 so many years of updates and changes).

PAfE generally does not deal well with "Mega-slices" versus perspectives but we don't see a significant / multiple orders of magnitude difference between on-premise PAfE / PAW and PAoC PAfE/ PAW.

Just to support Burnstripe on this you are unlikely to get a 5 million cell DBRW working at a good speed but the performance difference is due to PAfE rather than latency (IMO).

We've been through a few iterations of this issue with customers transitioning to PAfE

Option 1 : Move to quick reports if you absolutely have to do this in Excel, if your slice is not suitable to do in a single symmetrical slice then have multiple quick reports and then use Excel formula to refactor the data into your mega slice. Certainly don't continue to spend time trying to get a VBA driven approach to work.

Option 2 : Use a TI process (PAs ETL tool) to reproduce the export in a text file.

PAfE also performs tests against named ranges on every calculation pass (why I don't know...). If your workbook has a lot of named ranges (1,000s) then this also impacts performance. Also watch out for hidden named ranges, I've come across books with many 1,000s of hidden named ranges. Cleaning this up will help.

[WilliamSmith]

Sharing in case this is an option for you. We now use a TM1 library to query data into SQL Server, then use Power Query to load data into Excel / PowerBI. It is very performant. If you are a MS shop the C# library is linked below, or if you're a python dev tm1py should work in the same way. We like SQL Server because it doesn't require any 3rd party ODBC drivers or configuration to work with Excel.

https://www.nuget.org/packages/AndromedaTM1Sharp

[gtonkin]

Sharing in case this is an option for you…

https://www.nuget.org/packages/AndromedaTM1Sharp

Thanks for sharing!