Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Analysis Server and Reporting Services (2005)  Customer Dimension Problems

Author  Topic  mzinga Starting Member 1 Post Posted - 2009-01-26 : 19:16:19 I'm having issues with my customer dimension. We have made some changes to this model, and now the customer dimension is having major fits. I'm a recent transplant from AS2000, so not sure that we created the dimension properly to begin with. We are using a third-party application that facilitates cube browsing and updating. When we tried to auto-build the dimension the third-party application did not work, so now we have created the dimension manually. At this time, the issues are in OLAP and not in pulling the data into the third party application. Let me outline how we created the dimension, and would like to get some feedback to see what the issues may be. Manual dimension creation -- Dimension Table def Cust_Num (PK_INT) Super_Segment (Varchar) Segment (Varchar) Sales_Manager (Varchar) Headquarter (Varchar) Banner (Varchar) State (Varchar) Customer_Key (Varchar) The customer key column is a concat field of Cust_Num + Banner Defined Customer_Key as the key level of the dimension Structure of the Dimension should be Super_Segment --> Segment --> Sales_Manager --> Headquarter --> Banner --> State --> Customer Key Do define these relationships defined the following attribute relationships Customer Key attr relationship to state State relationship to banner Banner relationship to Headquarter Headquarter relationship to Sales manager Sales Manager relationship to Segment Segment Relationship to Super Segment Then defined the hierarchy as illustrated ( Super_Segment --> Segment --> Sales Manager --> Headquarter --> Banner --> State --> Customer Key The dimension performed properly with the following structure ( Super_Segment --> Headquarter --> Banner --> State) Dimensional table was purged and Segment, Sales Manager, and Customer Key were added, we ensured that all dimensional data was valid. With the new dimension, it appears that the data that AS is receiving is not the data in the dimensional table. We are getting Customer Keys related to parents that it does not belong too. Have put about 80 hours into this before I turned here, so any and all comments would be welcome.