27 January 2014

Going back to the beginning

Is time travel possible?

Alas and alack, no.

Although I have searched for just such a device, to my knowledge there is no WayBack Machine that can take me back to 1992.  Yes, that long distant time from the 20th century before the Global War on Terror, before the Internet (at the time I had an AppleLink account which gave me email  to someone actually outside of my Fortune 50 employer as email used to stop at the company network’s end – it was a different time), a time when I did OLAP on an IBM mainframe using Comshare’s System W, and a time when all of the hair on my head was 100% devoid of grey.  

22 years is a fair clip ago when it comes to personal history and it is an eternity in the technology field.

And yet there has been in my professional life a constant since that time (for me it started in 1993 or 1994 but give me artistic license).

What oh what oh what could it be?

Note to young people (like, oh, under the age of 30) – once upon a time, people (honestly, even the women had to wear ties of a sort)  in corporate America were expected to wear a suit and tie (all the time – to the bathroom, to lunch in the cafeteria, on the way out the door – always), took notes on paper (actually, I still do), had 3270 terminals at their desks (nope, don’t have one of those, thankfully), etc.  In other words, “The past is a foreign country:  they do things differently there.

With that preamble, what might be the one constant from early in the Clinton administration?

No, not suits – those are gone except for weddings and funerals.

No, not ties – see the above.

No, not rolodexes – they were obsoleted by smart phones.

No, not mainframes – they are certainly around but are no longer the primary target of corporate IT.

It’s Essbase.  Yes, Essbase is that old – over 22 years.  If Essbase were human, it could vote, marry, take a loan out for a house, buy a gun, and serve in the armed services.  In other words, Essbase is a mature product.

That it is still around, largely in its original form is testament to its genius.  Yes, there have been many improvements and additions, but at its core, Essbase still does what Essbase did when it was first invented by Bob Earle and Jim Dorrian back in 1992.  Don’t believe me?  Take a look at the patent for BSO Essbase.  Yes, of course ASO Essbase is internally quite different, but read that application and see if what was described is all that functionally different than what it does today.

Not a time machine, but something almost as good

As I wrote, I haven’t found a time machine.  But I recently did get to spend a fascinating afternoon with one of the two fathers of Essbase – Bob Earle.  I must hasten to add that this is because of the generosity and contacts of my good buddy Tim Tow who seemingly knows everyone.

Fellow ODTUG board members Tim, Natalie Delemar, and I met Bob for a few drinks at a hotel in Seattle.  We were all there because of the ODTUG board face to face meeting (we have two a year, one right before Kscope14, one six months earlier, usually in the host city).  This was our chance to meet one of the progenitors of Essbase and we were all quite keen to meet him.

Here we are.  
Natalie, Yr. Obdnt. Srvnt., Bob Earle, and Tim Tow

So what was it like?

Bob is an incredibly self-effacing guy.  He was astonished that Essbase was still around, amazed that people cared about it so much, and was, I think, sort of gobsmacked that we were so interested in his story.  He was also very clear that he was only one half of the team that invented Essbase.

Essbase was an idea, an opportunity, and (I think) a really fascinating and stressful chapter in Bob’s life.

I will also note that Bob is a very private person, and asked that what we discussed remains confidential.  I will say that I think he found the genesis of Essbase to be quite the roller coaster and he has completely divorced himself from the high technology world.  Take from that what you will.

It was a true honor to meet the man that set so many of our careers on the path they now are.  I’ve often thought of what I might be doing if my former boss, Mike Rose, hadn’t thrown Comshare’s resold version of Essbase on my desk and said (I am paraphrasing, but this is pretty much as it happened), “My mainframe costs are killing me, see what you can do with this product.”  

The rest, as the saying goes, is history.

Here I am with the guy that unwittingly landed me right here at this blog.  

Sometimes you can go home again.

22 January 2014

Shared Services Stupid Trick No. 4

Challenges

I like them.  No, not impossible ones, like cleaning out the Augean Stables.  I’m no Hercules.  Those who have seen my manly physique know I am more like the before than the after in this famous-if-you-ever-read-comic-books ad.  And before you get any ideas, know that yr. obdnt. srvnt. does not habitually kick chairs when frustrated, seek out “dance floor hogs” for a damn good whacking, or practice “dynamic tension”.  I do like hiking, however.  And isometrics are not a bad way of building anaerobic strength.

What I really enjoy is being told that something is “impossible” when I have a sneaking suspicion that maybe it isn’t.  And then trying to see who’s right – Mr. Know-All or me.  Sometimes I win, sometimes I lose but no matter the outcome I’m the wiser for trying.  This is one of those posts.

