How could I forget this?
No, I don’t mean the memory in your laptop, or mobile, or server. I mean my memory.
Back in the middle of August (that’s August, 2014 for those of you who read this in years hence) I wrote that writing an EPMA dimension parent/child query was beyond me. And I was delighted to see that Daniel Willis wrote a query to do just that. I was impressed, because I thought it was hard. And it is.
There was even a contributor to that thread who isn’t much of a SQL coder and he had the query. Jeez, I thought, how dumb could I get? Probably it’s best not to ask questions like this as the answer may not be to one’s liking.
And then I found it
I was looking through some old documents of mine for a completely unrelated purpose and I found…an entire document about writing EPMA queries. OMG. Written by yr. obt. svt. Double OMG. I completely, and I do mean completely, forgot about this, and I spent a fair amount of time on it the first time round. It’s a good thing that my memory has always been terrible; else I would be worried about my fast-approaching senility.
Regardless of my future mental condition (and after all, given the above, who will be able to tell the difference between that and the way I am today? Just call me The Professor.), upon review the queries really aren’t half bad and they go further than any other published work to demystify the tables although to my mind (which, let’s face it, has some issues as per above) there’s still one more step that needs to be taken to make these kinds of queries really useful. Of that, more anon.
The background
The genesis of all this was to write a query that compared EPMA and Planning dimensionality (so you can guess what kind of project I was on) and then figure out the differences; this could be extended to Essbase although that would require a step to bring Essbase dimensionality into SQL.
What I found was that the EPMA repository is much more difficult to master than the Planning one. I think that’s where my comment about “I can’t figure it out” came from. What I mean by that is that there are many interrelated tables in EPMA, a poor industry understanding about how those tables work (despite the extant queries, there really isn’t much), and a relational design that is highly normalized.
Despite the challenges, I was able to pull dimensionality from the EPMA tables; only default and inherited properties cannot be derived from the EPMA tables. In discussion with a Java programmer friend (hey, Jason Jones), it appears as though that information is derived in the application layer. However, it should not be difficult to note all possible default values and to compute a bottom up property inheritance query. While this would be slow in an interactive query it would be easy to create these queries in stored procedures and then run them from there on a scheduled basis to denormalized tables or part of an overall batch process for metadata updates. ← Good luck on this, Daniel, Celvin, or whoever else is crazy enough (or more like good enough) to write this. :)
Blog post organization
The query at the end of this post is impossibly long and complex, at least to me. And as I noted, the tables, their relationships with each other, and usage isn’t generally known. To that end, this post will walk through some of the more interesting areas of the EPMA schema. This is by no means exhaustive, so please don’t think that I’ve covered it all. I encourage you to use this as a jumping off point.
A note on EPMA libraries and applications
EPMA stores multiple schema states by deploy date. In other words, what the EPMA dimensionality looked like when a deploy for an application is completed is stored in EPMA. It appears, based on the Oracle schema documentation, that this functionality is slowly being removed from the product. However the logging is still there in the form of a Library ID. The most current Library ID is always 1.
Each individual HFM, Planning, Essbase, etc. application has its own name. The shared dimensions also have an application name – that application name is “Master”. Local applications follow that application’s name, e.g., 2Test.
Queries, queries, queries
A really fantastic way to figure out what’s stored where
As I was writing this query, I really struggled with first finding and then figuring out where EPMA had stuck the value values for things like new UDAs, attributes, etc. (believe me, it ain't exactly straightforward -- just read below). Whilst desperately searching through the web to do a whole database search (hey, I was desperate), I stumbled across this query:
It is, at least for T-SQL, amazing. And fast. And it will find that text. And it saved my bacon. Highly recommended, both the query and Ely’s Farm Products.
Property Application
Description
Display application properties. This is not used in the overall parent/child query but it is fascinating.
Code
SELECT
--PA.* ,
L.c_library_name AS "Library"
, A.c_application_name AS "AppName"
, D.c_dimension_name AS "PropDimName"
, D.e_dimension_type AS "PropDimType"
, M.c_member_name AS "PropMbrName"
, PA.c_property_value AS "Property"
FROM DS_Property_Application PA
INNER JOIN DS_Library L
ON PA.i_library_id = L.i_library_id
INNER JOIN DS_Application A
ON PA.i_application_id = A.i_application_id
AND PA.i_library_id = A.i_library_id
INNER JOIN DS_Dimension D
ON PA.i_prop_def_dimension_id = D.i_dimension_id
AND PA.i_library_id = D.i_library_id
INNER JOIN DS_Member M
ON PA.i_prop_def_member_id = M.i_member_id
AND PA.i_library_id = M.i_library_id
WHERE
PA.i_library_id = 1
ORDER BY 1, 2, 3
Sample output
Property Application Array
Description
Display application technology type. Again this is not part of the parent/child query but qualifies as interesting information.
Code
SELECT
--PAA.*,
L.c_library_name AS "Library"
, A.c_application_name AS "AppName"
, D1.c_dimension_name AS "RefDimName"
, D1.e_dimension_type AS "RefDimType"
, M1.c_member_name AS "RefMbrName"
, D2.c_dimension_name AS "PropDimName"
, M2.c_member_name AS "PropMbrName"
FROM DS_Property_Application_Array PAA
INNER JOIN DS_Library L
ON PAA.i_library_id = L.i_library_id
INNER JOIN DS_Application A
ON PAA.i_application_id = A.i_application_id
AND PAA.i_library_id = A.i_library_id
INNER JOIN DS_Dimension D1
ON PAA.i_ref_dimension_id = D1.i_dimension_id
AND PAA.i_library_id = D1.i_library_id
INNER JOIN DS_Dimension D2
ON PAA.i_prop_def_dimension_id = D2.i_dimension_id
AND PAA.i_library_id = D2.i_library_id
INNER JOIN DS_Member M1
ON PAA.i_ref_member_id = M1.i_member_id
AND PAA.i_library_id = M1.i_library_id
INNER JOIN DS_Member M2
ON PAA.i_prop_def_member_id = M2.i_member_id
AND PAA.i_library_id = M2.i_library_id
WHERE PAA.i_library_id = 1
Sample output
Property Application Array
Description
This query doesn’t seem to return information that can be used by humans. I think it is used internally to figure out how to display information. Note the ApplicationAssignedRealtimeValidations values in the PropMbrName field. I think this is used to tell EPMA what should be done via the application layer when looking at an application setting.
Code
SELECT
--PAR.*,
L.c_library_name AS "Library"
, A.c_application_name AS "AppName"
, D1.c_dimension_name AS "PropDimName"
, D1.e_dimension_type AS "PropDimType"
, M.c_member_name AS "PropMbrName"
, D2.c_dimension_name AS "RefDimName"
, D2.e_dimension_type AS "RefDimType"
FROM DS_Property_Application_Ref PAR
INNER JOIN DS_Library L
ON PAR.i_library_id = L.i_library_id
INNER JOIN DS_Application A
ON PAR.i_application_id = A.i_application_id
AND PAR.i_library_id = A.i_library_id
INNER JOIN DS_Dimension D1
ON PAR.i_prop_def_dimension_id = D1.i_dimension_id
AND PAR.i_library_id = D1.i_library_id
INNER JOIN DS_Member M
ON PAR.i_prop_def_member_id = M.i_member_id
AND PAR.i_library_id = M.i_library_id
INNER JOIN DS_Dimension D2
ON PAR.i_ref_dimension_id = D2.i_dimension_id
WHERE PAR.i_library_id = 1
ORDER BY 2
Sample output
Property Dimension
Description
Show dimension level settings by application. In the final query, this is used to get dimension names mostly. This is the last query that the parent/child query does not use in this document.
NB – Dimensions are normal metadata (Measures, Product, Time, etc.) but also properties of members, e.g., UDAs, Attributes, and Aliases. For people not familiar with EPMA, this is a bit of a puzzler but is just how the tool is architected. I believe that Hyperion (this tool originated under their aegis and was then known as BPMA) did this so that the property dimensions could be shared across multiple applications.
Code
SELECT
--PD.*,
L.c_library_name AS "Library",
--D.i_dimension_id,
A.c_application_name AS "Application",
D.c_dimension_name AS "Dimension",
M.c_member_name AS "Member",
PD.c_property_value AS "Value"
FROM DS_Property_Dimension PD
INNER JOIN DS_Library L
ON PD.i_library_id = L.i_library_id
INNER JOIN DS_Dimension D
ON PD.i_dimension_id = D.i_dimension_id
AND PD.i_library_id = D.i_library_id
INNER JOIN DS_Application A
ON PD.i_application_id = A.i_application_id
AND PD.i_library_id = A.i_library_id
INNER JOIN DS_Member M
ON PD.i_prop_def_member_id = M.i_member_id
AND PD.i_library_id = M.i_library_id
--AND PD.i_dimension_id = M.i_dimension_id
WHERE
L.i_library_id=1
ORDER BY "Application", "Dimension", "Member"
Sample output
Property Member
Description
EPMA/technology level metadata properties as well as member specific properties. This is a weird mix of data – if the dimension name is Properties, it seems to be metadata that drives EPMA. If the dimension name corresponds to an EPMA application, the metadata is that member’s property information.
NB – The output to this query is pretty big – almost 3,500 rows. What you will see in this post is just a snippet of the metadata.
Code
WITH PM AS
(
SELECT
--PM.*,
A.c_application_name AS "Application",
D.c_dimension_name AS "Dimension",
M1.c_member_name AS "Member",
D1.c_dimension_name,
M2.c_member_name AS "Property",
PM.c_property_value AS "Value"
FROM DS_Property_Member PM
INNER JOIN DS_Dimension D
ON PM.i_dimension_id = D.i_dimension_id
AND PM.i_library_id = D.i_library_id
INNER JOIN DS_Dimension D1
ON PM.i_prop_def_dimension_id = D1.i_dimension_id
AND PM.i_library_id = D1.i_library_id
INNER JOIN DS_Application A
ON PM.i_application_id= A.i_application_id
AND PM.i_library_id = A.i_library_id
INNER JOIN DS_Member M1
ON PM.i_member_id = M1.i_member_id
AND PM.i_library_id = M1.i_library_id
INNER JOIN DS_Member M2
ON PM.i_prop_def_member_id = M2.i_member_id
AND PM.i_library_id = M2.i_library_id
WHERE PM.i_library_id = 1
--AND M1.c_member_name = 'BALANCE SHEET'
--ORDER BY "Application", "Dimension", "Member", "Property"
)
SELECT
--DISTINCT "Property"
*
FROM PM
Sample output
Property Member Memo
Description
Mix of technology defaults and allowable values (and hey, it looks like Cold Fusion is the programming language of choice within EPMA) as well as member properties. The primary member property seems to be the member formula for both BSO and ASO.
Code
WITH PMM AS
(
SELECT
--PMM.*,
A.c_application_name AS "Application",
D.c_dimension_name AS "Dimension",
m1.c_member_name AS "Member",
M2.c_member_name AS "Property",
RTRIM(CONVERT(VARCHAR(4000), PMM.x_property_value)) AS "Value"
FROM DS_PROPERTY_MEMBER_MEMO PMM
INNER JOIN DS_Dimension D
ON PMM.i_dimension_id = D.i_dimension_id
AND PMM.i_library_id = D.i_library_id
INNER JOIN DS_Application A
ON PMM.i_application_id= A.i_application_id
AND PMM.i_library_id = A.i_library_id
INNER JOIN DS_Member M1
ON PMM.i_member_id = M1.i_member_id
AND PMM.i_library_id = M1.i_library_id
INNER JOIN DS_Member M2
ON PMM.i_prop_def_member_id = M2.i_member_id
AND PMM.i_library_id = M2.i_library_id
WHERE
PMM.i_library_id =1
)
SELECT
*
FROM PMM
Sample output
Property Member Array
Description
On a member basis, UDAs, attribute dimension associations, and Alias(es) are returned. The way I had to join this to DS_Member and DS_Dimension was hard to figure out at first – the UDAs and attributes are NOT directly stored in the DS_Property_Member_Array table but they can be joined to.
Code
WITH PMA AS
(
SELECT
--PMA.*,
A.c_application_name AS "Application",
D.c_dimension_name AS "Dimension",
M1.c_member_name AS "Member",
M2.c_member_name AS "Property",
PMA.c_value AS "Value"
FROM DS_Property_Member_Array PMA
INNER JOIN DS_Dimension D
ON PMA.i_dimension_id = D.i_dimension_id
AND PMA.i_library_id = D.i_library_id
INNER JOIN DS_Application A
ON PMA.i_application_id= A.i_application_id
AND PMA.i_library_id = A.i_library_id
INNER JOIN DS_Member M1
ON PMA.i_member_id = M1.i_member_id
AND PMA.i_library_id = M1.i_library_id
INNER JOIN DS_Member M2
ON PMA.i_prop_def_member_id = M2.i_member_id
AND PMA.i_library_id = M2.i_library_id
WHERE PMA.i_library_id = 1
--AND M1.c_member_name = 'BALANCE SHEET'
--ORDER BY "Application", "Dimension", "Member"
)
SELECT
--DISTINCT "Property"
*
FROM PMA
--WHERE "Member" LIKE 'FY%'
ORDER BY "Application", "Dimension", "Member", "Property"
Sample output
Property Member Relationship
Description
Parent child table with some member properties.
NB – These member properties only return members with explicit assignments.
Code
WITH PR AS
(
SELECT
--PR.*,
L.c_library_name AS "Library",
A.c_application_name AS "Application",
D.c_dimension_name AS "Dimension",
M1.c_member_name AS "Parent",
M2.c_member_name AS "Child",
M3.c_member_name AS "Property",
PR.c_property_value AS "Value"
FROM DS_Property_Relationship PR
INNER JOIN DS_Library L
ON PR.i_library_id = L.i_library_id
INNER JOIN DS_Application A
ON PR.i_application_id = A.i_application_id
AND PR.i_library_id = A.i_library_id
INNER JOIN DS_Dimension D
ON PR.i_dimension_id = D.i_dimension_id
AND PR.i_library_id = D.i_library_id
INNER JOIN DS_Member M1
ON PR.i_parent_member_id = M1.i_member_id
AND PR.i_library_id = M1.i_library_id
INNER JOIN DS_Member M2
ON PR.i_child_member_id = M2.i_member_id
AND PR.i_library_id = M2.i_library_id
INNER JOIN DS_Member M3
ON PR.i_prop_def_member_id = M3.i_member_id
AND PR.i_library_id = M3.i_library_id
WHERE
PR.i_library_id = 1
--ORDER BY "Application", "Dimension", "Parent", "Child", "Property", "Value"
)
SELECT
*
FROM PR
ORDER BY "Application", "Dimension", "Parent", "Child", "Property", "Value"
Sample output
Full explicit query
Description
This is the query that pulls together everything – parent/child relationship, member properties, the lot. Note the central role of DS_Relationship as it is the table that drives the recursive table contents.
EPMA stores member properties in multiple rows per member. The only way to display that information is to either create individual columns for each property and display if valued or show them in a comma-delimited list. I chose, for the purposes of flexibility, to show them in that comma delimited list by member property table source.
Please note that i_parent_member_id, i_child_member_id, and most importantly i_child_member_order columns are included in this query to allow the sorting of the members in the proper parent/child order.
For those of you using PL/SQL, use the much simpler wm_concat function (if your Oracle instance has this installed) to get the properties in lieu of FOR XML PATH and STUFF and ISNULL. See, sometimes a language variant does make things simpler. Or harder.
Lastly, because of the complexity of the code and the time that the multiple JOINS require, I have limited this query to the Period dimension only. Take off, or modify, the WHERE clause and it can be changed to suit your purpose.
Code
As I wrote in the introduction, I wrote the parent/child portion of this query quite some time ago but I never went as far as I could with getting member properties. It can be a bit tricky, although most of the joined properties come from DS_Property_Member_Array; there may be more than I found.
You will note that this code contains a lot of CTEs and a lot of joining to get values. It is, charitably, workmanlike. I spent a ridiculous amount of time getting this all working and have a bunch of other things to do – again, I encourage your improvements.
Also, to get this to perform halfway decently, I used WHERE clauses when possible. To avoid losing my mind by typing the same thing over and over and over, temporary variables were created. This was written in T-SQL so you may need to change it a bit for PL/SQL.
USE HYP_EPMA;
-- Declare the dimension type
-- Values are: Local, Shared
DECLARE @DimType AS nvarchar(15)
SET @DimType = 'Shared' ;
-- Declare the dimension name
DECLARE @DimName AS varchar(150) ;
SET @DimName = 'Measures' ;
-- Declare applicaiton name
DECLARE @AppName AS nvarchar(255);
-- Values are: Master, appname
SET @AppName = 'Master' ;
-- Declare the dimension id
DECLARE @DimID AS int ;
-- Set @DimID through subquery to get the right i_dimension_id as it can be duplicated between Shared and Local
-- and across applications if the dimension name is duplicated.
SET @DimID = (
SELECT DISTINCT
D.i_dimension_id
FROM DS_Dimension D
-- Must do a LEFT OUTER JOIN because when @DimType = 'Shared' the D.i_parent_applicaiton_id is NULL
LEFT OUTER JOIN DS_Application A
ON D.i_parent_application_id = A.i_application_id
WHERE
D.c_dimension_name = @DimName
AND D.e_dimension_state = @DimType
AND A.c_application_name = @AppName
);
-- Main query
WITH
-- Not all dimensions/apps/dimension ids have DS_Property_Member_Memo entries
-- Aliases, UDAs, Attributes
PMA AS
(
SELECT --DISTINCT
PMA.*,
A.c_application_name AS "Application",
D.c_dimension_name AS "Dimension",
M1.c_member_name AS "Member",
M2.c_member_name AS "Property_old",
PMA.c_value AS "Value"
, CASE
WHEN M2.c_member_name = 'Alias' -- Alias
THEN M2.c_member_name + ' = ' + PMA.c_value
WHEN M2.c_member_name = 'MemberClass' -- Don't want this
THEN ''
ELSE -- Everything else
M2.c_member_name + ' = ' + M3.c_member_name
END
AS "Property"
FROM DS_Property_Member_Array PMA
INNER JOIN DS_Dimension D
ON PMA.i_dimension_id = D.i_dimension_id
AND PMA.i_library_id = D.i_library_id
INNER JOIN DS_Application A
ON PMA.i_application_id= A.i_application_id
AND PMA.i_library_id = A.i_library_id
INNER JOIN DS_Member M1
ON PMA.i_member_id = M1.i_member_id
AND PMA.i_library_id = M1.i_library_id
INNER JOIN DS_Member M2
ON PMA.i_prop_def_member_id = M2.i_member_id
AND PMA.i_library_id = M2.i_library_id
INNER JOIN DS_Member M3
ON PMA.i_ref_member_id = M3.i_member_id
AND PMA.i_library_id = M3.i_library_id
WHERE
PMA.i_library_id = 1
AND D.c_dimension_name = @DimName
--AND D.i_dimension_id = @DimID
AND A.c_application_name = @AppName
),
-- Not all dimensions/apps/dimension ids have DS_Property_Member_Memo entries
-- Member formulas
PMM AS
(
SELECT
PMM.*,
A.c_application_name AS "Application",
D.c_dimension_name AS "Dimension",
m1.c_member_name AS "Member",
M2.c_member_name AS "Property",
RTRIM(CONVERT(VARCHAR(4000), PMM.x_property_value)) AS "Value"
FROM DS_PROPERTY_MEMBER_MEMO PMM
INNER JOIN DS_Dimension D
ON PMM.i_dimension_id = D.i_dimension_id
AND PMM.i_library_id = D.i_library_id
INNER JOIN DS_Application A
ON PMM.i_application_id= A.i_application_id
AND PMM.i_library_id = A.i_library_id
INNER JOIN DS_Member M1
ON PMM.i_member_id = M1.i_member_id
AND PMM.i_library_id = M1.i_library_id
INNER JOIN DS_Member M2
ON PMM.i_prop_def_member_id = M2.i_member_id
AND PMM.i_library_id = M2.i_library_id
WHERE
PMM.i_library_id =1
AND D.e_dimension_state = @DimType
AND D.c_dimension_name = @DimName
AND A.c_application_name =@AppName
--ORDER BY "Application", "Dimension", "Member", "Property", "Value"
),
-- Not all dimensions/apps/dimension ids have DS_Property_Member entries
-- TimeBalance, SolveOrder, etc.
PM AS
(
SELECT
PM.*,
A.c_application_name AS "Application",
D.c_dimension_name AS "Dimension",
M1.c_member_name AS "Member",
M2.c_member_name AS "Property",
PM.c_property_value AS "Value"
FROM DS_Property_Member PM
INNER JOIN DS_Dimension D
ON PM.i_dimension_id = D.i_dimension_id
AND PM.i_library_id = D.i_library_id
INNER JOIN DS_Application A
ON PM.i_application_id= A.i_application_id
AND PM.i_library_id = A.i_library_id
INNER JOIN DS_Member M1
ON PM.i_member_id = M1.i_member_id
AND PM.i_library_id = M1.i_library_id
INNER JOIN DS_Member M2
ON PM.i_prop_def_member_id = M2.i_member_id
AND PM.i_library_id = M2.i_library_id
WHERE
PM.i_library_id = 1
AND D.e_dimension_state = @DimType
AND D.c_dimension_name = @DimName
AND A.c_application_name = @AppName
--ORDER BY "Application", "Dimension", "Member", "Property"
),
-- Not all dimensions/apps/dimension ids have DS_Property_Relationship entries
-- Consolidations, Data Storage
PR AS
(
SELECT
PR.*,
L.c_library_name AS "Library",
A.c_application_name AS "Application",
D.c_dimension_name AS "Dimension",
M1.c_member_name AS "Parent",
M2.c_member_name AS "Child",
M3.c_member_name AS "Property",
PR.c_property_value AS "Value"
FROM DS_Property_Relationship PR
INNER JOIN DS_Library L
ON PR.i_library_id = L.i_library_id
INNER JOIN DS_Application A
ON PR.i_application_id = A.i_application_id
AND PR.i_library_id = A.i_library_id
INNER JOIN DS_Dimension D
ON PR.i_dimension_id = D.i_dimension_id
AND PR.i_library_id = D.i_library_id
INNER JOIN DS_Member M1
ON PR.i_parent_member_id = M1.i_member_id
AND PR.i_library_id = M1.i_library_id
INNER JOIN DS_Member M2
ON PR.i_child_member_id = M2.i_member_id
AND PR.i_library_id = M2.i_library_id
INNER JOIN DS_Member M3
ON PR.i_prop_def_member_id = M3.i_member_id
AND PR.i_library_id = M3.i_library_id
WHERE
PR.i_library_id = 1
AND D.e_dimension_state = @DimType
AND D.c_dimension_name = @DimName
AND A.c_application_name = @AppName
--ORDER BY "Application", "Dimension", "Parent", "Child", "Property", "Value"
),
R AS
(
-- Parent/Child by dimension
SELECT
R.*,
l.c_library_name AS "Library",
D.c_dimension_name AS "Dimension",
M1.c_member_name AS "Parent",
M2.c_member_name AS "Child"
FROM DS_Relationship R
INNER JOIN DS_Library L
ON R.i_library_id = L.i_library_id
INNER JOIN DS_Member M1
ON R.i_parent_member_id = M1.i_member_id
AND R.i_library_id = M1.i_library_id
INNER JOIN DS_Member M2
ON R.i_child_member_id = M2.i_member_id
AND R.i_library_id = M2.i_library_id
INNER JOIN DS_Dimension D
ON R.i_dimension_id = D.i_dimension_id
AND R.i_library_id = D.i_library_id
WHERE
R.i_library_id = 1
AND D.e_dimension_state = @DimType
AND D.c_dimension_name = @DimName
--ORDER BY 3,5
)
SELECT DISTINCT
R.i_parent_member_id,
R.i_child_member_id,
--PMA.i_member_id,
R.i_child_member_order,
R."Library",
PMA."Application",
R."Dimension",
R."Parent",
R."Child",
ISNULL(STUFF((SELECT DISTINCT ',' + PR.Property + '=' + PR.Value
FROM R
INNER JOIN DS_Property_Relationship PR1
ON PR1.i_child_member_id = PR.i_child_member_id
WHERE
PR.i_child_member_id = R.i_child_member_id AND
PR.Child = R.Child AND
PR.i_library_id = 1
FOR XML PATH ('')
), 1, 1, ''), '') AS 'PR',
ISNULL(STUFF((SELECT DISTINCT ',' + PMA.Property
FROM PMA
WHERE
PMA.i_member_id = R.i_child_member_id
AND PMA.i_library_id = 1
FOR XML PATH ('')
), 1, 2, ''), '') AS 'PMA', -- The STUFF should only need one character so I have something weird here.
ISNULL(STUFF((SELECT DISTINCT ',' + PMM.Property + '=' + RTRIM(CONVERT(VARCHAR(4000), PMM.x_property_value))
FROM PMM
WHERE
PMM.i_member_id = R.i_child_member_id
AND PMM.i_library_id = 1
FOR XML PATH ('')
), 1, 1, ''), '') AS 'PMM',
ISNULL(STUFF((SELECT DISTINCT ',' + PM.Property + '=' + PM.Value
FROM PM
WHERE
PM.i_member_id = R.i_child_member_id
/*AND PM.i_library_id = 1
AND PM.Application = @AppName
AND PM.Dimension = @DimName */
FOR XML PATH ('')
), 1, 1, ''), '') AS 'PM'
FROM R
INNER JOIN PMA
ON R.i_child_member_id = PMA.i_member_id
AND R.i_library_id = PMA.i_library_id
AND R.Dimension = @DimName
LEFT OUTER JOIN PR
ON R.Child = PR.Child
AND PR.i_library_id = R.i_library_id
AND R.Dimension = @DimName
LEFT OUTER JOIN PMM
ON R.Child = PMM.Member
AND R.i_library_id = PMM.i_library_id
LEFT OUTER JOIN PM
ON R.Child = PM.Member
AND R.i_library_id = PM.i_library_id
ORDER BY "Library", "Application", R.i_parent_member_id, R.i_child_member_order
Sample output
You will note that when I query the Age dimension the output is different – that is a result of Local (local to the application, not shared) dimensions versus Shared (potentially shared across multiple applications and even technologies). I made changes in Shared to try to test some of the properties.
I should note that AreaCode and Bozo are attribute dimensions. The only place that actually seemed to tag these dimensions as attributes is DS_Transaction_History. I could have joined to that as well to get the Attribute tag but again, someone else can take up the torch on this.
A note about the properties I coded for: most of them are accessible through DS_Property_Member_Application although some are directly available in other property dimensions as you will see. I certainly left out some, e.g., no HFM, I tested this against ASO Essbase, etc. so this is by no means complete but it is certainly a good start.
These are a bit hard to read as some much is output. To give you a better view of it, please download the Excel file that contains the query results here.
Local Age
Shared Age
Shared Measures
What’s missing
At the end of 20 pages in Microsoft Word and yr. obt. svt. missed something? Alas yes, and it is a whopper – all of the above only captures explicitly set properties, not inherited ones. At least by default, EPMA tries to save users effort by automatically inheriting properties as set (and that too can be inherited from the next ancestor level up) at the parent level. It is quite possible to have a default property at the top of the dimension and every single member from there to the bottom of the dimension has the same property.
And, as noted above, those inherited properties ar exactly what this query does not contain. I feel kind of bad about not figuring this out but I spent an enormous amount of time figuring out how to query the explicitly set properties. I have a life, sort of, too and of course clients and other side projects. Yes, an excuse, and a pretty lame one at that.
I would be obliged to whoever manages to figure that one out. And tells world+dog. I have to believe it can be done in SQL but maybe it really and truly can only be done in the web application layer. Would someone please figure it all out?
Be seeing you.
Hi Cameron,
ReplyDeleteThanks for your fantastic blogs.
Has the property update issues between planning / bso and aso within EPMA been fixed in 11.1.2.4? I am hearing that still the storage property updated in one automatically flows to other two as well and there is no way to centrally maintain different data storage for a given member i.e. "store" for planning and "dynamic calc" for ASO within shared library? please share your thoughts. Thank you.
Regards,
Vijay