EPMA queries for member properties and more – Understanding EPMA tables Part III

Cameron Lackpour and Dan Pressman

Three of us were working on developing some EPMA SQL queries (or just say, trying to make sense of the EPMA backend tables).

Cameron was kind enough to share the queries he and Dan developed and I was supposed to start/continue with my EPMA series.

I failed to do so, I can say that I had a tight schedule (it is a real fact) and all sort of valid excuses, however I’m not going to say those.

Better late than Never 🙂

Take a look at the below given posts where few EPMA tables are explained and two hacks

Getting Member formulas from an EPMA Application – Understanding EPMA tables Part I
Change History Years in Planning on an EPMA application – Understanding EPMA tables Part II

I’m not sure whether we’ll be seeing EPMA in a near future (Data synchronization is going away soon, who know what is next). For those EPMA die-hard fans here we go.

If you would like to see all the EPMA application and their Application Class (Planning, Essbase, HFM,….)

You can use the below given SQL

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

and here is the output

DS_PROPERTY_APPLICATION_ARRAY stores the property information of an application. Application Class is a Dimension in EPMA and has a value 4. Different Application classes has their own values.

Let’s look at the ID’s of different Applications.

SELECT 
  M.c_member_name,
--  PMM.*,
  pmm.i_member_id,
  pmm.i_prop_def_member_id,
  pmm.x_property_value
  FROM DS_PROPERTY_MEMBER_MEMO PMM
INNER JOIN ds_member M
 ON  PMM.i_member_id=M.i_member_id
      AND PMM.i_library_id=M.i_library_id
WHERE PMM.i_library_id=1
      AND PMM.i_dimension_id=4; --application class id

X_PROPERTY_VALUE gets loaded from a physical location when you start EPMA and it holds some surprises. (more to follow in coming blog posts)

Now you know what application you have 🙂 let’s look at their properties

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

SQL
and here is the output

You’ll be surprised to see the information returned by that SQL (I was surprised that it didn’t return the StartYear 😉 )

More fun, here is a query which will give you all the “SmartList” members.

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
WHERE "Value" = 'SmartList'

Output

The same query can be used to pull all members which has Time Balance. Change the where to “WHERE “Property” = ‘TimeBalance’;”

If you read Part I ,I did talk about a SQL which will give you all the members which has a formula in EPMA. However that is not the only information which is there in DS_PROPERTY_MEMBER_MEMO table, you’ll have to wait for the next post to know more 😉