I’m in the process of hacking EPMA and I’m lost in the vast (madness) structure of EPMA tables.
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)
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.
FROM ds_property_member_memo a,
WHERE a.I_LIBRARY_ID =1 AND
AND c.i_library_id=1 and
AND a.i_member_id = b.i_member_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…..