Visual KPI supports the use of Microsoft Excel as a data source. You can create interfaces to existing Excel worksheets or workbooks, effectively using them as a Microsoft Excel interface.
Transpara does not recommend using Excel as a production data source for several reasons. But if you choose to use a Microsoft Excel interface, we have some best practices and tips for you.
Still want to read data from Excel? Here’s how it works:
The Microsoft Excel interface is made up of a Web service and three Excel scripts (Get Current Value, Get Trend Data, and Get Historical Value).
The SQL scripts each use four fields, two of which are used in your KPIs:
- LookUp (this is the tag name)
- Value (this is the value associated with the tag name)
The other two fields are used to find the correct value:
- Sheet1$ (the table name)
- date (the date/time the row was last updated)
These are generic field names.
How Visual KPI Reads Data from Excel
When displaying KPI values from Excel as a data source, Visual KPI reads the tag name associated with a specific KPI column (such as Value, Target, Low, etc.) and then searches the Sheet1$ table for a row that has the same name in the LookUp column. When the interface finds the correct row, it retrieves the Value in the same row and returns that value to Visual KPI.
When displaying a KPI’s trend using a Microsoft Excel interface, Visual KPI reads the tag name associated with a specific KPI column (such as Value, Target, Low, etc.) and then searches the Sheet1$ table for each row that has the same name in the LookUp column and where the date (timestamp) falls within the correct time range specified as STARTDATE and ENDDATE. Time range is based on the current Trend Range in Visual KPI. When the interface has completed its search, it retrieves the Value and date (timestamp) in the same row and returns these to Visual KPI, which in turn, draws the trend graph.
Using Visual KPI Server Manager with a Microsoft Excel Interface
To use a Microsoft Excel interface with Visual KPI, you need to specify the connection parameters for your database and modify the queries to reference the actual field names for SampleData, LookUp, Value, and date.
Visual KPI Server Manager allows you to make these changes during installation of the Microsoft Excel Interface. Should you need to modify the connection parameters after the interface has been installed, you can use Visual KPI Server Manager to make the changes.
If you want to test the Microsoft Excel interface before you connect it to your real Excel data, you can create and work with the Excel Demo file.