Use Microsoft Office and SharePoint to Extract Data from WorkEngine's Reporting Database – Part 1

I want to take some time to talk about Microsoft Office 2010 and ad-hoc reporting in WorkEngine.

In this first of two posts, I will review the basic premise of ad-hoc reporting: a business user, or end user’s ability to directly manipulate data – specifically, data from WorkEngine’s SQL database, and data in Microsoft Office Excel 2010.

To do this, I will use the example of an Office Data Connection file with the WorkEngine Reporting Solution and provide a practical outline for creating a data connection file that extracts information from that particular SQL database.

The concept of directly connecting to a database is extensible, and the needs of an organization should be well-thought out in order for the technology to be really useful and well-performing. The many details of administering and architecting an ad-hoc reporting solution that uses Office 2010 to connect to a custom SQL database are probably impossible to blog, but I will create a second part to this post that covers the ideas behind SharePoint administration and ad-hoc reporting architecture, with these technical assumptions and best practices in mind.

Getting back to the topic at hand, the outline I will walk you through now is technical by nature, but you just need to know that what you are doing is opening a door to your data. I hope you will understand the basic concept of how a data connection works at the end. Also, my hope is that you will feel confident in extracting your own data from the LOB applications your organization uses.

To start, I am using Excel 2010 and WorkEngine 2010 (which is built on SharePoint 2010). Yes, only the latest and greatest for this gal! However, if you are using an earlier version of any of those applications, the concept is adaptable. (If you are a 2007 WorkEngine/SharePoint/Office customer, let me know how I can help with the correlation.)

For practical application of this exercise, you will need to have a WorkEngine site collection, with a reporting database, and that setup information. If you are a hosted EPM Live customer, the information you need was sent to the Site Collection Administrator in an email, similar to the one below.

WorkEngine Reporting Information: Sample Email

If you are not an EPM Live customer, but would like to follow along with practical application of the outline, create a trial site by filling out this form.

If you setup your own WorkEngine site collection, use this reference table to associate the types of information you need to participate with other commonly used names:

#
Item
Also known as
Examples
1
Server Name
Data Source, Connection String
EPM Live Hosted: mydb.epmlive.com,42069
In-house/Onsite:  *per  IT policy
2
Database Name
Initial Catalog, Database
EPM Live Hosted: _RPTworkenginesite
In-house/Onsite:  *per  IT policy
3
Login
Username, User ID
EPM Live Hosted: RPTworkenginesite
In-house/Onsite:  *per  IT policy
4
Password
Passwords are passwords…
EPM Live Hosted: *randomly generated
In-house/Onsite:  *per  IT policy

Connection String Information: Reference Table

Here is the practical outline that I would recommend to get started with extracting data.

Open Microsoft Excel 2010 to begin.

Go to the Data tab. Look for the From Other Sources button and choose From SQL Server on the menu.

Initiate the Data Connection Wizard

Enter your database Server name and the Log on credentials when prompted.

<pThis example uses the online database server name: mydb.epmlive.com,42069, and a demo site name:

Data Connection Wizard: Connect to Database Server

Notice the available databases in the next dialogue box, which is based on the server you entered. Pull the drop down menu and select your database name.

The list that is populated in the larger window, once you select your database name, is a list of all the tables now available for that specific database. Find the one that contains the data you want to extract.

For my EPM Live customers: Here is a summary of our most popular EPM and Application Management template list names and corresponding table names. There is a link at the end of this post for you to download Reporting Starter Kits that leverage some of these lists.

Default WorkEngine List Name
Corresponding Table in Reporting Solution
Project Center
LSTProjectCenter
Task Center
LSTTaskCenter
Issues
LSTIssues
Risks
LSTRisks
Actions
LSTActions
Changes
LSTChanges
Resources
LSTResourcePool
My Timesheet
RPTTSData
Applications
LSTApplications
Backlog
LSTBacklog
Releases
LSTReleases
Test Cases
LSTTestCases
Features
LSTFeatures