The genesis

Over on Network54’s Essbase board, there was a rather long thread about Things That We Wish Oracle EPM Did.  And in that thread, there was a comment or at least an implication that SQL queries couldn’t join Shared Services provisioning to Planning access but that XML processing could.  On the face of it, this didn’t make sense – why would one technology be better than another when it came to matching up data?  There was also the admission that the LCM->XML->MS Access code took over an hour.  Surely I could beat that performance with good old SQL.   Maybe.

The challenge

It seems pretty straightforward.  Based on that thread post, using SQL, could I:
  1. Match Shared Services provisioning and roles with Planning application security
  2. Do so in less than an hour

Of course this is a challenge that I set for myself and if I ended up not being able to do it, well, I tried, failed, and maybe would have to rethink the power of SQL.  And if I solved it, hey presto – an excuse for a blog post.  As you’re reading a blog post, I reckon not many of you are sitting on the proverbial edge.

Okay, I cheated

There’s cheating and then there’s cheating.  I was pretty sure that I could do this because I had already written a four part series on Planning security and a three part series on Shared Services security.  I think I know the basics of querying Oracle EPM security.  So not cheating, but at least an idea that this maybe it wouldn’t be all that difficult to do.

And for once I was right

In fact, this query was simply the mashup of the direct Shared Services and implicit Planning queries.  Joining across databases (this is SQL Server – Oracle would just be joining across schemas and works the same way) is easy:  just specify the database name before the table and Bob’s Your Uncle.  It took me all of three minutes to do this – it is nice when one has the code already done although when I wrote it I never thought I would join the results.

The code

USE HYP_SharedServices
GO

--    Declare the anchor member as a variable as it will be referenced more than once
DECLARE @TopCat AS nvarchar(4000)
SET @TopCat = 'PLN_SampApp1' ;

/**********************************************************************************/
/*    NOTE NOTE NOTE NOTE -- The below is direct Shared Services assignment.        */
/**********************************************************************************/
WITH TopCat AS
(
   --    Set up the anchor member
   SELECT
       G.LOWER_IDENTITY_ID AS 'Parent_ID'
       , G.LOWER_IDENTITY_ID AS 'Child_ID'
       --    Need to CAST as VARCHAR(200) to allow UNION ALL
        , G.NAME AS 'Parent'
       , CAST('' AS VARCHAR(200)) AS 'Child'
       , 1 AS 'Gen'
   FROM CSS_GROUPS G
   -- This is where you put in the topmost member of the tree
   WHERE G.NAME = @TopCat
),
Related AS
(
   --    Almost Parent/Child, but not quite
   --    I made this a separate CTE because it's cleaner down in the true Parent/Child query
   SELECT
       GM.LOWER_GROUP_IDENTITY AS 'Parent_ID'
       , GM.LOWER_MEMBER_IDENTITY AS 'Child_ID'
       , G1.NAME AS 'Parent'
       -- 1 = user, 2 = group; Cast both as VARCHAR(200) to allow UNION ALL
       , CASE
           --    Subqueries for Users and Groups to get the names
               WHEN
                   GM.MEMBER_TYPE = 1
               THEN
                   (SELECT
                       CAST(U.NAME AS VARCHAR(200))
                   FROM CSS_USERS U
                   WHERE U.LOWER_IDENTITY_ID = GM.LOWER_MEMBER_IDENTITY)
               WHEN
                   GM.MEMBER_TYPE = 2
               THEN
                   (SELECT
                       CAST(G2.NAME AS VARCHAR(200))
                   FROM CSS_GROUPS G2
                   WHERE G2.LOWER_IDENTITY_ID = GM.LOWER_MEMBER_IDENTITY)
           END
       AS 'Child'
   FROM CSS_GROUP_MEMBERS GM
   INNER JOIN CSS_GROUPS G1 ON
       GM.LOWER_GROUP_IDENTITY = G1.LOWER_IDENTITY_ID
),
ParentChild AS
(
   -- Set the anchor
   SELECT
       T.*
   FROM TopCat T
   UNION ALL
   -- Oooh, fancy, we're going recursive here
   SELECT
       R.Parent_ID
       , R.Child_ID
       , R.Parent
       , R.Child
       , PC.Gen + 1
   FROM Related R     
   --    This is the recursive JOIN that ties child to parent
   INNER JOIN ParentChild PC
       ON R.Parent_ID = PC.Child_ID
   ),
