BusinessObjects Board

Calculating measure in universe level give wrong data

building semantic layer
#1

Here is my situation,

I have Market dimension table and Product Dimension connected to Fact table,

Market Table product dimension Fact Table
ro_id ro_id ro_id
Mkt_desc product dimension Volume
Sg_Flag

Two dimension tables are connected to Fact table

Requirement is to get market volume, product volume and Market share(Market volume/product volume)*100

to get market volume in report I created measure Market volume((fact.volume) where sg_flag=y) and to get product volume I created measure Product Volume( (fact.volume)) and to get Market share I created measure Mrkt share( Market Volume/ Product Volume)*100

I used 2 data provider to get market volume and product volume with respective dimension in report.the report fetches proper data, but when I tried to pull the market share measure in report the maker share is not fetching right values. but when I do the Market share calculation in report level I am getting expected value. but my user need it as an object in universe level.

Need your valuable suggestion.

subu82 (BOB member since 2008-12-29)

#2

Four quick hints:

  1. Always use a SQL aggregate function in the SELECT box (of Universe Desinger) when defining a measure, see also here: diff between Select SUM (Table. Field) & Select Table. F

  2. Do avoid WHERE clauses, instead use CASE WHEN… statements, because multiple WHERE clauses in measures are always logically ANDed, leading to no data, e.g.:

SELECT Country FROM SomeTable
WHERE Country = 'USA' AND Country = 'Germany'

leads to an empty result set.

  1. All ratios such as percentages must either be calculated locally in the report or set to database delegated in the universe to ensure correct data, because 70% + 80 % does typically not equal (70% +80% ) / 2. You need a weighted avareage instead.

  2. Get a consultant on board who has sound universe design experience to assist you.

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