CODEWITHSUNDEEP
mdxolapolap-cube
tcash21
tcash21

Reputation: 4995

MDX Inner Join returns duplicate rows and inflates measures

I have two tables in my cube that I am joining on by ZIP but end up with duplicate records because there are multiple rows per ZIP. 

TABLE_A
ZIP     CATEGORY   TYPE    VALUE90  VALUE75
33211   TOYS       TRAINS  GT        LT
33211   TOYS       TRAINS  GT        GT
33211   KITCHEN    TRAINS  GT        LT


TABLE_B   
ZIP       SALES   CATEGORY   DATE
33211     5.00     TOYS      10/10/11

I want to be able to sum sales for the zips that are of type TRAINS and have VALUE90 = GT, however since it's doing an inner join on ZIPS I get multiple rows and in this example would get SALES = 10.00. In SQL I could do a select in (select distinct(zips) ...) but how can I do this in MDX? There's probably a better way to structure Table_A but I'm not sure what it would be. I essentially have values like 90, 75,50,25 for a particular level and I need to be able to query for zips by categories and types for different combinations of those values.

SELECT 
    NON EMPTY {([CATEGORY].Members)} ON 0,
    NON EMPTY {([Measures].[SALES])} ON 1
FROM [mycube]
WHERE ([TABLE_A].[TYPE].[TRAINS].[GT])

Upvotes: 0

Views: 1084

Answers (2)

Heidi Steen
Heidi Steen

Reputation: 81

I agree, it looks like many-to-many, just not set up all the way. The documentation on many-to-many was just rewritten last month (SSAS product documentation), see if it helps? http://technet.microsoft.com/en-us/library/ms365407.aspx

Upvotes: 0

FrankPl
FrankPl

Reputation: 13315

You have a design that is not a star schema. The dimension table (TABLE_A) should have a primary key that is unique per record, and that should be referenced from the fact table (the table containing the measures).

Upvotes: 2

Related Questions