This article covers all of the advanced functionality for importing KPI and Initiative values. For information about how to start an import, or how to easily import values that are already in a specific format, please see the Simple Value Imports (KPIs and Initiatives) article.
The first step when importing KPI or Initiative values is to choose what type of import you want. When you want more powerful data import options, choose Advanced Import. This turns your value import into a 9-step wizard with full export, transform, and load (ETL) capabilities.
The first thing you’ll need to do is choose a data source. For example, you can choose Database...
... and write a SQL query. You can choose any existing database connection, or you can click "New Connection" to set up a connection to a variety of databases:
- Microsoft SQL Server
- SAP HANA
- IBM DB2
You can manage all import connections in the administration section.
Or you can choose Spreadsheet...
... pick one of several ways to get the spreadsheet into Spider Impact...
… and then do any last-minute cleanup. In this example we’re importing from an Excel spreadsheet, so we can choose a worksheet on top. Many spreadsheets have header data that you don’t want to import in the first few rows, for example column labels or a spreadsheet title. If one of these rows contains your column labels, be sure to flip the switch on that row. You can also use the Ignore checkboxes to hide irrelevant data.
Next is the optional transformation stage. If you want to import your data as-is, just click next. If you want to change your data before it’s imported, however, click Add Transformation. This is covered more in-depth in the Transforming Values While Importing article.
Identifying Fields in Import Data
At this point, it doesn’t matter where your data is coming from, it all looks the same. Now you’ll need to tell the software where to find the data you want to import. To do this, just drag and drop the column labels onto the top of each column. If your columns are named something that Spider Impact recognizes, we’ll do this for you automatically.
One of the things you’ll need to tell the software is where to find the dates for the values. In this example we’ve chosen “dates are in a column” and we’ve matched the Date label with the date column.
You can also choose a specific date for all of the values you’re importing.
You can even choose a relative period. In this example we’re importing KPI values into the current period.
Finally, for KPI values you can choose “dates are in a header row.” This allows you to import multiple values for each KPI row.
Identifying Destination KPIs
After you’ve chosen what data you want to import, the next step is choosing where you want that data to go. Just choose the items you want to import data into, or choose an item higher in the tree to select every KPI or Initiative below it.
To see the items that you’ve already selected, just click the Edit KPIs button.
This will open a second-level dialog where you can view and remove the KPIs that you’ve selected to import data into.
Mapping Import Data to Destination KPIs
Now it’s time to match the import data with the destination items. We’ll do our best to automatically choose a matching based on name.
Saving and Scheduling
Your value import is now ready to run.
If you want, you can also save your import so you can quickly run it again later with new data. By assigning other users and groups as owners, you can share this import with other people.
You can even schedule the import to run on a recurring basis. In this example we’re going to import a new version of the data every Sunday night.
You can manage imports and import connections in the Administration section.