I’m in the process of hacking EPMA and I’m lost in the vast (madness) structure of EPMA tables.
I was under an impression that I’ll be able to convert all the Classic Planning hacks (like history year addition, change weekly distribution, change start month) into EPMA hacks.
For those who are interested have a look at Cameron’s post on changing weekly distribution of an EPMA Planning Application here.
To be honest at this point of time, I don’t know whether I’ll be able to achieve what I started.
I’ve seen people adding a relationship status as “Complicated” in Facebook and I think that matches the relationship I’m having with EPMA now 🙂
It’s a hate and hate and hate and little love relation.
Now you’ll be wondering why I love it, it just puzzles me and scares me with the number of tables it updates. (I’m pretty sure you’ll be lost when you start playing with it), I don’t think you need a reason for the hate part of it.
I thought of starting a post on EPMA tables so that I won’t forget what I was able to understand while analyzing (messing up) the tables.
So let’s start (it is going to be a bumpy ride, so fasten your seat belts)
DS_APPLICATION
The moment I looked at this, I was confused why do I need to keep the history of all the deployments.
Oh yes each time you deploy a planning application this table will add two entries
- Shared Library
- Application Library
How’ll you know which one is the latest, greatest application, it’s easy (or is it) I_LIBRARY_ID is 1.
I’m going to skip to a different table, just to explain I_LAST_DEPLOYED_TRANSACTION_ID and I_FIRST_DEPLOY_TRANSACTION_ID, these IDs are coming from DS_TRANSACTION_HISTORY
This is a biggy with 31 columns in it, it has records for everything that you perform in EPMA. It even records the session and tells you who did the update/delete/insert.
Join TRANSACTION session with SM_SESSION it will give you user name, user id (shared services id), logon time. (wow that’s too much of information 😉 )
I think this is going to be a start of a new series, so let me give you a quick SQL which can be used to pull member formula from an EPMA application.
SELECT b.c_member_name,c.c_dimension_name,a.x_property_value
FROM ds_property_member_memo a,
ds_member b,
ds_dimension c
WHERE a.I_LIBRARY_ID =1 ANDa.i_application_id=2
AND b.i_library_id=1
AND c.i_library_id=1 andc.e_dimension_state='Local'
AND a.i_member_id = b.i_member_id
AND b.i_dimension_id=c.i_dimension_id;
Use your application’s id in the highlighted area (the one which has library id =1). You can use Local or Shared depending upon your application dimension state.
to be continued…..
