none
SSAS Partition SLICE property

    Question

  • Hello.

    I am trying to assign a slice property value for partitions in my cube.  The partition hosts data that has a process date (business jargon) of less than 2 years of the current date.  Therefore, it needs to be a dynamically calculated slice.  I am trying the following expression:

    "[Process Date].[Fiscal Quarter].&[1900-01-01T00:00:00]&[1900-01-01T00:00:00] : [Process Date].[Fiscal Quarter].&[" + CSTR(CINT(MID(CSTR(NOW()), 7, 4))-3) + "-10-01T00:00:00]&[" + CSTR(CINT(MID(CSTR(NOW()), 7, 4))-3) + "-10-01T00:00:00]"

    Actually I know that if this did work then it would include all data from 01/01/1900 (Unknown) to December 31st of (Current Year-3) which is not what I want, but I am taking small steps - I actually want Q4 of each year from 1900 to Current Year - 3.

    When I process the partition I get the following message/error, could someone please offer some advice on this? Thanks in advance:

    <

    return xmlns="urn:schemas-microsoft-com:xml-analysis">

    <

    results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults">

    <

    root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">

    <

    Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />

    <

    Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">

    <

    Error ErrorCode="3238789192" Description="XML for Analysis parser: The CurrentCatalog XML/A property was not specified." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />

    <

    Error ErrorCode="3240034318" Description="Errors in the OLAP storage engine: An error occurred while processing the 'Fact Employee Sales Y LT 02 Q04' partition of the 'Measures - Sales Transactions' measure group for the 'Employee Program Sales' cube from the SPA_SSAS database." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />

    <

    Error ErrorCode="3239837698" Description="Server: The operation has been cancelled." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />

    </

    Messages>

    </

    root>

    </

    results>

    </

    return>

    Wednesday, May 23, 2012 3:42 PM

Answers

  • I think you may need to keep digging through your errors until you find an error that says "Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated." My guess is that's the underlying error.

    Unfortunatly you cannot use any dynamic MDX expressions of any sort in the Slice property for partitions. You can't even use anything as simple as {null:[Date].[Calendar].[2012]}. You can only use hardcoded tuple or member references. In the prior example, you could need to enumerate every member rather than using the : (range) operator.

    The only way to accomplish what you're wanting to do would be to write an external application (like in C# or PowerShell) which would connect to the cube in AMO (Analysis Management Objects, or the Microsoft.AnalysisServices assembly), Unprocess the partition, then set the Slice property to the new members.

    Partitions with rolling periods of data are less common. What is more common is having a 2011 partition and a 2012 partition, then creating a 2013 partition when we get to 2013, and dropping the 2011 partition. Basically, adding and dropping partitions so that you have a rolling period of data in your cube, but a static date range of data in each partition. Slice works better in that situation. You don't have to do it this way, but it's more common.


    http://artisconsulting.com/Blogs/GregGalloway

    Monday, May 28, 2012 8:54 PM

All replies

  • I think you may need to keep digging through your errors until you find an error that says "Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated." My guess is that's the underlying error.

    Unfortunatly you cannot use any dynamic MDX expressions of any sort in the Slice property for partitions. You can't even use anything as simple as {null:[Date].[Calendar].[2012]}. You can only use hardcoded tuple or member references. In the prior example, you could need to enumerate every member rather than using the : (range) operator.

    The only way to accomplish what you're wanting to do would be to write an external application (like in C# or PowerShell) which would connect to the cube in AMO (Analysis Management Objects, or the Microsoft.AnalysisServices assembly), Unprocess the partition, then set the Slice property to the new members.

    Partitions with rolling periods of data are less common. What is more common is having a 2011 partition and a 2012 partition, then creating a 2013 partition when we get to 2013, and dropping the 2011 partition. Basically, adding and dropping partitions so that you have a rolling period of data in your cube, but a static date range of data in each partition. Slice works better in that situation. You don't have to do it this way, but it's more common.


    http://artisconsulting.com/Blogs/GregGalloway

    Monday, May 28, 2012 8:54 PM
  • As an extension to what Greg has said, you can also use the ascmd command line tool to fire XMLA Alter scripts to set the slice or better still, if you are using SSIS to process the OLAP objects, you can use a script task to set the slice (either using AMO or Alter XMLA scripts) immediately before you process the partition(s) with the hardcoded set of members.

    http://bi-logger.blogspot.com/

    Tuesday, May 29, 2012 7:22 AM