Data Model from "Generic" table

[bond_bhai]

Hello All,
We have a requirement where we have a source which looks some thing like this -

Date, USER_ID, ORG_ID, ACTIVITY_NAME, C1, C2, C3, C4 .... until C30

A Sample of data looks something like this -

1/1/2014, XXX,1234, Help Request, c0a8f2bc011700aa4f5aa5a6367abf00,MBL,HELP,Web
1/1/2014, YYY,1234, Balance Request, c0a8f2bc011700aa4f5aa5a6367abf00,MBL,BAL CHK, 9813230,BAL CHECKING,1

What exactly C1, C2, C3 holds is actually stored in a metadata table.

Ex: For Help Request (Row 1 in the above example), C1 = Some Id, C2 = App Id, C3 = Ph#, c4 = Original Source
For Balance Request (Row 2 in the above example), C1 = Some Id, C2 = App Id, C3 = Message Text, c4 = Acct#, c5 = Request Type

With this information, how would one design a data model for this? Do we usually transpose the "Generic Fields" like C1, C2 etc into Rows? Any ideas/suggestions are very much appreciated!

Thanks!

[BoxesAndLines]

If you are bringing this data into a dimensional model, then yes, you need to transpose the row data to columns. The dimensional model is designed for performance. The is nothing that is high performing about pivoting a table every time you want to query it. These models are typically called EAV (Entity, Attribute, Value) models.

[ngalemmo]

Don't let the way data is received dictate your model.  Its about the content of the source data, not its delivery format.

Figure out the model.  Maybe you wind up loading multiple fact tables, maybe you have odd fields nobody needs, whatever.  Bottom line, its just a source in a format that is annoying to work with.

[Sponsored content]