Difference Between "nvl() and Sum(nvl())..."

[Former Member]

Hi Techies,

I have a requirement of changing the definition of measure Object in universe...

I have a measure object defined at my universe as nvl(table_name.column_name,0) and I have ran the report and it was successful..

Now the scenario is I want to change my existing measure object definition to sum(nvl(table_name.column_name,0))...

Here my concern is that will this change have any impact on the reports developed, I mean the values that the existing report is showing ...

How ever I have to do the analysis and data validation with the sample data ..... I am also doing the same ...

If some one can give me the impact of nvl() and sum(nvl()) that would be much more helpful to me...

[Former Member]

The nvl() function is just going to replace No_value/ Null with 0.

So, you are asking if you add aggregation function (sum, count, etc.,) in this measure does it affect the existing reports,

Can't say exact answer, because

Before : your backend sql,

select column1, column2, column_name, ... from table_name

Result set : 100 records

After you add sum(): your sql,

select column1, column2, sum(column_name), ... from table_name

Result set: 2 Records

This sum() is going to do a group by () on your database and bring the aggregated values and improves performance and may drastically minimize number of records. So if the report developers puposefully didn't put sum() in this measure (Ex: They want see each record without any aggregation), then adding sum() may affect that report,

If this Measure is just used to find totals etc, then its not going to affect the reports

For more info: SQL Aggregate Versus Universe Projection

[Former Member]

Hi Srikanth,

Thanks a lot for the wonderful and useful explanation ......

and you have understood my query exactly ..

Here I have another Question i.e., In all my measure objects the select statement is given as "nvl(table_name.column_name,0) and the PROJECTION LEVEL (from the object properties set ) is set to SUM...

And now my question is If I change my select statement from  "nvl(table_name.column_name,0)" to "sum(nvl(table_name.column_name,0)) " ...WITH OUT CHANGING THE PROJECTION LEVEL AGGREGATION ....will this have any Impact...?

[Former Member]

select

     col1, col2, nvl(col3,0)

from

     table4

select

     col1, col2, sum(nvl(col3,0))

from

     table4

group by

     col1,col2

It DOES NOT depend. Both will give you the same BO report results with the measure projection set to sum. The difference is that you will bring all the individual rows back without the sum and the aggregate work will be done on the webi server, whereas with the sum in place, the aggregation is done at the database query level.

Adding the sum will make NO DIFFERENCE to the overall result and WILL improve Webi report performance. Go ahead and do it.

[Former Member]

Here is the scenario for my 'IT DEPENDS' answer,

I have a Date dimension & Quantity Measure in Universe,

Date - Table.DateColumn

Quantity - nvl(Table.Quantity,0)  -- Projection sum

-> Created a webi report on the above 2 objects, added new variable Dimension and added Quantity measure in it.

-> Created 2 Blocks one with my new Variable Dimension (Assume my user don't want aggregation of Quantity) and other with actual Quantity Object from Universe

Result: Screenshot (Before)

Later I went to back and changed Quantity in Universe,

Quantity - sum(nvl(Table.Quantity,0))  -- Projection sum

Results: Screenshot (After)

[Former Member]

Like I explained in screenshot, IF someone puposefully excluded sum()  and converted that Measure Object into Dimsion in any of your reports, it may change, otherwise like Mark said, it won't effect your results as long as projection is same.

[Former Member]

We're talking about measures though - off course dimensions behave differently because they have no projection options and you wouldn't expect them to be grouped together. I'm not sure what the example above is meant to achieve?