Cube update

[Toto]

Hello,

we have a 20 GB big cube that we would have to be available 24/7. The data in the cube is updated several times a day so we think about how to make this update process in order to make sure that the cube is avaiable all the time or at least reduce the update time to an absolute minimum.

Which concepts should we have in mind and compare to each other?

Thanks a lot,

Toto

[jim wood]

If you are only doing a data append I guess it depends on the data size rather than the size of the cube. The other thing to bare in mind is that updating the cube so often will leave to a low level of cached results, potentially leading to adverse performance.

My first question is that do you really need to update the information so many times during the day?

Could you have an in day cube which picks up all the updates for the current day and then loads them in to the main cube at the end of the day? (If you only update the main cube at the end of the day you will also be able to build some view building processes in to a schedlue and pre-cache results to try and help performance.)

Jim.

[mattgoff]

If locking is the issue, you can have a hidden "load" cube with no rules or feeders linking to it. Your chore can load data to this cube, then process/copy data into your live cube. Since that second process is a local transfer, it's easily an order of magnitude less lock time on the live cube.

I do this for our GL data-- the source server can get bogged down, especially during close, and take forever to run my query. Switching to the above arrangement reduced my GL cube load lock time from as long as eight minutes (worst case) down to less than twenty seconds. This allowed me to up the load frequency from every four hours to every hour. The chore doesn't run any faster, but it dramatically improves usability.

Matt

[dymoy]

I've dealt with cubes that contained nearly 50GB of data. If you're able, you might consider only reloading the data that's change or likely to change rather than the entire database. You can refresh the entire cube during the weekends or after hours if needed. This usually works if you're dealing with historical data (e.g. a general ledger) which is not likely to change versus a smaller subset of data that will change (i.e current month). If you can do this, your load times should be terrific - I was able to reload a full month of a general ledger with millions of accounts in less than 1.5 minutes. Most users weren't even aware I was locking the cube during the load.