Problem with report filter based on a indexed LOV

[alfons_gonzalez]

Hi guys,

I'm facing the following situation ,We have a universe (e.g: eFashion.unx) with a dimension (Color)  that has been associated to a PK (index awareness). The dimension has also associated a default List of Values.

In a given report we have used this field to add a filter on the query by choosing one of the values provided by the LOV (Yellow Stripped). As expected the SQL shows that the ID instead of the name of the field

SELECT

  Article_Color_Lookup.Color_label

FROM

  Article_Color_Lookup

WHERE

  Article_Color_Lookup.Article_color_lookup_id  =  148

But we have noticed that when report is promoted to a new environment where the Id of the row is different (e.g: 5148 instead of 148) the Id corresponding to the LOV item is not updated in the SQL query.

I suspect that the issue comes from the fact that LOV are never refreshed (e.g: in the prompts user is always asked to refresh to obtain it), but in this case there is no way to workaround the issue (well, replace the LOV value with a constant one which does not use the index)

Anyone has faced this issue? Do you think that this is the right behaviour or we are missing something?

Thanks,

Alfons

[Former Member]

Hi Alfons,

As far as I believe, the value in the SQL will not get updated automatically. User might have to refresh the LOVs and choose the 'Yellow Stripped ' value again in order to reset it.

Also it seems like either there are more colors in the lookup table in the new environment or the ETL is starting the surrogate key count from 5000 which is generating the id as 5148 instead of 148.

I have seen this approach in DataStage where an inital value can be decided for surrogate key value generation.

In case of surrogate keys, we can end in such situations because they are meaningless values and not linked with the dimension values directly

Either the user can fix it by refreshing the report again OR you can check with ETL team if they are using different logics to load Article_Color_Lookup table in both environments.

Hope it will help.

Regards,

Yuvraj

[Former Member]

Just curious..is the id a surrogate key?

[alfons_gonzalez]

Yes, it is.