none
Comparing results of two MDX queries

    Question

  •  

    In our application we need to compare transaction totals/averages of customers to the totals/averages of the groups they belong to. The groups are defined by the values of various levels in the hierarchy of the dimension where the customer Id is the leaf. The levels are based on different fields of the customer table, while the transaction values are taken from the ActivityHist table.

    The first set of values is returned by an MDX query similar to the following:

    Code Snippet

    With Member Measures.PeerGroup As 'new1CustomerRiskClass.currentmember.parent.parent.uniquename '
    select { Measures.[PeerGroup], Measures.[baseamt]} on
    columns,
    {nonEmptyCrossjoin(new1CustomerRiskClass.[Id].members
    ,[RecvPay].[RecvPay].Members)}
        Dimension PROPERTIES [Id].Name, [RecvPay].[recvpay].Name
        on rows
    from new1 where ([bookdate].&[2007].&[3].&[8])

     

     

    Here new1CustomerRiskClass is the customer dimension with levels of RiskClass.[Group Id].[Id]
    RecvPay is a separate dimension containing integer values (1 for receive, 2 for pay) and bookadate is yet another dimension with a hierarchy of year.quarter.month.dayofyear (here the last one is not being used)

    The second set of values is returned by a query similar to the following:

    Code Snippet

    With Member Measures.AmtAvg As 'avg(Descendants(new1customerriskclass.Currentmember, [id]), measures.[CustAvgAmt])', FORMAT_STRING =
    '#.00'
    Member Measures.AmtTolerance As  'Iif(MaxAmtAvg = MinAmtAvg, MaxAmtAvg * 0.5, 10 * stdev(Descendants(new1customerriskclass.Currentmember, [id]),
    measures.[CustAvgAmt]))' , FORMAT_STRING = '#.00'
    Member Measures.PeerGroup As 'new1customerriskclass.currentmember.uniquename '
    Select { Measures.[PeerGroup], Measures.[AmtAvg]} on columns,
    {Filter(nonemptycrossjoin(new1customerriskclass.riskclass.members
    ,[RecvPay].[recvPay].members),
             (Measures.[AmtAvg] > 0.0))} Dimension
    PROPERTIES
            [RecvPay].[RecvPay].name  on rows
    from new1

     

     

    The dimensions are as before

    It's more complex than that, but this is a sufficient example

    What we did with this in the previous version of our application, running on AS 2000, was to dump the results of each into a temporary table from SQL using linked servers and a 'Select * from OpenQuery' and then do a join to calculate the differences.

    The problems we are running into trying to do this in AS 2005 are as follows:

    • The first query when executed via linked server, when run on AS 2000 used to return just four columns: [Id], [PeerGroup], and [BaseAmt]. Now in AS 2005 it returns extra columns for each level above the [Id] level in the customer dimension (in my example above, it returns [RiskClass] and [Group Id] followed by the same columns as in AS 2000). We do nut want these extra columns.
    • In both queries, some the columns which used to be returned as nvarchar in 2000 are now returned as ntext, meaning that things like RecvPay fail to convert into Integer upon insert.
    • Since the column names are inconsistent, we have to use "Select *", so we cannot use an explicit conversion

    My question then is, is there a better way to efficiently perform this comparison in AS 2005 without involving SQL and Linked Servers? If not, how can I do this in SQL? Are the column names guaranteed to be consistantly following some convention? Can I get rid of the extra columns in the first query? Can I get the values to be returned as nvarchar or some other compatible format?

     

    Thanks in advance,

    Boris Zakharin, MCAD
    Metavante Risk and Compliance

    Monday, August 27, 2007 4:30 PM