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

Answer 1

Girija,

 

The following is from an Excel 2007 OLAP cube browsing session.  Same in Management Studio Cube Browser.

 

It includes Sales Reason in addition to Sales Reason Type.

 

A bit puzzling, the Other Sales Reason Type does not sum up correctly:

 

 

	Values
Row Labels	Internet Sales Amount	Internet Order Count
Marketing	$27,475.82	722
Other	$18,678,948.02	20,576
Manufacturer	$5,998,122.10	1,746
Other	$248,483.34	1,395
Price	$10,975,842.56	17,473
Quality	$5,549,896.77	1,551
Review	$1,694,881.98	1,245
Promotion	$6,361,828.95	3,515
Grand Total	$29,358,677.22	27,659

 

The query:

 

Code Snippet

SELECT D.SalesReasonReasonType,

Total=Sum(F.SalesAmount),

Orders=COUNT(DISTINCT F.SalesOrderNumber)

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

 

 

results:

 

SalesReasonReasonType	Total	Orders
Marketing	$27,475.82	722
Other	$24,467,226.75	20576
Promotion	$6,361,828.95	3515

 

If nobody comes up with a good explanation, you can file a bug submission at: http://connect.microsoft.com/sqlserver

 

Let us know what happens.

Answer 2

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

Answer 3

 

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

Answer 4

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

Answer 5

Darren,

 

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

 

Regards..

Girija Shankar