locked
ProClarity Standard Loses Connection to the Cube RRS feed

  • Question

  • I'm not a "guru" in the various technologies that ProClarity uses to connect to the cube to display the views I have published.  However, I have the following issue:

    1. When I connect to the ProClarity Standard web site, the views load as expected.  The user I connect as is an Admin on that domain.
    2. After I connect other non-admin users can access their views without any issues.
    3. After awhile though the non-admin users can no longer see the views.  The get either:
      • Cube can not be found
      • Request can not be completed.
    4. I can then log in as the admin user, launch a view (any view these views are not cached I'm fairly certain) and I see it.
    5. I can then clear my local cache, launch my browser, log back in as the non-admin user and the reports work for a period of time.  Then stop working.

    These views are delivered over the web.  My machine is not on the domain of the web server nor is my user name or password the same on my local machine as it is on the remote domain.  I log into the system through the pop up box (DOMAIN\username and password).  Of course our testing never found this because we were either logging in as Administrators outright or logging in as users right after we logged in as administrators.

    This indicates to me that some sort of connection is established by Admin users that non-admin users can't.  I know this is all very technical and such, but I think it is an accurate definition of the problem.  My security setup for the web site is Basic Authentication (I'm relaying on the SSL encryption to protect the u/n and p/w which is, from what I understand, an OK solution).  Anonymous access is disabled.  I would think that the user has rights to the cube since once the connection is established they can access all the views regardless of the cached state.

    Also, my DefaultApp Pool Properties are set to recycle the worker processes every 1740 minutes (the connection times out much sooner than that and I don't think this is the issue).  Under the Perfomance tab it says to Shutdown Worker processes after being idle for 20 minutes (this sounds like the right timeframe).  Finally, under the identity tab it is predefined as a Network Service.

    Thank you for reading my long post.  I look forward to what I hope is a straightforward solution as my customers are getting somewhat fed up.   

    Wednesday, March 25, 2009 8:37 PM

Answers

  • OK, I found the problem.  Since we host many client's cubes there is a security role for each client that allows a group of Windows users (the client's group) access to their cube.  The idea being that if you don't have rights to the cube, you can't look at the other customer's cube.  Of course that cube had the wrong user group associated with it.  Why exactly they could see if after an Admin logged in is a bit beyond me, but maybe the cach recreated all the views so the user's never had to go to the cube?  In any case, associating the right user group to the role in the cube fixed this issue.
    • Marked as answer by desertpanda Tuesday, March 31, 2009 7:56 PM
    Tuesday, March 31, 2009 7:56 PM

