# Semi additive measures in ssas standard version

### Question

• Hi,

I am using Sql server 2008 r2 standard version. in this i developed a cube.

i am facing issues while applying semi additive measures to columns.

i have measures table and  product,customer,time dimensions table. in measures we have one column price of product. when we generate report all price values are showed in total. but we want to show the each product price separately.

example:  product "A" price is 100 and a customer buys product quantity two,  total amount will be 200  but it shows price also 200. but we want to show as 100.

how can we achieve semi additive measures in sql server 2008 r2 standard version.

Chandra

Wednesday, January 16, 2013 11:03 AM

• Hello Chandra,

Semi Additive measures are not supported in the Standard Edition of 2008 R2. To implement it , you have to get Enterprise edition.

http://www.microsoft.com/en-in/sqlserver/product-info/compare.aspx

Best Regards Sorna

Wednesday, January 16, 2013 11:42 AM
• Hi,

One way is to do this calculation is on the fact table in the data source view of the cube(Price x Quantity). Put the price information in the product dimension instead. If you want the price information in the fact table you can use the Min() aggregation function.

BR

Wednesday, January 16, 2013 11:54 AM

### All replies

• Hello Chandra,

Semi Additive measures are not supported in the Standard Edition of 2008 R2. To implement it , you have to get Enterprise edition.

http://www.microsoft.com/en-in/sqlserver/product-info/compare.aspx

Best Regards Sorna

Wednesday, January 16, 2013 11:42 AM
• Hi,

One way is to do this calculation is on the fact table in the data source view of the cube(Price x Quantity). Put the price information in the product dimension instead. If you want the price information in the fact table you can use the Min() aggregation function.

BR

Wednesday, January 16, 2013 11:54 AM
• first things first: price is not semi additive. it is non-additive. you can't aggregate price in any dimension.

you can always express price with sold quantity and sold value. the ratio of the two is the price. this should be a calculated measure. this measure does make sense for a product group, and extended period of time, or a even geographical area. in these cases, it will be an average price.

• Proposed as answer by Wednesday, July 24, 2013 7:47 PM
Wednesday, January 16, 2013 12:21 PM
• Although the SQL Server standard edition does not provide semi additive measures, nothing prevents you to define your own. Have a look at http://ms-olap.blogspot.com/2012/08/semi-additive-measures-in-sql-server.html

Joe

anon

Wednesday, July 24, 2013 7:49 PM