Bride Tables

[tim_goodsell]

hI

In my datawarehouse I have the following supperannuation insurance scenario.

A customer has one or more superannuation accounts, each superannuation account has a salary associated with it, this salary can change over time and this salary has an effective date associated with it.

I am not very sure what dimensions/facts to create so that at any pint in time I can find out the maximum salary for each client, so far I have created

1) A customer dimension (type 1)
2) A member dimension (scd type 2)

We need to know at any point in time the maximum salary for a customer

Regards

Tim

[ngalemmo]

So I can better understand the situation, does a customer repesent a group of members (i.e. is the customer an employer and members are employees with coverage)? Is there a 1:1 relationship between members and accounts? Are there other measures relating to customers you need to maintain?

[Jeff Smith]

Does the effective date of the Salary change as the salary changes? Do you know in advance what the salary will be? In other words, can you have salaries with effective dates in the future?

[tim_goodsell]

Hi

A customer is a person who has superannuation
A member is a fund account

A customer can have 0, 1 or more fund accounts.

For a customer we only want to store the date of birth and the latest address details
For a member we are going to store the fund type, fund name, member id and fund code.

Salaries are associated with members, for every fund account (member) there is salary associated with it. The salary can change over time (effective date)


My design so far is:

Customer Dimension (type 1)
-------------------
CustomerKey
Customer ID (Natural Key)
...other artributes


Member Dimension (probably type 2)
-----------------
MemberKey
Fund Code } Natural Key
Member Id }
ClientKey
... other attributes

Salary Dimension
---------------
SalaryKey
Fund Code
Member Id
Effective Date
.. other attributes


At the end of each month I need to calculate the maximum salary for each client at the end of the month. I was intending to have a monthly snapshot fact table for each member to obtain this.

Member Montly Snapshot
------------------------
MonthKey
MemberKey
Fund Code
Member Id
CllentKey
Salary
.. other attributes


Regards

Tim

[ngalemmo]

If you don't want to record salary as part of the member dimension then you have three dimensions: date, customer & member.

You then have a salary fact with customer key, member key, effective date, expiration date (the latter two being degenerate) and salary.

For the snapshot you would have date key, customer key and max salary.

You should not put attributes in your fact tables. Having both member key and fund code, for example, is redundant. If you want to have a fund dimension, fine... then put the FK to that dimension in your fact table.

I don't understand why you want member in the snapshot if the purpose of the snapshot is to show maximum salary for the customer, unless that happens to be the account that has the maximum salary. The salary fact is more than sufficient to get point in time salary information for a particular member.

[Sponsored content]