Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIEE 'binning' aggregation without including lowest dimension
I have a fact/dim combination in OBIEE that looks something like this: <span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">Order_number</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> Order_Quantity </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">1234</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">150</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">2345</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">80</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">3456</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">20</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">4567</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">50</span> What I would like to do is create a report that aggregates the total number of orders with quantities in the defined 'bins'. For example, there are 3 orders with less than 100 quantity, and one with greater than 100: <span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">Quantity_Bin</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">#</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> Orders </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">></span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">100</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">100</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">3</span> I can do this quite easily using a 'CASE WHEN' statement and a pivot table, however that requires me to include the 'order_number' field on the report. The problem is that table has 1 million+ rows, which are all returned to the presentation server even though they aren't displayed on the report. This causes an error Exceeded configured maximum number of allowed input records. Can I specify obiee to do this calculation/aggregation without returning a row for every order_number? |
Answers
-
If you calculate something on the lowest level of granularity..,..guess what. It must be done on the lowest level of granularity.
If you push it to the RPD though and make a physically calculated column (NOT derived calculated - that would happen post-aggregate) then you could do a pre-aggregate calculation like
case when PHYSICAL_TABLE.PHYSICAL_COLUMN_QTY > 100 then 1 else 0 end
and then set its aggregation rule to SUM. The RPD would then offload the work and return only the aggregated result set to the front-end.
Assuming 5 "bins" the front-end would receive 5 measures instead 1+ million rows
0 -
Thank you Christian.
Was hoping to avoid doing this in the RPD because then the bins sizes can't be changed within answers... but it sounds like that's my only option. I'll give that a try.
0 -
938767 wrote:because then the bins sizes can't be changed within answers...
Why? That's what request variables are for...
0 -
But only if you have a fixed number of bins, right? If you've defined five bins in the logical expression then want a add a sixth, I don't think that would be possible through a request variable.
0 -
No. But that's what you have. You can't have both minimal number of rows returned to the GUI *AND* total flexibility.
It's software. At some stage it has to read things and at some stage it has to calculate things. If it could do everything automagically on the fly without needing any processing power or effort then it would be magic.
0 -
You are always going to trade flexibility for performance and vice versa.
Do the binning in your EDW - then the bins can't change without a 'restart'- performance is great but is terribly inflexible
Do the binning out in the presentation server at run-time - get dynamic bins - highly flexible, but performance, on a high volume of rows, is going to be terrible
Perhaps OBIEE is the wrong tool for this requirement ... seems like you need something that can handle the flexibility WITH a better degree of performance -- OBIEE wasn't designed as a 'schema-on-read' type tool.
0 -
@938767 If you create new threads on SO could you please have the courtesy of closing your open oens here and over there which are done? Thanks. Forums are giving and taking.
0