If you have a lot of Measures that need to be updated on a regular basis, you may find it easier to import the actual values (and thresholds) either from a spreadsheet or by connecting to an external system/database.
Both are possible. This guide covers importing Actual Values from a spreadsheet or csv file. For database linkage, please contact Intrafocus.
Create Your Scorecard
First you need to create a scorecard.
There needs to be a destination for the Actual Values of every KPI/Measure that you want to update.
For this guide we have created a simple scorecard containing Perspectives, Objectives and Measures.
If you need help creating a scorecard, go to the Adding an Organization and Creating a Scorecard user guides.
A separate, but connected application is used to import actual values.
Copy the URL of your system up to and including .com, go to a new browser window, paste the URL and add /connect and hit enter. The result should look like this:
Use the same username and password that you use for the main application and log into Connect:
Connect has a very basic interface as its only purpose is to import data from other systems or spreadsheets/csv files. The spreadsheet/csv Data Source has already been defined so we can go straight to Imports.
Click on Create New.
The Edit Import screen will appear. All of the lozenges will be grey indicating nothing has been updated.
The Basic Information screen will appear. Give the import a name. Try and be specific, add a date if required.
At this point you will need to have an import spreadsheet ready. The application will accept several formats. Rows and columns can be excluded. However we advise, for best results, you keep the format as simple as possible.
For this example we are assuming that Actual Values are added Monthly.
There are two main formats described in this user guide:
- Updating one month and including thresholds – this is useful as a standard monthly upload that includes variable thresholds.
- Updating using historical data – this is useful when you want to load several months of data in one go.
The format for monthly historical data should look like this:
The format for single month data with thresholds should look like this:
Things to notice in the single month version:
- The dates have been added as a row. These can be omitted and the system will default to the current date.
- Threshold can be added for the measures that have variable monthly thresholds.
- Notes can be added.
The following example shows how to import single month data. If you want to add historical data (or future data e.g. forecasts or targets) the same process can be used with the historical data spreadsheet/csv. Be sure to use the right configuration parameters during the Source Data view phase.
On the Basic Information screen, click on Browse and select the spreadsheet from your computer or shared location. It will appear in the File box.
Click on OK at the top right of the screen.
The Edit Import screen will appear. The Basic Information has been coloured blue indicating that part of the process is complete.
Click on Source Data.
Your file will appear. Check that the Headers for each column are in the correct place. The application will match header labels when they are exact.
There are several options you can use when importing depending on the spreadsheet/csv file structure you have used:
For this example we have used dates in rows so have checked the radio button – Use the date specified in each data row for imports.
If you are importing historical data, you would select. Use a date header row for imports.
Check each of the options have defaulted correctly and click the OK button.
The Edit Import screen will appear. The Source Data lozenge is now blue indicating that part of the process is complete.
Click on Destination Metrics.
The Destination Metrics screen will appear:
Click on the Organization where you have created the scorecard (under Organization Tree). In our example, this is International Limited.
The scorecard will appear in the right-hand panel. Click on the scorecard (or on the elements of the scorecard you are interested in) and the Measures will appear in the bottom part of the screen.
When you are happy all of the Measures are included, click the OK button at the top right of the screen. The Edit Import screen will re-appear:
Click on Mapping (from the progress screen) and the Mapping screen will appear:
Where there are exact matches, the application will match the spreadsheet names to the measure names in the application’s scorecard.
In our example, Net Operating Profit does not match with Net Profit so the item can be dragged and dropped into the correct place.
Check that all of the Measures you want to map are mapped correctly.
Click the OK button at the top right of the screen.
The Edit Import screen appears telling you that Mapping is complete. You have the option to Schedule a regular update if you wish.
Scheduled Updates – Important – these will only work if you put your data in a place where the application can reach it. The application cannot make calls to areas behind a firewall. Typically, data can be put temporarily into a DMZ for the update process.
Click on the Save button at the top right of the screen and the following screen will appear:
The import has been created and mapped. This is a one-off process (unless the scorecard changes). From now, your data file can be uploaded and updated on a regular basis.
To run the update, click on the Run Now button.
If the run is successful a green Success box will appear together with the Last Run date.
If the run is unsuccessful an amber or red status will appear. Click on the amber or red box to find out why the run failed.
Click back to the main application. Click on Scorecards and then select the scorecard you have just updated to check the update has succeeded.
In our example, you can see that the April data has been loaded. For the KPI/measure Net Profit we also added the Goal/Red Flag thresholds:
A completed measure in a scorecard might look like this: