Replace data in a cube?

[UlfB]

Hi,

I have started looking into doing incremental updates of a cube and it seems rather easy to add more data to an existing cube, but what happens if you would need to replace some of the data you already have loaded?
Let's say you do monthly incremental updates of a cube, so for 2011 you have loaded January, February, March and April. Then someone discovers that the figures for February are all wrong and you get a delivery of new, correct February data. Is it possible to somehow delete just the old February data and load the new? Or do you have to delete the whole cube and start all over and rebuild it from January and forward?

[AngelaHall]

You should find that the entire cube data is updated.

http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003241630.htm

"The new cube generation enables the original cube to remain online for queries during the entire update process. This is because the original cube is not modified. The original cube continues in production as before while the new cube is available for administrative review and updates (such as security updates or new global calculated members). This provides the administrator with an opportunity to examine and verify the correctness of the updates before bringing the cube online for production without affecting the original cube. "

~ Angela

[Urri]

Hello, Angela!

I'm faced with the same problem, which UlfB describes, and unfortunately, I was't able to find the suitable answer in Your reply.

As I understand it, incremental updates or updating in-place are the ways to add new data to existing cube, not to replace it.

Actually, I need to replace (or completely delete) only some portion of cube data without rebuilding the whole cube or creating a new generation of cube.

There is a way to do this?

[boschy]

To keep things simple, I refresh our cube data each month.

By refresh I mean (a) perform a physical cube data file delete, then (b) do an upload step, all using PROC OLAP.

Maybe you can explain why you need to keep the existing data while adding new data, instead of doing a full refresh?

We extract fresh datasets each month, so it's easier just to load that dataset than perform incremental updates.

[Urri]

Hello, boschy!

Our cube is also supplemented by new data on monthly basis.

Each monthly dataset consumes about 80 GB of disk space and it's uploading with PROC OLAP ADD_DATA UPDATE_INPLACE statement takes 3 to 4 hours. So if we will try to make a reload data for a full year, it will take more than a day, and we need much more free space on disks to store the necessary data.
In my opinion this is not a good solution if we need to replace data only for the one month.

I think there is less resource and time consuming way to replace old historical portions of data, than a full year refresh, is't it? But i can't find it yet :smileyplain:

[boschy]

OK, that makes sense.

Just curious...How does your 80GB dataset cube perform? I suppose it should be fine if you have the common aggregations defined.

[LinusH]

Maybe you could benefit from using a ROLAP strategy for storing the Cube data, which will let you do updates using standard data management tools (SQL, data step etc).

SAS(R) 9.3 OLAP Server: User's Guide

[Urri]

Hi there!

Thanks to all for replies.

I also came to conclusion that a ROLAP aggregations would be the better solution in this case.

But all the same, is there a way to delete a portion of MOLAP cube data without full cube rebuilding?

In accordance with SAS(R) 9.3 OLAP Server: User's Guide: "SAS MOLAP aggregation storage maintains the cube data in the same table format as the format that is used by the SAS Scalable Performance Data (SPD) Engine."

May be it is possible to assign a folder which contains cube data as a library through SASSPDS Engine and then update it directly?

[LinusH]

I haven't tested, but I doubt it would work. And if you should succeed to do so anyway, I'm pretty sure that wouldn't be supported by the Institute.