Universe with total measure

[Former Member]

Hi!

My goal is to create a universe with 1 dimension (Year) and 2 measures - quantity and total quantity, where total quantity = sum(quantity) = aggregated value for all available years in the data.

Is it possible?

Thanks

[marc_daniau]

Yes it is possible. Two methods can be used:

1) Check the SQL Server object "Customer Calls\Percent of Total" in Warehouse.blx from sample universe documented at http://scn.sap.com/docs/DOC-22145

2) Another method can be used as described in SAP HANA example on pages 11 and 12 in

https://scn.sap.com/docs/DOC-34504

[Former Member]

Hi!

Thank you, 2-nd approach is great!

Is there any way to calculate running sum (cumulative) not total over years in such universe?

[marc_daniau]

If the database you are using supports SQL analytical functions, the running sum can be done with a sum over syntax. A sample query is given below.

SELECT

dim, val,

sum(val) over (partition by dim order by val rows unbounded preceding) my_running_sum

FROM my_table

For more details on analytical functions, please refer to your database SQL reference.

[swati_patil4]

Hi Yury,

When we design the Universe we need to understand what is the purpose of developing the Universe.

In your scenarion you would like to create an Universe with only 1 dimension and 2 measures. That is Year, Quantity and Total Quantity.

When you will create Quantity as a measure in Universe and if you use Sum function in projection when the measure will be aggregated there is no need to create Total Quantity, as the aim of creating Total Quantity for all the years available.

You will get the Total Quantity as you want if you pull Quantity object in a report without Year object. In this case the quantity will be aggregated at report level.

Please let me know if it helps.

Thanks,

Swati

[Former Member]

Thank you.

But my goal is to implement all data at the universe level. I'm going to use this universe in SAP Dashboard as well as in Webi. I want to show in one table Year, quantity, Total quantity (or running sum - doesn't matter). I just want to implement the value for the Total quantity only once in the universe and then use it everywhere.

What is wrong with my approach? Is it possible?

[Former Member]

Thank you.

But my goal is to implement all data at the universe level. I'm going to use this universe in SAP Dashboard as well as in Webi. I want to show in one table Year, quantity, Total quantity (or running sum - doesn't matter). I just want to implement the value for the Total quantity only once in the universe and then use it everywhere.

What is wrong with my approach? Is it possible?

[swati_patil4]

Hi Yury,

your approch is not wrong however, I thought it in different way. For example If we have different years and and different quantitites for each year.

let's say

year          Quantity

2005          45

2006          75

2007          90

in this case Total Quantity would be 45+75+90 = 210

As you said if you take only Year and Total quantity on report level it will look like

year     Total Qantity

2005     210

2006     210

2007     210

So it may be possible that it will misinterpret the report's data and it could be taken into consideration that each year has the same Total Quantity.

To avoid this kinf of situation I suggested you to use the Total Quantity at report level instead of creating it at Universe level. However you can create it at Universe level also.

Please let me know if it helps.

Thanks,

Swati