Customer Count

Hello,

I have a Fact table containing sales information, and is linked to a customer dimension through a CustomerKey and to a product dimension. The Customer Dimension is a slowly changing dimension having CustomerKey as primary key and CustomerPIN as BusinessKey. A customer can have historical records in the Customer Dimension.

I want to count the number of clients per product. If I create a measure Customer Count as Distinct Count on the CustomerKey, my results are not quite correct because 2 different Customer Keys might belong to the same Customer since the latter is an SCD. So what I want is actually Distinct Count on CustomerPIN, but I am not able to create this measure correctly. It is being created as a measure group under Customer but is not linked to the sales.

I am not sure if I'm doing the right way or if I'm missing something.

Appreciate your help,

Grace

Hi Grace,

One possibility is to use a Named Query for the fact table, where the original fact table is joined to the Customer table, so as to add a Customer PIN field on which a distinct count measure can be created.

|||

Thanks Deepak,

I was able to create a Named Query having SalesKey as Logical Key with Person PIN according to the Person Key of each Sales transaction. And I added a relation between SalesKey from the New Named Query and the Fact table. Then, I was able to create a measure as Distinct Count on Person PIN and use it correctly.

However I was wondering if maybe since I can modify my underlying fact table to originally include this Customer PIN in addition to the Customer Key, and fill both from the SSIS, wouldn't that be faster and less complex? What do you suggest?

Thanks again,

Grace

|||

Grace,

My own inclination would be avoid loading redundant data, unless the cube processing performance with the Named Query is a problem. But I can't say that I've dealt frequently with this scenario, so maybe others on this Forum could comment as well?