Key-Value Pairs

We have quite a bit of data in vertical files or tables, so ones where data is stored as key-value pairs. We have a few ideas of how we might handle Business Terms for that data, but would be interested in how others do this. Are you using code sets? Do you use a custom relationship? How is it conveyed in a simple fashion.

Best -

Dave Wright

davidwright

Posted 4 years ago · Edited 2 years ago·Last reply 3 years ago

4 comments

grantrollerson

·3 years ago · Edited

You might want to look at Data Lineage from Business Perspective , as discussed by Irina Steenbeek in this short video. You can perform the contextualisation (or qualification) of the asset in the application logical layer (if it’s not possible to do it in physical via views).

davidwright

OP3 years ago · Edited

@grant.rollerson Sorry for the delay in responding to your suggestion. The thing is that we do not want to create views, because that is not how the data is utilized or consumed. What we are looking for is how to represent this at the conceptual metadata layer.

The basic question is how to state that a Business Term represents the value in the “value” columns where the “name” column is X. I guess that we could create some sort of mapping object, though we have never done that going from business to data assets (only data to data / data to technical).

arthurburkhardt

·3 years ago · Edited

On a logical level, you want to think about your KV model as you would a relational one.
So either you can document with table/columns something like:

Table	Column
Customer	CustomerID
Customer	CustomerID:${CustomerID}.Name
Customer	CustomerID:${CustomerID}.DOB
Customer	CustomerID:${CustomerID}.Address

e.g. From RDBMS to Key-Value Store: Data Modeling Techniques | by Wishmitha S. Mendis | Medium

The key is represented by the “column” name and the value is represented by the “column” itself.

grantrollerson

·4 years ago · Edited

@dlwright.wellington.com With the caveat I’m a humble CSM and not a professional services hired gun… I’d suggest pivoting the data out into a view, or views depending on the use case
UC1) a table t_trade_valuation with columns date, trade_id, type and amount. If we have types UNREA (unrealised profit & loss) REA (realised profit & loss) and CASH. You might solve this by creating a view v_trade_valuation with columns date, trade_id, amount_rea, amount_unrea, amount_cash
UC2) a table t_trade_valuation with columns date, trade_id, type and amount. If we have types X, Y and Z where product = Loan and types A, B and C where product = Credit Default Swap. You might solve this by creating two views v_trade_valuation_loan with columns date, trade_id, amount_x, amount_y, amount_z and v_trade_valuation_cds with columns date, trade_id, amount_a, amount_b, amount_c
UC3) a table t_trade_extension with columns date, trade_id, property_name, property_value, likewise solve with a view
UC4) a table t_trade_extension with columns date, trade_id, char_cust_element1, likewise solve with a view