OBIEE 'binning' aggregation without including lowest dimension

938767

0down votefavorite

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?

[Deleted User]

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

938767

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.

[Deleted User]

938767 wrote:because then the bins sizes can't be changed within answers... 

Why? That's what request variables are for...

938767

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.

[Deleted User]

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.

Thomas Dodds

+1 @Christian Berg

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.

[Deleted User]

@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.