Saturday, 10 January 2015

Testing report performance in Workday

As someone who supports users who write/run queries in PeopleSoft, I thought I'd share a few features that I like about Workday report writing, functionality that doesn't exist in PeopleSoft.

1. The ability to 'test' a report while building/editing it.

Best practice in query/reporting writing dictates, 'test early, test often' to ensure that you are not breaking something or building it incorrectly.  Often, when a user is building something terribly complex in PeopleSoft, however, testing such a query can take 10-15 minutes per test run, which can be a hassle if you're adding multiple criteria, expressions, outer joins, etc.  *The assumption here is that you have a large PeopleSoft database.  Small instances may not have such obstacles.

Workday delivers this 'test' button when you're building or editing a report.  In this case, I'm editing a Workday delivered sample report, Employee Birthdays:

When you test a report, it does a small run of it, delivering 10 results.  In this test output, I may notice that I have blank birthdays or terminated people in my report, thus causing me to stop and consider another data source, before I'm too deeply into the report build:

Here are the data sources, by the way.  It's been over a year since I formally took Workday report writer training and I'd say that it one of the more difficult things, to choose the right or most efficient data source for what you want to do as there are just so many of them, and many are similar.

Once you're happy with your report, when you use the 'Run' button, you'll get the whole dataset, rather than the 10 test rows:

 2. Analyzing report performance

There are many ways to build the same report in Workday.  For example, let's say that I want to run a report on active employees.  Referencing the data source screen above, I could build a report on 'All Active and Terminated Workers' and filter out the terms, or I could just build directly on 'All Workers' which would be more efficient.  Both (and a variety of other) options are possible, but some are more efficient than others.  To measure report performance, Workday has recently (in the version 20ish range) opened up some options that were previously only for Workday internal use.

So check this out...I test run my report, under the 'Test Report Performance' menu.  Here I'm running a summarized headcount report:

 I get some output, great!

Now, I can see some logs.  If I would have skipped the above step, I would not have had these available to me:

Here are the log outputs:

It's mainly self-explanatory, but if your report is running slow, you're able to analyze where the hold up is located.  For example, my report here has no sorting or filters, but potentially if you were doing a lot of filtering or sub-filtering rather than using an efficient data source, that would quickly become apparent here.

In my current PeopleSoft world, to do the equivalent troubleshooting, I'd first eyeball the query and go through the chosen fields, filters, expressions, etc. to see if anything is clunky.  I'd review the table joins and tables used, to see if they are bulky ones.  Finally, if I didn't see anything with my trained eye, I'd need to take it into a SQL tool and start to take it apart line by line to see where the slow down is occurring, potentially with a database admin to look at the server side, if I still couldn't get to the root cause.  As you can imagine, I'm pleased as punch that Workday puts these logs out there, as potentially it makes it easier for the support resources to get to the needed information and fix any issues.

After a hiatus, I'm ready to start writing again.  If you've read this far and if this has been helpful and you'd like to see more, please click any of my ads.  It's nice to know that someone is reading me.  :)

No comments:

Post a Comment