extra custom fields
3 posters
Page 1 of 1
extra custom fields
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?
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
Re: extra custom fields
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: extra custom fields
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?
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
Re: extra custom fields
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
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
Re: extra custom fields
Very well put Mr. Honey.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: extra custom fields
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?
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
Similar topics
» employee dimension - adding extra fields
» Which is the Best model to support measures with custom roll up formulaes in a ROLAP environment?
» Dimension fields depend on other fields
» Naming of ID and code fields
» Y/N fields in dimension or as measure with 0 and 1 (or both)?
» Which is the Best model to support measures with custom roll up formulaes in a ROLAP environment?
» Dimension fields depend on other fields
» Naming of ID and code fields
» Y/N fields in dimension or as measure with 0 and 1 (or both)?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|