Bride Tables

View previous topic View next topic Go down

Bride Tables

Post  tim_goodsell on Tue Oct 05, 2010 10:02 pm

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




tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Bride Tables

Post  ngalemmo on Wed Oct 06, 2010 12:49 pm

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Bride Tables

Post  Jeff Smith on Wed Oct 06, 2010 1:33 pm

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?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Bride Tables

Post  tim_goodsell on Wed Oct 06, 2010 8:06 pm

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


tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Bride Tables

Post  ngalemmo on Thu Oct 07, 2010 12:16 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Bride Tables

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum