Miscellaneous Fields

View previous topic View next topic Go down

Miscellaneous Fields

Post  jayz on Tue Sep 16, 2014 1:38 pm

I have a fairly large account dimension ~ 6.5 million rows and 35 columns. It is a type1 SCD.

In the source table there are Miscellaneous fields (1-14) that can contain just about any piece of information varchar(50). I don't have any control over these fields or what goes in them.
I need to start pulling these misc fields and keeping a history on them (type2).

I am not sure what the best approach would be.
Any help is appreciated.

jayz

Posts : 3
Join date : 2014-09-16

View user profile

Back to top Go down

Re: Miscellaneous Fields

Post  ngalemmo on Tue Sep 16, 2014 2:12 pm

Putting fields that can contain anything about anything into a data warehouse is a disaster waiting to happen. Once you have them loaded, how do you expect anyone to use them? To do any useful query they would need to place a context around the value (what does it represent, what do the different values meanů), meaning the end user will need to do some (if not a lot of) research to figure out what they are looking at.

For that reason, it makes more sense to do the work once, in the beginning, to figure out what the data is before you load it.

As far as storing the data goes, if this is truly a changing set of attributes, you may want to consider a factless fact table. Dimensions such as account, attribute type, attribute value, would provide the necessary context and allow the data to be integrated with the account. But such a table can be tricky to use. If the context turns out to be a lot more stable, placing them as properly named and documented attributes in the dimension is also an option.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Miscellaneous Fields

Post  jayz on Tue Sep 16, 2014 3:06 pm

Thanks for the response, I really appreciate your time.
To clarify the misc fields usage a little. We have about 100 clients that use this data. The Misc fields are for each of these clients to use, so while I say the data can be anything each client standardizes and uses each misc field for their own purpose.
So for example client 1 may have client number in misc 9 field where client 2 may have it in misc 1 field.
Client 1 may have campaign information in misc 1 where client 2 has no campaign information at all.

My point is that they will know what is in each misc field (and most of them aren't used).


jayz

Posts : 3
Join date : 2014-09-16

View user profile

Back to top Go down

Re: Miscellaneous Fields

Post  ngalemmo on Tue Sep 16, 2014 3:54 pm

I would probably just load the data as-is into the dimension, then have some customer based metadata the drives the query interface.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Miscellaneous Fields

Post  jayz on Wed Sep 17, 2014 6:54 am

I'm concerned that having the misc fields (type 2) in the Account Dimension (type 1) will make the account dimension too large. I was thinking of putting each misc field in its own dimension, but that creates so many keys. Is there a different option I should consider?

jayz

Posts : 3
Join date : 2014-09-16

View user profile

Back to top Go down

Re: Miscellaneous Fields

Post  BoxesAndLines on Wed Sep 17, 2014 7:48 am

Use a junk dimension.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Miscellaneous Fields

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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