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

Issue with aggregate table data in answers

Received Response
71
Views
10
Comments
User_JL3CW
User_JL3CW Rank 6 - Analytics Lead
edited August 2024 in Oracle Analytics Cloud and Server

Hi All,

I am not able to see the data in answers, when querying with month level summary.

I have created a table with monthly summary and pulled into physical layer, and mapped in BMM layer with fact and dimension tables. When i query then my report does show the data, but when i run the physical query in the oracle database from log file, it shows the data.

Details: table structure.

create table month_sales_agg

(

prod_id number not null,

prod_name varchar2(100) not null,

prod_category_desc varchar2(100)  not null,

prod_subcategory_desc varchar2(100) not null,

calendar_month_desc varchar2(100) not null,

calendar_quarter_desc varchar2(100) not null,

calendar_year number not null,

quantity_sold number not null,

amount_sold number not null

);

Mapped in dimension tables: Product, time, sales_fact.

DB: oracle 12c, OBIEE 12c.

Thanks,

Liv.

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    What query do you see generated by the BI server in the log files? Are there any exceptions in the log files?

  • SonPat99
    SonPat99 Rank 6 - Analytics Lead

    Hello,

    Can you share the physical and BMM diagrams?

    Also, are you seeing any errors or warnings in the logs just after the Logical Query?

  • User_JL3CW
    User_JL3CW Rank 6 - Analytics Lead

    Hi,

    The following physical query from log file.

    WITH

    SAWITH0 AS (select sum(T1400.QUANTITY_SOLD) as c1,

         sum(T1400.AMOUNT_SOLD) as c2,

         T1400.PROD_SUBCATEGORY_DESC as c3,

         T1400.CALENDAR_MONTH_DESC as c4

    from

         MONTH_SALES_AGG T1400

    group by T1400.CALENDAR_MONTH_DESC, T1400.PROD_SUBCATEGORY_DESC)

    select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6 from ( select 0 as c1,

         D1.c3 as c2,

         D1.c4 as c3,

         D1.c4 as c4,

         D1.c2 as c5,

         D1.c1 as c6

    from

         SAWITH0 D1

    order by c3, c2 ) D1 where rownum <= 65001;

    when i run this from sql developer i could see the data, but not in answers.

  • SonPat99
    SonPat99 Rank 6 - Analytics Lead

    Can you share the physical and BMM diagrams? I can see only AGG Table is used to populate the data in report.

  • Gianni Ceresa
    Gianni Ceresa Mod

    What I can already say from the query is that your model isn't clean: how come that attributes and measures comes from a single table being an aggregated one?

    A logical fact table must have only measures (aggregations), attributes must come from logical dimensions.

    Without a proper model with clean hierarchies on your dimensions you can't set correctly content levels on the aggregated table.

    Sure there is a chance that those columns are exactly the joins to the dimensions you want to report on, but as your aggregated table has multiple columns for the same hierarchy I bet on a wrong model.

  • User_JL3CW
    User_JL3CW Rank 6 - Analytics Lead

    Hi,

    Please find the physical layer and BMM layer diagrams: I have created summary table for producing monthly data for the product.

    Physical layer:

    pastedImage_0.png

    BMM Layer:

    pastedImage_1.png

  • Gianni Ceresa
    Gianni Ceresa Mod

    A logical fact table doesn't have logical keys, just as it doesn't have attributes. All it must have is measures.

    You need to clean up that model because right now it's "strange".

  • User_JL3CW
    User_JL3CW Rank 6 - Analytics Lead

    There is a second level of logical table sources (Products_new, Years, Sales_Year). Please refer above, previous response. I have created two dimensions and a sales fact for summary at year level. Year table have year_id and year and year_id is primary key. There is no mapping in BMM layer for fact table timeid column.

    pastedImage_2.png

    But for logical table source the level is set as follows.

    pastedImage_0.png

    I am able to get the proper result in answers for the second level of LTS for these three dimensions i.e year level summary.

    Main concern here is: Every time i need to create dimensions along with fact for different summaries.

    Cannot i create one summary table to achieve each summary requirement.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner 
    3319067 wrote:Cannot i create one summary table to achieve each summary requirement.

    Let me rephrase your question:

    "Can I not have one summary table which contains atomic data and several layers of aggregated data at the same time?"

    Answer: No because it makes zero sense whatsoever. That's just not how data modelling works no matter which methodology you use.

  • User_JL3CW
    User_JL3CW Rank 6 - Analytics Lead

    Thanks for the information. Will follow the dimension modeling rules.