>EPM Live Customers: Popular WorkEngine template list table names

I’m using the second part of the “Your Connection String” bullet from the EPM Live Online Services email to select the database name, and also choosing the WorkEngine Timesheet table.

If you don’t Connect to a specific table during this step, you will be prompted to select the table you want to extract when the data connection is refreshed.

Import Data kicks off after the Data Connection Wizard closes

Data Connection Wizard: Select Database and Table

Side note (from above screenshot) on RPTLog, RPTPeriods, RPTResLink, RPTSettings, and RPTWork: these tables are part of the WorkEngine Reporting Solution base schema, which has a point-in-time snapshot capability. Ultimately, the solution makes trend reporting across the platform successful, as it could include any SharePoint list. See the links at the end of this post for more info on the WorkEngine Reporting Solution.

The last screen for creating the Data Connection has a lot of detail. I will explain these areas one by one:

Data Connection Wizard: Save Data Connection File and Finish

  1. File Name – these file types always use the extension ODC (for Office Data Connection). If you are storing the file locally, use as many words as you need to in order to have a clear description. If you are planning to store the file on a SharePoint site, consider that the file name will be part of the URL – you may want to make it as short as possible while remaining descriptive.
  2. Browse – save this file to a location other than the default. The Windows 7 default location is C:Users[username]DocumentsMy Data Sources.
  3. Save Password in file – a handy option for connections using SQL authentication, but this option will warn you if you don’t have encryption, which may or may not be a security requirement at your company. Check with your IT group if you are not sure.
  4. Description – standard Multi-line text field. 255 character max. Write a description that explains how the data connection is meant to be used. That way you can remember why you set it up later!
  5. Friendly Name – in my experience, this comes into play when you import data as a table, and then reference the table in the same (or another) workbook. Excel will reference the outbound connection to the table by this ‘Friendly Name’ property.
  6. Search Keywords: I haven’t used this but it seems pretty straightforward.
  7. Always attempt to use this file to refresh data – very handy option for deployments where the Administrator is configuring reports for a business on the back-end and then pushing them out for consumption. We will go into that setup in part 2.
  8. Excel Services: Authentication settings… – I may cover these options in a later post. Excel Services is a SharePoint 2010 Enterprise feature, and is optional as part of the architecture for data extraction from a SQL warehouse.

I would highly recommend that you make a note of the configuration options that you chose before you click Finish!

 

 

Once you click Finish, the Import Data wizard pops up immediately. You will most likely want to use the connection to generate a Pivot Table or a combo Pivot Table + Chart, but you can do a flat extract as a table as well.

Here is a non-exhaustive list of reasons to import data as a Table:

  • When you need a local copy of the entire database table in your workbook in order to build charts or pivot tables from a point-in-time version of the data
  • When you need to insert a custom formula(s) that should be dynamically calculated based on inserted or removed rows from the source data (which would happen on a refresh)
  • When you need to perform ad-hoc queries of the database table for different views of the information
  • When you need an export of the entire database table for use (import) to another LOB application

Import Data kicks off after the Data Connection Wizard closes

By default the data goes into the Existing worksheet, and looks something like this:

Result of selecting Import Data as Pivot Table and Chart Option

Technically, we are done with what we set out to do at this point, which was to extract data from SQL into MS Office.

But, now that you know how easy it is to unlock that door, I know that what you really want to do is create a snazzy report.

So, bust out a few drag & drop moves, spice it up with a pre-designed MS chart layout……and… there it is! Comin’ in hot… :)

But just in case you need a bit more guidance, here are the references that I used to get savvy when I first started:

EPM Live product customers: you can download WorkEngine Reporting Starter Kits from the EPM Live KB: http://kb.epmlive.com/DownloadsandUpgrades.aspx

Next time, I’ll take a look at the Administrator’s burden in the ad-hoc reporting setup, and lay out how to setup centralized data connections with SharePoint 2010.