Card data - a Dimension or Fact and relationship among Card, Account and Client

View previous topic View next topic Go down

Card data - a Dimension or Fact and relationship among Card, Account and Client

Post  zahid_ash on Thu Sep 15, 2011 1:58 am

Hi,

In our business scenario we have DIM_Client and DIM_Account dimensions which are linked using factless fact table Fact_ClientAccount.
In 2nd phases we have to add Visa Card data to data warehouse.

Client and Card has one to many relationship i.e. One client can have multiple cards issued and One card belongs to One Client only. Account and Client has one to many relationship i.e. One account can have multiple cards because there could be joint account holder and both have the cards.

Cards data include figures like batch id, card's last 4 digits, issued date and CardsStatus (which could be Active, Closed, Suspended etc.)

Initially Card looks like a dimension as status is a SCD2. But it would not make any sense if I link Client->Card->Account by one to many relationship as two dimension can be joined by fact only, if it's not a hierarchy (this is what I think of about joining dimensions , correct me if I am wrong).

Second thought is that Card is a fact and split the CardStatus in new dimension and then join them using ClientKey, AccountKey, CardStatusKey as follows :


DIM_Client:

ClientKey,
ClientCode,
ClientName,
Clientotherfields

=====================

DIM_Account

AccountKey
AccountCode,
AccountTitle,
AccountOtherFields

=====================

DIM_CardStatus

CardStatusKey
CardStatus

=====================

Fact_Card

ClientKey,
AccountKey,
CardStatusKey,
BatchId,
IssuedDate,
CardNumber


Or is there any other suggestions to build a relationship among card , account and client . And is Card fact or Dimension?


Thanks.
Zee








zahid_ash

Posts : 5
Join date : 2011-09-15

View user profile

Back to top Go down

Re: Card data - a Dimension or Fact and relationship among Card, Account and Client

Post  zahid_ash on Thu Sep 15, 2011 8:24 pm

I was thinking of the relationship and come up with the following instead of the above one. Is it correct ? where Client->Card and Account->Card have 1 to Many relationships

In DIM_Card - BatchId, IssuedDate and CArdNumber (last 4 digit of Card Number) makes a business key to identify a card.


DIM_Client:

ClientKey,
ClientCode,
ClientName,
Clientotherfields,
StartDate,
EndDate

=====================

DIM_Account

AccountKey
AccountCode,
AccountTitle,
AccountOtherFields,
StartDate,
EndDate

=====================

DIM_Card

CardKey
BatchId,
IssuedDate,
CardNumber
CardStatus,
StartDate,
EndDate

=====================

FactlessFact_ClientCardAccount

ClientKey,
AccountKey,
CardKey,
StartDate
EndDate



Thanks

zahid_ash

Posts : 5
Join date : 2011-09-15

View user profile

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