A question about logical level keys: As per general OBIEE best practice as well as the CEAL doc, the primary key of each dimension level must always be unique. We know this and implemented our RPD accordingly. However, when I ran the Check Model process, I got several errors for “[38152] The primary key of XXXXX logical level is not unique. This will cause failures during aggregate persistence and data discrepancies in created aggregates”. After a lot of investigation, I eventually realised that for some Type 2 SCD dimensions in the data warehouse, a combination of the natural key + the effective date is not unique. This is causing the error about the non unique logical level keys.
In these instances, the only way to uniquely identify a record is to use the surrogate (meaningless, randomly generated) key from the DW table. As per OBIEE best practice, this surrogate key shouldn’t be used in the BMM layer of repository, only the natural key. So now I want to find out, if I do use the surrogate key instead, will the aggregates still work? Are there any other issues known?
Thanks