How to dimension model this operational data

Go down

How to dimension model this operational data

Post  PDBrent on Wed Apr 08, 2015 4:34 pm

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!


Posts : 1
Join date : 2015-04-08

View user profile

Back to top Go down

Re: How to dimension model this operational data

Post  ngalemmo on Wed Apr 08, 2015 7:30 pm

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.

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

View user profile

Back to top Go down

Back to top

- Similar topics

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