Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Analysis Services 2012 - Does Alter Partition Update the Header?

Answered Analysis Services 2012 - Does Alter Partition Update the Header?

  • Tuesday, February 26, 2013 6:24 PM
     
     
    Sign In to Vote
    0

    Does anyone know if running an Alter statement (XMLA) which modifies the Query Definition on an already processed partition results in an updated partition header?

    We are trying to manage processing for several large distinct count measure groups and need to know the partition header is still optimized for read performance following a query definition change. Thanks in advance for any info -- Jeremy

    OS: Windows Server 2008 R2, SQL Server 2012

    <Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Object>
            <Database>TestCubeID</Database></DatabaseID>
            <CubeID>TestCubeID</CubeID>
            <MeasureGroupID>User Count</MeasureGroupID>
            <PartitionID>User Count-1</PartitionID>
        </Object>
        <ObjectDefinition>
            <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
                <ID>User Count-1</ID>
                <Name>User Count-1</Name>
                <Source xsi:type="QueryBinding">
                    <DataSourceID>WHLST</DataSourceID>
                    <QueryDefinition> SELECT.... between 18226868 and 19140628</QueryDefinition>
                </Source>
                <StorageMode>Molap</StorageMode>
                <ProcessingMode>Regular</ProcessingMode>
                <ProactiveCaching>
                    <SilenceInterval>-PT1S</SilenceInterval>
                    <Latency>-PT1S</Latency>
                    <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
                    <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
                    <Source xsi:type="ProactiveCachingInheritedBinding" />
                </ProactiveCaching>
                <EstimatedRows>20000000</EstimatedRows>
                <AggregationDesignID>Aggregation1</AggregationDesignID>
            </Partition>
        </ObjectDefinition>
    </Alter>

     

