ViewConstruct not helping me speed things up in Excel

[ChrisF79]

I have some pretty large spreadsheets that I update monthly. If I simply open one up, it takes approximately 15 minutes to calculate as it is full of DBRW formulas. They DBRW's are very similar and reference a very specific set of data.

I went into Architect and created a view that contains these same values for dimensions, unchecked the "Skip Consolidated Values" and "Skip Zero/Blank" as well to be safe. I then threw a ViewConstruct('mycube','myview'); in an epilogue of a process and ran it. It only takes about 10 seconds to run which has me a bit concerned. Naturally, when I open my Excel sheet, I'm still looking at the full 15 minutes to update so something isn't working for me.

Do you have any tips as to how to successfully use ViewConstruct to precache large sets of data? I really need to speed this up for our reporting process and I'm a bit stumped. Thanks

[Alan Kirk]

I have some pretty large spreadsheets that I update monthly. If I simply open one up, it takes approximately 15 minutes to calculate as it is full of DBRW formulas. They DBRW's are very similar and reference a very specific set of data.

I went into Architect and created a view that contains these same values for dimensions, unchecked the "Skip Consolidated Values" and "Skip Zero/Blank" as well to be safe. I then threw a ViewConstruct('mycube','myview'); in an epilogue of a process and ran it. It only takes about 10 seconds to run which has me a bit concerned. Naturally, when I open my Excel sheet, I'm still looking at the full 15 minutes to update so something isn't working for me.

Do you have any tips as to how to successfully use ViewConstruct to precache large sets of data? I really need to speed this up for our reporting process and I'm a bit stumped. Thanks

Two suggestions:
1/ Make sure that the calculation mode in Excel is manual, not automatic.
2/ If the cube reference is hard coded, take a look in the Reference Guide for what the View function. It doesn't always help but I've had some reports show astronomical improvements in calculation time when changed from hard coded cube references to a View function.

[ChrisF79]

Thanks for the fast reply. Calculation mode is set to manual. I'll open a file and hit F9 and sit back and wait. It is incredibly tedious.

So looking at the VIEW function, I would be using that in lieu of DBRW's?

[Alan Kirk]

Thanks for the fast reply. Calculation mode is set to manual. I'll open a file and hit F9 and sit back and wait. It is incredibly tedious.

So looking at the VIEW function, I would be using that in lieu of DBRW's?

No, the View function is used to define, for want of a better term, a virtual mini-cube based on the fixed (title) elements. It replaces the cell which contains the cube name. (Or if you have the cube name hard coded in your DBRWs, replace the hard coding with a reference to a cell that contains the View function.) The DBRWs then look to that mini-cube rather than needing to sift through the whole thing.

[ChrisF79]

Thanks. I'll definitely give that a shot.

[lotsaram]

So looking at the VIEW function, I would be using that in lieu of DBRW's?

No. You would be using VIEW in place of or in addition to a harecoded string reference for the server:cubemane.

As Alan says it is not always a silver bullet but I have seen many reports recalculate.in 5 second or less after properly implementing view formula(s) versus over 5 minutes without.