Data Model from "Generic" table

View previous topic View next topic Go down

Data Model from "Generic" table

Post  bond_bhai on Wed Jan 08, 2014 2:12 am

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!

bond_bhai

Posts : 1
Join date : 2014-01-08

View user profile

Back to top Go down

Re: Data Model from "Generic" table

Post  BoxesAndLines on Wed Jan 08, 2014 8:11 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Data Model from "Generic" table

Post  ngalemmo on Fri Jan 10, 2014 1:54 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Data Model from "Generic" table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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