# MDX Calculation for a complex query

### Question

• Hi,

I have a little bit of experience using MDX queries using SSAS 2012, but this time I found a complex problem to solve with this calculation. I have the following query (this query is used to calculate an indicator of efficiency in the company)

SELECT A.atribute_number
,SUM(DISTINCT A.atribute_value)
FROM fact_table A
INNER JOIN dim_table B ON (A.keyValue = B.keyValue)
WHERE B.value = 1 AND A.value2 < 1 AND B.range IN (2,5,6,5)
AND A.contact_id IN (SELECT contact_id
FROM fact_table
WHERE B.range = 4
AND value = 2);

I have already done some MDX queries  to try to get same results, but I couldn't get same resut yet. I read some books and tutorials that whichever SQL query should be replicate using a MDX to query (Is that true?).

My strategy to solve this query is as following:

1) The agregations were calculated previously in the cube definition, so nothing to do in this part (SELECT section in the query)

2) In the Filters (WHERE section in the query), I tried to incude that conditions in the MDX WHERE section, but I have no idea how to manage the condition A.value < 1, because in a MDX query there is no "<" conditional, also I have no idea how to translate the subquery section (.. AND A.contact_id IN (SELECT contact_id... ) .. ).

Currently, My goal is to replicate the SQL functionality using MDX.

I have used MDX to calculate Measures, but I am lost trying to solve/translate this query. (Sorry if something wrong with my approach to solve this issue, but I'm new with MDX :D)

Any kind of advice or suggestion will be appreciated.

Tuesday, November 27, 2012 1:10 PM

• thanks
• Marked as answer by Wednesday, December 12, 2012 3:20 PM
Wednesday, December 12, 2012 3:20 PM

### All replies

• Hi there,

For your < condition, try using filter or see this http://msdn.microsoft.com/en-us/library/ms144854.aspx or this http://sqlblog.com/blogs/mosha/archive/2007/11/22/optimizing-count-filter-expressions-in-mdx.aspx or see this http://www.databasejournal.com/features/mssql/article.php/3662721/MDX-Clauses-and-Keywords-Use-HAVING-to-Filter-an-Axis.htm

For the IN statement you'll want to create a set to define this list or create a physical attribute in contact dimension to denote these types of contacts then filter on the attribute.  Filter should work in this case too.

http://gumper.com/mdx.html

http://www.ssas-info.com/ssas-cheat-sheets/922-mdx-various-queries-cheat-sheet

Hope this gives some ideas.

cheers,

Andrew

Andrew Sears, T4G Limited, http://www.performancepointing.com

Tuesday, November 27, 2012 5:57 PM
• Thanks a lot.

I will try.

Wednesday, November 28, 2012 2:33 AM
• Sorry, but I still have troubles with the subquery :S.

This section of the query is a headache:

AND A.contact_id IN (SELECT contact_id
FROM fact_table
WHERE B.range = 4
AND value = 2);

If you notice the query is filtering the same fact_table. I have created a fact_dimension on my cube, but when I have tried to filter the group of sets in the WHERE sentence I receive an error message that says that a sets is not allowed there.

What should be the right way to filter the fact_table proberly? I have tried the subcube and subselect idea, but without success until now.

Do you mind giving me an example how to apply the cube filter properly?

Wednesday, November 28, 2012 3:19 PM
• Hi there,

Something like this should work

WHERE ( filter({[Range].[2],[Range].[3]},[Measures].[Value] = 2) )