Shared Services Stupid Trick No. 2

Provisioning

As I wrote in my first post on this subject, after a bit of pain around figuring out how to read and understand the Shared Services schema, I can now run a query that will give me all the descendants of a given Shared Services group.  Huzzah for me!  Ahem.

But while that is nice (and the pat on the back I just gave myself felt oh so good), I want, no I need to know what the provisioning is for all of those descendants of a Shared Services group.  

Are any of us, really, immune from the seven deadly sins?  I can’t imagine how I would be, and certainly greed (along with the rest) is one of my faults.  Wait, is this camerons-blog-for-theologians.com?  Or camerons-blog-for-wearing-a-hair-shirt.com?  Or camerons-blog-for-repentance.com?  

Nope, the subject is Essbase, not original sin, and you’re reading camerons-blog-for-essbase-hackers.com.  But I’m not taking back my desire to wring more out of Shared Services than I originally claimed I wanted.  As Gordon Gecko said, “Greed is good”.  Or maybe I should call this blog the outcome of Adam Smith’s Invisible Hand.  Although I am not totally clear on how I maximize my economic gains by writing this beast.  

Back to a semblance of relevance – can I marry up the parent child list to directly assigned security?  Can I?  Hopefully, or I won’t have much of a blog post.

Roles

Given that Oracle EPM roles are defined in Shared Services, it doesn’t seem shocking that the table CSS_ROLES contains, well, the Oracle EPM roles.  But did you know there are 191 of them?  

Here’s a simple query (it will become part of a Common Table Expression a bit later on) that combines CSS_ROLE with CSS_ROLE_LOCALES (which stores localized by language role names and description).

--    Figure out all of the possible roles in each project

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')

Which gives us something like this:

By the way, if you wonder how I came up with that WHERE statement I am not ashamed to say that I used SQL Server’s Profiler to watch (aka, steal) SQL code as it came out of Shared Services.

I can almost hear you say, “Wait a minute.  Are you saying that you set up SQL Server Profiler on your VM and then just regurgitate the queries back to us, while claiming that you wrote it all yourself?”  I so wish that were the case as the time it would take to write these posts would be quite a bit quicker.  Alas, no.

The query you see above is the only bit of code (okay, there is one more snippet) I was able to “borrow” from Oracle.  What does Shared Services (and all of the other Oracle EPM tools – trust me, I’ve looked) do?  Take a look at the lines that say “exec sp_execute 1” and “declare @p1 int set…”.  I am here to tell you some of what the products do are “normal” queries and much of the rest is done within the application, including the population of those stored procedures which btw destroy themselves on execution.  This overall query, other than this one bit (again, with one other qualification) is, for better or worse, 100% Cameron.  Probably worse, but I will persevere on.

Applications

But looking at SQL Server Profiler showed me something else:  use of a view called VW_APPLICATIONS.  A quick perusal of the Shared Services schema guide shows exactly no mention of that view.  What oh what oh what could that view provide?  

Code

Go take a look at the view if you wish.  Wait, this is the second code bit that is Not By Cameron, but again, I am pointing this out, so it isn’t exactly subterfuge or fraud on my part.

NB – If you stick the above code into a query, make sure you remove the last parentheses.

Fields

Output

Oooh, isn’t that pretty?  We have nothing less than a nice view of all of the applications in pretty almost-English.  Click here to embiggen the picture to see all of the apps on my VM.

3 + 1 +1 = 5

Before I confuse myself with higher mathematics, it’s really quite simple.

• 3 = the CTEs TopCat, Related, and ParentChild that you saw in our last thrilling installment
• 1 = the CTE RolesByProduct which I illustrated above
• 1 also = the CTE Direct Security of which more anon

Put them all together and the answer isn’t really 5 but a direct (as in what is directly provisioned to the inclusive descendants of a Shared Services group) report on group hierarchy and the security assigned at each level.

The code

USE HYP_SharedServices

GO

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

        , CAST(G.NAME AS VARCHAR(200)) 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 = 'PLN_SampApp1'

    WHERE G.NAME = 'ESB_Essbase'

),

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.

DirectSecurity 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    )

SELECT

    DS.*

FROM DirectSecurity DS

Here’s the Essbase-related group ESB_Essbase.

And here’s the Planning-related group PLN_SampApp1.  And yes, I really did assign Calculation Manager rights to a lower level group just to see what the query did with it.

Inner versus Outer

No, I am not talking about belly buttons but instead the somewhat unusual use of LEFT OUTER JOINS at the end of the DirectSecurity CTE.  These are necessary because the users (you are looking at a proper hierarchical security model after all) have no security.  Trying to INNER JOIN these users across provisioning, applications, and roles would result in them being dropped.  Instead, LEFT OUTER JOINs are used to include them in the output.  Go on, play with the code and you’ll see what I mean.

What’s next?

While it’s nice to have a report that shows the hierarchical relationship between groups, sub-groups, and users along with their security, this is a top down report.

What about a query that gives me all of users in the application, their implicit and explicit security, and the hierarchical group lineage so you can see how that provisioning gets assigned?  Yes, that is available on a user by user basis in Shared Services but not as a group report.  Now I just have to finish that bit of code…

Be seeing you.