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