--    Figure out all of the possible roles in each project
RolesByProduct AS
(
   SELECT
       R.LOWER_IDENTITY_ID
       , R.ROLE_ID
       , R.PRODUCTCODE
       , R.PRE_CONFIG_STATUS
       , L.VERSION
       , L.NAME
       , L.DESCRIPTION
   FROM CSS_ROLES R
   INNER JOIN CSS_ROLE_LOCALES L
       ON R.LOWER_IDENTITY_ID = L.LOWER_IDENTITY_ID
   WHERE
       LOWER(L.LOCALE) IN (LOWER('en_US'))  
       AND L.LOWER_NAME LIKE LOWER(N'%')
       AND LOWER(L.VERSION) = LOWER (N'11.1.2.0')
),   
--    Figure out direct security assignments for all levels of the hieararchy.
--    Use the undocumented view VW_APPLICATIONS to get a pretty descriptions of the
--    products and projects.
DirectSharedServicesSecurity AS
   (
   SELECT
       --PC.*
       --, I.LOWER_ROLE_IDENTITY
       --, I.LOWER_APPLICATION_ID
       PC.Parent
       , PC.Child
       , PC.Gen
       , VA.PROJECT_NAME
       , VA.APP_DISPLAY_NAME
       , R.NAME
       , R.DESCRIPTION
       , VA.PRODUCT_NAME
   FROM ParentChild PC
   LEFT OUTER JOIN CSS_PROVISIONING_INFO I
       ON PC.Child_ID = I.LOWER_MEMBER_IDENTITY
   LEFT OUTER JOIN VW_APPLICATIONS VA
       ON I.LOWER_APPLICATION_ID = LOWER(VA.APPLICATION_ID)       
   LEFT OUTER JOIN RolesByProduct R
       ON I.LOWER_ROLE_IDENTITY = R.LOWER_IDENTITY_ID    ),
