Can't Seem to Figure Out Why I See What I Do -- Should Be EASY

Let's say my customer dimension is similar to the following:

Customer_key | CustName | AcctOwner
--
1 | ABC Company | Joe Cool
1 | ABC Company | John Doe

* My customer dimension is a Named Query in the DSV where I'm doing a LEFT OUTER JOIN to generate the AcctOwner

Now let's say my Sales Fact table looks like the following:

Customer_key | Product_key | InvoiceAmt
-
1 | 1 | 5.00
1 | 2 | 2.75
1 | 3 | 0.25

Now if my pivot simply contains CustName, AcctOwner and InvoiceAmt, shouldn't I see the following:

CustName | AcctOwner | InvoiceAmt
--
ABC Company | Joe Cool | 8.00
ABC Company | John Doe | 8.00

However, I only see one line and I cannot figure out why I see the line that I do in the cube; can't seem to figure out why it's showing the Account Owner that it's showing.

Surely I'm having a brain fart and it's something really stupid where I'll be embarrassed. Please let me know if additional information is needed.

It's an AS2005 SP2 cube.

It's because your customer keys are not unique. When SSAS builds the dimension it will collect a unique list of the keys, then it will generate the CustName and AcctOwner attributes, with logic Equivalent to:

UDPATE <dimension> SET AcctOwner = dimCustomer.AcctOwner WHERE Customer_Key = 1

I suspect that what you have here is that AcctOwner should not be an attribute of the customer, but rather is a many to many relationship where one account owner can have many customers and one customer can have many account owners.

If this is the case you need to create a bridge table with the account owner and customer relationship and set this up as a measure group which you can use in a many-to-many relationship. There is information in BOL on many-to-many relationships, but there is also an extensive whitepaper available here: http://www.sqlbi.eu/Projects/Manytomanydimensionalmodeling/tabid/80/Default.aspx

|||

Darren Gosbell wrote:

It's because your customer keys are not unique. When SSAS builds the dimension it will collect a unique list of the keys, then it will generate the CustName and AcctOwner attributes, with logic Equivalent to:

UDPATE <dimension> SET AcctOwner = dimCustomer.AcctOwner WHERE Customer_Key = 1

I suspect that what you have here is that AcctOwner should not be an attribute of the customer, but rather is a many to many relationship where one account owner can have many customers and one customer can have many account owners.

If this is the case you need to create a bridge table with the account owner and customer relationship and set this up as a measure group which you can use in a many-to-many relationship. There is information in BOL on many-to-many relationships, but there is also an extensive whitepaper available here: http://www.sqlbi.eu/Projects/Manytomanydimensionalmodeling/tabid/80/Default.aspx

Darren,

Thanks for the input. It should NOT be a many-to-many relationship, but there are currently a few cases of bad data (from the source CRM system) where a Customer has more than one Account Owner. Once the data cleanup is performed, it will be a one-to-one relationship.

With that said, I was just testing this attribute (i.e., Account Owner) and came across the above scenario and became baffled on what I saw. I had expected both Account Owners to populate in the pivot.

Any additional thoughts?

|||

If you data is dirty you either need to clean it before loading it, or adjust your design to allow it to display. You could build a dimension table that uses an identity column to generate a unique key for each row, but

You will not see both account owners, because for a given Customer_key value there can only be one Account Owner value, so usually what happens is that the last in wins. Essentially in the scenario you have outlined, the dimension processing updates the Account Owner twice, once to set it to the first value "Joe Cool" and the "Joe Cool" gets overwritten with the value "John Doe".

The relationship between the key of an dimension and the other attributes can either be a one to one or a one to many where many keys can map to a single attribute value, but a single key CANNOT map to more than on attribute value.