How to dimension model this operational data
2 posters
Page 1 of 1
How to dimension model this operational data
I'm in early phase of designing a DW for a manufacturing line (product assembly data)
My question is how to approach things. It has the following features.
Source data will come from different line databases - the number of separate sources could vary.
These sources can have identical measure or dimensional data ( machines, operators), since they might be setup the same using external data- However with manual input, and other factors, there's likely non-identical measure data that is unique to a particular line/source.
The source line identifier will be included as a dimensional table, to track this.
Some of the metric data can be shared across more than one line (database), but for the most part is limited to a single line- therefor the data can be associated with one measure. My question are to do with the type of surrogate or natural keys I need, and how to relate the consolidated Measure and fact data. It seems I'll have grouping of measure/fact data by the Lines. but in some cases there's a merge of data required.
Thank you anyone!
My question is how to approach things. It has the following features.
Source data will come from different line databases - the number of separate sources could vary.
These sources can have identical measure or dimensional data ( machines, operators), since they might be setup the same using external data- However with manual input, and other factors, there's likely non-identical measure data that is unique to a particular line/source.
The source line identifier will be included as a dimensional table, to track this.
Some of the metric data can be shared across more than one line (database), but for the most part is limited to a single line- therefor the data can be associated with one measure. My question are to do with the type of surrogate or natural keys I need, and how to relate the consolidated Measure and fact data. It seems I'll have grouping of measure/fact data by the Lines. but in some cases there's a merge of data required.
Thank you anyone!
PDBrent- Posts : 1
Join date : 2015-04-08
Re: How to dimension model this operational data
To summarize you have different lines and different machines logging different measures...
There are two basic ways to model this. Represent measures as columns in a table, or, have a single measure and have measure type as a dimension. The latter will store anything, but is significantly more complex to query. You may wind up doing both.
I would first try by identifying and organizing all the measures. Organize clusters of measures into fact tables with a common surrogate PK so different facts can be combined as needed for analysis. Each fact should carry the necessary dimensionality so they can be used individually. If this covers everything, you're good to go.
If you still have a bunch of leftover measures that the business wants, but for whatever reason cannot be classified or is not of immediate interest, you can store them in a table with the same surrogate key, a measure type dimension FK and the measure value. You can create views as needed to flatten the data into columns for ease of use. However, there may be performance issues depending on your particular DBMS and how it handles such views.
There are two basic ways to model this. Represent measures as columns in a table, or, have a single measure and have measure type as a dimension. The latter will store anything, but is significantly more complex to query. You may wind up doing both.
I would first try by identifying and organizing all the measures. Organize clusters of measures into fact tables with a common surrogate PK so different facts can be combined as needed for analysis. Each fact should carry the necessary dimensionality so they can be used individually. If this covers everything, you're good to go.
If you still have a bunch of leftover measures that the business wants, but for whatever reason cannot be classified or is not of immediate interest, you can store them in a table with the same surrogate key, a measure type dimension FK and the measure value. You can create views as needed to flatten the data into columns for ease of use. However, there may be performance issues depending on your particular DBMS and how it handles such views.
Similar topics
» star data model and reporting with different dimension groupings
» Model available time per day in timesheet data warehouse: fact or dimension or neither?
» How to model dimension data including dynamic fields from the OLTP system?
» Operational Reporting from a Dimensional Model
» Realtime operational data
» Model available time per day in timesheet data warehouse: fact or dimension or neither?
» How to model dimension data including dynamic fields from the OLTP system?
» Operational Reporting from a Dimensional Model
» Realtime operational data
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum