Introduction
Writing this blog post was an absolute stinker. It really is a pity (at least from my bank account’s perspective) that I don’t get paid for this stuff because I spent days figuring out how to write this. Weekends gone – poof!!! Evenings eaten up – zip!!! Walking around in a fog thinking about how this has to work – duh!!! Sigh. All because, really, my mad SQL skilz are pretty bad. But I finally stumbled into the answer and, if you continue on, I will stop whining, show you the code, give a whole bunch of cool links that illustrate interesting SQL theory, somehow relate this all to Essbase, and finally show this in action. It actually is kind of cool – or at least I tell myself that in an attempt to convince myself that this was worthwhile. Read on and decide for yourself.
The problem
I started this series off with figuring out how to get a simple parent child view of users and groups in Shared Services and then followed it up querying the direct security against said Shared Services objects.
But as I wrote, I then wanted to know how to get the implicitly assigned (assigned at higher levels in the group security and then inherited downwards) user security and to do that I would need to figure out how to display group membership inheritance at a user level. And then I would need to do the same thing, but only for security, which isn’t really in a parent/child format. Oh, interesting.
A review of Common Table Expressions
I have used CTEs quite a bit in this blog, mostly for recursive query purposes as so much of what we do in Oracle OLAP land (Essbase, Planning, HPCM, etc.) is hierarchal. I also like CTEs because they are an easy way to break complicated code into understandable chunks and do away with most subqueries which I personally find very difficult to follow.
Even Oracle agrees
All of your PL/SQL aficionados out there be aware that CONNECT BY has been deprecated and CTEs are the way forward in Oracle as well. Again, I like the syntax better than that command, and the example you will see (sooner or later) below is in Microsoft’s T-SQL. Yeah, I know, I need to install my 11.1.2.3 install with Oracle. It’s on my list of Things To Do.
Some theory for you to mull over
If you’ve been reading this series of queries (there actually is one more to come, but it may come in the new year) you know that I’ve used CTEs to derive hierarchy. There is actually quite a bit out there on hierarchy theory out there on the web. I found this excellent overview by Mike Hillyer on hierarchies in MySQL.
Don’t get hung up on the flavor of SQL – it is an excellent overview on how hierarchy in SQL works and even more importantly, it goes into the different models for dealing with hierarchy in SQL: Adjacency List and Nested Set. Read Mike’s (btw, I have no idea who Mike is, but it is an excellent post and is of a style I try, and usually fail, to emulate) post, see that I have used the inferior Adjacency List approach, and finally note that there is an entire book about managing hierarchies in SQL: Joe Celko’s Trees and Hierarchies in SQL for Smarties. (NB – My Wheel of Trust add-in for Firefox says don’t go to the link in Mike’s blog post; the one I have linked is to Amazon.) I have ordered the book and reflected yet again that maybe dropping out of my school’s Computer Science program in favor of Business Administration was not the best academic decision I could have made. So it goes.
A tale of two queries
All of the above is prelude – the real meat of this post is the generation of two separate queries. At Cameron’s Blog for Essbase Hackers, you get two for the price of one. Actually, since it’s free (cf. my comment about how I would be rich if I wrote this for a client), you get two for the price of none, but I digress yet again.
Query the first
If you think about how Shared Services represents inherited rights (effective roles in Shared Services Speak), there is an Inheritance Information column that shows how the user received rights as shown below.
As I wrote about in the last post, I most definitely tried to use SQL Server’s SQL Profiler to try to capture the SQL that created the above report. If the best programmers are lazy then I must be awesome because sloth is one of my manifold sins. Alas, this is all I get out of SQL Profiler:
Shared Services, like the other EPM products, is a mix of SQL metadata and application level logic and those stored procedures are created and destroyed by the application layer. This just meant I had to come up with a different approach to figuring out how to get that hierarchy.
I spent quite a bit of time trying to figure out how to present the information (the above screen shot from Shared Services is okay for a single user but not a good fit for multiple users) and surveyed my storied Stupid Tricks email list consisting of the Best and Brightest. Celvin Kattookaran and Markus Shipley both suggested that both the user group inheritance (the ancestry of the user) and the product provisioning path (the ancestry of the roles) be displayed on a single line.
Use case
Imagine a Parent Child table as below:
Then imagine the following provisioning:
I wanted to create something that looks like this:
Or at least something close to the last setup as that gives me both group ancestry (who’s in what) and provision (who has what). In the end I came up with something pretty close to this as the bit about having the group assignment within the security column turned out to not be necessary.
What did I already have
I already had a hierarchical query from part one of this series that would give me something like this:
Nice, but how oh how oh how do I translate that to something that looks like the Ancestors column? The trick, thanks to an assist from Dan Pressman, is to use the recursive nature of a CTE and the additive nature of fields within that CTE to build the Ancestors field. Think about what I just wrote – you can, in essence, treat a recurisve CTE like a traditional loop and concatenate strings (or mathetmatically add values to numeric fields). Pretty cool, eh? Let’s look at 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'
--, G.NAME AS 'Parent'
, CAST('' AS VARCHAR(200)) AS 'Parent'
, CAST(G.NAME AS VARCHAR(200)) AS 'Child'
-- Need to CAST as VARCHAR(200) to allow UNION ALL
--, 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 = 'GroupZ'
--WHERE G.NAME = 'ESB_Essbase'
WHERE G.NAME = 'PLN_SampApp1'
),
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'
, CAST(G1.NAME AS VARCHAR(200)) 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.*
, CAST( '' AS nvarchar(4000) ) as Ancestors
, CAST( '' AS nvarchar(4000) ) AS sortkey
FROM TopCat T
UNION ALL
-- Oooh, fancy, we're going recursive here
SELECT
R.Parent_ID
, R.Child_ID
--, R.Parent
-- This is the really cool part
, PC.Child -- Overload from recursive query that is the same value as R.Parent
, R.Child
-- Note that is is being incremented as it advances a generation, items at the
-- same generation are the function of the INNER JOIN.
, PC.Gen + 1
, PC.Ancestors
+ CASE WHEN LEN( PC.Ancestors ) = 0
THEN ''
ELSE '/'
END
+ R.Parent AS Ancestors
, PC.sortkey + LEFT(R.parent + space(50), 50) AS sortkey
FROM Related R
-- This is the recursive JOIN that ties child to parent
INNER JOIN ParentChild PC
ON R.Parent_ID = PC.Child_ID
)
SELECT
Parent
, Child
, Gen
, Ancestors
, sortkey
FROM ParentChild
ORDER BY Gen, sortkey + LEFT(Parent + space(50), 50)
And that gives us something pretty darn close to what I wanted, at least for the group inheritance part of it:
And if I change this to something a little more complicated like the PLN_SampApp1 group I used before, the query (I am only changing the anchor in the TopCat CTE) returns:
Btw, you will note that GroupZ and PLN_SampApp1 are in the Child, not the parent, fields. This is not by accident and is needed to build the lineage in Ancestors.
Nice, isn’t it? Pretty awesome, actually, and half of what I am looking for, but it’s not enough. I still want to know: who’s a user and who’s a group (so I can filter), what at each level is the inherited security, and finally at the user level, what is the inherited security? Demanding, aren’t I?
Query the second
I already have a query that gives me the directly assigned security to a user or group although of course that isn’t quite the same as a query that returns that information in an inherited mode for each level of the hierarchy.
I spent a lot of time trying to meld that direct query with the one Dan helped me with. It was more time than I really want to think about and was one of my better exercises in frustration.
It finally occurred to me that what I really wanted to do was take this:
And combine it with this:
And all I needed to do was:
- Put both queries into CTEs (and the queries were already 95% there with just the last displaying SELECT statement to put into a CTE)
- LEFT JOIN the direct security to the inherited group ancestry query.
But this left me in a quandary – could I apply a recursive CTE to the output of a recursive CTE? And I was getting confused with all of the code – I needed a simple example of this. To my delight and surprise I found one with a personal connection.
Scotland the Brave
I went searching the web for a super simple CTE and happily found one that had both a CTE and an example of how to do build an Ancestors field on stackoverflow.com. If you are not sure how an Adjacency List CTE manipulates data, this is the one to read.
And even better, this query mentions one of my favorite places on earth, Edinburgh, Scotland as I come from lowland Scots stock (where did you think “Cameron” came from?) . Here’s the query in its direct form:
-- Declare temporary table
DECLARE @tbl TABLE (
Id INT
,[Name] VARCHAR(20)
,ParentId INT
)
-- Value temporary table
INSERT INTO @tbl( Id, Name, ParentId )
VALUES
(1, 'Europe', NULL)
,(2, 'Asia', NULL)
,(3, 'Germany', 1)
,(4, 'UK', 1)
,(5, 'China', 2)
,(6, 'India', 2)
,(7, 'Scotland', 4)
,(8, 'Edinburgh', 7)
,(9, 'Leith', 8)
;
-- 1st recursive CTE
WITH abcd
AS (
-- anchor
SELECT id, [Name], ParentID,
CAST(([Name]) AS VARCHAR(1000)) AS "Path"
FROM @tbl
WHERE ParentId IS NULL
UNION ALL
--recursive member
SELECT t.id, t.[Name], t.ParentID,
CAST((a.path + '/' + t.Name) AS VARCHAR(1000)) AS "Path"
FROM @tbl AS t
INNER JOIN abcd AS a
ON t.ParentId = a.id
)
SELECT * FROM abcd
We can see the descendants of Europe and Asia:
So what happens if I want to try out that second recursive CTE? The code would then look like this:
-- 1st recursive CTE
WITH abcd
AS (
-- anchor
SELECT id, [Name], ParentID,
CAST(([Name]) AS NVARCHAR(1000)) AS "Path"
FROM @tbl
WHERE ParentId IS NULL
UNION ALL
--recursive member
SELECT t.id, t.[Name], t.ParentID,
CAST((a.path + '/' + t.Name) AS NVARCHAR(1000)) AS "Path"
FROM @tbl AS t
INNER JOIN abcd AS a
ON t.ParentId = a.id
),
-- 2nd recursive CTE
TryMe AS
(
SELECT
A.id, A.Name, A.ParentId, A.Path
, CAST(A.Id AS NVARCHAR(1000)) AS "Ancestry"
FROM abcd A
WHERE A.Id = '1' --OR A.Id = '2'
UNION ALL
SELECT
R.Id , R.Name, R.ParentId, R.Path
, CAST(CAST(T.Ancestry AS NVARCHAR(1000))
+ '/' + CAST(R.ID AS NVARCHAR(1000)) AS NVARCHAR(1000)) AS "Ancestry"
FROM
abcd R
INNER JOIN TryMe AS T
ON R.ParentId = T.Id
)
SELECT * FROM TryMe
Note that I made the TryMe anchor just Europe as I am trying to make the output as straightforward as possible. That second query gives me:
Isn't that stoatin’? This code proves that a recursive CTE can call on the output of another CTE. Once I knew that, the road ahead was clear.
Slight digression
The Leith police dismisseth us
They thought we sought to stay;
The Leith police dismisseth us
They thought we'd stay all day.
The Leith police dismisseth us,
We both sighed sighs apiece;
And the sighs that we sighed as we said goodbye
Were the size of the Leith police.
They thought we sought to stay;
The Leith police dismisseth us
They thought we'd stay all day.
The Leith police dismisseth us,
We both sighed sighs apiece;
And the sighs that we sighed as we said goodbye
Were the size of the Leith police.
It is quite hard to say, even slowly. And completely impossible when one is giddy on geek triumph.
Combining the CTEs
So what does it all look like? Like this:
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 = 'GroupZ' ;
-- Define the anchor member
WITH TopCat AS
(
-- Set up the anchor member
SELECT
G.LOWER_IDENTITY_ID AS 'Parent_ID'
, G.LOWER_IDENTITY_ID AS 'Child_ID'
--, G.NAME AS 'Parent'
-- Need to CAST as VARCHAR(200) to allow UNION ALL
, CAST('' AS VARCHAR(200)) AS 'Parent'
, CAST(G.NAME AS VARCHAR(200)) AS 'Child'
, CAST('Group' AS VARCHAR(200)) AS 'Type'
, 1 AS 'Gen'
FROM CSS_GROUPS G
-- This is where you put in the topmost member of the tree
WHERE G.NAME = @TopCat
),
-- Get related information
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'
, CAST(G1.NAME AS VARCHAR(200)) 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'
, CASE
WHEN GM.MEMBER_TYPE = 2 THEN CAST('Group' AS VARCHAR(200))
WHEN GM.MEMBER_TYPE = 1 THEN CAST('User' AS VARCHAR(200))
END
AS 'Type'
FROM CSS_GROUP_MEMBERS GM
INNER JOIN CSS_GROUPS G1 ON
GM.LOWER_GROUP_IDENTITY = G1.LOWER_IDENTITY_ID
),
-- Recursive CTE to show the basic parent/child hierarchy
SimplePC 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
, R.Type
, PC.Gen + 1
FROM Related R
-- This is the recursive JOIN that ties child to parent
INNER JOIN SimplePC 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_ID
, PC.Child_ID
, PC.Parent
, PC.Child
, PC.Gen
, PC.Type
, VA.PROJECT_NAME
, VA.APP_DISPLAY_NAME
, R.NAME
, R.DESCRIPTION
, VA.PRODUCT_NAME
FROM SimplePC PC
LEFT OUTER JOIN CSS_PROVISIONING_INFO I
ON PC.Child_ID = I.LOWER_MEMBER_IDENTITY
LEFT JOIN VW_APPLICATIONS VA
ON I.LOWER_APPLICATION_ID = LOWER(VA.APPLICATION_ID)
LEFT JOIN RolesByProduct R
ON I.LOWER_ROLE_IDENTITY = R.LOWER_IDENTITY_ID
),
-- Parent/child ancestor lineage
ParentChild AS
(
-- Set the anchor
SELECT
T.Parent_ID
, T.Child_ID
, T.Parent
, T.Child
, T.Gen
, T.Type
, CAST( '' AS nvarchar(4000) ) as Ancestors
, CAST( '' AS nvarchar(4000) ) AS sortkey
FROM TopCat T
UNION ALL
-- Oooh, fancy, we're going recursive here
SELECT
R.Parent_ID
, R.Child_ID
--, R.Parent
, PC.Child -- Overload from recursive query that is the same value as R.Parent
, R.Child
, PC.Gen + 1
, CAST(R.Type AS VARCHAR(200))
, PC.Ancestors
+ CASE WHEN LEN( PC.Ancestors ) = 0
THEN ''
ELSE '->'
END
+ R.Parent AS Ancestors
, PC.sortkey + LEFT(R.parent + space(50), 50) AS sortkey
FROM Related R
-- This is the recursive JOIN that ties child to parent
INNER JOIN ParentChild PC
ON R.Parent_ID = PC.Child_ID
-- Cannot put an outer join in the recursive part of a recursive
-- common table expression.
-- But it sure would have been nice.
),
-- JOIN the direct security to the Ancestors inherited lineage query
AlmostThere AS
(
SELECT
PC.Parent_ID
, PC.Child_ID
, PC.Parent
, PC.Child
, PC.Gen
, PC.Type
, PC.Ancestors
, PC.sortkey
, ISNULL(DS.PROJECT_NAME + '|' + DS.APP_DISPLAY_NAME + '|' + DS.NAME + '|' + DS.DESCRIPTION + '|' + DS.PRODUCT_NAME, '') AS 'Security'
FROM ParentChild PC
LEFT OUTER JOIN DirectSecurity DS
ON PC.Child_ID = DS.Child_ID
),
-- AlmostThere is, as the name implies, almost there but does not have security in an
-- inherited column
LastRecursive AS
(
-- Set the anchor
SELECT
A.*
, CAST('' AS NVARCHAR(4000)) AS 'Path'
FROM AlmostThere A
WHERE A.Child = @TopCat
UNION ALL
-- Recursive bit
SELECT
R.*
, CAST(LR.Path + '/' + LR.Security AS NVARCHAR(4000)) AS 'Path'
FROM
AlmostThere R
INNER JOIN LastRecursive LR
ON R.Parent = LR.Child
)
SELECT
LR.Parent
, LR.Child
, LR.Gen
, LR.Type
, LR.Ancestors
, LR.Path
FROM LastRecursive LR
ORDER BY Gen, sortkey + LEFT(LR.Parent + space(50), 50)
And that outputs:
Did you see that? The ancestry in the Ancestors column (‘natch) and the group security inheritance in the Path column (double ‘natch). That is straight from the fridge. And this is for all groups, users, and levels. What if I only want the users as I originally wrote? Simply stick a WHERE clause to filter just Type = ‘User’ as below:
.
.
.
SELECT
LR.Parent
, LR.Child
, LR.Gen
, LR.Type
, LR.Ancestors
, LR.Path
FROM LastRecursive LR
WHERE LR.Type = 'User'
ORDER BY Gen, sortkey + LEFT(LR.Parent + space(50), 50)
And that returns:
Let’s try it for PLN_SampApp1 while limiting the columns (I also cut back on the product provisioning information):
And finally ESB_Essbase:
That’s it – easy peasy lemon squeezy. Except it wasn’t. But now that’s it done, well, the work doesn’t seem so bad. Except it was. But it’s also all over. Mostly.
That’s it? Really?
So what do we now have in this three part series, other than proof I eat pain like candy?
- A query (this post) that displays both the inherited group ancestry and the concomitant role provisioning.
And of course digressions into German literature, philosophy, Original Sin, the Invisible Hand, the architecture of Oracle EPM applications, their tables, CTE theory, Scotland, Edinburgh, tongue twisters, Bathgate, and 1950s hipster slang. Like I wrote, value for money is what this blog is all about. Or maybe this 14 page opus (at least where I draft all of this in Word) just my way of saying, “Merry Christmas!”
But are we done with this? Nope, or at least I think that we’re not quite done.
It was mentioned to me in a Network54 thread that all of this was nice, but an XML-based approach that uses LCM output (always out of date, IMHO) was “better” because it could link across products. That may be, but I have a sneaking suspicion that it would be easy to link the above (really, any of the three query types) to Planning security output which, miracle diablu, I appear to have written.
So the next challenge will be tying the Shared Services security to Planning security. Can it be done? Stay tuned to this blog.
Be seeing you.