Microsoft KB Archive/307748

= PRB: Duplicate Rows in Dimension Tables Inflate Measures =

Article ID: 307748

Article Last Modified on 2/19/2007

-

APPLIES TO


 * Microsoft SQL Server OLAP Services
 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q307748



SYMPTOMS
When building a cube, the value of measures contained in the cube are inflated or do not match the values returned when querying the source fact table.



CAUSE
One or more of the source tables supporting one or more dimensions contain duplicate entries.



WORKAROUND
Make sure that the process used for scrubbing data removes duplicate rows in the tables used to support dimensions.



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.

Additional query words: OLAP

Keywords: kbprb KB307748

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.