locked
Using a dimension atribute as a measure RRS feed

  • Question

  • Hi,

    I have Invoicing values in a dimension table and I want these as a measure. I can't add them in my fact table because the grain of my fact table is different i.e. my sales fact table may have 6 lines relating to the 1 invoice.

    What I want to do is to create the dimension value as a calculated member. However, when I do this all the values are zero.

    WITH MEMBER [measures].[test] AS STRTOVALUE( [Service Invoice Amount].[Calculated Net Value].CURRENTMEMBER.Properties( "Key" ))
    	 
    	 SELECT [measures].[test] ON COLUMNS, 
    		NON EMPTY { (
    					[Service Order Assignment].[Employee Number].[Employee Number].ALLMEMBERS * 
    					[Service Order Assignment].[Employee Name].[Employee Name].ALLMEMBERS) 
    					} ON ROWS 
    		FROM [ServiceOrder]
    		WHERE [Service Order Assignment].[Customer Order Number].&[A027391534]

    Monday, March 25, 2013 8:15 PM

Answers

  • The best thing to do here is to create "real measures" from your dimension table. A single table can play the role of both a dimension and a fact at the same time. You will get much better performance and you will find most scenarios will work better than an equivalent calculation.

    http://darren.gosbell.com - please mark correct answers

    Tuesday, March 26, 2013 4:04 AM
  • That's correct.

    If you remove the attribute and replace it with a measure I think the processing time should be similar.

    If you keep the attribute and add the measure it will require an additional scan of the dimension table/view


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Eileen Zhao Wednesday, April 3, 2013 8:26 AM
    Tuesday, March 26, 2013 9:47 AM

All replies

  • The best thing to do here is to create "real measures" from your dimension table. A single table can play the role of both a dimension and a fact at the same time. You will get much better performance and you will find most scenarios will work better than an equivalent calculation.

    http://darren.gosbell.com - please mark correct answers

    Tuesday, March 26, 2013 4:04 AM
  • So, just to clarify. Within BIDS click on the Measures and add the view for the Service Invoice Amount as a new measure group and add the Calculated Net value as a measure.

    If this is correct what impact would this have on processing time?

    Tuesday, March 26, 2013 9:06 AM
  • That's correct.

    If you remove the attribute and replace it with a measure I think the processing time should be similar.

    If you keep the attribute and add the measure it will require an additional scan of the dimension table/view


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Eileen Zhao Wednesday, April 3, 2013 8:26 AM
    Tuesday, March 26, 2013 9:47 AM