Oracle Transactional Business Intelligence
Rows overcounting in Analysis
Summary
How to only count the number of rows showing in an analysis
Content
Hello Oracle communities,
When I attempt to display the total number of rows shown in an analysis in BI, and it resulted in the number of rows counted (12636) being beyond the number of rows displayed (2) per enclosed screenshot. This over counting happens with both count() and sum() functions. The "Orders" column, data be counted, is a fact component.
I also tried running count() on the "Order" column, a dimension column showing the number ID of an order. "1" showed up per each row under that new calculated column.
What should be changed to the formula so it produces a total number of orders that match with the number of orders shown in the analysis? In this case 2, instead of 12636.
Thank you.
Version
Oracle Business Intelligence 11.1.1.9.0
Answers
-
A table prompt is not a filter. The system will run the logical sql in your query (you can see it on the advanced tab). That is how many rows are returned from the metadata repository database (RPD) and therefore how many are counted. Your table prompt is a display thing after all the data has been returned to show/hide some of your rows on screen.
1) If you build a dashboard prompt (or even an analysis prompt) for your 3 "table prompts" columns then add the 3 filter columns to your criteria filters as "is prompted" then your query will only actually bring back the 2 rows based on the prompt values. This will also be more efficient since you only get the data you wish to see. If you change the prompts it will run the query again with a different where clause.
2) An alternative is to use BY in your aggregate function count("orders" by col 1, 2, 3 in your table prompt). This will do a partition over by in the physical sql. So you will get 2 2 in your count on the 2 rows.
-
Thank you again Nathan for the explanation and 2 methods, which both works.
Good day.
