Import Data Into Datawarehouse
The Import Data Into Datawarehouse function on the Datawarehouse tab of the Admin Page is available for Administrators to import data into the iSite Datawarehouse from external data stores or systems. The ability to import data in bulk fashion allows you the flexibility to get your iSite License up and running in a quicker amount of time than having to hand enter data manually.
Only users with the Portal Administrator or Financial Administrator permission will have access to this function.
Admin > Datawarehouse > Import Data Into Datawarehouse
Available Types of Import Data
The Import Data Into Datawarehouse function provides import capabilities into a number of tables within iSite. A list of those tables are as follows:
- Reporting Tables
- Funding Actions
- Invoice Details
- Payment Details
- Planned Contract Values
- Purchase Requests
- Time-Phased Baseline Plans
- Travel In Process
- Unposted Labor
- Lookup Tables
- Burden Rates
- Employee Labor Rates
- Financial Months
- Labor Classes
- Labor Class Rates
- Labor Subclasses
- Labor Subclass Rates
- WBS Codes
- Task Management Tables
- Milestone Management Tables
- Risk Management Tables
- Timekeeping Tables
- Utility Tables
- Security Tables
Import Specification Spreadsheet
As a courtesy, an Import Specification spreadsheet link will be presented which will detail the column names, order of columns and required columns needed for file import. This spreadsheet can also be used to prep data for import using the Cut/Paste Tab-Delimited Text option. This is described below.
Foreign Key Checking
When importing data into iSite, you have the ability to enforce Foreign Keys, which, in the context of relational databases, is a field (or collection of fields) in one table that uniquely defines a row of another table. For instance, when importing Actuals, you have a field called Account Code. If Foreign Key checking is turned on, iSite will not import the Actuals record unless a record exists in the Accounts table that matches the Account Code.
See the Foreign Key Wikipedia entry for more detailed information on Foreign Keys.
Existing Records Disposition
When importing data into iSite, you have the ability to control what happens with data that already exists in the table that you want to import into. If records already exist, you can choose one of the following options:
- Replace Records
- Any records that are in the table will be deleted and replaced with the data being imported. This is a permanent action and cannot be undone.
- Append Records
- Any records that are being imported are ADDED to the existing table.
- Keep in mind that certain tables require unique key values. Thus, errors will occur if you try to import records with duplicate key values.
- Merge Records
- iSite will attempt to replace existing matching records with records that are being imported and will append new records where no match exists. This is the safest option.
- This option may not be available on all tables.
- When specifying the Merge option, you will be presented with one or more options for the Key values to replace.
- For instance, if you choose to import Actuals and choose the Merge Records option, you will be presented with 3 choices
- TaskCode, SubTaskCode
- If you chose FinancialMonthCode as the Key, then any records in the Actuals table that has the same FinancialMonthCode as the Imported Data would be deleted.
Data Import Method
There are two methods for importing data into iSite:
- Import Tab-Delimited Text File
- A text file with tab-delimited fields and one record each row.
- Column Headers on the first row
- Cut/Paste Tab-Delimited Text
- You can prepare data in Microsoft Excel and then cut/paste the data into a text area on the Data Import page.
- Data will automatically be prepared as tab-delimited text.
See the Delimiter-separated values Wikipedia entry for more detailed information on delimited files.
Importing Data From Tab-Delimited Text File
- In the Import Type section, select the type of table data that you want to import by selecting a value from the Import Type drop-down control.
- When a selection is made, a link to an import specification document will be available.
- In the Import Method section, select Import Tab-Delimited Text File from the Data Import Method radio button options.
- Click the Choose File or Browse... button (depending on your browser) to select a properly formatted tab-delimited text file from your file system that matches the import specification of the Table you are trying to import into.
- In the Import Options section, Indicate whether or not Foreign Key Checking should occur by selecting a value in the Perform Foreign Key Checking? drop-down control.
- Select an option to handle options for existing records in the target table by selecting a value in the Existing Records Disposition drop-down control.
- If choosing to Merge Records, select the Data Key to use for record merging by selecting a value in the Replace Data Key drop-down control.
- Click the Import File Now button to import the desired file with the specified options, or click the Cancel button to return to the Admin menu.