Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
unexpected result with "Extract"

Jawab unexpected result with "Extract"

  • Tuesday, December 11, 2012 11:42 AM
     
      Has Code
    Sign In to Vote
    0

    if I run the following query:

    with member [Measures].[SOR Cost] as  ([D Services].[Service Unit Price].CurrentMember.membervalue) 
select [Measures].[SOR Cost] on 0, 
 Filter(([D Services].[Service Description].children,[D Services].[Service Unit Price].children),
 [Measures].[SOR Cost]>0)on 1
from [TC Cube]

    I got:

    I wanted to hide the second column, so I modified the last query and added EXTRACT:

    with member [Measures].[SOR Cost] as  ([D Services].[Service Unit Price].CurrentMember.membervalue) 
select [Measures].[SOR Cost] on 0, 
 Extract(existing (Filter(([D Services].[Service Description].children,[D Services].[Service Unit Price].children),
 [Measures].[SOR Cost]>0)),
		[D Services].[Service Description]) on 1
from [TC Cube]

    unfortunately I got:

    anybody knows why I'm getting All All All All ...  in the SOR Cost ?!! and how can i fix it ?

     

All Replies

  • Tuesday, December 11, 2012 3:12 PM
    Moderator
     
     
    Sign In to Vote
    0

    The reason you're getting All in your SOR Cost measure is because the name of the currentmember on the Service Unit Price hierarchy is the All Member. You took it out of the query, and I'm guessing that one Service Unit Price can be associated with many Service Descriptions in the dimension table, and vice versa.

    What you probably want to do here is to create a new measure group from your dimension table, with SOR Cost as a new real (ie not calculated) measure in it. You can then put a regular relationship between your D Services dimension and this new measure group and you should be able to get the results you're looking for. You might need to use some MDX to stop the Prices aggregating, or use the None AggregateFunction on your measure.

    HTH,

    Chris

    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    • Proposed As Answer by Daryl Wenman-Bateson Tuesday, December 11, 2012 4:32 PM
    • Unproposed As Answer by Butmah Wednesday, December 12, 2012 3:25 AM
    •  
     
  • Wednesday, December 12, 2012 12:36 PM
     
     
    Sign In to Vote
    0

    Thanks Christopher, but I want to make some points more clear:

    • as you may noted, Service Unit Price and Service Description are from the same dimension. the Unit Price is the price of the service and the Description is just a description for the service ! .. yes it may happen that two services have the same price!
    • I didn't like the idea of converting the service unit price to a measure, because it doesn't change by changing the keys from the other dimensions. I see it like the name of the employee, it doesn't change by changing the date, the company, or location, the same happen with the service price.
    • this is just for training and learning purposes; so i want to fix the "query" if it's possible. actually, after fixing it, I want to multiply the SOR cost by the number of hours on the service to get the total cost.



    • Edited by Butmah Wednesday, December 12, 2012 12:37 PM
    •  
     
  • Wednesday, December 12, 2012 1:14 PM
    Moderator
     
     
    Sign In to Vote
    0

    At the moment there's not going to be any way to fix the query - or at least not without writing some unpleasant MDX and making some assumptions you probably wouldn't want to make in the real world. This is one of those cases where you would need to fix your cube or dimension design instead: at the moment, in your second query, the expression [D Services].[Service Unit Price].CurrentMember returns the All Member on Service Unit Price because your dimension design tells SSAS that one service can have multiple prices (is this correct)?

    Building measures from dimension tables is not, in fact, a bad thing: it's a very useful technique. Here's one example of this:
    http://sqlblog.com/blogs/mosha/archive/2007/05/27/counting-days-in-mdx.aspx

    Just because the measure varies by just one dimension doesn't mean it shouldn't be a measure.

    HTH,

    Chris

    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

     
  • Thursday, December 13, 2012 2:00 AM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    I agree with Chris that making this column a measure is probably the best way to go. I do weekend/weekday calculations in some of my cubes by creating a measure group over my date dimension which is only linked back to the date dimension itself. This technique can simplify your MDX and improve performance.

    But if you just want to "fix" the query without altering the structure, one option is to drop the 3rd column instead of trying to drop the second one.

    eg.

    select{} on 0, 
 Filter(([D Services].[Service Description].children,[D Services].[Service Unit Price].children),
 [D Services].[Service Unit Price].CurrentMember.MemberValue >0)on 1
from [TC Cube]


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

     
  • Thursday, December 13, 2012 4:27 AM
     
     
    Sign In to Vote
    0

    Thanks all.

    Christopher, the answer to your question is: the price of the service is just a normal attribute for the service... one service id has one price and one description and both of those attributes are in the same dimension table (please reread the first point in my previous reply). I didn't know that i have to set this in the dimension or cube, and i don't know how to do this. please help :) possibly this will be the solution.
    Another thing Chris, if "[D Services].[Service Unit Price].CurrentMember returns the All Member" as you said, why if i didn't use the Extract it works fine ?! .. please recheck my first query and first result in my first post.

    Darren, please reread the third point of my second post, you will fine that my intention is " to multiply the SOR cost by the number of hours on the service to get the total cost."; so I don't think the query you've suggested will help.

     
  • Thursday, December 13, 2012 4:56 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Christopher, the answer to your question is: the price of the service is just a normal attribute for the service... one service id has one price and one description and both of those attributes are in the same dimension table (please reread the first point in my previous reply). I didn't know that i have to set this in the dimension or cube, and i don't know how to do this. please help :) possibly this will be the solution.

    You set this via the attribute relationships in your dimension. At the moment you either do not have a relationship between description and unit price or the unit price is "under" the description. Which because SSAS does not support 1 to 1 relationships means that a description can have one or more unit prices. One work around would be to have the unit price on the "one" side of the relationship and the description on the "many" side. Then when you have a description SSAS will know that there is one and only one possible unit price so it will display that instead of the all member. (this is known as attribute overwriting)

    Another thing Chris, if "[D Services].[Service Unit Price].CurrentMember returns the All Member" as you said, why if i didn't use the Extract it works fine ?! .. please recheck my first query and first result in my first post.

    Because calling extract removes the unit price attribute from the context of the query and in the absence of any attribute overwrites it will revert back to it's default member (i.e. the ALL member)

    Darren, please reread the third point of my second post, you will fine that my intention is " to multiply the SOR cost by the number of hours on the service to get the total cost."; so I don't think the query you've suggested will help.

    Then as I said in the first part of my reply, creating a measure group with Service Unit Price as a measure that is linked only to the service dimension is the best approach. You could then create a measure expression to do the Unit Price * hours on service. But the best approach in terms of query performance would be to do the multiplication of hours and unit price in a view and load the total cost into the cube as a measure. Then it can be pre-aggregated and cached rather than forcing SSAS to recalculate this at the leaf level all the time.

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

    • Marked As Answer by Butmah Thursday, December 13, 2012 9:18 AM
    •  
     
  • Thursday, December 13, 2012 10:17 AM
     
     Answered Has Code
    Sign In to Vote
    0

    here it is :

    with member [Measures].[SOR Cost] as 
generate([D Services].[Service Unit Price].children,[D Services].[Service Unit Price].CurrentMember.membervalue) 
select [Measures].[SOR Cost] on 0, 
 [D Services].[Service Description].children  on 1
from [TC Cube]

    result


    • Marked As Answer by Butmah Thursday, December 13, 2012 10:17 AM
    • Edited by Butmah Thursday, December 13, 2012 10:20 AM
    •