Thursday 13 August 2015

JDE batch performance analysis from EXCEL

What do you mean from excel?  Let’s be honest, I could probably write an ERP in excel, it is THAT good.  I know that every company I work with would probably topple over if they could not use excel anymore.  How many people have made the leap to google docs, but cannot let go of excel because of all the proprietary code, connections and calculations are held in excel! 

Anyway, I love it and want to show you something that I’ve done with excel, I think that I’ve shown this before, but I just did a cool enhancement!

So, I’ve been working with batch performance for a client, and it’s hard work to work out what is slow and what is not, what are the trends and where should I start – or is it?  Not really.  I’ve blogged previously about advanced queries over F986114 which can do comparisons over periods of time, compare month end to year end, compare day to day and week to week.  That is cool.  I also have queries that can go over F986114 and look for jobs that took longer than 4 minutes for example.  I use a ODBC connection to do this in a speadsheet, it gives me a list of jobs that took a while to process.  I sort this by time taken and then then a distinct list of the UBE|VERSION combo:

image

The list is loaded with a simple button press.

image

Button 1.  I can then choose a report|version combo and click “graph runtime history”, this allows me to see whether the averages were an aberration and allows me to see trend data over time:

image

What I can then do also, is I have a formula that uses the parameterised URL functionality in JDE, so I don’t have to go search through heaps of “job execution details”, I go to the page that I want:

image

Which takes you to:

image

Nice! As long as you are logged in once, you’ll be able to get there.

The only parameters you need are the logic server name and the job number, which is in my list:

=+HYPERLINK("http://e1webprod.myriad.local:8080/jde/ShortcutLauncher?OID=P986114A_W986114AA_ZJDE0001&FormDSTmpl=|1|2|&FormDSData=|E1APPPRD|"&K13&"|","details")

So the formula for excel to build the launcher looks like the above.  Remember that the parameterised URL had a version for the P986114A – which was wrong and did not work I had to change that:


image


So you’ve just built a super powerful spreadsheet for historical UBE performance analysis in JDE!


You could do a poor mans/womens version by taking a dump of the F986114 (no ODBC) and doing similar things.

2 comments:

Anonymous said...

Hi Shanon,

I thank you for all your helpful posts in your blog, would it be possible you share with us your excel template, specially the macro in the excel .

Chris said...

Shannon,

This may be a dumb question, but if we haven't activated the UBE audit logging (F986114/F986114A), then where ELSE is this information stored? If I look at jobs on the Submitted Job Search and click Execution Detail, I can see audit data...so, I suspect it must be somewhere, but my F986114/F986114A tables are empty.

We are on Apps 9.1, Tools 9.1.2.

Regards,
Chris