What is the usefulness of multiple logical level keys in a non-Time dimension? — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

What is the usefulness of multiple logical level keys in a non-Time dimension?

Received Response
11
Views
3
Comments
Mark.Thompson
Mark.Thompson Rank 6 - Analytics Lead

From time to time in different clients' RPDs, I find hierarchy levels that have multiple logical level keys (level-based hierarchy).

For a Time dimension that makes sense, as we expect to probably see two logical level keys for each level - one for the 'Use for Display' key to control which column we see when drilling, and another for chronological ordering - two keys to fulfill two different purposes.

But it is also possible to create multiple logical level keys for non-Time dimensions. For example, the State level in my Geography dimension might have a logical level key on the State_ID column (an integer, surrogate key column with values like 44 for Texas and 29 for New York), plus another level key on the State_Abbreviation column (e.g. TX or NY).

My question is: What is the usefulness of multiple logical level keys in a non-Time dimension? Does the system do anything different because of the presence of multiple logical level keys?

Documents such as docs.oracle.com/middleware/1221/biee/BIEMG/dimensions.htm#BIEMG1395 only touch the outermost boundaries of the question. They aren't providing a clear description of "why" we might need multiple logical level keys.

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Tried to stay away from this since it opens Pandora's box, but here goes:

    If you use one data source with one-to-one mapped LTSs in the logical model, then forget that capability and don't ask. It will have close to zero impact.

    If you have many LTS possibly across many sources (and source types) then the internal optimization engine will definitely handle things - as in which path to choose, how to optimize "aggregate by".

    tl;dr it's a very specific feature which allows you to play with the internal optimizer. 99.99% of the time you are better off looking at performance in a different place though since the issues are somewhere else entirely (DB mostly) and I've never seen this resolve any specific problem except for pushing filter clauses to specifically indexed fields.

  • Mark.Thompson
    Mark.Thompson Rank 6 - Analytics Lead

    Thank you, Christian. Good info, sir.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    De nada :)