locked
Assignments hanging when filter changed RRS feed

  • Question

  • Hi. We have been having this problem for the last 4-5 months and I asked a question here (in the old forum) in October I think but did not get any satisfying answer.

    Background:

    Our setup is two servers. One with PPS planning and one with SQL Server Enterprise where the pps databases and cubes are (amongst other db and cubes).

    We have financial model without shares with 8 dimensions (Account, entity, product, time, scenario, businessprocess, product identifier and timedataview)
    In the entity dimension there are 53 members, 512 in the account dimension, 98 in the product dimension and 160 in the product identifier dimension.

    We have one form for the financial department with the following:

    On columns: Time (12 months) and budget (scenario)
    On rows:       Account (label and name), product (label and name), product identifier (name)
    Filter:            Entity, businessprocess on input and timedateview on periodic, both hidden  

    The form suppresses empty rows and is filled out with last years numbers which the user then needs to alter.
     
    There are no macros or formulas in the form and the model does not contain any rules, associations or jobs.

    The cycle we created lasted for 3 months and there are 25 people planning in the whole with 7 of able to see many entities. All other users can only access their own entity and therefore the problem does not happen for them.

    The problem:

    When a user in the finance department gets his/hers assignment they open it up and start budgeting. They might change entity before they start entering numbers as they have unlimited access and need to budget for many entities. They then submit a draft. After they submit a draft the problem starts. Every time after that excel hangs on executing query if they try to change entity. Sometimes it has worked to clear the local cache but usually not. If they close down excel and clear the cache they can open the assignment again but when they try to change entity it hangs again. The only thing that has worked for us is purging the assignment and creating a new assignment for the person. Then everything is back to normal until the person decides to submit draft and then changes the entity filter. It does not matter if they close Excel between or not. Automatically cache assignments is not on.

    Just to make it clear. When the user opens the assignment after we have recreated it for them, they can change entities at will, but as soon as they have submitted draft the problem starts.

    I hope someone can assist with this problem as it is very inconvenient. We can manage as we can always purge the assignment and create them again, but when a person from the finance department has to alter the budget for 10 entities in a day, it gets very frustrating for all parties involved.

    Best regard,

    Ásgeir Gunnarsson

    Ásgeir Gunnarsson
    Monday, January 5, 2009 12:58 PM

Answers

  • Also you should change the setting in the form properties to true for clear changes after workflow.  It looks like the change list is growing too large.  This will improve the performance of your forms considerably.  If you are not using design time formulas in the form then set the settings to false for inherit design time formulas and capture design time formulas.  After you make these changes you need to re-publish the form using the same name and it will update the assignments.

    Thanks,

    Aseem Nayar - MSFT

     


    This posting is provided "AS IS" with no warranties, and confers no rights
    Thursday, March 19, 2009 7:53 PM
    Moderator

All replies

  • Yes I remember this query and I'm afraid you'll get a similar answer. We need to know if the mdx queries are the bottleneck or if there is a problem in the addin. When the hang happens after changing the filter how long do the mdx queries take on the server? There should be an "update" and a "select" and if you are using SP1 or later a 2nd "select". How does that time compare to the refresh time in the addin?

    -Peter Eb.
    • Marked as answer by AseemNModerator Wednesday, January 14, 2009 8:23 PM
    • Unmarked as answer by BidgeirMVP Wednesday, February 18, 2009 4:24 PM
    Monday, January 5, 2009 6:10 PM
    Moderator
  • Hi Peter. I’ve been away so therefore I'm answering now. I really don't understand your suggestion. How do I compare the query time with the refresh time in the addin?
    Can you explain to me the steps I need to take to be able to grab the query to run it on the server? You say there should be an "update" in the query, but is that not only in the submitting process that happens? Or do I not understand it?

    The problem only happens to assignments that have the status partial. It’s not during submit draft the problem happens, but every attempt after the submit draft it hangs. How can that you previously submitted a draft influence the query time?

    If the assignment has a status of started it takes no time to change the entity in the filter. If the assignment has the status of partial, it takes about 8 minutes on my machine to change the filter.

    If I purge the assignment and make a new one, it takes no time again.

    I just cannot understand how come the assignment status and the fact that it has been submitted as a draft has anything to do with the query time.

    Regards,

    Ásgeir Gunnarsson


    Ásgeir Gunnarsson
    Wednesday, February 18, 2009 4:24 PM
  • Well, I haven't lokked too deep into the problem but just to reduce the amount of frustration your users have to go through, you can ask the users to keep making changes to filters and adding numbers in the form BUT WITHOUT Submitting the data back. Once they are done making all the changes to data in the input form, they can do a single Submit Draft and al their changes will be pushed to database in one single go. They won't have to perform multiple Submits to get their data pushed into the DB.

    Note: If the data being input is huge, errors might happen while submitting the data. If this happens, users can break their submissions down to 2 or 3, and submit data for each of these breakups one by one. However, this will bring you back to square one where you have to create assignments 2-3 times for each submit. 


    Thursday, March 19, 2009 2:24 PM
  • Thanks for the tip Muqadder. I have actually tried that and it seems to be working to extend, especially after the users start to trust the product more. In the beginning everybody submitted all the time because they were afraid the data would be lost.

    In the economical climate we are experiencing it is hard to plan far ahead and the budget were frequently updated in the 2 months we were budgeting, so I had problems keeping the users happy when they were constantly stuck. But now it ok, atleast until the fall when we start another cycle, as the forecasts are more easy to deal with.
    Thursday, March 19, 2009 4:24 PM
  • Also you should change the setting in the form properties to true for clear changes after workflow.  It looks like the change list is growing too large.  This will improve the performance of your forms considerably.  If you are not using design time formulas in the form then set the settings to false for inherit design time formulas and capture design time formulas.  After you make these changes you need to re-publish the form using the same name and it will update the assignments.

    Thanks,

    Aseem Nayar - MSFT

     


    This posting is provided "AS IS" with no warranties, and confers no rights
    Thursday, March 19, 2009 7:53 PM
    Moderator
  • Thanks for the tip Aseem. I will try that. The only thing that I dont see is the "Clear changes after workflow" option. Is that something that came with sp2? We have not implemeted that, only sp1.
    Ásgeir Gunnarsson
    Friday, March 20, 2009 2:31 PM
  • No it should be there at SP1.  Where are you looking?  Go into the Properties of the Form and Click on Workbook it should there.  If it isn't then this form has not been upgraded to SP1.


    Aseem Nayar - MSFT
    This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, March 20, 2009 4:00 PM
    Moderator
  • Thanks again. I looked in the wrong place
    Monday, April 20, 2009 9:52 AM