none
Partition Slicing: Issues and performance <Urgent> RRS feed

  • Question

  • Hi,

    I am using ssas 2005 sp1. I have about half a million rows per day and keep a rolling window of one month. Each day is in a seperate partition. We have MOLAP cubes. Here are the issues I am facing:

    1. Do we need to specify the "SLICE" property of the partition? I read somewhere that MOLAP partitions do it automatically by the query restriction used. If I need to specify it what should be the values for a slice equal to a day. (I have stereotype time dimension with hierarchies and defaults)

    2. during processing some partitions gave me the following error: "Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated."
    There is also a connect raised for this: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=155391 

    I checked for one partition and it was because that query did not have any data in it. I removed the slice property value and for now the errors have seemed to vanish. Here is the value that I had in the slice property:

    3. Finally I shomehow processed only about 12 days of data. (12 partitions) to start with. I clicked on the BI studio 'Browser' and also had my sql profiler open. I was startled to see that it was reading from all the 12 partitions: "Started reading from partition xxxx1, ...... from xxxx2 and so on till partition 12. It took ages for it to do this and finally show up "Finished reading from partition xxxx1, ......and so on for all 12 partitions.  The duration for the start/finish partition read shows 70108. (all partitions have it about 6xxxx to 7xxxx. I dont know what the numbers stand for, but am sure that they are high!

    finally I get this query in the end of all the partition reading: "Select filter([Measures].members, [Measures].currentmember.member_caption = [Measures].currentmember.member_caption) on columns from [ABCCUBE]" what is this query and why does it take time? can I stop this!

    My question is: why should the partitions be accessed when I have not even selected any measure or dimension in the BI studio cube browser? I just clicked on the browser tab in BI studio???? the entire BI studio hangs with a white background!! What have I configured wrong? How can I correct this?

    I will post more on this after trying to browse and checking the profiler. I think the partitions are being accessed incorrectly and the similar will happen when I start browsing.

    Please help as soon as possible.

    Regards

    jeudi 8 mars 2007 20:42

Réponses

  •  Vijay R wrote:
    I have about half a million rows per day and keep a rolling window of one month. Each day is in a seperate partition.

    That may be too many partitions depending on how many years you have. See the "Optimal Number and Size of Partitions" paragraph in http://blogs.msdn.com/sqlcat/archive/2007/03/05/ssas-partition-slicing.aspx for more info.(Or see the performance guide)

     Vijay R wrote:
    1. Do we need to specify the "SLICE" property of the partition? I read somewhere that MOLAP partitions do it automatically by the query restriction used. If I need to specify it what should be the values for a slice equal to a day. (I have stereotype time dimension with hierarchies and defaults)

    Yes. Always specify the slice if you can. Analysis Services does store a bitmap index (commonly called the autoslice) to automatically note what's in that partition, but the autoslice is less beneficial than setting the slice in several situations.

     Vijay R wrote:
    2. during processing some partitions gave me the following error: "Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated."
    There is also a connect raised for this: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=155391 

    When you set your partition slice, the "check" button doesn't really validate as much as it should. So you need to manually validate the slice. If your slice is ZZZZZ then run the following query to validate it will accept it:

    select StrToSet("ZZZZ",CONSTRAINED) on 0
    from [your cube]

    Basically, you have to hardcode the members. No functions are allowed. That's the bottom line for what restrictions it's enforcing.

     Vijay R wrote:
    3.  I clicked on the BI studio 'Browser' and also had my sql profiler open. I was startled to see that it was reading from all the 12 partitions.

    Yup... there's no reason they should be running that stupid MDX, but the cube browser does. Designing aggregations with no attributes specified (i.e. designing an aggregation for the All slice) should help though it's still going to read from aggs for each partition. Also, if you have some LastNonEmpty measures, you should see the performance guide where it talks about designing aggs at granularity of the Date dimension so they an be used by LastNonEmpty and other semi-additive measures.

    vendredi 9 mars 2007 18:56
    Modérateur

Toutes les réponses

  •  Vijay R wrote:
    I have about half a million rows per day and keep a rolling window of one month. Each day is in a seperate partition.

    That may be too many partitions depending on how many years you have. See the "Optimal Number and Size of Partitions" paragraph in http://blogs.msdn.com/sqlcat/archive/2007/03/05/ssas-partition-slicing.aspx for more info.(Or see the performance guide)

     Vijay R wrote:
    1. Do we need to specify the "SLICE" property of the partition? I read somewhere that MOLAP partitions do it automatically by the query restriction used. If I need to specify it what should be the values for a slice equal to a day. (I have stereotype time dimension with hierarchies and defaults)

    Yes. Always specify the slice if you can. Analysis Services does store a bitmap index (commonly called the autoslice) to automatically note what's in that partition, but the autoslice is less beneficial than setting the slice in several situations.

     Vijay R wrote:
    2. during processing some partitions gave me the following error: "Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated."
    There is also a connect raised for this: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=155391 

    When you set your partition slice, the "check" button doesn't really validate as much as it should. So you need to manually validate the slice. If your slice is ZZZZZ then run the following query to validate it will accept it:

    select StrToSet("ZZZZ",CONSTRAINED) on 0
    from [your cube]

    Basically, you have to hardcode the members. No functions are allowed. That's the bottom line for what restrictions it's enforcing.

     Vijay R wrote:
    3.  I clicked on the BI studio 'Browser' and also had my sql profiler open. I was startled to see that it was reading from all the 12 partitions.

    Yup... there's no reason they should be running that stupid MDX, but the cube browser does. Designing aggregations with no attributes specified (i.e. designing an aggregation for the All slice) should help though it's still going to read from aggs for each partition. Also, if you have some LastNonEmpty measures, you should see the performance guide where it talks about designing aggs at granularity of the Date dimension so they an be used by LastNonEmpty and other semi-additive measures.

    vendredi 9 mars 2007 18:56
    Modérateur
  • Hi ,

    Thanks a lot for solving my queries.

    I still have the slice doubt, especially as to what are the correct values? for the slice equal to one day I have specified the following:

    [DIM DATE].[DATE].&[2007-02-01T00:00:00].

    also, can I specify a range for the Slice property? I mean if I want a slice from 01-Feb-2007 to 21-Feb-2007, how should I specify it?

    1. regarding the number of partitions, I dont keep all the previous week's, days in one partition. Meaning at first I have 5 partitions for each day. (1st to 5th) then when I move to the next week (8th to 12th), I merge the 1st to 5th partitions into one single partition. So at a time I have only 6 partitions, the current 5 days as seperate partitions and the preveious weeks uptil one month(current) in one single partition. Although the size of the partition becomes bigger, I hope it helps to improve the performance.

    So, is this the correct approach? Is there any other way where I can get faster response times? the processing time is not a problem, I dont mind it taking any amount of time!

     

    Regards

    vendredi 9 mars 2007 19:49
  •  Vijay R wrote:
    I still have the slice doubt, especially as to what are the correct values? for the slice equal to one day I have specified the following:

    [DIM DATE].[DATE].&[2007-02-01T00:00:00].

    also, can I specify a range for the Slice property? I mean if I want a slice from 01-Feb-2007 to 21-Feb-2007, how should I specify it?

    It has to be a hardcode set. If you try:

    select StrToSet("{[DIM DATE].[DATE].&[2007-02-01T00:00:00]:[DIM DATE].[DATE].&[2007-02-05T00:00:00]}",CONSTRAINED) on 0
    from [your cube]

    you will notice it bombs. You have to hardcode the list of members such as:

    {[DIM DATE].[DATE].&[2007-02-01T00:00:00], [DIM DATE].[DATE].&[2007-02-02T00:00:00], [DIM DATE].[DATE].&[2007-02-03T00:00:00]}

    Or, you can use an attribute at a different granularity. For instance, if your slice covered one week, you could just use the following slice:

    [DIM DATE].[WEEK].[Week 1, 2007]

    Regarding # of partitions, I can't give recomendations beyond those references I already mentioned. You've got to balance processing time, query time, maintenance headache, data volume, SSAS service startup time, etc. when choosing how many partitions to build. Just try out different schemes.

    If you want to try out different schemes you might leverage
    http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=Partition&referringTitle=Home:

     

    vendredi 9 mars 2007 20:29
    Modérateur
  • Hi,

    Thanks a lot again for the quick answers.

    I noticed a problem in my merged partition (3 weeks). It did not contain the same data in the dimensions joined, as compared to before they were merged. Not sure as to what could be the problem. I am trying to process them again and check.

    Is there something else that I have to do after merging the partitions? Like process them again or some properties to be set?

     

    Regards

    vendredi 9 mars 2007 20:43
  • Sorry. I don't have any experience with merging partitions. Just make sure that the resulting partition has the correct slice set and the correct where clause on the SQL query if you reprocess it.
    vendredi 9 mars 2007 21:44
    Modérateur
  • I have a related question...The measure group which I have partitioned by date has more than one date dimension (they are roleplaying...imagine Date of Sale and Date of Invoice Creation). In terms of defining the slices, if I partitioned by calendar year, what date dimension should I use to slice (i imagine that I should use whatever date key is used in the partition WHERE clause), but will that preclude the other date dimension from being sliced correctly?

    In other words if I partitioned using the date of sale key and create slices based on date of sale dim, what happens when someone uses the date of invoice creation dim in their query...will the slice have any effect or will it worsen query performance?

    L


    mmMmmm...yummy...
    vendredi 3 juin 2011 23:36