none
Adding custom aggregations in existing default aggregation created by Analysis service

    Question

  • There is a measuregroup which has single partition, now I need aggregations as following

    1) Default agggregations that SSAS creates

    2) Two custom aggregations

    I can achieve this through Visual basic, other tools. But, I want to achieve the same using XLMA, making a SOAP request to SSAS.

    The challenge I have here is, if I alter the default aggregations created by SSAS, and try to add two custom aggrgations, the existing aggregations are overridden by these two custom aggregations.

    Things tried, but did not give achievement are as follows:

    1) Alter the the aggregation design, and try to add extra two custom aggregations, this overrides the existing aggregations.

    2) Using SSMS, altered the existing aggregations and added two custom aggregations, achieves the goal, but this cannot be done making a SOAP request to SSAS, as getting the entire XMLA of existing aggregations does not seem feasible.

    Our design is , single measuregroup with single partitions with, two aggregations design assign to it/ Single aggregation design including both default aggregations created by SSAS along with two custom aggregations.

    Note: A single partition can have only one aggregation assigned to it, and only one partition is required by the design.

    Wednesday, May 16, 2018 10:30 AM

All replies

  • Hi sridar1992,

    Thanks for your question.

    >>>Our design is , single measuregroup with single partitions with, two aggregations design assign to it/ Single aggregation design including both default aggregations created by SSAS along with two custom aggregations.
    Just like you said, a single partition can have only one aggregation assigned to it, It is not support to assign two aggregations to a single partition currently.

    In your scenario, you might create several partitions fro your measure group and assign the custom aggregations to the partitions without aggregations.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Friday, May 18, 2018 11:06 PM
    Thursday, May 17, 2018 2:20 AM
    Moderator
  • Is there any way where I can alter the default aggregations and insert my two custom aggregations into it,

    for an eg, using ssms,In cube, I can Right click on aggregations that has been materialized(Hence,it has aggregations created by ssas)--> Script aggregations design as --> Alter to --> New Query Editor window --> Add my two custom aggregations into it --> Then execute that XLMA(Clicking F5)

    The above steps serves my purpose as, I have the entire XMLA of default aggregations, and I only add my two custom aggregations in it and execute it.

    This works, but I want to do same thing using a SOAP request, How can I achieve it.

    Thursday, May 17, 2018 8:07 AM
  • Hi,

    I agree with willson, I do not think you can insert two custom aggregation into a single measuregroup with single partitions .


    Friday, May 18, 2018 11:05 PM
  • The tool gives me the provision where I can alter the aggregations, and insert two custom aggregations into it. I just need an alternate way to do the same from my application.
    Monday, May 21, 2018 8:57 AM
  • Hi There,

    I can not think of a way to do so. I would also like to learn if someone can achieve this new feature.

    Tuesday, May 22, 2018 5:49 AM
  • I strongly feel Microsoft being such a huge company, it must have a work around. But, I dont know which way it is. A help is really appreciated here.
    Wednesday, May 23, 2018 3:26 PM
  • Hi sridar1992,

    Thanks for your response.

    Please refer to another thread that you have posted.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 25, 2018 2:41 AM
    Moderator
  • Thanks for your kind reply, but it is overriding the previously created aggregations in it.

    I would want my existing aggregations to be there, and after altering, these two aggregations must be added, keeping the precious one.

    I want a kind of append mode for adding aggregations

    Monday, May 28, 2018 11:23 AM
  • why do you need to add aggregations to a single partition?
    Monday, May 28, 2018 12:44 PM
  • Need to create reports using 5 to 6 dimensions.... Custom aggregations would help create this report(with performance).

    When using 1 or 2 or 3 dimensions, the default aggregations by ssas would be useful.

    Hence, need both default aggregations and custom aggregations

    Monday, May 28, 2018 2:50 PM
  • Need to create reports using 5 to 6 dimensions.... Custom aggregations would help create this report(with performance).

    When using 1 or 2 or 3 dimensions, the default aggregations by ssas would be useful.

    Hence, need both default aggregations and custom aggregations

    No, the default aggregations will do it, no need to add custom aggregations.
    Monday, May 28, 2018 10:35 PM
  • The default would take care but, adding the two custom aggregations gives extra performance
    Tuesday, May 29, 2018 2:37 PM
  • I got this issue resolved,

    Steps:

    1) After materializing your aggregation design, SSAS creates default aggregations.

    2) Now, that you have default aggregations created and you want to add/append your custom aggregations into the same aggregation design using XMLA, as SSAS using XMLA works in 1/0 mode. Hence, you would require the default aggregation design XMLA at your application, to get that use the following.

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

           <RequestType>DISCOVER_XML_METADATA</RequestType>

           <Restrictions>

                  <RestrictionList>

                         <ObjectExpansion>ExpandFull</ObjectExpansion>

                         <DatabaseID>DATABASE_ID</DatabaseID>

                         <CubeID>CUBE_ID</CubeID>

                         <MeasureGroupID>MEASURE_GROUP_ID</MeasureGroupID>

                         <AggregationDesignID>DEFAULT_AGGREGATION_DESIGN</AggregationDesignID>

                  </RestrictionList>

           </Restrictions>

           <Properties>

                  <PropertyList>

                  </PropertyList>

           </Properties>

    </Discover>

    This will give you default aggregation design XMLA.

    3) Create your custom aggregations, and then alter the default aggregation design-->Add custom aggregations+the output default aggregations from step 1.

    4) Now execute this alter command.

    Doing so, you would have your default+custom aggregations in one aggregation design, using XMLA+language like JAVA.

    Hope this helps!

    Thursday, June 14, 2018 7:03 AM
  • Hi sridar1992,

    Thanks for sharing the solution. I am glad to know that you have found the solution by yourself, Please kindly mark it as an answer to close this thread.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, June 14, 2018 8:02 AM
    Moderator