/**********************************************************************************/
/*    NOTE NOTE NOTE NOTE -- The below is Planning implicit security assignment.    */
/**********************************************************************************/
 -- Dimensions and all of their members
    Dimensions AS
    (
    SELECT DISTINCT
        O.OBJECT_ID,
        O.OBJECT_NAME AS "Member",
         (SELECT OB.OBJECT_NAME
            FROM HYP_PLN_RestoreApp.dbo.HSP_OBJECT OB
            WHERE OB.OBJECT_ID = M.DIM_ID) AS "Dimension"
    FROM HYP_PLN_RestoreApp.dbo.HSP_OBJECT O
    INNER JOIN
        HYP_PLN_RestoreApp.dbo.HSP_MEMBER M
        ON M.MEMBER_ID = O.OBJECT_ID
    --ORDER BY O.OBJECT_ID ASC
    ),
 -- All of the other object types, including the ones that aren't documented or in HYP_PLN_RestoreApp.dbo.HSP_OBJECT_TYPE
 ObjType AS
    (
     /*
       1 to 50 defined in HYP_PLN_RestoreApp.dbo.HSP_OBJECT_TYPE
       103 = Menus
       107 = Composite forms
       115 = Deployed Business Rule
       116 = Looks like Business Rules, but don't exist in CM?  So orphaned?
       117 = Calculation Manager variables
       118 = Business Rule Folder
       119 = CalcMgrRulesets -- that's actually the OBJECT_NAME, so defined by system?
       120 = There are four valies in a three Plan Type Planning app:
             CalcMgrVariables
             CalcMgrVariablesPTName1
             CalcMgrVariablesPTName2
             CalcMgrVariablesPTName3        
     */
     SELECT DISTINCT
       OBJECT_TYPE AS "OBJECT_TYPE",
       TYPE_NAME AS "TYPE_NAME"
     FROM HYP_PLN_RestoreApp.dbo.HSP_OBJECT_TYPE
     UNION
     SELECT
       CONVERT(INT, '103') AS "OBJECT_TYPE",
       'Menu' AS "TYPE_NAME"
     UNION
     SELECT
       CONVERT(INT, '107') AS "OBJECT_TYPE",
       'Composite' AS "TYPE_NAME"
     UNION
     SELECT
       CONVERT(INT, '115') AS "OBJECT_TYPE",
       'Business Rule' AS "TYPE_NAME"
     UNION
     SELECT
       CONVERT(INT, '118') AS "OBJECT_TYPE",
       'Business Rule Folder' AS "TYPE_NAME"
     --ORDER BY OBJECT_TYPE, TYPE_NAME    
    ),
 --  Get every object in the application
 ObjectID AS
   (
     SELECT
       OBJECT_ID,
       OBJECT_NAME,
       OBJECT_TYPE,
       SECCLASS_ID
     FROM HYP_PLN_RestoreApp.dbo.HSP_OBJECT
   ),
 -- This is almost the end of the road, but it doesn't take into account implicit security
 -- Stop here if that isn't important
 FinalCTE AS
   (
     SELECT --DISTINCT
       --OT.TYPE_NAME AS "Type",
       --  If the OBJECT_TYPE = 50 then it is a user-defined or custom dimension
       --  so do a subquery to pull the dimension name
       CASE
         WHEN O_ID.OBJECT_TYPE != 50 THEN OT.TYPE_NAME
         ELSE (SELECT D."Dimension"
                 FROM Dimensions D
                 WHERE O_ID.OBJECT_ID = D.OBJECT_ID)
       END AS "Type",
       O_ID.OBJECT_NAME AS "Object",
       CASE
       -- Subquery to get user or group type
           (SELECT OA.OBJECT_TYPE
               FROM HYP_PLN_RestoreApp.dbo.HSP_OBJECT OA
           WHERE OA.OBJECT_ID = AC.USER_ID)
           WHEN 5 THEN 'User'
           WHEN 6 THEN 'Group'
       END AS "Security Type",
      (SELECT OA.OBJECT_NAME
           FROM HYP_PLN_RestoreApp.dbo.HSP_OBJECT OA
           WHERE OA.OBJECT_ID = AC.USER_ID) AS "User/Group Name",
       CASE AC.ACCESS_MODE
           WHEN 1 THEN 'Read'
           WHEN 2 THEN 'Write'
           WHEN 3 THEN 'Write'
           WHEN 4 THEN 'Launch'
           WHEN -1 THEN 'Deny'
       END AS "Read/Write",
       CASE AC.FLAGS
         WHEN 0 THEN '"' + O_ID.OBJECT_NAME + '"'
         WHEN 5 THEN '@CHI("' + O_ID.OBJECT_NAME + '")'
         WHEN 6 THEN '@ICHI("' + O_ID.OBJECT_NAME + '")'
         WHEN 8 THEN '@DES("' + O_ID.OBJECT_NAME + '")'
         WHEN 9 THEN '@IDES("' + O_ID.OBJECT_NAME + '")'
        END AS "Hierarchy function"      
     FROM ObjectID O_ID
     INNER JOIN
       ObjType OT ON OT.OBJECT_TYPE = O_ID.OBJECT_TYPE
     INNER JOIN HYP_PLN_RestoreApp.dbo.HSP_ACCESS_CONTROL AC
       ON O_ID.OBJECT_ID = AC.OBJECT_ID
   ),
 --  Figure out what the relationship is between users and groups
 --    All users in all groups
 SuperSet AS
   (
     SELECT
       O1.OBJECT_NAME AS "Group",
       O2.OBJECT_NAME AS "User"
     FROM HYP_PLN_RestoreApp.dbo.HSP_USERSINGROUP G
     INNER JOIN HYP_PLN_RestoreApp.dbo.HSP_OBJECT O1
       ON G.GROUP_ID = O1.OBJECT_ID
     INNER JOIN HYP_PLN_RestoreApp.dbo.HSP_OBJECT O2
       ON G.USER_ID = O2.OBJECT_ID  
   ),
 --  Just the users in the specified group(s)
 --  NB -- This could be expanded to multiple groups if need be
 SpecificGroup AS
   (
     SELECT
       O1.OBJECT_NAME AS "Group",
       O2.OBJECT_NAME AS "User"
     FROM HYP_PLN_RestoreApp.dbo.HSP_USERSINGROUP G
     INNER JOIN HYP_PLN_RestoreApp.dbo.HSP_OBJECT O1
       ON G.GROUP_ID = O1.OBJECT_ID
     INNER JOIN HYP_PLN_RestoreApp.dbo.HSP_OBJECT O2
       ON G.USER_ID = O2.OBJECT_ID  
     --WHERE
     --  O1.OBJECT_NAME LIKE '%EMEA'
   ),
 -- Use an IN statement with a subquery to limit the users from the SuperSet CTE to the users in the
 -- SpecificGroup CTE
 UsersInGroups AS
   (
     SELECT
       S.*
     FROM SuperSet S
     WHERE S."User" IN (SELECT "User" FROM SpecificGroup)      
   ),
 -- Use an IN statement with a subquery against a DISTINCT to limit the groups from the SuperSet
 -- CTE.  This will return all of the inherited groups.    
 GroupsInGroups AS
    (
        SELECT DISTINCT
            S."Group"
        FROM SuperSet S
        WHERE S."User" IN (SELECT "USER" FROM SpecificGroup)
    ),
 -- Get the security that is specifically assigned to users
 -- Use an IN statement with a subquery to limit the users from the FinalCTE CTE to the users in the
 -- SpecificGroup CTE
 UserDefinedSecurity AS
 (
   SELECT
       F."Type",
       F."Object",
       F."Security Type",
       F."User/Group Name",
       --'User-assigned' AS "Parent Group",
       F."Read/Write",
       F."Hierarchy function"
   FROM FinalCTE F
   WHERE
        "Security Type" = 'User'
        AND F."User/Group Name" IN (SELECT "USER" FROM SpecificGroup)
 ),
 --  Get the security that is specifically assigned to groups
 --  The join between the CTE UsersInGroups and FinalCTE is the key to implicit security
 GroupDefinedSecurity AS
 (
   SELECT
       F."Type",
       F."Object",
       F."Security Type",
       G."Group" AS "User/Group Name",
       --F."User/Group Name" AS "Parent Group",
       F."Read/Write",
       F."Hierarchy function"
   FROM FinalCTE F
   INNER JOIN GroupsInGroups G
     ON G."Group" = F."User/Group Name"
 ),
 --  UNION the explicit to the user and the implicit via a group security
 UserAndGroupDefinedSecurity AS
 (
   SELECT
     *
   FROM UserDefinedSecurity  
   UNION
   SELECT * FROM GroupDefinedSecurity
 ),
 PlanningSecurity AS  
 (--  Now report out however you like
 SELECT
   "User/Group Name",
   "Security Type",
   --"Parent Group",
   "Type",
   "Object",
   "Read/Write",
   "Hierarchy function"
 FROM UserAndGroupDefinedSecurity
 )
