Greetings,

I have an interesting scenario, which I could not figure out how to
handle.

In our bank, each customer has one and only one main branch. These
branches may change in time. Therefore I use Kimball type2 approach and
add a new entry to the customer dimension table when such thing
happens. So far so good.

For some business reason there is a mapping table that maps branches to
branches. We use customers branch and a column in the mapping table to
yield another branch. This is something like finding an auxilary
branch. Unfortunately these mappings change frequently. Naturaly when a
single row of mapping table changes more than one customer is affected.

I would like to be able to track history of these mappings, that is
when a query to the fact table uses some time in the past, I would like
to use the mapping at that point in time.

I thought of putting the auxilary branch on the fact table itself. This
partially solves the problem. The most problematic part is when
counting customers with respect to auxilarry branch at sometime in the
past. Or any other query that doesn't use any of the fact tables that
the customer dimension is related to.

Thanks in advance.

nvs wrote:
Thanks for your reply.

Making the auxillary branch a new dimension partially solves the
problem.

Each customer does not necessarily create an entry in the fact table.
Hence, taking a distinct count on customers over the fact table may not
give the actual count at every point in time. Moreover such query is
very costly on a long fact table.

I thought of making a separate specific fact table (a factless fact
table) to keep track of customer situation, similar to university
course attendance fact table example that one can find in every data
warehouse article.

What do you think?

Thanks again.