Want to know The Truth About CPM?

15 September 2014

EPMA, my memory, and some new (and old) queries

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.

1 comment:

Unknown said...

Hi Cameron,

Thanks 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