PRB: Duplicate Rows in Dimension Tables Inflate Measures (307748)

MORE INFORMATION

The SQL syntax used to create dimensions is different than the SQL syntax used to populate cubes. Queries used to create dimensions use syntax similar to the following:

SELECT DISTINCT CUSTOMER.COUNTRY, CUSTOMER.STATE_PROVINCE, 
  CUSTOMER.CITY, CUSTOMER.CUSTOMER_ID, CUSTOMER.FNAME+CUSTOMER.LNAME
  FROM CUSTOMER
				

Because the query used to build dimensions uses a DISTINCT keyword, duplicate rows are eliminated from the resultset.

Queries used to populate a cube, however, use syntax similar to the following:

SELECT STORE.STORE_ID, TIME_BY_DAY.THE_YEAR, TIME_BY_DAY.QUARTER,
 PROMOTION.MEDIA_TYPE, PROMOTION.PROMOTION_NAME, CUSTOMER.CUSTOMER_ID,
 CUSTOMER.EDUCATION, CUSTOMER.GENDER, CUSTOMER.MARITAL_STATUS, 
 CUSTOMER.YEARLY_INCOME, SALES_FACT_1997.UNIT_SALES, 
 SALES_FACT_1997.STORE_COSE, SALES_FACT_1997.STORE_SALES, 
 SALES_FACT_1997.PRODUCT_ID,
 SALES_FACT_1997.STORE_SALES-SALES_FACT_1997.STORE_COST FROM 
 SALES_FACT_1997, STORE, TIME_BY_DAY, PRODUCT, PROMOTION, CUSTOMER
 WHERE
  (SALES_FACT_1997.STORE_ID=STORE.STORE_ID) AND 
  (SALES_FACT_1997.TIME_ID=TIME_BY_DAY.TIME_ID) AND 
  (SALES_FACT_1997.PRODUCT_ID=PRODUCT.PRODUCT_ID) AND 
  (SALES_FACT_1997.PROMOTION_ID=PROMOTION.PROMOTION_ID) AND 
  (SALES_FACT_1997.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID)
				

This syntax effectively results in an inner join between the fact table and all matching rows in the dimension tables.