none
Using AMO to design UBO aggregates run into an error

    Question

  • Hi.

    I try to use used based optimization with the following AMO code. But I get an error, that an attribute of a dimension is NULL and the AggregationDesigner cannot build an aggregate on this. Is this a problem in the code (like a problem with the query list (I do put the "Dataset" column into it only actually. ). - If I try to build an aggregate with the wizard in SSDT everything is fine.

    Cheers,

    Nils

        Dim strSQL As String = "SELECT [Dataset],COUNT(*) FROM [dbo].[OlapQueryLog] WHERE MSOLAP_ObjectPath = @ObjPath AND StartTime >= @StartTime GROUP BY [Dataset]"
                Dim SqlComm As SqlCommand = New SqlCommand(strSQL, _SqlConn)
                SqlComm.Parameters.Add(StartTime)
                SqlComm.Parameters.Add(ObjPath)
                Dim SqlReader As SqlDataReader = SqlComm.ExecuteReader
    
                Dim FirstTime As Boolean = True
                Dim Finished As Boolean = False
                Dim Optimization As Double
                Dim Storage As Double
                Dim AggregationCount As Int64
    
                If SqlReader.HasRows Then
                    While SqlReader.Read
                        QueryList.Add(SqlReader.GetString(0)) 'SqlReader.GetInt32(1).ToString + "," +
                    End While
    
                    With AggrDesign
                        .InitializeDesign()
                        While Not Finished And Optimization < MaxOptimization And Storage < MaxStorage And AggregationCount < MaxAggregationCount
                            If FirstTime Then
                                .DesignAggregations(Optimization, Storage, AggregationCount, Finished, QueryList)
                                FirstTime = False
                            Else
                                .DesignAggregations(Optimization, Storage, AggregationCount, Finished)
                            End If
                        End While                    
                        .FinalizeDesign()
                        .Update()
                    End With
                End If


    Tuesday, August 27, 2013 1:38 PM

All replies

  • Hi Whuppi,

    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.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Friday, August 30, 2013 5:15 AM
  • Hello,

    Where do you add the aggregation design to the measure group? I can't see it in your example script.
    Please see following blog for an example:
    http://sqlblog.com/blogs/erin_welker/archive/2009/11/15/automating-ubo.aspx


    HTH,
    Orsi
    Microsoft Online Community Support

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, September 17, 2013 2:47 PM
  • Where do you add the aggregation design to the measure group? I can't see it in your example script.
    Please see following blog for an example:
    http://sqlblog.com/blogs/erin_welker/archive/2009/11/15/automating-ubo.aspx

    Hi.

    I use the aggregation design that is actually mapped to the measure group. It is a only a small detail from the whole program.

    The main issue seems that I have to use unmaterialized reference dimensions in the cube. The QueryLog stores it like normal ones. And if I send the QueryLog to AMO it tries to build aggregates on it which is not possible. As a result it seems that UBO is not usable with unmaterialized reference dimensions. I will try to do some further investigations...   but is more or less similar to the UBO wizard which wrongly ignores unmaterialized ref. dims.

    Cheers,

    Nils

    PS. Yes, this error can be reproduced with unmaterialized ref. dims. only. Without the scripts works fine. Seems to be a bug in UBO implementation, that no mapping to the intermediate attribute is done.


    Tuesday, September 17, 2013 3:01 PM
  • Hello Nils,

    It is by design that attributes from nonmaterialized reference dimension cannot be included in aggregations, because the key attribute of the reference dimension is not included in the fact table.
    You have two option to avoid the error:
    - use materialized reference dimension
    - use AggregationUsage property None to remove the attributes from the aggregation

    HTH,
    Orsi
    Microsoft Online Community Support

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, September 18, 2013 11:17 AM
  • Hi Orsi,

    as a remarked the topic is about UBO and not manual aggregation design. You cannot change the aggregation matrix if you like to create aggregates automatically.

    - use materialized reference dimension is not possible because of the bug with unkown member and that every partition has to be reprocessed on a change

    - AggregationUsage is not important, because I does not want to include the reference dimension itself, I like to add the intermediate dimension attribute instead. This will include the reference dimension indirectly to the aggregate and will increase performance well.

    Thanks,

    Nils

    Wednesday, September 18, 2013 11:55 AM