Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Data mart design

4 posters

Go down

Data mart design Empty Data mart design

Post  dshams 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

Back to top Go down

Data mart design Empty Re: Data mart design

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Data mart design Empty Data mart design

Post  dshams 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

Back to top Go down

Data mart design Empty Re: Data mart design

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

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

http://aginity.com

Back to top Go down

Data mart design Empty Re: Data mart design

Post  marric01 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 : 48
Location : Montréal, Québec , Canada

Back to top Go down

Data mart design Empty Data mart design

Post  dshams 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

Back to top Go down

Data mart design Empty Re: Data mart design

Post  marric01 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 : 48
Location : Montréal, Québec , Canada

Back to top Go down

Data mart design Empty Data mart design

Post  dshams Tue Dec 14, 2010 1:36 pm

Thanks to all.

dshams

Posts : 13
Join date : 2010-12-08

Back to top Go down

Data mart design Empty Re: Data mart design

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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