none
SCOPE statement to specific

    Question

  • I have the following MDX that is supposed to create a calculated dimension member and populate all measures with the values from an existing member up to a timeframe, and then populate the measures past that timeframe with a second existing dimension member.  In other words - Latest Thinking measures are the same as Actual values until this period, then Latest Thinking measures are the same as S&OP values from now until the end of time.

    My problem is that with this statement I can ONLY see measure values if I bring in both the [Analysis Type].[Latest Thinking] and [Posting Date].[Fiscal].[Fisc Day of Year].Parent.Parent members.  All other queries with [Latest Thinking] tuples show null measures.  For example, I would like to see Latest Thinking by Customer or Material.

    --

    CREATE MEMBER CURRENTCUBE.[Finance Information].[Analysis Type].[Latest Thinking]
    AS NULL;

    SCOPE ( MeasureGroupMeasures('COPA') );

    ([Finance Information].[Analysis Type].[Latest Thinking]
     , {NULL : EXISTS([Posting Date].[Fiscal].[Fisc Day Of Year],[Posting Date].[IsToday].[True]).Item(0).Parent.Parent.Lag(1)}) =
       ([Finance Information].[Analysis Type].&[Actual]);

    ([Finance Information].[Analysis Type].[Latest Thinking]
       , {EXISTS([Posting Date].[Fiscal].[Fisc Day Of Year],[Posting Date].[IsToday].[True]).Item(0).Parent.Parent : NULL}) =
        ([Finance Information].[Analysis Type].&[S&OP]);

    END SCOPE;

    ----

    Any help or thoughts would be greatly appreciated.

    Thursday, June 14, 2012 5:59 PM

Answers

  • Justin,

         There is a difference between Physical members and Calculated Members when it comes to SCOPING over them. If you overwrite Physical Memebers like something that is tied to a row in a dimension table, or a physical base measure, then the values aggregate up hierarchies. This is not the case for Calculated Members. I have seen this with Measures but I suspect it is the same for members of other dimensions. Here is one thing you can try, if everyyhing else in your logic is what you want it to be and it is just the aggreagtion that is missing: Add a row to your dimension table for this member. Then by default, no rows from your fact table are tied to it. Then you can use the same Scope statement to give it values.

    Shabnam

    • Marked as answer by Justin Crosby Tuesday, June 19, 2012 1:25 PM
    Thursday, June 14, 2012 7:01 PM

