How to reduce refresh time of 50k DBRW

[Kapoorsaab]

Hi All,

I am a new user of TM1, but knows a bit of VBA coding. Currently we have files with approx 50,000 DBRW formulae and it takes approx 2 hours to refresh that sheet. Request ytou to please guide on the best possible solutions/workaround I can go for.

Thanks in Advance

Admin Note: Moved to correct forum. This does NOT belong in Enhancements.

[lotsaram]

Reading the request for assistance guidelines and the FAQ would be a good start.

50K DBRW cells does not seem that much to me at all. Assuming you have a moderately complex workbook of 10 sheets with 12 monthly columns of data in each this would only equate to around 400 - 450 rows per sheet to reach 50000 cells. Really this is not much and should calculate in a few seconds or less.

- Is the Excel calculation mode set to MANUAL?
- Does each worksheet have an appropriate VIEW formula?

These 2 factors alone can easily account for a 100x difference in recalculation performance. You will need someone who has some expertise to determine whether the VIEW functions are correct.

If an equivalent cube view refresh i very fast compared to an Excel sheet with the same data then your issue is in Excel not the model itself.

If everything is well in Excel then look at the model as very slow performance is usually to do with inefficient rules or overfed rules. You would need an expert to troubleshoot this.