nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Issue with aggregate table data in answers

User_JL3CW

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.

Find more posts tagged with

Accepted answers

All comments

Joel Acha

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

SonPat99

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

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

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

Gianni Ceresa

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

Hi,

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

Physical layer:

BMM Layer:

Gianni Ceresa

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

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.

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

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]

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

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