Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

How to dimension model this operational data

2 posters

Go down

How to dimension model this operational data Empty How to dimension model this operational data

Post  PDBrent 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!


PDBrent

Posts : 1
Join date : 2015-04-08

Back to top Go down

How to dimension model this operational data Empty Re: How to dimension model this operational data

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Back to top

- Similar topics

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