User security and cubeview performance

[dbeavon]

I am trying to troubleshoot a performance issue when running cubeviews as a non-admin.

If a user is not in the "Administrators" group, then a simple cubeview might take a lot longer than it would otherwise (by a large factor, like 3 to 5.) 

Is this a known issue?  Are there any tricks to avoid the massive performance hit?

[NicoleBruno]

Hello! 

I haven't experienced this at all and I haven't received complaints from users either. Are they simple CVs? Is there something in the CVs that only admins are able to view compared to users? I honestly wouldn't even know where to begin but just wanted to chime in that this isn't a known issue as far as I'm aware (for reference, we've been live on OS since 2017). 

-Nicole

[TonyToniTone]

Hello dbeavon,

In a vacuum, Cube View rendering times should not have any impact based on roles such as Admin and Non-Admin.  Cube View rendering is impacted by many things ( Cube View design, data volumes, high traffic server activity, complex security model, etc. ).  This being said, I offer a couple possible explanations for why you may be seeing a difference between Administrators and Non-Admins.

1.  Cube View execution happening on different General Application Servers

• In the Azure environment, when a Cube View is executed, the Cube View execution request is assigned to an Application Server that is assigned for General Access.  When a Cube View executes, it will consume 1 CPU on the General Application Server until the Cube View completes the execution.  If you have 2 Application Servers marked as General, you could have a situation that when you execute your Cube View as an Admin, you execute on Gen Server 1.  Others may have been assigned to Gen Server 2.  Depending on the task activity on the Gen Server 2, it could impact Cube View rendering performance.  For troubleshooting purposes, have you checked the Task Activity log to see which Cube Views are executing on which Application Servers?  Then identify if there is a difference in servers between when you execute as an Admin vs. Non-Admins?  Meaning do you have a traffic logjam in which Cube View queueing is happening or is it potential hardware limitations on a specific server.  

2.  Complex Security Model for Non-Admins

• As an Administrator, when you execute a Cube View, the system knows not to check security for the data as an Administrator gets access to virtually everything in OneStream.  When you are a non-Admin, there is going to be security checks in order to return the authorized set of data for the user.  So if you have unique security groups set on every OneStream object, in addition to Read access to Entity data, and finally have slice security to only a subset of data within the Entity, then you have a lot of checkpoints for security to get through before the final results of data are shown in a Cube View.  

If you continue to have questions, I would suggest submitting a ticket and discussing with OneStream Technical Support.  Not sure if your inquiry is technical or application related in nature.  OneStream Support expertise can help out here.  

[dbeavon]

Thanks for the feedback. I'm pretty certain the issue is with #2.  It is a consistent slowness, rather than something that may come up as a result of being on the wrong server.

My understanding is that there was another project within my company where this issue was encountered as well.  I don't have the details.  They are out of the office at the moment, but I think they said it was a well-known issue.  I was hoping to find an explanation and a work-around here in the forums.  But google is not my friend right now.

You have a lot of checkpoints for security to get through before the final results of data are shown in a Cube View.  

Yes it sounds like a complex problem.  Is there any elevated logging or diagnostics that might help troubleshoot something like this?  Should we run SQL profiler and look for certain types of queries against the security tables of the database?  Is there any way for onestream itself to track & measure the proportion of time that is spent on security calculations?  Are there any onestream perfmon counters on your middle tier?

I'm a developer and I don't have direct access to the onestream servers or these databases .  But I think it would help to have a discussion about the general concepts that we would need to understand before we started troubleshooting.  And perhaps you could point us to the tools that you would use to start digging into that issue #2.  I suspect that the platform team at my company could begin the process of investigating/troubleshooting, and then open a case with you if/when things get overly technical.

This is a valuable discussion for the community.  Since this came up in independent projects at my company, then other customers will probably encounter it as well.  My experience with performance issues is that the are never "solved" (except perhaps if you made everyone an admin).  Rather than hoping for the perfect solution, I was only looking for a community discussion of the tools and techniques to help with the optimization of our security groups.

[ChristianW]

Sliced security should only be used if needed. That's the reason, you can control it by entity.

Another possibility is error messages. They can consume a lot of time.

If you have some row and column calculations that are creating error messages, it can slow down the cubeview a lot, e.g. when the cubeview tries to multiply "No Access" with 1.5. Then it writes an error message to every cell of the cubeview where the error occurs. But if you have suppress enable, the cubeview doesn't even show the error messages. But the cubeview is slow anyway.

[dbeavon]

My cubeviews aren't generating errors in the cells. 

They are consuming a massive amount of memory on the app servers, however.  Perhaps thrashing is slowing things down, but I would think that affects each user the same (whether admin or not).

The cubeviews are executed indirectly via REST->DashboardAdapter->BR->FDX