none
What is wrong in my query

    Question

  •  

    Hi guys,

     

    I was trying to write a SQL query for a many to may scenario in ADW sample cube. I will explian it further:

     

    * The DimReseller Dimension is related to Fact Internet sales dimension in Many to Many relationship.

    * The intermediate measure group is Fact internet sales reason

    * If you see in DSV the Dim Reseller table is related to Fact Internet Sales Reason on "SalesReasonKey" and Fact Internet Sales Reason Table is related to Fact Internet Sales on " Sales OrderNumber and Sales OrderLineNumber"

    * After Processing the cube I dragged Measure "SalesAmount" from Fact Internet Sales aand Attribute "Sales Reason Reason Type" from DimSalesReason dimension. The dataset returned was as below :

     

    Marketing : 2745.8200000002

    Other : 18678948.021594

    Promotion: 6361828.95280113

     

    Now I wrote a SQL query to follow the scenarion. The query used and the returned data is as follows :

    Code Snippet

    SELECT D.SalesReasonReasonType,Sum(F.SalesAmount)

    FROM (SELECT DISTINCT SalesReasonReasonType ,SalesReasonKey FROM [dbo].[DimSalesReason]) AS D

    INNER Join FactInternetSalesReason IMG ON D.SalesReasonKey = IMG.SalesReasonKey

    INNER JOIN FactInternetSales F on F.SalesOrderNumber = IMG.SalesOrderNumber And F.SalesOrderLineNumber = IMG.SalesOrderLineNumber

    GROUP BY D.SalesReasonReasonType

     

     

    Marketing : 2745.82

    Other : 24467226.7516

    Promotion: 6361828.9528

     

    ****As you can mark very clearly the data only diffeers on "Other" value.

     

    What am I doing wrong here. For all other attributes of the same dimension (apart from this attribute above) everything is giving right result (For SQL if I change the corresponding column name).

     

    Please let me know where I am going wrong.

     

     

    Regards..

    Girija Shankar

    Sunday, December 7, 2008 11:08 AM
    Answerer

Answers

  • This is not a bug in the M2M relationships. M2M relationships give you results similar to a distinct count in that they are non-additive.

     

     If I make a $100 order and list my reasons as Price and Quality you would see results like

     

    Other   100   1

    Price    100   1

    Quality 100  1

     

    You would not want to see $200 and 2 orders as this would not be correct. You would see inflated figures just because I listed more than 1 reason for buying.

     

    What you want to do is to get the distinct orders in each Type and then join that back to the FactInternetSales. It should look like the following:

     

    SELECT m2m.SalesReasonReasonType,Sum(F.SalesAmount)

    FROM FactInternetSales F

    INNER JOIN

    (

      Select distinct salesOrderNumber, SalesOrderLineNumber, D.SalesReasonReasonType

      FROM [dbo].[DimSalesReason] AS D

      INNER Join FactInternetSalesReason IMG

        ON D.SalesReasonKey = IMG.SalesReasonKey

    ) m2m

    on F.SalesOrderNumber = m2m.SalesOrderNumber

    And F.SalesOrderLineNumber = m2m.SalesOrderLineNumber

    GROUP BY m2m.SalesReasonReasonType

    Sunday, December 7, 2008 8:54 PM
    Moderator

All replies

  • Well nothing wrong in the query, I'm also puzzled.  If get any clue do share with us.

     

    Only one suggestion with query you don't need

    (SELECT DISTINCT SalesReasonReasonType ,SalesReasonKey FROM [dbo].[DimSalesReason])

     

    Simply use table name DimSalesReason, distinct hampering the query performance.

     

     Girija Shankar (India) wrote:

     

     

    Code Snippet

    SELECT D.SalesReasonReasonType,Sum(F.SalesAmount)

    FROM (SELECT DISTINCT SalesReasonReasonType ,SalesReasonKey FROM [dbo].[DimSalesReason]) AS D

    INNER Join FactInternetSalesReason IMG ON D.SalesReasonKey = IMG.SalesReasonKey

    INNER JOIN FactInternetSales F on F.SalesOrderNumber = IMG.SalesOrderNumber And F.SalesOrderLineNumber = IMG.SalesOrderLineNumber

    GROUP BY D.SalesReasonReasonType

     

     

    Sunday, December 7, 2008 3:59 PM
    Moderator
  •  

    Yes,

     

    correct, the total for the Other is not summing up correctly. The result of SQL is actually correct as it gives total solution.

     

    Donot know how to get an exact answer to this behaviour. Is this a bug on many to many .. Let us wait for some other expert inputs on this before confirming this is a bug.

     

    Regards..

    Girija Shankar

    Sunday, December 7, 2008 4:45 PM
    Answerer
  • This is not a bug in the M2M relationships. M2M relationships give you results similar to a distinct count in that they are non-additive.

     

     If I make a $100 order and list my reasons as Price and Quality you would see results like

     

    Other   100   1

    Price    100   1

    Quality 100  1

     

    You would not want to see $200 and 2 orders as this would not be correct. You would see inflated figures just because I listed more than 1 reason for buying.

     

    What you want to do is to get the distinct orders in each Type and then join that back to the FactInternetSales. It should look like the following:

     

    SELECT m2m.SalesReasonReasonType,Sum(F.SalesAmount)

    FROM FactInternetSales F

    INNER JOIN

    (

      Select distinct salesOrderNumber, SalesOrderLineNumber, D.SalesReasonReasonType

      FROM [dbo].[DimSalesReason] AS D

      INNER Join FactInternetSalesReason IMG

        ON D.SalesReasonKey = IMG.SalesReasonKey

    ) m2m

    on F.SalesOrderNumber = m2m.SalesOrderNumber

    And F.SalesOrderLineNumber = m2m.SalesOrderLineNumber

    GROUP BY m2m.SalesReasonReasonType

    Sunday, December 7, 2008 8:54 PM
    Moderator
  • Darren,

     

    Thanks for the reply.. I will check and get back to you if I have any issues.

     

    Regards..

    Girija Shankar

    Sunday, December 7, 2008 9:06 PM
    Answerer