So it's been kind of a while since I've had to do much cube setup work and I'm really rusty - my current client is picky about how the model is designed and wanted me to build as many of these calcs in MDX as possible, rather than deriving them in the ETL as separate measures (which probably would have been easier). The basic structure is simple - for the sake of this example, assume a fact table with a single additive measure in it (basically just a row count), and then a handful of dimensions.

The problem I have is that they define a ton of other "calculations" as a subset of this main row count and I could easily set up a query to return any of these numbers, but they want it built in as if these are all separate calcs.

So, the gist of it is, Calculated Measure A  = Sum(Base Measure B) for all fact records that have Billing Dimension attributes D, E, or F.

If I were to do this in SQL, it'd probably look something like this:

SELECT SUM(f.MeasureBValue) CalculatedMeasureA
FROM FactTable f
JOIN DimBilling d ON f.DimBillingId = d.DimBillingId
WHERE d.BillingAttributeName IN ('ValueD', 'ValueE', 'ValueF')

I started by using this, but this returns 0 if I'm not using [Billing].[Attribute] in my query, even with a default member of [Billing].[Attribute].[All]:

CREATE MEMBER currentcube.[Measures].[Calculated Measure A] AS
iif([Billing].[Attribute].currentmember=[Billing].[Attribute].[A] or [Billing].[Attribute].currentmember=[Billing].[Attribute].[B] or [Billing].[Attribute].currentmember=[Billing].[Attribute].[C]
	,[Measures].[Base Measure B]
	,0
)

Then I tried this, but if I put [Billing].[Attribute].members on rows of my query, they all show the same constant number:

CREATE MEMBER currentcube.[Measures].[Calculated Measure A] AS
Aggregate(
	{[Billing].[Attribute].[A],[Billing].[Attribute].[B],[Billing].[Attribute].[C]}
	,[Measures].[Base Measure B]
)

So... is there a better way to do this? Should I be summing/filtering perhaps?

Thanks in advance.

• Edited by Adam_Schmitt 16 hours 53 minutes ago

Hi

You can use the iif function. Check the below calculated member in the query for Adventureworks DB. You can use the similar script in cube level as well

with member calcmeasurenew as
 Iif(([Date].[Calendar].currentmember = [Date].[Calendar].[Calendar Year].&[2007]
       or [Date].[Calendar].currentmember = [Date].[Calendar].[Calendar Year].&[2006]) 
     , [Measures].[Reseller Sales Amount], null)
select {[Measures].[Reseller Sales Amount],calcmeasurenew} on 0,
        [Date].[Calendar].[Calendar Year].Members on 1
from [Adventure Works]

Result

Regards

Nishar

Please mark the answers

• Edited by MNisharMSDN Thursday, October 17, 2013 10:47 PM

That's basically what one of my options was... but if you take Calendar Year off the 1 axis of the query, doesn't everything return null? I don't get to have AdventureWorks installed here, but that's basically what happens if I use the similar logic in my database.

Ok, you want your calculation to behave differently between "All" and member level. , the below query might help you

Note: 

477 = watter bottle -30z

484 = Bike wash disolver

with member calcmeasurenew as
Aggregate	(
				IIF( [Product].[Product Categories].currentmember.properties("MEMBER_VALUE") = 'All Products',
					 {[Product].[Product Categories].[Product].&[477] , [Product].[Product Categories].[Product].&[484]}
					 ,iif( ([Product].[Product Categories].currentmember = [Product].[Product Categories].[Product].&[477]
					         or 
					        [Product].[Product Categories].currentmember = [Product].[Product Categories].[Product].&[484]
					        ) , [Product].[Product Categories].currentmember
					        ,null
					      )  
   
				    )
			,[Measures].[Reseller Sales Amount] 
			)
select {[Measures].[Reseller Sales Amount],calcmeasurenew} on 0,
       [Product].[Product Categories].[Product].Members on 1
from [Adventure Works]

and 

with member calcmeasurenew as
Aggregate	(
				IIF( [Product].[Product Categories].currentmember.properties("MEMBER_VALUE") = 'All Products',
					 {[Product].[Product Categories].[Product].&[477] , [Product].[Product Categories].[Product].&[484]}
					 ,iif( ([Product].[Product Categories].currentmember = [Product].[Product Categories].[Product].&[477]
					         or 
					        [Product].[Product Categories].currentmember = [Product].[Product Categories].[Product].&[484]
					        ) , [Product].[Product Categories].currentmember
					        ,null
					      )  
   
				    )
			,[Measures].[Reseller Sales Amount] 
			)
select {[Measures].[Reseller Sales Amount],calcmeasurenew} on 0,
       [Customer].[Customer Geography].[Country].Members on 1
from [Adventure Works]



Regards

Nishar

Please mark the answers

• Edited by MNisharMSDN 14 hours 49 minutes ago
• Marked as answer by Adam_Schmitt 14 hours 29 minutes ago

Thanks, I was afraid of that.

Probably time to push back on the client and get these into the ETL of the fact table as simple sums. I personally can't think of any good reason to make the cube this complicated, especially since power users will be exposed to the UDM I've created in views on the SQL server, which the DSV/cube point at.

In any case, I'm glad to know I wasn't missing something obvious in the MDX. On its surface, this approach looks like it should be much easier than it actually is, and from a maintenance standpoint if nothing else, I think putting this in SQL code in the ETL is a much better approach than trying to bend over backwards in the cube when I could simply be summing another fact column.

• Edited by Adam_Schmitt 14 hours 27 minutes ago

I partially agree with you but 15 years back even grouping in SQL was considered as complex.

careful about bringing this summed measure into your fact table. you might need to move this into a separate summary fact table to achieve the same result via reporting tools.

I don't know what you are facing out there but In my opinion keeping all/partial of your reports based on views is much more painful and require high maintenance than MDX.

Regards

Nishar