How to use an Excel data source for KPI’s in Datazen

Happy weekend, folks.  As mentioned in the guest post I did with Rob Collie, a folder of Excel files can be used as a data source for dashboards with Datazen.  However, when it comes to KPI’s, there isn’t out of the box support to use Excel as a data source for those.  But you can still use Excel if you setup a custom data connector to do so.  One caveat with this approach – what’s we’re basically doing is allowing Datazen Server to use Excel like it would a normal SQL Server data source.  This means you’ll need to write at least some basic T-SQL statements to actually bring back data from your Excel workbook.

Assuming you know how to do that, here are the steps you need to follow to enable this –

1. You need to download and install the Microsoft Access Database Engine 2010 Redistributable (choose the AccessDatabaseEngine_x64.exe option) on your Datazen server.

2. Create a new folder named OLEDB in the following location (this assumes you setup a custom data provider with my previous post.  If you haven’t, do that first.) –

C:\Program Files\Datazen Enterprise Server\service\dataproviderschemas

3. Download the following file – Excel.zip and extract the XML file to the folder you just created.

4.  Now login to your Datazen Control Panel and go to “KPIs” for the hub you want to setup with your KPIs.

5.  Click “Create New Group”

6.  You should see the new provider you setup in the previous steps listed as “Microsoft ACE OLEDB 12.0 Excel 2007-Newer” –

Enter a name for your KPI group, and leave the HDR=Yes item under Extended Properties if your Excel file contains a header row.  Otherwise, change it to “No”.

Next, in the data source area, put the file path of the Excel document you wish to you use.  Make sure it is some place the Datazen service account has access to.  Test the connection to make sure it works, and hit next if you see the “Connection Successful” message –

Select the groups who should have access to the data source, then hit Finish.  This will bring you back to the KPI menu with your new KPI hub showing –

If I click “Create New KPI”, I’m presented with the KPI Creation screen.  On the right hand side of the screen, I see my KPI Value/Goal/Status fields I can use my Excel data source for.

If I select the dropdown for “Value”, I can choose “Execute Query” and a “Define Query” link appears.  I need to click it to write my query –

Now I can write my query for this KPI Value.  The table name equates to the name of the tab in the Excel file, but you need to have a ‘$’ appended to it.  A sample query would look like this –

If successful, it should bring back a single result to be used with that KPI –

Hit ‘Update’ to save your query and return to the KPI setup screen.  You can now repeat the process for the Goal/Status/Trend Set items if you wish, or you may set any or all of those manually. 

If you want to learn more about KPI’s and how to set them up in Datazen, refer to the Datazen documentation.  Hope this was helpful, and enjoy the rest of your weekend!