Processing new data in a Cube.. SSAS

Question

I am new to SSAS and have worked my way to processing a cube with ALL the data thats in the table. I am doing that in Micorsoft VS by doing build and them process

Now I want teh cube to be able to process the most recent data that comes in the database. How can I go about doing that without processing teh entire data again. How can I set it in such a way that every night it processes the new data.

Another question is, how can I set it in a way that it goes back say 2 years and processes all the data that got in the fact table till now.

Does SSMS has anything to do with it or all this processing can be done via BIDs tool.

I am using VS 2008

Thanks

-Sarah

Answer 1

Hi Sarah,

one way to read the data as of today and then moving back say exactly 12 months is to access a view instead of the table.

The view limits the data through the where clause based on the date requirements you set.

As far as processing only the new data you need also to ask yourself if your dimensions are changing,

for exmaple are new product or customers coming into the dimensions. Then you need to understand what changes

are happening: for example are customers being deleted from the dimensio tables?

Based on those answers there may be some alternatives in SSAS (set them through BIDS) that allow incremental processing.

Here are two links to help you out:

http://technet.microsoft.com/en-us/library/ms175634.aspx

http://technet.microsoft.com/en-us/library/ms174774.aspx

Regards,

Mario

Answer 2

If your data volume is not too large you can process only a subset of available data - see here: http://artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=4

Or if you have a large amount of data you will need to implement cube partitions: http://msdn.microsoft.com/en-us/library/ms175688(v=sql.100).aspx

Be aware - this is available in developer and enterprise edition only for 2008.

Take time to design approriate partitions before rushing into development - key is the potential amount of data in each partition, too much or too little is a bad thing. See this whitepaper for details: http://www.microsoft.com/en-us/download/details.aspx?id=17303

You can process partitions individually through management studio or use c# and AMO. Example here

---

www.minidba.com | Sql Server Performance Tuning & Monitoring Software