Hi everyone,
I've got a small problem that I hope that someone can help me with. I am quite new to datawarehousing, so when I started out I didn't take partitioning measuregroups into consideration. Now my problem is taht the actual processing of my cubes takes to much time. Therfore I decided to start creating partitions, but since I have quite a lot of measuregroups I wanted to create them in SSIS.
This has worked out fine and the partitions are created, but since we are using standard edition of ssas 2005 I can't craete more than 3 partitions a measuregroup. Therfore I decided to alter my design a bit more and actually have one working partition and one history partition.
My questions are:
1. Since my old partition is based on a DsvTableBinding scheme, I guess I need to change them to QueryBinding instead. I guess that I will only have to do this the first time and afterwards, when the working partition is merged with the history partition everything should be working fine without any problems of double counting.
2. Are there any problems browsing a cube with this design? I mean if I have 2 different partition and want to look at data in both of them at the same time?
I hope this makes sense :)
Best Regards
Stefan Ghose
Hello Stefan,
You approach makes sense and should work. Browsing both partitions will be posible. However you should pay attention to the following:
1. Depending on the amount of your data, the historical partition might grow and eventually can became very large, which will slow down data retrival from it.
2. Merging partitions works quite well and tries to make the best job possible of avoiding fragmentation, but nevertheless some fragmentation will happen, which can cause both data expansion and query slowdown. Once in a while you'd need to do Full Process of the historical data.
3. Usually during partition merge you need to pay attention to aggregations in the result. (Result will have only agregations available in both partitions). With your approach, I don't foresee any problems, because probably your current partion will have the same aggregations (or more) then historical one.
4. It's not required, but still recomended to set the partition slice.
Hope this helps,
Irina
|||Hi Irina,
could you please describe benefits of the partition slice setting. Where AS earns income from this feature? Only during quering or someweher else?
|||The following article explains quite well about slices and filters. It refers to AS 2000 but has a lot of good information:
http://msdn2.microsoft.com/en-us/library/aa902650(sql.80).aspx
Search for "Slices and Filters"
|||Hi Irina,
thank you for the answers. It helped me set up a scalable design that I will be able to reuse for all my fact table. I combined my needs with a post by Jamie on blogs.conchango.com
http://blogs.conchango.com/jamiethomson/archive/2006/07/18/SSIS_2F00_SSAS_3A00_-Process-SSAS-dimensions-and-measure-groups-individually.aspx
It works really well, and I have managed to shave an hour off my measure group processing.
Kind Regards
Stefan Ghose
