MDX Inner Join returns duplicate rows and inflates measures

Question

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])

FrankPl FrankPl · Accepted Answer · 2013-10-10T16:21:36

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).

MDX Inner Join returns duplicate rows and inflates measures

2 Answers