All replies

  • What it sounds like is that you're logging in with "admin" users, loading the PAS cache, and then other users are able to see the views out of the PAS cache without having to go to the cube for the information.  However, once those PAS cache views expire and the users are forced to go to the cube for the data, they are denied.

    Let's try this as a test, with two views accessing the same cube:

    1.  Clear the PAS cache from the admin tool (right-click server node and choose to clear cache).
    2.  Login with an admin user and open "View A".
    3.  Login with a non-admin user and open "View B" (a different view than was accessed by the admin user).  Does this work?
    4.  With the non-admin user, open "View A".  Does this work?

    Couple of questions as well.  Are the customers logging into PAS with basic as well?  Is the cube on the same machine as the web server/PAS?
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 26, 2009 10:34 PM
  • Ben,

    Thanks for the response.  OK, so, following your directions I did the following:

    1.  Cleared the cache on Analytics Server Admin tool.  This gave no indicatin that it was done I don't think, but I'm pretty sure I cleared it.
    2.  Logged in as admin user and opened Total Monthly Visits view.  This worked.
    3.   Logged in as a non-admin user and opened Total Monthly Vists by Product and Month over Month % Visit Growth views. Both loaded correctly.
    4.  As that non-admin user I was able to open Total Monthly Visits.  This worked.

    To your questions:

    1.  I don't know what you mean logging into PAS with basic as well?  If you are asking if the PAS Server is within the domain, no.  This is a hosted solution by us that is delivered to our customers over the web.
    2.  The cube is on it's own SQL Server.  It gets rebuilt every night -- hence why I'm pretty sure in the early morning when my customer logs in the PAS Cache isn't being used since they are trying to look at yesterday's data and I can open any view (even on a completely different book I think) to enable them to get access to the cube for the view to be built.  You can also see that it isn't using the cache because the views they are querying are taking quite a bit longer the first time which I my completely scientific and objective way of determining cache usage :).

    Anyway, I open to other suggestions.  Do you think the Shutdown Worker processes is the culprit?

    Thanks.
    Friday, March 27, 2009 11:13 PM
  • OK, so I went back and did a somewhat different test:

    1. Cleared the cache on the server.
    2. Logged in as an admin user.  Open View A.
    3. Logged in as non-admin user and Opened View A, B, C.  It didn't matter the all worked.
    4. Then cleared the cache while I was logged in.
    5. Got the cube can't be found error.

    So, you are right (I mean, of course you are!  You are Ben Scott already! :)), it is something to do with this cache.  I looked in the Analytics server help file and the references to cache are pretty slim.  There is a reference to a TimeStamps table and how it is used to managed timed clearing of the cache.  I am guessing that the security context of a non-admin role and the fact that they don't have access to this database is immaterial, but I will note it here JIC.

    Look forward to any suggestions/ideas you may have.

    Friday, March 27, 2009 11:30 PM
  • Also, one other note in the similar vein of the PAS database.  It resides in a different instance than the cube for this customer.  So you have SQL2K5 RDBMS & SSAS on Instance 1 with the PAS database and our biggest customer and SQL2k5 RDBMS and SSAS on Instnace 2 with all other customers.  Thanks for your reply.
    • Marked as answer by desertpanda Tuesday, March 31, 2009 6:53 PM
    • Unmarked as answer by desertpanda Tuesday, March 31, 2009 6:53 PM
    Monday, March 30, 2009 6:43 PM
  • OK, I found the problem.  Since we host many client's cubes there is a security role for each client that allows a group of Windows users (the client's group) access to their cube.  The idea being that if you don't have rights to the cube, you can't look at the other customer's cube.  Of course that cube had the wrong user group associated with it.  Why exactly they could see if after an Admin logged in is a bit beyond me, but maybe the cach recreated all the views so the user's never had to go to the cube?  In any case, associating the right user group to the role in the cube fixed this issue.
    • Marked as answer by desertpanda Tuesday, March 31, 2009 7:56 PM
    Tuesday, March 31, 2009 7:56 PM
  • I'm glad to hear you found a solution, but I'm not sure I completely understand what you were seeing.  Cache files are generated on a per view basis, and of course the initial user into that view pays the price of the cold cache, runs the query against the cube, generates the image files, etc.  The next user in has the option of using the cached files for that view, but only if the Analysis Services role list matches the role list of the user who generated the cache files.  If the role list does not match, the new user could see data they should not be seeing, and so the user is forced to run the query against Analysis Services so that cube security can be enforced.  So, on the surface I'm not sure why having an admin user open a view caused non-admin users to be to able to see all the views, but we'd probably have to have a closer look at your setup to determine that, and since you've got it working now I'm sure you don't want to carry on with research.  However, if you do, the next step would be to take a look at the security setup on the cube.  Screenshots would probably be the best way to go about that so that we could see the roles and the group added to the roles, and identify which users are in which groups.
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 31, 2009 11:14 PM
  • Ben,

    You take the time to answer a lot of questions, I'm more than happy to dig into this a little deeper. I'm having some technical issues uploading pictures (as well as some privacy concerns), so I'll do my best to describe this verbally.  The setup is like this:

    1.  We have about 20 customers.  Each customer has two Groups, Professional Users (with the rights to get on ProClarity Pro and create views) and Standard Users (which, as you can imagine, have rights only to the views).
    2.  We have created libaries for each customer and published them giving the 2 user groups access to those libaries.  The hope is that Pro users will also then create their own views and publish them in their own libraries.
    3.  In an attempt to secure access to only the cube that each customer has for their own use -- and not try to access other customer's cubes -- we created a role called Read Only Users.  The membership of this Role is the Professional User group for each customer as mentioned in #1 above.

    4.  Other information regarding this role:

    a.  General: Role name and Read Definition is checked off.
    b.  Data Sources:  the name of hte data source with access None and a grayed check box for read definition.
    c.  Cubes:  The proper cube in question, Acess: Read, Local Cube/Drillthrough: None, Process: Unchecked.
    d.  Cell Data:  No restrictions.
    e.  Dimenstions:  al DB dimenstions selected in teh dimension set with all set to Access:Read, Read Definition: grayed checkbox, process unchecked.
    f.   Dimension Data:  Everything is select (sidebar: i'm going to have to ask what this is under a different post because I think I need to use it).
    g.  Mining Structures:  nothing.

    I think that's it.  In essence what happened was that the Customer A was in the Pro Users Group and it had access to the Libraries properly, but a differnet Customer B's Pro User Group was assigned membership into the cube role mentioned above. 

    I'm more than happy to provide any other info you think can help you track down this behavior which was somewhat strange.
    Thanks for your help.

    Wednesday, April 1, 2009 1:02 AM
  • I'm still not sure I completely understand why you were seeing the behavior you were seeing, but I think at this point we'd need to get pretty involved to determine how those users were gaining access to the cube only after a single view had been opened successfully via the Standard client, but by a user who would not have a matching role membership list from SSAS.  From what you describe, the users should neither have been getting the views out of the PAS cache, nor should they have been able to access data from the cube, but again, why that was happening I cannot say without spending some dedicated time looking at your environment.  Thanks for the follow-up, but it would appear this will need to remain a mystery at this point.
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, April 8, 2009 11:54 PM
  • You are probably right.  Mystery it shall remain for now.  What is important is that as far as I can tell a Pro User can't access another user's cube -- I've tried and I know the architecture and I can't.  What is also important is that your help definately helped pin this down.  I know I should have checked it, but sometimes when someone does a task for me 100 times and it is correct 100 times I figure the 101st time will be right too.  I figured wrong again, but I'll still play the odds!

    Thank you very much for your time and attention to this.  It is much appreciated and it is great to see the Pro Clarity community alive and well.
    Thursday, April 9, 2009 1:53 AM