All replies

  • Justin,

         I think this is because your Scope statement is defined at a different level of the [Posting Date] hierarchy than your query. In your query, when you skip the Posting Date, its default value of ALL is used. Your calculated memeber is NULL by default except for the intersections that you define within your SCOPE statement. Where does

    [Posting Date].[Fiscal].[Fisc Day of Year].Parent.Parent put you in the hierarchy? Can you specify your hierarchy levels and one sample member from each?

    Shabnam

      

     

    Thursday, June 14, 2012 6:33 PM
  • Shabnam,

    Thanks for taking a look!

    My Posting Date Hiearchy starts with year, and is defined like so:

    Fisc Year (2012)

    ->Fisc Period of Year  (201207)

    -->Fisc Week of Year (20120704)

    --->Fisc Day of Year (2012070403)

    My "IsToday" attribute is a single level attribute hierarchy, I use that to get the "today" Fisc Day of Year member so I know which period I'm in.

    The query going to the DefaultMember of ALL makes sense, but I guess I would have expected it to be able to aggregate up in the ALL member.  I guess I was expecting it to aggregate from the period level to create the "ALL"...maybe it can't do that from the middle of the hierarchy?  Should I add the leaf member to my SCOPE?

    Thursday, June 14, 2012 6:51 PM
  • Justin,

         There is a difference between Physical members and Calculated Members when it comes to SCOPING over them. If you overwrite Physical Memebers like something that is tied to a row in a dimension table, or a physical base measure, then the values aggregate up hierarchies. This is not the case for Calculated Members. I have seen this with Measures but I suspect it is the same for members of other dimensions. Here is one thing you can try, if everyyhing else in your logic is what you want it to be and it is just the aggreagtion that is missing: Add a row to your dimension table for this member. Then by default, no rows from your fact table are tied to it. Then you can use the same Scope statement to give it values.

    Shabnam

    • Marked as answer by Justin Crosby Tuesday, June 19, 2012 1:25 PM
    Thursday, June 14, 2012 7:01 PM
  • Adding a row to my dimension table might be a bit problematic as it's a junk dimension with lots of various attributes.  I do think I made some progress based on what you were saying though, got me thinking about overwriting the default member for the date dimension.  What I did was create a hardcoded (for now) set range for my dimension grain attribute, and then I did a third scope statement for the default member of the grain attribute.

    Works great for EVERYTHING except when I bring some attribute hiearchy (like the posting date hierarchy originally) that's in the same date dimension.  Then I just get the defaultmember's value repeated over and over.

    ---

    ([Finance Information].[Analysis Type].[Latest Thinking]
     , {NULL : [Posting Date].[Date ID].&[20120610]}) =
       ([Finance Information].[Analysis Type].&[Actual]);

    ([Finance Information].[Analysis Type].[Latest Thinking]
       , {[Posting Date].[Date ID].&[20120611] : NULL}) =
       ([Finance Information].[Analysis Type].&[S&OP]);

    ([Finance Information].[Analysis Type].[Latest Thinking]
       , [Posting Date].[Date ID].DefaultMember) =
    AGGREGATE({[Finance Information].[Analysis Type].[Latest Thinking] * [Posting Date].[Date ID].[Date ID]});

    Thursday, June 14, 2012 9:13 PM
  • Can you explain your [Finance Information] dimension a little bit more? For example, in your fact table do you have some rows marked as S&OP and others as Actual? Or is this some kind of Hanger dimension similar to what the BIDS wizard builds for Time Series calcs like Month To Date, etc. In this case, you have one default member connect to all rows and you define the rest of the members off of that one and Date dimension.

    Friday, June 15, 2012 1:51 AM
  • This is an interesting problem!

    Do you have access to AdventureWorks sample database? I think your dimension is similar to the Scenario dimension that is connected to Financial Reporting fact table. I was able to reproduce your problem. I defined a new member in the Scenario dimension using this:

    [My Scenario] is initially set to [Budget] for all days. No need to specify days here since it is for all of them.  I will overwrite it for the second part of the days with the next statement. (Your approach of assiging NULL first and two sets is valid as well.) Then I use a Scope statement to further define it from 2003/01/01 until the end by setting it to Actual.

    This does give me Budget values up until Jan 2003 and then Actuals after that. However, if you look at the total value, which is for all days, it is not the sum of My Scenario for the individual days. It is just equal to Budget (NULL in your case). 

    I then tested what happens if you use an existing member, in this case I used [Forecast]. I overwrote the values and this time, the total is correct. Even when you limit the days to one or a couple of days the total is still correct and sum of its values for days.

    Scope([Scenario].[Scenario].[Forecast],

              {[Date].[Date].&[20030101]:NULL} ) ;

              This = [Scendario].[Scenario].[Actual];

    One of the problems you are going to run into by trying to define the aggregations for the Default Member of the Date dimension (ALL in your case) is this: What would you do if someone picked two days in a query in their WHERE clause or a Subcube. You are no longer at the Default level of the date dimension and your values will not come back because you are not at day level either(You will get your original NULL back.) You can test this in the cube browser by choosing days from the top filter area which uses SubCubes.

    The only way that I can think of to get this to work is to add a physical member. I will let you know if I can think of something else. Now in your Junk dimension, can you take one of the rows for the row for [Actual] and repeat all values in a new row and just change the Analysis Type to Recent Thinking? Since SSAS takes Distinct values, you are not adding anything new to other dimensions. Would this work?

    Shabnam

    Friday, June 15, 2012 2:53 AM
  • The WHERE clause issue makes sense, so I tried the physical member like you suggested:

    There are different records for Actual and Forecast (S&OP) - and a couple other types that don't factor in here.  I had thought a bit and realized I CAN add a physical member to my dimension.  While I have a junk dimension (there's a dimension table record for each combination of a variety of values), I also added a surrogate key and that's my actual relationship.  I went ahead and appended a blank record, surrogate key of -1, with an attribute value of "Latest Thinking".  That got me a dimension member of Latest Thinking when I browse the dimension that isn't relate to any fact records.  I think that's what you meant by physical member, right?

    Note, I set the attribute's "IsAggregatable" to be False (as you would never want to see an "All" analysis type).

    Then I did two scope statements with my hardcoded members, but it doesn't seem to have made a difference.  I'm just getting started with SCOPE though, so I wonder if I just did something incorrect.  Here's the entirety of my MDX to populate the Latest Thinking member with values:

    I don't have adventure works AS setup, but I am more than happy to pull it down and set that up if I need to.

    Thanks for bearing with me.

    Friday, June 15, 2012 3:35 PM
  • Oh, and my testing query and results:

    Friday, June 15, 2012 3:38 PM
  • Just to confirm, the aggregation function on [Billing Qty] is set to SUM, correct?

    Shabnam

    Friday, June 15, 2012 6:25 PM
  • It is:

    And, here's something else that I'm almost positive I had seen work before, this still returns NULL for all latest thinking and numbers for actuals.  This was an attempt to go completely back to basics and just load my Latest Thinking member with my Actual member values.

    Friday, June 15, 2012 6:41 PM
  • The syntax of your Scope statements is correct. Everything you have noted about your dimension seems correct to me. The last statement is also correct. I am not sure what is missing. Is there any other code that touches these measures in your MDX script? I would comment out as much as I can and see if that makes a difference.
    The other suggestion is to try this to debug:

    Scope( [Finance Information]...[Latest Thinking],[Measures].[Billing Qty])

               This = 100;

    End Scope;

    Set it to a fix number and see if it shows up. Then add a day in and see if it shows up for that day. I have a similar case running in AdeventureWorks and the above queries work.

    Shabnam

    Friday, June 15, 2012 7:17 PM
  • Well, this is interesting.  I wiped my entire calculate script except for this:

    Andt hese are the results.

    Could the foo calculated member be in the cube space but Latest Thinking not be there since there are no fact records related to the latest thinking dimension member?

    Friday, June 15, 2012 7:42 PM
  • Did you comment out the "Calculate;" command as well? You need that in there. This is in the very beginning of the script. If it is not there, none of the measures are aggreageted up. I suspect this is what happend. If yes, put it back and then test.

    Friday, June 15, 2012 8:45 PM
  • Nope, left CALCULATE at the top.
    Friday, June 15, 2012 8:58 PM
  • Based on what I know the two members you created should behave the same in the query you used. What version of SSAS are you using by the way? I am running these on SSAS 2008 (Katmai).
    Friday, June 15, 2012 9:14 PM
  • I'm using 2008 R2.  For clarity, to get the physical member this is what I did in my view that feeds the AS dimension:

    Then I processed the dimension, verified that my member started showing up when I browsed (previous screen shot), and other than that it's been CALCULATIONS in the cube definition - which is down to the CALCULATE; my two SCOPES and my CREATE MEMBER.

    I suspect I'm missing something silly because I'm too close to it, so I'll let it simmer over the weekend and take another look on Monday - will update when I find something.

    Friday, June 15, 2012 9:23 PM
  • I just tried a code very similar to yours and got numbers back from AdventureWorks. Something must be different between our environments. Are you able to download AdventureWorks and deploy it to your SSAS server? Then if you like I can give you a couple of small changes I made to simulate your problem.

    Here is what I have and it does return numbers:

    My query is exactly like yours, I just replaced the member names with mine and they both return numbers.

    I have used this method of having a member with a -1 key that does not connect to the fact table before on several projects to implement Time Series calculations. The only member that connects to the fact tables is the Current Time member, everything else has keys that don't go anywhere. Inside the cube script their values are defined like Last Month, etc. This is the same model of calculation as you are using. I am positive that this method works since I have used it several times in different projects. The question is what is missing in your case? Is it a setting on the dimension, a version difference, not sure?

    Friday, June 15, 2012 9:32 PM
  • Recreated your work in AdventureWorks and I'm seeing the same thing you are.  So it's not a server thing, I guess.  I think that leaves Database/Dimension/Cube/DSV differences.  So far I've found two things:  I didn't have a default member for my attribute even though it was set as not aggregatable.  Changed that, didn't fix anything.

    The other thing is that I don't see my new physical member when I browse the table from the DSV.  It's really weird, because it's there in SQL and it's there as a member when I browse the dim.  That stood out but I haven't been able to find anything different in the dsv - there's not a whole lot going on there.  Other than the fact that my attribute is related to the surrogate ID whereas the scenario guy is the actual ID.

    Monday, June 18, 2012 3:06 PM
  • Not sure about why you don't see the new physical member from the DSV. Check the connection string and make sure it goes to the same server/database that you are querying in SQL. I think when you browse the cube from VS, it uses the cube on the deployement server as specified in the deployment properties.

    The only thing I see different is that the Scenario dimension has only one attribute and your dimension has several. The part that I am not clear on is how a new calculated member relates to all other attributes. Although I have used this method on a dimension that had three attributes befgore, the physical member existed with every other attribute combination in the SQL table. So insetad of just one row with -1, there were several rows with fabricated keys. 

    I would try one of these things whichever is easier to test:

    1) Make a temporary dimension for [Analysis Type] in your database that has only one attribute for [Analysis Type] in it and add the physical member to that.

    2) In AdventureWorks, pick a dimension like Product that has multiple attributes and let's say define a new product with a fabricated key and overwrite its value. This should closer to what you are trying to do than the Scenario dimension. Although Product is an additive dimension.

    I'll try the second approach later today and let you know what I find.

    Monday, June 18, 2012 3:30 PM
  • So I recreated a brand new database with a new cube and a new dimension that use the exact same datasource tables.  It works!  I've also identified that it's the dimension that's the problem (and not the cube) by creating a new cube to use the old dimension and I still see the issue.

    I'm trying to identify the differences between my old dimension and the new one (that looks identical).  So far the only thing I've found is that the Name column was explicitly defined (it could have been left as default like I did with the new guy).

    Will update again when I know more :)

    Monday, June 18, 2012 8:22 PM
  • Progress!

    The dimension had another attribute that had an explicit default member (this particular attribute only has two possible values).  My -1 physical member wasn't using 1 of those 2 values - net result is that my physical member didn't have a tuple with the default member.  Once I updated my one-off physical member to have the default value of this other attribute it fell into place.

    Now tomorrow I can get back to the original problem, but I think that'll go smoothly now.  Will update with the final solution tomorrow, hopefully.

    Monday, June 18, 2012 9:11 PM
  • Makes sense! When I had three attributes in a similar dimension, they all had their default members set to a specific member and I had all the combinations of the new members and the default ones in the table.

    Monday, June 18, 2012 9:22 PM
  • Got it.  I SCOPED at the date level so it got a bit complicated, but now every date + latest thinking uses the correct analysis type.  I had to create 2 physical members, like you said, one for my default member on the other attribute and one for the other option of that attribute.  You were a HUGE help, thanks so much for your time.  Not sure I would have ever gotten it without your expertise and opportunity to bounce ideas around.

    Tuesday, June 19, 2012 1:23 PM
  • Great! I am glad I was able to help. Thanks for sharing the final solution and thanks for your feedback.

    Shabnam

    Wednesday, June 20, 2012 1:17 AM