BusinessObjects Board

Formula question

building semantic layer
#1

How to build the following formule in the Universe?

Formule: SUM(Bypassed) / (SUM(Client) + SUM(Bypassed))

green = SUM(Bypassed) where Service = ‘Default’
blue= SUM(Bypassed) where Service <> ‘Default’

Table:


Proxy   | Service | Bypassed      | Client   | Server
A       | Default | 2             | 4        | 5
A       | NULL    | 6             | 12       | 23
B       | Default | 3             | 8        | 15
B       | NULL    | 1             | 3        | 11

For Proxy A: 2 / (16 + 6)
For Proxy B: 3 / (11 + 1)

I’ve tried to create two different measures SUM(bypassed) where Service = ‘Default’ and SUM(bypassed) where Service <> ‘Default’ and a third measure with

@Select(Traffic Fac\Bypassed Bytes Sum\Default Bypassed Bytes Sum) / (@Select(Traffic Fac\Intercepted Client Bytes Sum) + @Select(Traffic Fac\Bypassed Bytes Sum\Non Default Bypassed Bytes Sum))

This creates the following query in WEBI:

( sum(BLUECOAT_SVC_TRAFFIC_FAC.BYPASSED_BYTES) ) / (( sum(BLUECOAT_SVC_TRAFFIC_FAC.INTERCEPTED_CLIENT_BYTES) ) + ( sum(BLUECOAT_SVC_TRAFFIC_FAC.BYPASSED_BYTES) ))

Which is not correct!

bas_vdl :netherlands: (BOB member since 2012-11-08)

#2

Hi, try to delegate measure in database.
You can do it in measure properties.

dmitry.anoshin :ru: (BOB member since 2013-03-28)

#3

The measure ‘Group Default’ was already set to delegate

Measure ‘Group Default’:
@Select(Traffic Fac\Bypassed Bytes Sum\Default Bypassed Bytes Sum) / (@Select(Traffic Fac\Intercepted Client Bytes Sum) + @Select(Traffic Fac\Bypassed Bytes Sum\Non Default Bypassed Bytes Sum))

bas_vdl :netherlands: (BOB member since 2012-11-08)

#4

Please post the SQL defintions of these measures, have you used a case statement for the logic?

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

#5

Measure 1: Default Bypassed Bytes Sum
Projection: SUM
Select: sum(TRAFFIC_FAC.BYPASSED_BYTES)
Where: TRAFFIC_DIM.SERVICE = ‘Default’

Measure 2: Non Default Bypassed Bytes Sum
Projection: SUM
Select: sum(TRAFFIC_FAC.BYPASSED_BYTES)
Where: TRAFFIC_DIM.SERVICE <> ‘Default’

Measure 3: Group Default
Projection: Delegated
Select: CASE WHEN @Select(Traffic Fac\Bypassed Bytes Sum\Default Bypassed Bytes Sum) <=0
THEN
0
ELSE
@Select(Traffic Fac\Bypassed Bytes Sum\Default Bypassed Bytes Sum) / (@Select(Traffic Fac\Intercepted Client Bytes Sum) + @Select(Traffic Fac\Bypassed Bytes Sum\Non Default Bypassed Bytes Sum))
END

bas_vdl :netherlands: (BOB member since 2012-11-08)

#6

Look at the generated SQL code and you will see that the WHERE clauses of your measures are logically ANDed (TRAFFIC_DIM.SERVICE = ‘Default’ AND TRAFFIC_DIM.SERVICE <> ‘Default’) , thereby retrieving an empty result set.

Use SQL syntax: CASE WHEN… ELSE… END instead

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

#7

I dont see the where clauses at all in de generated SQL in WEBI

SELECT
  DATE_DIM.YEAR_MONTH_NUMBER,
  DATE_DIM.YEAR_MONTH_ABBR,
  DEVICE_DIM.DEVICE_NAME_PRTG,
  CASE WHEN ( sum(TRAFFIC_FAC.BYPASSED_BYTES) ) <=0
THEN 0
ELSE 
( sum(TRAFFIC_FAC.BYPASSED_BYTES) ) / (( sum(TRAFFIC_FAC.INTERCEPTED_CLIENT_BYTES) ) + ( sum(TRAFFIC_FAC.BYPASSED_BYTES) ))
END
FROM
  TRAFFIC_DIM INNER JOIN TRAFFIC_FAC ON (TRAFFIC_FAC.SVC_TRAFFIC_KEY=TRAFFIC_DIM.SVC_TRAFFIC_KEY)
   INNER JOIN ACCOUNT_DIM ON (TRAFFIC_FAC.ACCOUNT_KEY=ACCOUNT_DIM.ACCOUNT_KEY)
   INNER JOIN DEVICE_DIM ON (DEVICE_DIM.DEVICE_KEY=TRAFFIC_FAC.DEVICE_KEY)
   INNER JOIN DATE_DIM ON (DATE_DIM.DATE_KEY=TRAFFIC_FAC.DATE_KEY)
  
WHERE
  ACCOUNT_DIM.ACCOUNT_CODE  =  'BSS'
GROUP BY
  DATE_DIM.YEAR_MONTH_NUMBER, 
  DATE_DIM.YEAR_MONTH_ABBR, 
  DEVICE_DIM.DEVICE_NAME_PRTG

bas_vdl :netherlands: (BOB member since 2012-11-08)

#8

That is because for your final measure you are using @SELECT, which as the name suggests takes only the SELECT portion of measure 1 and measure 2.
Again, define your base measures (Default Bypassed Bytes Sum & Non Default Bypassed Bytes Sum ) using CASE WHEN … SQL syntax, no WHERE clause.

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

#9

Andreas, sorry i’m not following you here. My experiance with SAP BO is only a few months.

Can you please give me a hand here with some detailed steps?

bas_vdl :netherlands: (BOB member since 2012-11-08)

#10

A) @SELECT formula references only the SELECT definition, therefore the WHERE definition is missing.

B) Create a query with only measure 1 and measure 2 and look at the generated SQL code. You will see the conflicting WHERE clause.

Solution:
Define measure 1 such as:
Measure 1: Default Bypassed Bytes Sum
Projection: SUM
Select:
SUM
( CASE WHEN TRAFFIC_DIM.SERVICE = ‘Default’
THEN TRAFFIC_FAC.BYPASSED_BYTES
END
)

Do that for measure 2 as well, and you will see how beautifiul measure 3 will work, finally.

On a side note, please read also through this post of mine regarding definition of proper measures: diff between Select SUM (Table. Field) & Select Table. F

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

#11

That did the trick, thank you!!!

bas_vdl :netherlands: (BOB member since 2012-11-08)