Top 10 Gotchas of BI in a SaaS World

As we undergo a BI implementation with a cloud-based ERP provider, I thought it would be interesting to share some of our insights.  As you know from previous posts, Georgetown has been undergoing a large ERP implementation on the Workday ERP.  We are now live on both HCM and Financials.

The Business Intelligence (BI) project has been running in parallel to this effort and I wanted to share some initial findings in the event that it may be helpful for others.

Here are our top 10 gotchas:

#10 – Be cognizant of time zones.  When you are setting up incremental nightly data pulls, ensure that the user account that you are utilizing is in the same time zone as the underlying tenant.  Otherwise, you might spend hours validating data that may be hours out-of-sync (e.g. user account to extract data is in EST and the tenant is in PST).

#9 – Chop the data into sufficiently sized extracts to prevent timeout issues.  This may be less of an issue on a nightly basis, but is important if you are loading historical data or preparing for a time in which there is a large integration into the ERP.  Large integrations (e.g. from your Student Information System into your Financial System) can cause a nighly extract file to balloon and subsequently fail.

#8 – Send your team to deep ERP training – immediately!  Be prepared to know the ERP as well as the team administering it.

#7 – Ensure that your BI team receives the appropriate security that they require in the SaaS ERP.  If this gets into policy-related issues, ensure that you work these through with the respective owners in advance.  You may have to work through operational protocols and procedure that are not expected.

#6 – Plan a strategy for how you will create BI related content in your SaaS-based ERP.  Typically, this development needs to occur in a lower tenant and migrated to production.  How will you access these environments?  What is the path to migrate content?  Be prepared that some sandbox tenants may refresh on a weekly basis (i.e. BI content may not reside there for longer than a week).  Consider the notion of development in a dev tenant, migration to sandbox (which may refresh weekly), and then to production.  The timing of this process should feed into your planning.

#5 – Set up BI extract notifications.  We setup a specific email address which routes to our on call team.  These alerts notify our team if any of the extracts failed so that we can quickly extract and reprocess the file as needed.

#4 – Prepare to either learn an API in and out, or work with custom reports in the SaaS ERP.  Remember, there will be no direct connection to the database, so you’ll need to find alternate ways of efficiently extracting large amounts of data.  In Georgetown’s case, we wrote custom versions of reports from our SaaS-based ERP and then scheduled their output to a XML or CSV format.  These files are then processed by the nightly ETL routine.

#3 – Create a portal for consolidation and collaboration of reporting deliverables.  We set up a portal which provides instructions, documentation, links to metadata, ability to ask for help, report guides, etc.  Make this easy for your end-users to consume.

#2 – Test, Test, and Test.  SaaS-based ERP tenants typically go down one night a week for patches and maintenance.  This can be problematic for nightly BI extracts.  Make sure that you are aware of the outage schedules and can plan around them.  ETL routines may need to start a bit later on the day in which maintenance occurs.

#1 – Create an operational structure for the ongoing needs of the BI function in advance.  Georgetown is embarking upon a ‘BI Center of Excellence’ that will handle this sort of cross-functional structure which will be comprised of subject matter experts which represent different business units.  This will help the business units to continue to drive the development of the platform and allow IT to play a supportive role.

 

Business Intelligence with Workday

As part of my current project at Georgetown University, we have a requirement to extract large volumes of data for business intelligence purposes from Workday.  We’ve been running Workday HCM for about 2 years and plan to go-live with Workday Financials this July.  Historically, the university has been largely an advocate of on-premise solutions – and Workday represents one of the first large steps we’ve taken to move ERP data “into the cloud”.

What does this mean for business intelligence?  Let’s dive a bit deeper.

Provided that Workday is a SaaS (software-as-a-service) vendor, a direct on-premise database connection isn’t an option.  That being said, we had to explore options to get data of out of the Workday platform – and to do it quickly and efficiently.

Below are a few data extraction methods which we considered:

1. Utilize Workday Studio and leverage the web services-based Workday API
2. Join required Workday objects in a custom report and expose them as a RaaS (report-as-a-service)
3. Utilize Informatica Cloud

We quickly ruled out option #3.  We enjoy using Informatica and thought this might be a quick win.  Unfortunately, when aligning the data options available in Informatica Cloud with our requirements – we had several gaps (i.e. missing data, data was not defined down to the appropriate grain, etc).  Informatica offered to partner with us to expand capabilities (much appreciated), but unfortunately our timelines did not permit this to occur for our current project.  We continue to use Informatica PowerCenter as our best-in-class data integration tool.

So – which option was better #1 (API) or #2 (RaaS)?  This question was kicked around for quite some time.  After much deliberation, we decided on option #2 (RaaS).  You’re probably already asking why.  Isn’t the API faster?  Below were the results of our analysis:

1. The Workday API was hitting a governor limit for our tenant.  This may have been a specific configuration setting for our tenant but it was prohibitive to pull large amounts of data out of the API.  The API was timing out for certain data pulls at the 2 hour mark.  It also required an API developer to manage.  Workday has done a nice job of documenting their API.  We found this to be extremely helpful along the way.  I’m sure we’ll have a future use for the API.
2. Custom Reports allowed us the flexibility to join multiple objects that met our requirements.  The API calls were limited to what is publicly available in those calls.  We have a bit more flexibility to combine objects which support our requirements in Custom Reports.
3. Custom Reports are built using the Workday GUI.  This is an added benefit at it did not require a developer to maintain the API calls.  We can have a functional resource maintain the custom reports which are used for data extraction.
4. Custom Reports (so far) have been able to support the large data extraction that we require – without timing out.  An example is that we have been able to extract about 200K bi-weekly payroll records in about 12 minutes.  We will use this Custom Report-delivered-by-EIB mechanism to support incremental loads to our Finance and HCM data warehouses.

Using Workday’s Enterprise Interface Builder (EIB), we have been able to schedule the Custom Reports and have them output to a text file format which is conducive to load into our business intelligence platform.  The Workday Enterprise Interface Builder (EIB) tool provides an easy-to-use graphical and guided interface to define inbound and outbound integrations without requiring any programming. Used by the majority of Workday customers, the EIB can be used by both business and IT users to address a variety of integration needs.

Our project is still underway.  I’ll write another post with more details as our project moves further toward completion.   If you have any thoughts or suggestions regarding Workday data extraction / integration, please feel free to comment.  Suggestions and comments are welcome!