whenever we add new column to the report it is not displaying the values of that column — Oracle Analytics

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

whenever we add new column to the report it is not displaying the values of that column

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

Hi All,

we have 2 fact tables and 4 dimensions. In my report I am using one column from each table..whenever I add any columns from the fact tables other than the measures it is not displaying the right result set..why is this happening  ? Please see the below..

first is one simply taking one column from dimensions and facts.

p1.JPG

In the second image I am adding another column from one of the fact table..as soon as I add that column the measure values are gone.please see the below.

p2.JPG

Why is this happening.Any suggestions please..

Thanks !

«1

Answers

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

    Looks like in the background this request is running a query over different fact sources across conformed dimensions with some being non-conformed (Employee in this case) and the content levels in for the facts on the business model and mapping layer are not set correctly.

  • 3174977
    3174977 Rank 5 - Community Champion

    Hi Christian,

    we are having this employee name in one of the fact table and this column doesn't exist in any other dimension tables also..based on the join conditions I just modeled in the rpd.. when you say content levels for fact tables is it aggregation content group by ? is that where we set the content levels ?

    Thanks !

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

    pastedImage_0.png

    Here you see that "LTS01 Revenue (Billed Tim Join)" for example cotains no content with regards to dimension "H4 Offices...".

    In your example you probably just use a fact based attribute (taken from your description) which isn't a clean was of modelling it as you should model a proper logical degenerate dimension with its own hierarchy and set content levels appropriately.

  • 3174977
    3174977 Rank 5 - Community Champion

    Hi Christian,

    Please see the below.

    l1.JPGl2.JPG

    Am I missing anything else..Please help me out.. In Physical layer they are two seperate fact tables..In BMM layer are created one fact table and added as LTS..

    Thanks !

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

    As I said your fact-based attributes should NOT be modelled in the logical fact tables but modelled as a logical degenerate dimension - like "Attributes" in your case since you don't seem to be doing a lot of renaming - using the same physical source as the logical fact. Modelling it as a dimension allows you to specify a dimensional hierarchy object for it, then setting the fact LTS content levels and hence assuring proper aggregation.

  • 3174977
    3174977 Rank 5 - Community Champion

    Hi Christian,

    That's what I figured out and now I have separated the attributes from my facts and created as dimensions in BMM layer and have set up the content also..at least now I am able to see the data..but looks like it is giving wrong values.l3.JPG

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

    Why are you doing a front-end SUM BY calculation? If the business model is correct then the front-end will allow you to simply use attributes and measures and it will aggregate correctly.

    Basically you should degen your attributes into distinct dimensions and not just one if for example the project, employee and task are three different entities.

  • 3174977
    3174977 Rank 5 - Community Champion

    Even if do a group by or not as soon as I add employee name into the report is giving me the wrong result for plan hours. Actual hours are still correct even after adding the employee name..only problem with plan hours..

    l4.JPGl5.JPGl6.JPGl7.JPG

    Even if I add the employee name my plan hours and actual hours should still remain the same.. am I still missing anything else.

    Thanks !!

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

    Well is W_PA_HRS at all tied to the employee attribute at all? You obviously need to model things in a way that the BI server can navigate the aggregates. For example if it is inside the same logical fact table as W_PLAN_TIME (so W_TIME_F) then you must phyiscally alias the W_PLAN_TIME table, join it to W_PA_HRS and only then can you use a degenerate dimension.

  • 3174977
    3174977 Rank 5 - Community Champion

    l1.JPGemployee _name is coming from W_PA_Hrs table only..there is no other table with employee information. and both W_PA_HRS & W_PLAN_TIME tables are fact tables.. there is no join relation between those tables in physical layer..they are 2 seperate tables..In BMM I have created one fact W_TIME_F and added these two tables as seperate LTS.