December 19, 2008 at 4:38 am
Hey all,
I am a SSAS newbie so this question may appear dumb to you veterans 🙂
We are running SQL Server Standard 2008 and we have a SPROC which updates our fact tables in a datawarehouse database, this runs every 10 minutes.
We are using standard MOLAP, manually scheduled, we obviously can't use pre-emptive caching as that requires Enterprise edition.
At the moment once our update sproc has finished executing we update the cube, and this is currently achieved via a full reprocessing. It is this full processing which concerns me, some of our dimensions / fact tables are only appended to whilst some are updated as well.
It would make much more sense to me if we could get analysis services to just process the data which has changed. What mechanisms are best practice?
I was thinking that perhaps we have tables which contain new/modified data but then that doesn't seem to work with the cube design i.e. splitting of the fact table. I also thought perhaps we could use some kind of time stamp or updated flag but I don't know how this "where" predicate can be applied to the cube processing queries.
Any information is most welcome, thanks in advance for your help.
Regards,
Mark
December 19, 2008 at 6:05 am
Analysis Services incremental updating is based on the idea that fact records should never be updated. You should only add new fact records to the table. If you need to update an existing record, you should actually reverse out the old record and insert a new version.
If you do this, you can incrementally update your cubes.
For dimensions, you have the ability to process an update, or process an insert. If you are only inserting new records, existing aggregations will be left alone. If you do a process update and anything has changed, SSAS my drop flexible aggrgegations and you are likely to have to process indexes.
There is a lot more to this and it is an important concept to fully understand. I would recommend reading the books online cube processing topics, check out the MSDN documentation available on the website, and download and read the project REAL documentation from Microsoft. These free information sources are very useful.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply