IntroductionArrrgh, this one really got me going. Actually, I have noticed that I have never written a query against the Planning tables unless I am unable to get whatever it is out of Planning easily. And I suppose that sort of makes sense. And I also that means I am always annoyed and that missing Planning reporting features are my opportunity to increase my SQL skills, such as they are. Of course writing the query took way longer than I thought it would. Read on for the reason...
With that preamble, have you ever wondered what security is assigned to Calc Mgr rules in Planning? You basically have to go into each rule and edit the security to see what user or group has been assigned and what rights are set. Annoying, isn't it? And not practical when there are many rules. Here's an example of what it looks like:
I can see that the group PLN_CalcTest_Consol has Launch access to the rule AggAll, but what about AggPlan, CalcRev, etc., etc., etc.?
So yes, this is yet another opportunity to query the tables. And oh yes, I am using this as a teeny part of the Planning presentation I am giving with Jessica Cordova (hi, Jessica) at Kscope13.
NB – One other note, I was inspired to get around to this query in response to “vaio” and his Hyperion Business Rules security query from this Network54 thread: http://www.network54.com/Forum/58296/thread/1362011042/Export+only+Webform+security I figured if world+dog had it for EAS’ business rules, we needed it for Calc Mgr as CM is all there is from 184.108.40.206 onwards.
The reason this query drove me up the wallWould you believe that deployed Calculation Manager rules do NOT have an object type in Planning? Would you believe that I spent more than a little bit of time trying to find it?
Oh yes, both statements are true. The latter one you are going to have to take on trust. The former I can prove.
Here are the Object Types in the Planning app schema:
|User Defined Dimension Member|
And here is the OBJECT_TYPE that goes with Calc Mgr rules:
OBJECT_TYPE = '115'
See the 115? I only know that because I did a search on the name of one of the rules and thus figured it out.
Why would you care about OBJECT_TYPE 115?Well, once you (or I) knew this, you (or I) could write this:
Purpose: Calculation Manager security report by rule, group, and user
Modified: 1 Feb 2013
Notes: Common Table Expressions make joining mostly disparate objects
NB -- Calc Mgr rules do NOT have an OBJECT_TYPE in HSP_OBJECT.
The OBJECT_TYPE seems to be 115.
-- I am in love with CTEs over subqueries
-- CTE for Calc Mgr OBJECT_ID, Plan Type, and Name
BRName (CMID,PlanType, BRName) AS
HSP_OBJECT O ON O.OBJECT_ID = CMR.ID
-- CTE for Calc Mgr user OBJECT_ID, Calc Mgr OBJECT_ID, and Launch rights
BRAccess (UserID, CMID, Launch) AS
AC.USER_ID AS 'User ID',
O.OBJECT_ID AS 'CM Obj ID',
--O.OBJECT_NAME 'CM Name',
WHEN -1 THEN 'No Launch'
WHEN 4 THEN 'Launch'
END AS 'Access'
HSP_OBJECT O ON O.OBJECT_ID = AC.OBJECT_ID
O.OBJECT_TYPE = '115'
-- CTE for user OBJECT_ID, user name, group name
UsersInGroups (UserID, [User Name], [Group Name]) AS
--O.OBJECT_ID AS 'User ID',
O2.OBJECT_ID AS 'CM Obj ID',
O.OBJECT_NAME AS 'User Name',
O2.OBJECT_NAME AS 'Group Name'
HSP_OBJECT O ON O.OBJECT_ID = U.USER_ID
HSP_USERSINGROUP UG ON UG.USER_ID = U.USER_ID
HSP_OBJECT O2 ON O2.OBJECT_ID = UG.GROUP_ID
BRN.BRName AS 'Calc Mgr rule',
BRN.PlanType AS 'Plan Type',
UIG.[User Name] AS 'User name' ,
UIG.[Group Name] AS 'Group name'
UsersInGroups UIG ON UIG.UserID = BRA.UserID
BRName BRN ON BRN.CMID = BRA.CMID
ORDER BY BRN.BRName, UIG.[Group Name], UIG.[User Name]
And then you (or I) could run the above query, and get the following:
|Calc Mgr rule||Plan Type||Launch||User name||Group name|
Isn't that pretty? And useful? I (or you) think so.
The sting in the tailWould you believe I spent a good half hour poking around in the Calculation Manager tables?
Would you believe that the CALCMGROBJECTACCESS table in the Calc Manager schema is completely empty? I have no idea what it is for, but it isn't for rules deployed to Planning. Terrific.
But the good news is that with a little poking about writing a simple (well, CTEs aren’t totally beginner’s stuff but they are so easy to read and work with) query, you (or I) can easily look at who and what kind of access planners and groups have to deployed CM business rules.
Be seeing you.