VIEWCONSTRUCT

[DeeDee]

Hi, Does it make sense to think that, after nightly massive data load, if I run a TI with VIEWCONSTRUCT at the Top of all dimensions, eg. All Dept, All Accounts, All Times, All Measures, this VIEWCONSTRUCT may take 1 hour. Assuming there is no data change during the day, does it mean that ALL data are cached in Stargate View and all subsequent cube views retrieval during the rest of the day (any combination under the Top of House) will be much faster since all the data are already in cache ? Thanks !

[Gregor Koch]

Hi
AFAIK, if you do a VIEWCONSTRUCT on a view that has one element out of each dimension, in your case the 'All...' consolidations form each dimension, what will be put into the cache is one value. Just as you would see in it the cube view. My understanding is that using a consolidation (and only the consolidation) in a view does not cache the values for the children of it.
To achieve what you want you would have to create views (and use the VIEWCONSTRUCT) that actually represent, or would display, what is actually in your reports.
Cheers

[lotsaram]

In reporting model where no data changes intra-day ViewConstuct can be enormously useful to speed retrieval performance for users (provided sufficient memory is available to hold all the cached values). But what Gregor said is correct, when TM1 calculates consolidations it does so in one jump straight from the leaf level to the node being queried. Thus if you have cached a value of "Total Year" in the period dimension this does not mean that Q1, Q2, Q3, Q4 or any other intermediate consolidation will be cached as from the perspective of how TM1 calculates consolidations these values are independent. Thus you need to ensure that all relevant intersections that you want to cache are included in the view. Or probably better and easier, precalculate multiple views to get proper coverage of cached values.

If you are serious about using ViewConstruct then you also need to make sure that the VMM and VMT parameters in the }CubeProperties cube are set with appropriate values (particularly VMM). There are a few posts on this elsewhere.

[DeeDee]

Hi Gregor and Lotsaram, Thank you for the clarification. I did look into the VMM and VMT also, which I am doing some more testing later. Back to the ViewConstruct ,

But what Gregor said is correct, when TM1 calculates consolidations it does so in one jump straight from the leaf level to the node being queried. Thus if you have cached a value of "Total Year" in the period dimension this does not mean that Q1, Q2, Q3, Q4 or any other intermediate consolidation will be cached as from the perspective of how TM1 calculates consolidations these values are independent.

Just want to clarify, Q1: if none of the intermediate consolidations are cached, how about the lowest level leafs, will they be in cache? Q2: I thought consolidation values are calculated on the fly, so in case of cache, they are precalculated and sit in cache then?

Aside from doing multiple VIEWCONSTRUCTs, I can also use SubsetElementInsert + ViewSubsetAssign + ViewConstruct and create some sort of cross tab to increase the view granularity in cache?

Thanks again!

[tomok]

If none of the intermediate consolidations are cached, how about the lowest level leafs, will they be in cache?

The leaf data is already in RAM. Why would it need to be cached?

I thought consolidation values are calculated on the fly, so in case of cache, they are precalculated and sit in cache then?

Yes. The whole purpose of the cache is so that consolidated values can be retrieved from RAM instead of recalculated each time a user asks for them. The caveat here is that cache uses RAM resources so you have to make sure you allot the correct amount to the cache so that it doesn't blow up in your face.

[DeeDee]

Thank you guys for the explanation!

The leaf data is already in RAM. Why would it need to be cached?
Yes. The whole purpose of the cache is so that consolidated values can be retrieved from RAM instead of recalculated each time a user asks for them. The caveat here is that cache uses RAM resources so you have to make sure you allot the correct amount to the cache so that it doesn't blow up in your face.

Are you saying that all non blank leafs are always in cache already? tks.

[Alan Kirk]

Thank you guys for the explanation!

The leaf data is already in RAM. Why would it need to be cached?
Yes. The whole purpose of the cache is so that consolidated values can be retrieved from RAM instead of recalculated each time a user asks for them. The caveat here is that cache uses RAM resources so you have to make sure you allot the correct amount to the cache so that it doesn't blow up in your face.

Are you saying that all non blank leafs are always in cache already? tks.

That's the way TM1 works. Everything that is written to an intersection of N level ("leaf") elements is written to the cube's .cub file when you do a data save and when you shut down the server. The consolidations are not. When the server starts up it reads all of the N level values from the .cub file into physical memory, which means that they're already loaded when someone queries them. That's what gives it a speed advantage over other products which have to read those values from a hard disk drive.

Consolidations are calculated on demand, as has been explained above.

The situation can be slightly different with rules calculated N level elements, but for ones which are input by users or by interfaces from other systems, yes, the non-zero values are in memory from server start to server shutdown.