Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

How to create hierarchy columns in Oracle Analytics Server (OAS)

Received Response
89
Views
4
Comments
User_DX76J
User_DX76J Rank 1 - Community Starter
in Oracle Analytics Cloud and Server

From this link https://youtu.be/PBxADXChCqw

I found out that it has hierarchy columns of date, but when I tried to upload dataset (or create dataset from Database connection), it does not show as the video described (not in hierarchy form)

Not hierarchy form (what I have)

image.png

Hierarchy form (what I want)

image.png

Questions

  • How to create hierarchy columns in Oracle Analytics Server (OAS)?
  • Do I need to use Subject Area to create hierarchy columns? (If yes, then what is the difference between normal dataset and subject area)
    • And how to create subject area? because mine shown as attached picture image.png

Answers

  • SteveF-Oracle
    SteveF-Oracle Mod
    edited Jul 25, 2024 2:40PM

    Hi @User_DX76J ,

    Please do update your display name in your profile, so the community knows with whom we are communicating with.

    DV is capable to rendering hierarchies; however, they need to be defined in a subject area (Semantic Model / RPD ) for the datasource, prior to building your workbook.

    Here is an example from a Local Subject Area (i.e. - dataset from a Subject Area)

    image-2.png

    The time levels when expanding a dataset date field... are not really a time hierarchy - those are just options for extracting / deriving level based columns like Year / Quarter / Month.

    Creating a Subject Areas, is an entire topic on modeling

    Some primers:
    Time Dimension Design
    Model Time Series Data

    I will let other SME's chime in.

  • Gianni Ceresa
    Gianni Ceresa Mod

    Steve said it all…

    At this point in time, hierarchies as seen in the videos should come from the semantic model. In your case you are using a dataset, and there you don't really have a full model. It can be seen as a basic, simplistic, implicit model mostly.

    In the roadmap there are hierarchies for dataset planned for the future (no date provided), not sure how it will work and what it will be.

    If your data come from a database, you can model it in the semantic modeler and define the various elements (logical table, attributes, measures, logical hierarchies etc.). But as Steve said, creating a semantic model is an entire topic because you need to first get used on how it is done, how the tool works, make sure your model design is correct based on your analytics needs, build the model, deploy it and then maintain it. Also keeping in mind that the semantic model is unique in your server for all users: it's what provides the base of a "governed" analytics approach, with a unique, central, model deployed for everybody (based on permissions) to use without the users having to load data or build models.

    The semantic model is extremely powerful, but depending on your needs it could be way too much work to "just" have hierarchies available…

  • Bhaskar Konar
    Bhaskar Konar Rank 8 - Analytics & AI Strategist

    Thank you very much @SteveF-Oracle & @Gianni Ceresa!

    Very useful & helpful information. Thanks for sharing.

  • Junaid Zahid
    Junaid Zahid Rank 2 - Community Beginner

    I really think Oracle Analytics Server (OAS) should give us full Hierarchy building and Session Variable capability directly inside Data Visualization (DV) — without depending on the RPD (Admin Tool).

    In big enterprise setups (like Oracle ERP integrations or large data warehouse projects), we usually have complex hierarchies and user-level security defined in the RPD using Initialization Blocks and Session Variables. For example, in the Admin Tool we define variables like :USER, :GROUP, :REGION_ID, or custom ones like :ORG_LEVEL or :MANAGER_ID through init blocks connected to database tables or LDAP. These session variables decide what data a user can see when they log in — the hierarchy and levels change automatically based on the logged-in user.

    But when we try to build dashboards directly in Data Visualization (DV) — especially using PL/SQL or SQL queries as data sources — DV doesn’t recognize or use those RPD session variables. It means user-level filtering and dynamic hierarchy control are lost unless we go back and modify the RPD model.

    This becomes a problem because we want to avoid RPD dependency for every new report or dashboard. Our users now demand self-service dashboards through DV, and maintaining hierarchies only in the RPD slows us down.

    What we need is:

    • The ability to define hierarchies (Parent → Child → Level) directly in DV datasets.
    • The ability to use RPD session variables or create new DV-level session variables that change dynamically on user login.
    • Optionally, a way to reuse RPD initialization block logic inside DV so that user-based filters and hierarchy rules stay consistent.

    In my case, I have OAS 7.0 with SSO and WebLogic groups. The users and groups are mapped correctly, but since the hierarchy and session variables are defined in RPD, DV can’t detect them. I want the DV dashboard to automatically adjust according to the user login (for example, when user A logs in, only A’s hierarchy level data should load; when user B logs in, the dashboard should refresh based on B’s hierarchy).

    It would be really powerful if Oracle adds native support for session variables and hierarchies in DV. This would remove the need to modify RPD for every new self-service dashboard and make OAS much more flexible for modern analytics teams.

    Has anyone found a practical workaround for using session variables or hierarchy-based filtering directly inside DV without touching RPD?