The Table Management section of the Datawarehouse tab of the Admin Page provides the ability for Administrators to manage the datawarehouse tables within iSite that deal with Lookup values such as Account Codes, Financial Months, Labor Classes and more.


Availability

Only users with the Portal Administrator or Financials Administrator permission will have access to this function.


Navigation

Admin > Datawarehouse > Table Management


Available Functions

The Table Management section provides the following functions:


Adding Table Records

Deletion of Table Records 

Editing of Table Records

Importing of Table Records

Exporting of Table Records









Importing 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
        • TaskCode, SubTaskCode
        • FinancialMonthCode
      • 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

  1. 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.
    1. When a selection is made, a link to an import specification document will be available.
  2. In the Import Method section, select Import Tab-Delimited Text File from the Data Import Method radio button options.
  3. 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.
  4. 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.
  5. 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.
    1. 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.
  6. 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.







Exporting Tables 



File Formats

When executing the Export Datawarehouse Tables function, a .zip file will be created with one or more text files embedded inside of it.  Each text file is a delimited file, which means that each field of a table is separated (delimited) by a comma or a tab character.  Each record is separated by a new line.


See the Comma-separated values Wikipedia entry for more detailed information on delimited files.




Specifying Delivery Options

When the .zip file is created with your desired tables, you have multiple options to choose from for the delivery of your file.


  • E-Mail to Me
    • E-mails a copy of the .zip file to your iSite e-mail address.
  • E-Mail to Others
    • Allows you to specify one or more e-mail addresses to to send a copy of the .zip file to.
  • Save to Document Library
    • Allows you to specify a Document Library and Folder to save a copy of the .zip file to.
  • Download to Personal Download Queue


Exporting Data

  1. A default File Name will be generated for you.  However, if you want to specify a different file name, enter the new name in the File Name textbox.
  2. In the Export Options section, specify a record delimiter by selecting a value from the Delimiter drop-down control.
  3. Select the type of datawarehouse tables that you want to download using the Tables to Export drop-down control.
  4. Select one or more tables (if available) in the Available Tables list.
    1. If check boxes are visible, you may select multiple tables from the list.
    2. If radio buttons are visible, you may only select one table from the list at a time.
  5. In the Delivery Options section, select one or more delivery options for the .zip file after creation.
  6. Click the Generate Export File button to create the .zip file with the specified options, or click the Cancel button to return to the Admin menu.






------------------------------------------


Page ID:2287-9592-3747