Greetings.

I went through all articles and books but could not find a decent
answer to the following warehouse design problem.

We are a bank, and we have millions of customers.

Some of our customers are firms, and we coordinate paychecks on behalf
of them. Their employees are also our customers.

You can see that there is a self-reference to the customer dimension.
We need to keep an attribute over the customer dimension indicating who
his employer is.

The problem here is that we need to keep history for this customer
dimension, likely to be Kimball Type2. However anytime an attribute of
an employer changes, it requires a change in all of its employees as
well. This causes a rapid change. Not all our customers have employers,
and there is no reqiurement of keeping many to many relation between
employer and employee, for now.

We do not want to make employer another dimension attached to our
Accounts fact-table because it is too product specific
(paycheck-product). Moreover it is also not feasible to separate
paycheck product into another table because business asks questions
like "what are the total balance of savings accounts of customers
belong to X employer". It is also impossible to ask questions like "how
many employees does employer X have?" to the fact table because not
necessarily every customer create an entry in the fact table every
month due to inactivity or temporary paycheck suspension.

Another similar situation we have is that every customer has (one and
only one) an account-officer. Each time some property of an officer
changes, it ripples through all its customers. Because we would like to
keep everything historical, and need to display the performance of
every account-officer every-month in a year. Again making officer
another dimension makes it impossible to keep count of customers
belonging to an account officer. Maybe we should keep factless
fact-tables for all these count issues, but that might require too many
long fact tables.

Suggestions?

Sorry for the long post.

Thanks in advance.

Jörg Narr wrote:
What do you mean by that?