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

creating new logical column in BMM layer

Received Response
22
Views
5
Comments
3174977
3174977 Rank 5 - Community Champion
edited Aug 13, 2024 6:18PM in Oracle Analytics Cloud and Server

Hi All,

I am trying to create a new logical column in BMM layer with the below logic.. But I am not sure how to put the same in expression. I have two columns actual hours and plan hours. Now I wanted to create new column called Type which should for actual hours the type should be 'Actual' & for plan hours the type should be 'Plan'. How to create a new column with this logic..will it possible to create..Any suggestions.

Thanks !

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    were does your 2 columns (actual and plan hours) come from? Are they in the same logical table? Do you have a value for both columns in the same row?

    You probably see where I want to end up with my questions but it really depends on your model and where does these 2 columns come from to manage to set the value for your new columns as it will not be able to have both values in the same time, so the rule depends on the source of your 2 existing columns and how they are modelled together.

    PS: if the 2 columns come from 2 LTS just set the different values in both LTS.

  • 3174977
    3174977 Rank 5 - Community Champion

    HI Gianni,

    those two columns from two different physical tables and those two columns are being used in the report. let's say they are called actual hours coming from actual table and plan hours coming from plan table.they are two different physical tables in physical layer. In BMM layer those two tables are modeled into one fact table in BMM layer. My question is when we use these two columns in the report can another column be created in such a way it says the type of those hours whether they are actual or plan. Sorry if I am confusing.. Please see the below..I am trying to see if something like the below can be achieved.

    bmm.JPG

    Thanks !

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    You seems to have the good model to solve this easily ...

    So you have 2 physical tables, going into 2 logical table source into the same logical table.

    Look at this picture, it's what you can try to do it.

    Capture.PNG

    The idea is to add a new column in your logical table (in my picture it's the one named "Source").

    This new column is derived from a physical mapping and you enter the value you want (so "Actual" or "Plan") as the physical mapping.

    You write the value (the text) by hand: for the logical table source pointing on your actual data you write 'Actual', for the logical table source pointing to your plan data you write 'Plan'.

    Done

  • 3174977
    3174977 Rank 5 - Community Champion

    Everything will be in one new logical column right ?

    Thanks !

  • Gianni Ceresa
    Gianni Ceresa Mod

    Yes, you will have a single column in the logical layer and presentation layer (in the picture above it's the "Source" column, "Column A" is just there as random column returning some other data...