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

hitting wrong table when doing count in the analysis

875299

Hi All,

using version obiee 12c, i have created a basic report like below and counting the distinct sku code in the report itself and when i check the query , obiee generates 2 queries one for quantity hitting the right fact table and another for counting sku code's from random fact table.since for the counts it is hitting a random table ,i get wrong counts.

i think i see this issue in version 12c only.i know ways to make this report hit only one fact but when users want to create adhoc reports ,i cant explain all this to them.

please let me know if there is something i can do in the rpd to fix this or is this the way obiee 12c is designed to work?

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

a) What precise version of 12c? There were 16 so far.

b) Have you upgraded and had a different behavioir before or why do you claim this only happens in 12?

c) Shortest answer: No. Hiding behind the tool seems the easy option but 99.9% of such issues are due to bad and plainly wrong models.

Robert Angel

With SKU Code in your analysis shouldn't the result of the count always be 1?

Also, why isn't this calculation pushed back to the rpd?

I would try removing all but Fiscal Year and your count calc and try it again. Then add back the other fields (SKU Code last!) and see when your results go wrong.

Also view the physical sql on each iteration and observe the physical tables involved.

Armed with this information I would then look into your logical table source on the underlying fact table / agg fact tables and trace the links to the dimension fields involved through the business model to the physical in the query and try to ascertain if anything looks amiss - OBIEE does not do random, generally if the model is wrong the results are wrong...

Gianni Ceresa

875299 wrote:... and another for counting sku code's from random fact table.since for the counts it is hitting a random table ,i get wrong counts.

Honestly in almost 10 years using OBIEE I never saw it using a random table.

It uses what you designed and modeled in the RPD, I don't imagine it being really random.

And "random" isn't a synonym of "doesn't use the table I want" : so is it a random table (which means every time you run the query it's a different one without a logic) or is it just not the table you hoped?

875299

Thanks for the reply.

I am using version 12.2.1.0.0.

Like you said it is not random table but hits the same table every time i do a count of sku.

But my question is why is it generating 2nd set of sql for count of sku instead of doing the count in the same query used for quantity.

My concern here is if the user is creating an adhoc analysis and want to see the number of sku's for that quantity sold from sales fact,obiee is hitting inventory fact and giving me wrong count of sku's.

I have observed one more thing in rpd,sales fact has content level defined to 'Total level' for some dimensions where as inventory does not have any total level defined in the content.

Do you think this might be the reason for obiee generating a second pass sql for counts from inventory fact?

Gianni Ceresa

Everything is based on the RPD, so the answers to why this or that are all in the RPD.

The logic on how queries are generated based on the RPD can change between versions, but if your model is "safe" before to migrate it will still do the same job in most of cases. If queries really change it's more because your model had some weakness before and "by chance" things worked as expected.

As the rules on how queries are generated aren't published difficult to tell you exactly "this is because of that" without having the RPD in front.

875299

my entire RPD design was inspected by oracle and they did not find any kind of wrong practice in it,

I am going to create the counts columns in rpd and ask users not to create any count measures on dimension attributes in answers as it might hit any fact table, unless i specify implicit fact column on each subject area.

thanks for the help

Gianni Ceresa

I'm not sure if I would take "inspected by Oracle" as a guarantee of quality (when we see what they do in OBIA sometime I question myself if they really knew what they were doing and with which tool).

Adding a measure with the rule in the RPD is definitely the best approach in this case.

My opinion is that a self-service approach works without issues on small subject areas where things are kind of unique and there isn't a way (neither in the model nor in the subject area) to take something for something else or generate wrong queries.

In bigger models a minimum of knowledge of what's behind is required to avoid mistakes.

Sure not optimal, but at least it's a way to deal with it.

[Deleted User]

875299 wrote:my entire RPD design was inspected by oracle and they did not find any kind of wrong practice in it,

I'm sorry but I can't laugh even remotely hard enough about that statement.

Thomas Dodds

I'm with you ... oh, man! ROTFL (MAO is next...)

875299

I had to mention that because instead of thinking about the reason for the tool hitting another fact table when we do the count on dimension attribute,

you were sticking to the assumption that rpd model might be wrong.