April 27, 2010 at 10:53 am
Dear all, I am afraid my "challenge" may not be simple...
In our project, we want to use SSAS Stored Procedures to implement dimension member level security.
The design is relatively simple in that we have a set of SQL Server tables holding a link between the Dimension Member Business Keys a User has access to and his name.
Our SSAS Stored Procedure just calls a SQL Server stored procedure to return the list of dimension members allowed.
I have suspicions about the performance aspect but it is not my choice, nor my immediate challenge.
I am requested to hosts several test environments under the same server and I want to have only generic code in my SSAS Stored Procedure.
Here is the Challenge
How could I inform my SSAS Stored Procedure about the environment it is currently running in, without passing it such a parameter because then my cube itself would not be generic...?
All I need is a word like "Dev", "Test1", "Test2", etc... but how can I get this through to a generic static method?
Is there a way to start a piece of SSAS Stored Procedure at "startup time" for the SSAS instance?
Something a bit like a global.asax in ASP.Net?
I warned you it was not that simple...
Eric
PS: I hope there is not yet another simple/obvious answer that will make me feel stupid again...
I just don't get used to it!
April 27, 2010 at 11:18 am
Not saying the approach is good/correct, but to answer the question asked.... Why not include a hidden dimension that has only 1 Attribute Hierarchy, and within that, only a single value. For any given cube, you load that value from your source DB with either DEV, PROD or QA. Yes, you're passing it as part of the call to the sproc, but it does mean all cubes are 'generic' in that they will all contain the same structure, simply the data loaded to that item will be different per environment.
Steve.
April 27, 2010 at 3:17 pm
Nice!
Yes, I like the idea. It would do exactly what I need I think.
Thanks
April 27, 2010 at 3:20 pm
Actually, if you think the approach is not "correct", I am interested to hear about it.
Regarding the Dimension Security, it was not my choice and I tried to dissuade against it but obviously failed...
However, I am always interested to learn!
April 28, 2010 at 2:45 am
Yes, I think it would affect us and, as per the article, we could clear the cache if needs be.
Thanks for the link
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply