Cubes Partitions | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Cubes Partitions

Hi all.<br /><br />I’m trying to define some partitions in my cubes, so i could avoid processing all data all the time.<br />(Now I’m doing an inneficient Full Process every now and then. Every 2 hours)<br /><br />I’m planning to do three partitions.<br />1 – last day.<br />2 – last month. (excluding of course last day)<br />3 – remaining historical data.<br /><br />I can do this using a passthrough expresion when I build the partition. This expression goes straight to the where clause.<br /><br />But I’m afraid if I do so, Analisis Services won’t be able to improve the query response time reading only the corresponding partition if I for instance ask for last day information in a MDX query. Cause olap won’t be able to know what slices are in which partitions.<br /><br />So I guess that doing some sort of slice partitions will be better in query response time.(coz It will be able to figure out which slices are in which partitions).<br /><br />But when I try to define the partition for the historical data i can’t specify more than one year . for example Date.2003.<br /><br />How could I specify three different slices for last day, remaining last month and remaining historical data using data slices and NO a passthrou query in the where clause.<br /><br />Hope I made myself clear…this is pretty confusing, isn’t it? <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />Thanx.<br />
"How could I specify three different slices for last day, remaining last month and remaining historical data using data slices and NO a passthrou query in the where clause." How much data are you working with for each DataSlice? Have you tried creating 3 partitions Last Day, Remaining last month, historical data and then a dataslice for each partition?
Raulie
Hewlett-Packard Company
That’s exacltly what I’m trying to do. I’ve build 3 partitions, but when it cames to create the slices, I can only choose one member at a time. a year, a month or a day. not 2 , 3 or more of them for the slice. I can easily define the slice for today. but How coud I specify the slice for las month (up to but not including today) and the rest for historical data? and i’m trying to do this without a passthrough query, I’m trying to use a slice.

After you have created your slices you can further add "filters". Im not sure if thats what you want but it is worth looking into, BTW what is you exact reason for not using a pass-through?
Raulie
Hewlett-Packard Company
How many members can I add for a slice? I think just one.
What do you mean by adding more filters? I don’t want to use the where condition.

Hi Gus When you create the partition you define a dataslice which basically is a WHERE clause and can only choose one member, if you want to define more than one member use Filters, but in your case you don’t want to go that route. I dont see the problem with filters only make sure to avoid double countings. And yes this is pretty confusing. Raulie
Hewlett-Packard Company
Checkhttp://www.sql-server-performance.com/bill_pearson.asp link for suitable articles on partition. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Ok. I’m sure about that now. When you define a slice you can only add one memeber. With filters you have more flexibility (you may set up range of dates, everything you want), but the problem with filters is that you won’t get the query response speedup that you get when OLAP knows which data is in which partition (like when you set up a partition with a slice). But that’s not a problem anyway. it’ll check all the partitions ans in some of them there will be no data to answer a particular query. Thanx a lot everybody. But now I have another problem. I’m trying to speed up the cube processing time, by means of partitions.
Let’s suppose I have defined the partitions in such a way that I’m getting processed just the more recent records.
the problem is now with the virtual cubes that rely on my just recent processed cube. How can I avoid processing all the virtual cube? there is no partitions in the virtual cube….. All the time I gain processing just the partition with new data is lost when I’m forced to do full process (or refresh data) in the virtual cube.

]]>