Storage of Multi-Valued Lookups in clarity Data Warehouse

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

When a custom attribute is configured with a multi-value lookup (MVL), users are able to select one or more values. A key consideration then becomes how this data is stored when sent to the Data Warehouse (DWH). Understanding this storage method is useful for anticipating how reporting will use the data.

Environment

Clarity 16.4.0

Resolution

Combination of table DWH_CMN_MV_LOOKUP and the DWH_object table associated to the lookup attribute. example: DWH_INV_INVESTMENT

Notes:

• Typically lookups that are language dependent, the field will be added to the _ln table and the field +'key' DWH table. If the lookup is not language dependent field+ 'key' and field will be stored in the Datawarehouse table. 
• Multi-Value lookups stores data same as the lookup for field creation location. The fields are defined as varchar (4000) and the values concatenated together with a semi-colon stored in DWH_CMN_MV_LOOKUP table.
• The table DWH_CMN_MV_LOOKUP is populated with source DWH_ODF_MULTI_VALUED_LOOKUPS_V
• MVL Attribute Captions are not present in the standard DWH Object View. MVL Attribute Captions population and updates are done using DWH_CMN_MV_LOOKUP and the Lookup Table for that particular lookup type.