none
SQL -> MDX, merge sets RRS feed

  • Question

  •  

    Hello Experts,

     

    I’ve got a problem I described in the follow thread:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3623558&SiteID=1

     

    I didn’t get any answers and resolve this problem by using follow SQL statement:

     

    Code Snippet

    SELECT

             TR_1,  'TR Test1' AS CompType,   COUNT(ID)

    FROM dbo.myTable

    WHERE ID_FLAG = 1

    GROUP BY TR_1

    UNION

    SELECT

             TR_2,  'TR 2' AS CompType, COUNT(ID)

    FROM dbo.myTable

    WHERE ID_FLAG = 1

    GROUP BY TR_2

     

     

     

    Is something like this possible with MDX? I red UNION is not possible in MDX, maybe someone has another idea how I can resolve my problem.

     

    Best regards,

    Alex

    Thursday, July 17, 2008 10:14 AM

Answers

  •  

    After writing my previous post, I admit I was confused and shocked. Is it really so complicated or have I been astrained by rigid requests? I returned to our previous discussion and analyzed the basics. And then I got an idea of one, far more elegant way to solve your issue, which was on my mind from the start. Approach using only 2 calc measures (tupples), as I said once before. Here it is:

     

    Code Snippet

    WITH

    MEMBER [Measures].[Region] AS

      ( [Measures].[Sales Amount], [Sales Territory].[Sales Territory Region].&[10] )

    MEMBER [Measures].[Product] AS

      ( [Measures].[Sales Amount], [Product].[Product].&[552] )

    SELECT

    NON EMPTY

    {

       [Measures].[Region],

       [Measures].[Product]

    }

    ON COLUMNS

    FROM

    [Adventure Works]

     

    First impression after previewing such a report is: I don't see which product and region are in query. It says only "Region" and "Product". But, there is a way.

     

    If you make bolded items as parameters (region and product), they will be seen in parameters part of the report (above the report). Further more, you can edit the chart and set labels with expression. That way you will see which members are in report because you will use an expression with parameter value.

     

    In MDX, instead of [Product].[Product].&[552] you should write StrToMember([Product].[Product].&[" + @product + "]"), where @product is a parameter. Same for regions. If possible, use names instead of keys, for user-friendliness. Remember to remove &.

     

    Best regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Friday, July 18, 2008 11:20 AM
    Answerer

All replies

  • Can you specify your requirement little bit clearly.

     

    Thursday, July 17, 2008 10:25 AM
  •  

    Hi Alex,

     

    here's the basic principle of how to get it. Since you didn't provide any metadata (namings in SSAS), I'll write in general.

     

    Put your TR elements (TR_1 and TR_2) in rows. If you have a regular measure of distinct count type, put it in columns, otherwise you have to make it as calculated measure. Reply if you don't have it, so I'll write you one. Put Flag 1 member in filter and there you have your results.

     

    In case you need your results only in columns, not as a matrix, then put TR members in columns above measure instead on rows. In case you need to have only two elements on columns, then you should make calculated measure that represent tupple combination like this (TR_1, Count) and (TR_2, Count). That second field in T-SQL is redundant in SSAS, but if you need it, define it as calc measure also.

     

    So, you see, many things are possible. But you have to make a cube and have TR dimension, Flag dimension, and a measure of distinct count type.

     

    PS: Union is possible. In MDX you have Union() function which can also be written as +.

     

    Comments are welcome.

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Thursday, July 17, 2008 11:08 AM
    Answerer
  • Hello Mahendiran & Tomislav ,

     

    I would like to build a report with SSRS that gets his data from an AS-Cube.

    This report should contain a Column-Chart that contains two columns.

    Each column has to use a different data (in my example different attribute).

    How I can take this?

     

    With the sql-statement above I can reach this goal.

     

    Hope this explanation is more clearly

     

    @Tomislav:

    I've got a cube. But I'm not possible to build a chart with needed information.

    Yes, union is available, but has not the same function like in sql. Did I misunderstand something?

     

     

     

    Thursday, July 17, 2008 11:24 AM
  • Hello Tomislav,

     

    you are right. UNION and "+" going well if you use the structure below:

     

    Code Snippet

    [dimension].[attribute1].[level1].[item1].children +

    [dimension].[attribute1].[level1].[item2].children

     

     

    How can I implement the follow structure?

     

    Code Snippet

    [dimension].[attribute1].children +

    [dimension].[attribute2].children

     

     

    is this possible? I always get the error below:

    "Two sets specified in the function have different dimensionality."

     

    Best regards,

    Alex

    Thursday, July 17, 2008 12:01 PM
  •  

    Hi Alex,

     

    if you have elements from different hierarchies, you use CrossJoin in MDX. Or *.

     

    Code Snippet

    [dimension].[attribute1].children *

    [dimension].[attribute2].children

     

     

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Thursday, July 17, 2008 12:50 PM
    Answerer
  • Hello Tomislav,

     

    Ok Thanks a lot. The problem of this statement is, that right not I've got two and no more one column.

     

    best regards,

    Alex

    Thursday, July 17, 2008 2:28 PM
  •  

    Well Alex,

     

    in that case you need to construct calculated measures as aggregates or tupples. Aggregates are formed by Aggregate function. Whatever is inside comes out as one value. Tupples are formed like coordinates x,y,z, etc and placed inside brackets (). They are used to get a value at certain coordinate.

     

    Draw me a report the way you want it to come out. Rows and columns and values.

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Thursday, July 17, 2008 2:32 PM
    Answerer
  • Hello Tomislav,

     

    I would like to create a report with below function:

    e.g. you have the measure Count Sales and the two attributes Region and Products.

    Now I would like to create a columns chart (SSRS).

    First Column of this chart has to show the behavior between sales and Region and the second column has to show the behavior between sales and Products.

     

    How can I get this?

     

    Thanks,

    Alex

     

    Friday, July 18, 2008 6:32 AM
  • You can generate 2 charts. In 1 chart use Region as Category Groups, in 2nd chart use Product dimension as Category Group.

     

    Thanks, and Regards,

     

    Mahendiran

    Friday, July 18, 2008 7:51 AM
  •  

    Oh, you need it for SSRS? You should have posted under that forum. Or mention earlier.

     

    SSRS has some limitations. Only measures can go in columns, other dimensions on rows.

     

    Mahendiran's post is an answer to you. Use 2 charts. However, if you want to play tricks, here's one:

     

    Code Snippet

    WITH

    MEMBER [Sales Territory].[Sales Territory Region].[All Sales Territories].[Product 552] AS

    ' [Sales Territory].[Sales Territory Region].[All Sales Territories] '

    MEMBER [Measures].[X] AS

    ' iif ( [Sales Territory].[Sales Territory Region].CurrentMember Is

            [Sales Territory].[Sales Territory Region].[All Sales Territories].[Product 552],

            ( [Measures].[Sales Amount], [Product].[Product].&[552] ),

            [Measures].[Sales Amount]

          ) '

    SELECT

    NON EMPTY

    {   [Measures].[X]   } ON COLUMNS,

    NON EMPTY

    {

        [Sales Territory].[Sales Territory Region].&[10],

        [Sales Territory].[Sales Territory Region].[All Sales Territories].[Product 552]

    }

    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

    FROM

    [Adventure Works]

    CELL PROPERTIES

    VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

     

     

    This works on AW demo database.

     

    Explaination. We need only one column for attributes and one for measures. Since we have different dimensionality of attributes (members are from different dimensions) we need to mask one inside the other. So we define one calculated member. We can take any of those two. I took regions in my example. And I set it to All member. Then I created another measure to change the behaviour of original one. If I encounter my calc member, I'll know it and calculate for that product. Otherwise, I'll display normal value.

     

    I know it's not easy as in T-SQL and that it has many limitations, and that it is not ment to be used that way, but I tried to stick to your requirements and make whatever is possible. For curiosity if nothing else.

     

    Bottomline: use appropriate methods for request you have or accept side-effects. Meaning, use T-SQL query for that or 2 charts as Mahendiran advised. Treat my post as an experiment.

     

    Best regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

     

    Friday, July 18, 2008 10:27 AM
    Answerer
  •  

    After writing my previous post, I admit I was confused and shocked. Is it really so complicated or have I been astrained by rigid requests? I returned to our previous discussion and analyzed the basics. And then I got an idea of one, far more elegant way to solve your issue, which was on my mind from the start. Approach using only 2 calc measures (tupples), as I said once before. Here it is:

     

    Code Snippet

    WITH

    MEMBER [Measures].[Region] AS

      ( [Measures].[Sales Amount], [Sales Territory].[Sales Territory Region].&[10] )

    MEMBER [Measures].[Product] AS

      ( [Measures].[Sales Amount], [Product].[Product].&[552] )

    SELECT

    NON EMPTY

    {

       [Measures].[Region],

       [Measures].[Product]

    }

    ON COLUMNS

    FROM

    [Adventure Works]

     

    First impression after previewing such a report is: I don't see which product and region are in query. It says only "Region" and "Product". But, there is a way.

     

    If you make bolded items as parameters (region and product), they will be seen in parameters part of the report (above the report). Further more, you can edit the chart and set labels with expression. That way you will see which members are in report because you will use an expression with parameter value.

     

    In MDX, instead of [Product].[Product].&[552] you should write StrToMember([Product].[Product].&[" + @product + "]"), where @product is a parameter. Same for regions. If possible, use names instead of keys, for user-friendliness. Remember to remove &.

     

    Best regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Friday, July 18, 2008 11:20 AM
    Answerer
  •  

    Hello guys,

     

    Thanks a lot for your help. I gived up this issues and create two separate charts.

     

    @ Tomislav: Thanks a lot for the mdx-script. I’m very new in mdx.

     

    Thanks,

    Alex

    Friday, July 18, 2008 11:37 AM
  •  

    No problem Alex.

     

    Just be fair and give the credit to Mahendiran also, since it's his idea you're using in the end.

     

    Best regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Friday, July 18, 2008 11:48 AM
    Answerer
  • Thanks Tomislav Piasevoli I am watching you in many forumns,

     

    Thanks, and Regards,

     

     

    Friday, July 18, 2008 1:23 PM