Frequency of processing a cube (via DTS)?

[StevenK]

I'm working with Microsoft SQL Server / Analysis Services:

What is a sensible period of time to leave between the processing of a cube?
The cube is to highlight unpaid invoices - and is to form the basis of a Microsoft Excel pivot table report.

I don't want the client to run this report (having entered in some payments against invoices) and find the spreadsheet does not reflect this...
But similarly don't want to snarl up the clients server with the (re-)processing of this cube...

If anyone can suggest how often they reprocess cubes (via a DTS package) this would be a great help.
Thanks in advance.

Steve

 

[rac2]

I dont think there is a technical solution to your concerns.

The solution depends on the pattern of the workflow, the number of people using the report, and the ramifications of old information.

If the report is updated on a schedule, then it will always be out-of-date. I would handle this by adding a heading to the reports showing the date and time that it was last updated and even a statement that activity after that moment is not included and a reference to further explanation stating the frequency of the report plus advice to take a bagel break and come back later.

Or process the cube as the first step in preparing the report. This would be done by the reporting interface such as a web page. With this approach the report is always current.

The downside will be that the person will wait a few moments before the report is available. That is not unusual.

But if many people are continuously requesting the report, then the server becomes busy and all work grinds to a halt; the people complain to your supervisor who comes to your cubicle and does what supervisors do. In this case my advice is to consult with your clients and ask them to suggest a reasonable interval for scheduled updates.