x

NonAdditive Measure in Cube

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
more ▼

asked Aug 23, 2012 at 06:18 AM in Default

Kirman50 gravatar image

Kirman50
0 2 2 3

Can you a briefly desccribed what you want to achieve with the NonAdditive dimension? Do you want to clasify the data by the Market Rate or what?
Aug 24, 2012 at 05:22 AM Pavel Pawlowski

Pavel , In Fact Table we have Market Rate.

So if the user put the following Dimensions in Pivot

  1. Time.Month.Level
  2. Customer.Customer Name
  3. Channel.Channel Level 3
  4. Settlement . Setllement Level 4
  5. Market Rate
  6. Sales Margin
  7. USD Volume

Hope you get the gist of the requirement.

Aug 24, 2012 at 12:28 PM Kirman50
Please anyone can help me on this requirement?
Aug 25, 2012 at 06:50 PM Kirman50
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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:

  1. 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.
  2. Use another suitable Aggregate function eg. Last Non Empty if this is acceptabe.
  3. 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.
more ▼

answered Aug 27, 2012 at 09:40 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Pavel...I am not understanding the Option 1 at all..request you to please eloborate more on this...
Aug 27, 2012 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, 2012 at 05:05 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x171

asked: Aug 23, 2012 at 06:18 AM

Seen: 1580 times

Last Updated: Aug 27, 2012 at 05:05 PM