none
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.

    thanks in advance.

    Tuesday, November 27, 2012 1:10 PM

Answers

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.

    More links

    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?

    Thanks in advance.


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

    Something like this should work

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

    Substituting your proper dimension values.

    cheers,

    Andrew


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

    Monday, December 03, 2012 2:04 PM
  • thanks
    • Marked as answer by Marcelo Claure Wednesday, December 12, 2012 3:20 PM
    Wednesday, December 12, 2012 3:20 PM