none
SSAS Aggregation- Why does “usage based optimization” wizard create different aggregations for the same set of QlapQueryLog data RRS feed

  • Question

  • Why does “usage based optimization” wizard create different aggregations for the same set of QlapQueryLog data?

    To explain the above question further , if the OlapQuerylog has logs over 5 days and if the aggregations are created using the below 2 approaches :

    1. Using wizard on the entire log records
    2. Using wizard to create aggregations on logs per day – so 1 set of aggregation created for day 1 , again create additional aggregation for day2 , again additional aggregation for day 3 and so on

    Why does the two approaches lead to different aggregations?

    Since the OlapQueryLog table has same data anyway , shouldn’t the wizard create the same aggregations ?

    Thursday, July 4, 2019 6:04 AM

All replies

  • Hi Prashant,

    Because in the UBO wizard, there is a specify query criteria wizard, which filters the queries per date, per user or per frequency from query log table. Based on specific queries with long duration, it would lead to different aggregations.

    That is not surprising.

    For more details, please see:

    Improve SQL Server Analysis Services Performance with the Usage Based Optimization Wizard

    Best Regards,

    Will


    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, July 4, 2019 10:07 AM
  • Hi will ,

    Don’t think it is due to the filters etc.

    Please see some further explanation on the question.

    ---

    We have got a set of specific queries which we ran to generate the OlapQueryLog  table using same user.

    To further clarify on these queries – these are distinct queries fetched from out production log based on their frequencies.

    So basically these are the most frequent queries that have been fired in the production . But we have taken only one occurrence of the query , which means every query only occurs once.

    These queries  were executed in batches of 5 through 5 different days to get the OlapQuerylog’s data.

    While using the UBO, we followed two techniques:

    1. We took the entire log and we did not check any filter criteria – dates/user/frequency – this created a certain number of aggregations

    At a later instance we dropped the above aggregations and recreated them as below based on the same existing olapquerylog data

    1. We created the aggregations in batches: first for logs on day 1( date filter) , then additional aggregations based on logs created on day two and so on…

    But if you see we actually again took the entire log in considerations and included all days … only the aggregation creation was in 5 steps rather than at one go.

    This now resulted in much more number of aggregations on the cube.

    SO what we need to understand is , if the queries are exact same/ the logs are same /user is same/duration is same… why does a phased approach result in more aggregation since the base information available to the UBO is same


    PrashantAgrawal

    Monday, July 8, 2019 10:48 AM
  • Hi PrashantAgrawal,

    Thanks for your detailed explanation.

    How many aggregations would be created doesn't just depends on the number of OlapQuerylog table records . Also, it is not as you said that the logs, queries, etc. are the same as first operation. How many aggregations would be created or what aggregations would be created is based on the summary analysis of current OlapQuerylog table records, not a measurement of numbers of table records. 

    As you know, there is a column dataset in the table OlapQuerylog, it is designed to be read by SSAS and not necessarily us humans. Most factors of creating aggregations is mainly based on this column.

    Reference

    SSAS OlapQueryLog – Analyzing Dimension Attribute Usage

    Best Regards,

    Will


    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.

    Wednesday, July 10, 2019 8:41 AM
  • test

    Admin Forums Test

    Wednesday, July 10, 2019 8:49 AM