Q: We are in the process of designing a sales data mart for a retail company. We have a source table with flex fields. The name and type of the fields are dynamically updated and stored in configuration tables. The values of the fields are stored in another table. We need to get these tables into our data mart. We are planning to store these as key value pairs in our data mart. Any tips on this would help.

Tom Haughey's Answer:First, a key value pair is a data model abstraction structure which stores a generic value that is interpreted using other data type and definition tables. Key value pairs will be useful under two circumstances: there are many flex fields and the flex fields are widely variable. If there are few fields, say three, then they could simply be inserted into a table as a small array of columns, together with a Data Type and Units to define their meaning. Even if there are many, as long as all or most flex fields occur all the time, it is possible to have even a large set of columns within one table with each repeating column dedicated to one flex field. If the flex fields vary a lot, then it is better to use key value pairs to avoid the possibility of having many empty columns.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access