Data mart design

View previous topic View next topic Go down

Data mart design

Post  dshams on Mon Dec 13, 2010 2:55 pm

Lets say I need to model Frauds. There is 2 departments handle different Frauds say (Contract and Health care). I am going to model this in MD (fact, dimension). Now should I just create one Fact table(Business Process) and use say Fraud type (Contract, healthcare) as one of the dimensions (some dimensions will be specific to Fraud type, it will leave an empty cell in the fact table)? or should I create 2 different Fact table one per each Fraud type using conf dimensions(departmental).

Thanks

dshams

Posts : 13
Join date : 2010-12-08

View user profile

Back to top Go down

Re: Data mart design

Post  BoxesAndLines on Tue Dec 14, 2010 11:14 am

If the dimensionality is consistent across types, I would combine to a single fact. If you have a dimension that is not applicable for a given fraud type, you wouldn't store a null, you would store the default dimension row. You can also add an additional default row to indicate that the dimension is not applicable for the given fact row. This could be helpful to distinguish missing values vs. not applicable values.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Data mart design

Post  dshams on Tue Dec 14, 2010 11:43 am

Thanks, but I am not sure if i understand "If dimensionality is consistent across types" what do you really mean by taht?

dshams

Posts : 13
Join date : 2010-12-08

View user profile

Back to top Go down

Re: Data mart design

Post  ngalemmo on Tue Dec 14, 2010 11:51 am

If they involve different processes with different measures, you are probably better off with separate fact tables and an aggregate if they are analyzed together.

If you go with one fact table and there are some dimensions that are exclusive to a particular type, allow for a 'not applicable' dimension row so you can always populate a foreign key in the fact table. Never populate a fact table with null FKs.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Data mart design

Post  marric01 on Tue Dec 14, 2010 12:22 pm

Yes, B&L and ngalemmo are right.

I'm my DW, I always put a "N/A" and "Unknown" row for each dimension and my ETL always trap everything. Users can easly see if there is something wrong and they know that "N/A" and "Unknow" have really different meaning.

Good luck with your project, I hope everything go well for you !

Richard

marric01

Posts : 23
Join date : 2010-08-18
Age : 41
Location : Montréal, Québec , Canada

View user profile

Back to top Go down

Data mart design

Post  dshams on Tue Dec 14, 2010 12:38 pm

So I should go departmental and create different facts for each type becuse there process and measure are diierent, right?

Thanks

dshams

Posts : 13
Join date : 2010-12-08

View user profile

Back to top Go down

Re: Data mart design

Post  marric01 on Tue Dec 14, 2010 1:13 pm

dshapourian wrote:So I should go departmental and create different facts for each type becuse there process and measure are diierent, right?

Thanks

Well, if you have really different measures ... you should go with 2 fact table. If the measures are the same and, lets say, you have 1 or 2 dimensions that are related to a specific departement, I would go with 1 fact table and used the "Not applicable" is those dimension (Add a row in the dimension).

Hope this help ;-)



marric01

Posts : 23
Join date : 2010-08-18
Age : 41
Location : Montréal, Québec , Canada

View user profile

Back to top Go down

Data mart design

Post  dshams on Tue Dec 14, 2010 1:36 pm

Thanks to all.

dshams

Posts : 13
Join date : 2010-12-08

View user profile

Back to top Go down

Re: Data mart design

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