NonAdditive Measure in Cube

Question

Hi,

I want to add "Market Rate" measure in SSAS Cube as Nonadditive , please let me know the steps to be performed.

I have stated aggregate function for "Market Rate" as "None" doesn't seem that it is working.

Also there are blogs that we have to create a degenerate dimension.

I am totally clueless.

Request you to please help on this issue.

Thanks

Answer 1

0

So.. Your problem is, that Non Additive Measures will show values only on the lowest granularity. For example if you had a Fact Dimension and go to the Leaf members of that dimension, than the Non Additive Measure will show you a value.

If you are above the lowest granularity, value will not be shown.

If you would like to show the Market Rate also on higher granularity levels, you have basicaly three possibilities:

Create a Market Rate Dimension and instead of storing concrete market rate in the fact table, store the dimension keys. Then you can easily show the Market Rates as you can slice by the Market Rates.
Use another suitable Aggregate function eg. Last Non Empty if this is acceptabe.
Create a calculated measure which will retrieve appropriate value

I suggest you to go using the option 1., as it will give you the best performance.

answered Aug 27 '12 at 09:40 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

Pavel...I am not understanding the Option 1 at all..request you to please eloborate more on this...

Aug 27 '12 at 04:30 PM Kirman50

I mean, you create a dimension [Market Rate] which will contain each unique Market Rate. Then in the Fact Table you will have ID of the [Market Rate] (of course you can eventually leave the current field if it will be required for other purposes.

Then you can use query like the one below for example.

SELECT
 {
 [Measures].[USD Volume]
 } ON COLUMNS,
 NON EMPTY {
 [Date].[Calendar].[Month].MEMBERS
 *
 [Customer].[Customer].[Customer].MEMBERS
 *
 [Market Rate].[Market Rate].[Market Rate].MEMBERS

 } ON ROWS
FROM [Your Cube]

Anyway if the Market Rate is directly related to an existing dimension, you can implement it as additional attribute of that particular dimension.

The best final implementation depends on your needs and data.

Aug 27 '12 at 05:05 PM Pavel Pawlowski

(comments are locked)

NonAdditive Measure in Cube

Follow this question

Related Questions