All Replies

  • Thursday, February 28, 2013 6:57 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Jeremy,

    Thank you for your question. I am currently looking into this issue and will give you an update as soon as possible.

    Thank you for your understanding and support.

    If you have any feedback on our support, please click here.

    Regards,

    Elvis Long
    TechNet Community Support

     
  • Thursday, February 28, 2013 2:18 PM
     
     
    Sign In to Vote
    0

    Hello Jeremy,

    [Q]Does anyone know if running an Alter statement (XMLA) which modifies the Query Definition on an already processed partition results in an updated partition header?

    If I am understanding this correctly, you want to modify the SQL Query used by a partition and as want to partition status as Processed. In simple words you don’t want to process partition after modifying query underneath it.

    Thanks

    Karan Gulati {MCSE, MCDBA, MCTS, OCAP, OCP,SSAS Maestro (MCM)}| DS Escalation Services

    Facebook BI Page|Twitter|Linked In|Personal Site|Blog Space

     
  • Monday, March 04, 2013 5:44 PM
     
     
    Sign In to Vote
    0

    Hi Karan - Correct, I want to know if the header is updated after (1) running an alter statement on the partition which changes the Query Definition (SQL) or (2) running merge on the partition. The importance of the partition header is detailed in a couple of articles including Analysis Services Distinct Count optimization and a separate write-up on using SSDs to achieve optimal parallelism.

    Specifically "The Analysis Services Storage Engine will initially query the header file to determine which data partitions to query for the range of distinct count values. This way, the storage engine queries only partitions that have the values required to complete the calculation." 

    http://sqlcat.com/sqlcat/b/technicalnotes/archive/2010/09/20/analysis-services-distinct-count-optimization-using-solid-state-devices.aspx

    Example workflow (1) create new partition and process (2) alter partition query definition (3) merge 3-4 other partitions into the new partition  

     
  • Tuesday, March 05, 2013 3:03 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    The header data that you are referring to does not get updated when you run an alter statement.

    That header information is only updated by processing and partition merging. 

    So in your example workflow you could do step 2 at any point. Or you could even skip it entirely. It would not affect the data in the partition, but that would mean that if you ever re-processed that partition it would not pull in the correct data.

    http://darren.gosbell.com - please mark correct answers

     
  • Tuesday, March 05, 2013 8:27 AM
     
     
    Sign In to Vote
    0

    Make sense...Darren.

    Jeremy, if you got your answer. please mark it as correct answer or let us know if you need futher clarifications.

    Thanks

    Karan Gulati {MCSE, MCDBA, MCTS, OCAP, OCP,SSAS Maestro (MCM)}| DS Escalation Services

    Facebook BI Page|Twitter|Linked In|Personal Site|Blog Space

     
  • Tuesday, March 05, 2013 5:25 PM
     
     
    Sign In to Vote
    0

    "That header information is only updated by processing and partition merging. " 

    Thank you! Thats what I needed to know

    The partition will be merged at the end - so it should have an updated header based on the new query definition applied in the previous step. 

     
  • Tuesday, March 05, 2013 7:46 PM
    Moderator
     
     
    Sign In to Vote
    0

    The partition will be merged at the end - so it should have an updated header based on the new query definition applied in the previous step. 

    This is not correct.

    The header in the merged partition is based on the header information in the source and destination partitions. It is based on the data in the partitions, the query definition is irrelevant to the merging operation. The header stores the max and min values for the internal DataId values for each dimension and the merge operation simple takes the smallest minimum and the largest maximum for each dimension and uses that in the new destination partition.

    The only time the query definition is relevant in a merged partition is if you ever re-process the partition to reload all the data. If you never plan to reprocess the data you technically don't even need to change the query definition (although I think it is good housekeeping to keep the query definition in synch with the data if you can)

    http://darren.gosbell.com - please mark correct answers

     
  • Wednesday, March 06, 2013 12:05 AM
     
     
    Sign In to Vote
    0
    Hi Darren - Thanks for the clarification.

    Does the below example look correct?

    1) Start w/ 4 Partitions
    Partition 1,  Where Clause: WHERE UserKey BETWEEN -1 and 10
    Partition 2,  Where Clause: WHERE UserKey BETWEEN 11 and 20
    Partition 3,  Where Clause: WHERE UserKey BETWEEN 21 and 30
    Partition 4,  Where Clause: WHERE UserKey BETWEEN 31 and 100

    2) Add 2 partitions
    Partition 5,  Where Clause: WHERE UserKey BETWEEN 101 and 1000 and Date BETWEEN '1/1/2013' and '1/31/2013'
    Partition 6,  Where Clause: WHERE UserKey BETWEEN 101 and 1000 and Date BETWEEN '2/1/2013' and '2/28/2013'

    3) Merge partitions 6 into 5

    4) Final result (no alter on partition 5)
    Partition 1,  Where Clause: WHERE UserKey BETWEEN -1 and 10
    Partition 2,  Where Clause: WHERE UserKey BETWEEN 11 and 20
    Partition 3,  Where Clause: WHERE UserKey BETWEEN 21 and 30
    Partition 4,  Where Clause: WHERE UserKey BETWEEN 31 and 100
    Partition 5,  Where Clause: WHERE UserKey BETWEEN 101 and 1000 and Date BETWEEN '1/1/2013' and '1/31/2013' **

    **For Partition 5, the header will have UserKey 101 to 1000 and Date 1/1/2012 to 2/28/2013

    This is very helpful. Thanks again
     
  • Wednesday, March 06, 2013 9:32 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    That is conceptually correct. Technically it's a bit more complicated as the header does not store member values, it stores member DataIDs and these can become fragmented which means that the header values can cover a wider range that the actual data in the partition.

    And while it's not strictly necessary to update the partition query in order to merge the partitions it's good housekeeping to update it as the partition then becomes self documenting. 

    http://darren.gosbell.com - please mark correct answers

     
  • Monday, March 18, 2013 8:37 AM
     
     
    Sign In to Vote
    0

    Hello Jeremy,

    Please let us know if you need further clarification around this topic, or are we good to mark Darren's reply as correct answer to your question.

    Regards

    Thanks

    Karan Gulati {MCSE, MCDBA, MCTS, OCAP, OCP,SSAS Maestro (MCM)}|DS Escalation Services

    Facebook BI Page|Twitter|Linked In|Personal Site|Blog Space


     
  • Thursday, March 28, 2013 6:16 PM
     
     
    Sign In to Vote
    0
    Hi Karan - Darren answered my question. Thank you for the help!