extra custom fields

View previous topic View next topic Go down

extra custom fields

Post  j on Wed Nov 13, 2013 6:14 pm

What is the best way to design data ware house ( star schema) for custom fields
I have multiple data source system which essentially collecting the same information but with a lot of extra fields per system and I need to report those extra fields as well..
I would like to create a central data warehouse contain data from multiple data source ( with conformed dimensions) but at the same time I would like to store extra fields from different data source as well.. what is the best way to design this? Should I create separate fact tables contain only extra fields per data source? and join to the main fact table ( conformed dimensions)….
What is the best way to design stat schema when there are a lot of custom fields from different data sources?

j

Posts : 6
Join date : 2010-05-15

View user profile

Back to top Go down

Re: extra custom fields

Post  BoxesAndLines on Thu Nov 14, 2013 12:11 pm

Extra custom fields are typically dimension data, think 3rd party demographics, reference data, etc. Integrate this data into your existing facts just like any other dimensional data.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: extra custom fields

Post  j on Thu Nov 14, 2013 4:02 pm

Thansk..but

Problem is there will be hundreds of extra fields cannot be conformed per each data source..

For example, source 1 and 2 have the same granularity for fact, but each has hundreds of extra fields

source 1

OrderID age gender extracolumn1 extracolumn 2 extracolumn 3..etc


Source 2

Order ID age gender extracolumn5 extracolumn6 extracolumn7..etc


Source 3
----


I don’t want to put them all in one fact table with conformed fields since a lot of them will have null value and the fact table will be very wide…

Is there better way to do sore extra fields specific to each data source?

j

Posts : 6
Join date : 2010-05-15

View user profile

Back to top Go down

Re: extra custom fields

Post  Mike Honey on Thu Nov 14, 2013 8:22 pm

Hi j,

I would build a dimension for Order (with any common attributes, eg age, gender), then mini-dimensions (one for each source). The dimension business key for all these dimensions would be OrderID, and I would ensure that each mini-dimension gets the same surrogate key as the Order dimension. That way you can join directly from the fact to any mini-dimension.

It does sound like the data modelling phase has been skipped and you are building a data-dump / copy of the source system data.  In a successful DW/BI implementation, each attribute is analyzed for relevance & reporting requirements, and then categorized into separate dimensions.  For example, probably age and gender should be attributes of Customer or a similar dimension.

This approach avoids the issue you raised and also avoids wasting human and computer work on extracting, loading and storing data that is never used.

Good luck!
Mike
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: extra custom fields

Post  BoxesAndLines on Thu Nov 14, 2013 9:31 pm

Very well put Mr. Honey.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: extra custom fields

Post  j on Fri Nov 22, 2013 4:03 pm

Thanks for the reply,
Actually, these are patient data coming from different hospitals. Even though they share the same granularity, each hospital collects a lot of different fields as well as common fields (i.e. age, gender, diagnosis…)
Is it better to create a separate fact table per hospital in relational data warehouse layer and join each fact table in reporting layer by common dimension using view or something in BI reporting layer?
 If I try to combine each data set to one fact , I think it will be really messy and it won't be easy to scale out??? ….What is your suggestion?

j

Posts : 6
Join date : 2010-05-15

View user profile

Back to top Go down

Re: extra custom fields

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