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:

The list is loaded with a simple button press.

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:

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:

Which takes you to:

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:

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.