Data mart design

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

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.

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

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.

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

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

Thanks

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 ;-)

Thanks to all.