1

Since our SQL server is Standard edition and cannot do proactive caching, I was wondering if Integration Services can detect changes in a database/table, so it can then process the cube automatically? Or is this wishful thinking?

David
  • 439
  • 1
  • 5
  • 17

1 Answers1

1

I don't believe that it will support this natively, but you could do some thing like the following

1) Add a trigger to the table that will set a last modified date value each time the a record is inserted or updated.

2) Create an SSIS package to reprocess your cubes.

3) Have the ssis package write a last run date time value to track the last time it executed

4) Have your SSIS package compare the last modification date of the table to the last run time of package. If modification have occurred then the SSIS package could perform its processing.

5) Schedule the package to run every five minutes or so to catch new data.

The caveat here is that you will be in effect reprocessing the cubes every time the data changes. This may be OK if the data changes infrequently and your cubes are small, but will probably cripple your cubes if the data are updated frequently. Proactive Caching does more then just auto-update the cubes. It also manages SSAS cache so that the new data are merged in without causing a performance penalty. The model I described here doesn't achieve that.

In general I try to talk my customers out of real-time cube updates. The issue is that it can make analysis tricky if the numbers are changing every-few minutes. I've had a lot of people request it but change their mind once we talk though the implications. That being said there are legitimate use cases for it.

Bart
  • 126
  • 3
  • 1
    This is a nice tip, but after thinking about it I agree with your advice. It does not make sense in our case to process every time data changes. We were thinking about a dashboard system but even then is does not need to be totally real time. Thanks. – David Sep 02 '11 at 22:15