locked
Assignment hanging when filter changed RRS feed

  • Question

  • I have a problem that I need help with.

    We have a form that has amongst other things entity as a filter. A user chooses a entity, makes some changes and submits draft. Then he chooses another entity from the filter and then the assignment hangs on executing query . We have waited for couple of hours and it did not finish. He has tried to close down excel and open it up again between changing entities but that did not work either.

    Can someone please help. We are in the middle of budgeting and this is causing a lot of negativity amongst our users.

    I would include a screen shot of were it stops, but a cant insert it

    Ásgeir Gunnarsson
    Wednesday, September 24, 2008 8:20 AM

Answers

  • You can use sql profiler to connect to the AS cube. (if the offline cube is enabled - which it is by default for the addin - you'll have to connect to the .cub file)

    If you are running sp1 and you see 2 mdx queries being executed and the 2nd one is the query that takes a long time. (this usually happens with a lot of rules defined in the model) This is a problem we are trying to address in sp2. The workaround for olap experts in the meantime is to set default members on the dimensions to something that is not referenced by so many rules.


    The cube refresh time is more complicated than just 1 number in the admin console. You can find some information about this in a post of mine
    Wednesday, September 24, 2008 9:30 PM
    Moderator

All replies

  • I forgot one thing. To resolve the problem, I need to purge the assignment and assign the form again.

    Ásgeir

    UPDATE: The form hangs even though the user does not change anything in the filter. If the user submits draft and then tries to open it again it hangs. It seems that everybody is experiencing the problem after they submit a draft.

    Everybody uses the same form but have access to different entities. Can the problem be in the form?

    Another problem is that when a user changes the attribute "hide empty rows" to false it hangs for a long time in "Executing query" before giving the error "Error when executing MDX query". Some users need to do that as they need to use an account that has no values to it currently.
    Wednesday, September 24, 2008 8:30 AM
  • Have you logged the queries on the server side to see why the query isn't returning quickly?

    After each of the scenarios you've described I believe a refresh is happening so it will be very sensitive to the performance of the query. Especially if you have a large number of current changes the queries can slow down significantly from the initial state of having no entries.
    Wednesday, September 24, 2008 8:15 PM
    Moderator
  • Thanks for your reply Peter.

     

    No we have not logged the queries on the server. How do we go about doing that?

     

    We have tried a scenario where a user changes one number, submits draft and then tries to change the filter. Also another that changed one number, submitted draft, closed down Excel and opened the assignment again. In all these cases the assignment hangs and does not deliver anything no matter how long we wait. We have waited for couple of hours once. If the user presses the stop button it will stop, but when he tries to close down Excel it takes really long time and usually has to be closed by killing the Excel process. In every attempt after that to access the assignment it hangs. The only way to get the user back on track is to purge his assignment and make a new one.

     

    The leader of our finance department has to make budgets for several support units and is getting pretty tired on doing it for one unit, submitting draft and then having me make him a new assignment so he can start the next one. He likes to submit draft after each one, so another employee can look at the SSRS report we have made and come with their view, and he should be able to submit draft as often as he want right?

     

    Is it not right that the refresh time is only the time we have set in the Admin Console? We have waited beyond that and it still happens. And after it happens once it happens alway until I purge the assignments

     

    Ásgeir Gunnarsson
    Wednesday, September 24, 2008 8:34 PM
  • You can use sql profiler to connect to the AS cube. (if the offline cube is enabled - which it is by default for the addin - you'll have to connect to the .cub file)

    If you are running sp1 and you see 2 mdx queries being executed and the 2nd one is the query that takes a long time. (this usually happens with a lot of rules defined in the model) This is a problem we are trying to address in sp2. The workaround for olap experts in the meantime is to set default members on the dimensions to something that is not referenced by so many rules.


    The cube refresh time is more complicated than just 1 number in the admin console. You can find some information about this in a post of mine
    Wednesday, September 24, 2008 9:30 PM
    Moderator
  • Thanks for the answers Peter.

     

    I will look into it tomorrow when I get back to work (its evening here in Iceland now).

     

    Ásgeir

    Wednesday, September 24, 2008 9:57 PM
  • I have made some further experiments and it seems that if I have submitted a draft, it will take about 15 min. to run the query every time I connect again. It does finish (contrary to what my users told me) and it works fine.

     

    Is it normal that after you submit a draft the query time goes up like that? The users are not going to be happy about having to wait 15 min. or more every time they open their assignments if they have submitted a draft previously.

     

    I have no experience with sql profiler so I am not sure what events to trace and I don’t know how to connect to local cube. A little advise there would be helpful if possible.

     

    We have no rules defined in the model (which is a financial model without shares) so that’s not the problem, and when I did my experiment I was the only one working on the planning server. So I don’t think it has something to do with overload. I also waited to see that the data was already in the cube (we have reports) before I tried to open it again but there was still the 15 min. waiting

     

    I have the option "automatically cache assignments locally" turned off. Is the query running on local hardware and therefore it depends on the computer the query is running on how fast it is? I see that when I start the assignment the Excel process uses 50 % of my computers CPU but does not go over that. It stays on 50% for the duration of the query.

     

    Hope to get some help :-)

     

    Ásgeir Gunnarsson

    Thursday, September 25, 2008 8:44 AM
  • Update on the problem.

     

    If I erase the Offline cache located in C:\Documents and Settings\USERNAME\Local Settings\Application Data\Microsoft\PerformancePoint\OfflineCache then the query time is back to its normal few seconds, but if I submit partial again, I need to erase the offline cache again otherwise the query time is back to 15 min. I have the option "automatically cache assignments locally" turned off and a new local cube is not made, however I need to erase all the .dat files to get it to run in normal time.

     

    Does anyone know how to address the problem? 

     

    Ásgeir

     

    Thursday, September 25, 2008 10:20 AM
  • With the automatically cache assignments locally turned off, only the local cube is not created locally. The other thing stored in the cache are just metadata about the assignment and the current changes. Probably the what-if query is what is taking the most time.

    (in sql profiler i connect to the as server and turn off all events except query start and query end. probably the update query takes some time, and possibly the select query after that. But if the current changes are empty then the select query will be fast)

    After submission then you'll probably want your users to use the "clear current changes" option to wipe their current changes from their cache. In SP1 there is also a report option to do this automatically but you'll want to make sure that there is no danger of submission failure otherwise the users would lose their work.


    Thursday, September 25, 2008 5:12 PM
    Moderator
  •  

    Thanks for your answers again Peter. I will try out your suggestions tomorrow.

     

    Is it normal behavior that after submitting draft you need to clear the cache to not have to wait for 15 min. the next time you open the assignment? It does not sound normal to me. We have a very simple model with no rules and nothing fancy. Only one form and usually never more than 2-3 people working at the same time.

     

    Ásgeir

    Thursday, September 25, 2008 5:52 PM
  • No its not normal. There may be time between the submission and when the data is propagated to the cube. But you shouldn't have 15 minutes of wait time when refreshing an assignment. Do you have a good bandwidth connection from the users machine to the server?
    Thursday, September 25, 2008 8:17 PM
    Moderator
  •  

    Yes we have excellent connection to the server, and as I said before the delay stays until the local cache is cleard and is then gone until the assignment is submitted as draft the next time. I suspect there must be something wrong with our setup. I will now go back to developement and create a new setup and see if I can figure out what is wrong.

     

    Thanks alot for your answers Peter, they have helped me figure out the problem, even though I have not been able to solve it.

     

    Ásgeir Gunnarsson

    Thursday, September 25, 2008 10:08 PM