Add lookups in Data Mart ETL expression

[Hrishikesh]

In the Data Warehouse mappings, there is a lookup functionality built-in to perform any code lookups from the landing tables in Compose.

I was looking for similar functionality in the Data Mart ETL too.

We have built dimensions where some of the column are codes only known to the source system, in the DataMart layer we want to provide meaningful descriptions for those codes by doing a lookup from the code description dimension (which has very static data that doesn't change very often).

One of the ways to implement this is using custom ETL code in Compose, but since we have so many code columns we are looking for a standard lookup functionality in the Data Mart expressions to avoid maintaining a lot of custom code.

Also we want to avoid doing this type of data denormalization in the warehouse., this requirement fits well in the Data Mart/BI layer to be built for reporting.

Do you have a similar problem to deal with in Compose?

[TimGarrod]

Hi,   the typical way to handle this would be to create the lookup tables in the DW model and define the relationships from the code column to the lookup table code in the main model. 

This allows the description to be pulled into the data mart automatically, reduces the need to denormalize the data in the DW model, but allows for easy altering of the code / descriptive values if needed through the normal Compose process.

[Hrishikesh]

@TimGarrod  it will work, but we have hundreds of such code columns in our model which means we will have to define relationship for each one of such columns in our model, on a different note we also observed there is a negative impact on warehouse ETL performance, as you add more relationships in the model in Compose, hence we did not go down that path.

We need the lookup capability only as per need basis in the Data Mart layer, per BI user's request.

Is there a technical limitation in Compose to add lookup capability in Data Mart mapping same as it exists in the Warehouse?

[TimGarrod]

A LookUP is a JOIN. So I don't think this would be any different to having these modeled as a type 1 only entity. You'd get the same performance with the benefit of any change to those being detected as part of mart processing.
At this time there is no lookup function in the mart - as the model supports the same notion in a more controlled / governed manner.
Having said that - you COULD write a correlated select in the expression... ( SELECT name from my_lookup where ${ModelEntity.CodeColumn} = lookup_code_Column)

[Hrishikesh]

Ok here is the problem: take an example that we have 1000 code columns in our model that may have description based on values and the Data Mart requires only 20 of those code columns to require a corresponding description.

With your approach, you are suggesting to create 1000 relationships in our Core model.

Which means on every ETL run, the Compose ETL commands will perform the heavy-lifting of maintaining 1000 relationship IDs by using JOINs. But in reality only 20 of those relationships will be used in the Data Mart for BI consumption.

Thinking in modern cloud terms, that is a lot of waste of compute!

Correlated select runs for every row in the table, which may not be desirable for very high volume facts or dimensions. A lookup using JOINs is much desirable.

[TimGarrod · Accepted solution]

I wasn’t considering that number of lookups 😊 Note, if you don’t need all lookups – you don’t have to model all the relationships.
Having said that, I completely understand your point – I’m just trying to provide an out of the box method to support your requirements. (I don’t like a correlated select either 😊). I suggest entering a feature request in the Ideation section of the community.

On the left <-- you’ll see Ideation toward the bottom - this is an area where you can request new features for the products that product management review. Please explain the use case as you have done here so Product understand the requirement / use case etc.

[Hrishikesh]

Thanks I submitted to the ideas board