Max over Partition - analytical SQL functions

building semantic layer
1

I have created an object with this function e.g

SELECT dept, MAX(salary) OVER (PARTITION BY dept) 
FROM employees.

This works. However, I am unable to use this in a report with other dimensions as it comes up with a “Group by Error”

My question is how do you define these objects to be flexible enough to be used in a universe by end users with different dimensions?

Thanks

bobjober :uk: (BOB member since 2007-04-27)

2

In short, I don’t think you can.

Analytical functions are fixed at run time, you can sometimes use a report level variable with Input output context to define a total, but thats about as good as it gets…

Mak 1 :uk: (BOB member since 2005-01-06)

3

Thanks for your response Mak 1. Can’t see what the point of these functions are then. :hb:

bobjober :uk: (BOB member since 2007-04-27)

4

Well, they can get you out of a hole 8).

However, its like percentage calculations in SQL, they are only good at run time. You cannot slice and dice them in BO as BO does not support two phase calculations…

Mak 1 :uk: (BOB member since 2005-01-06)

5

Well, well one of my most favorite topics when it comes to BusinessObjects 8)

A) BusinessObjects does support 2-phase calculation, but at the report level (via local report variables).

B) BusinessObjects does not support 2-phase calculation at the universe level alone.

C) With XI R3.0 smart measures were introduced (see also: database delegated), look at those :slight_smile:
Smart measures still fall short of the ideal solution, that is: I wish we had the capability to define local report variables, but a global (universe level that is). THAT would be the best solution :mrgreen: :yesnod:

Andreas :de: (BOB member since 2002-06-20)

6

Correct… :slight_smile: , I did mean at SQL run time, in this instance.

I have heard a bit about these"smart" measures do they require a further DB query, or are these re-calculated from the cube?

Mak 1 :uk: (BOB member since 2005-01-06)

7

Hello Mike, smart measures do require a refresh of the data provider as the measure is re-calculated at the required aggregation levels in the database. That is why it is still not the ideal solution…

Andreas :de: (BOB member since 2002-06-20)

8

Hi Andreas, Its Mark actually - I have noticed how you dislike being called Andrea - I wonder why… :rotf: ?

I thought that was the case, still, it will make the use of analytics more intuative for the user.

Agreed, I would love a global universe solution for this…

Mak 1 :uk: (BOB member since 2005-01-06)

9

Sorry Mark, a shoot from the hip :oops: while I was typing

Andreas :de: (BOB member since 2002-06-20)

10

Hey, no problem - bought a smile to my face on a Friday afternoon… :slight_smile: .

Mak 1 :uk: (BOB member since 2005-01-06)

11

It will not generate GROUP BY clause until you use an aggregate function around a measure in ORDER BY clause.

The MAX() over() , MIN OVER() are those functions which do not have order by clause by default in Designer.

Your universe object syntax should look something like this :


MAX(salary) OVER (PARTITION BY dept) ORDER BY (SUM(SALARY))

It should generate a GROUP BY clause for you.

or
you can them directly in:

–You can use them by editing the SQL…
– Or in Derived Tables.

BO_Chief :us: (BOB member since 2004-06-06)