--  Join the two query threads on user/group
SELECT
    DS.Parent AS 'SS Parent'
    , DS.Child AS 'SS Child'
    , DS.PROJECT_NAME AS 'SS Project'
    , DS.APP_DISPLAY_NAME AS 'SS App'
    , DS.NAME AS 'SS Role'
    , DS.DESCRIPTION AS 'SS Role Desc'
    , DS.PRODUCT_NAME AS 'SS Product'
    , PS.[Security Type] AS 'PLN Security Type'
    , PS.Type AS 'PLN Object'
    , PS.[Read/Write] AS 'PLN R/W'
    , PS.[Hierarchy function] AS 'PLN Hierarchy'
FROM DirectSharedServicesSecurity DS
LEFT OUTER JOIN PlanningSecurity PS
    ON DS.Child = PS."User/Group Name"

That’s a fair bit of code, about 350 lines.  In the real world, I would probably stick the Shared Services and Planning sides of the query into views and then join across the views to get the results to make things a bit more readable but that is an exercise for you, Gentle Reader, if you are so inclined.

Note that I used a LEFT OUTER JOIN to ensure that a lack of Planning security didn’t suppress Shared Services provisioning/roles.


The time

I suspect I will never know enough SQL to actually tune my queries.  The good news is that the set of what gets queried out of the EPM repositories is so small that efficiency is not much of an issue.  As an example, this query took exactly zero seconds to return the result:

I took a look at SQL Profiler to see if I could get more detail.  Instantaneous isn’t totally correct, but pretty darn close:
 
Based on the time, I think that 13 milliseconds beat 60 minutes quite handily.  :)

What have we learnt?

A couple of things, in order of increasing importance:
  1. SQL is really cool.
  2. Simplicity (yes, the query is long, but this is simpler than LCM, XML, and MS Access) trumps complexity.
  3. There are few absolutes when it comes to code.  Or perhaps what I mean is that it is dangerous to state (or to be fair, imply) that something is impossible, or even that one approach is “better” than another.  I for one have been shown many times that my impossible is someone else’s common practice.  Sometimes I laugh at my ignorance, other times I wince, but I always come away from the experience that little bit wiser.  Heaven knows I need the help.
  4. I better not really believe that what I did is “the best”, cf. the above point.  At best it is an approach that works quickly and was easy to write.  Pride goeth before the fall and all that.

So with those points and caveats, did I meet the challenge I set for myself?  Yes, I was able to use publicly available (albeit unsupported) code (yeah, my own, but still it is public) and join together security information from multiple EPM products with really very little effort.  Huzzah for me, etc., and I do enjoy that all too rare pat on the back even if I gave it to myself.

But that’s mere ego and thus not important.  What is really important is that you too, Gentle Reader, can combine the queries and code that I and others (Celvin, I am looking at you) are putting out there.  I really was very pleasantly surprised that it was actually so easy.  If you’re not taking advantage of all of these great resources…well, you need to interview your brains.

Be seeing you.