Import Records
You use Import records to point to, or attach to, historical data from an external data source. Typically, Import records are used when CCH Tagetik Supply Chain Planning is initially configured to import historical data from a data source.
A CCH Tagetik Supply Chain Planning instance can have multiple Import records, and may involve both dynamic and static data source integration:
Static vs. Dynamic Definitions
| Element | Definition |
|---|---|
| Static | Import record points to static data files that have been uploaded to CCH Tagetik Supply Chain Planning. The process of adding a static data source involves the CCH Tagetik Supply Chain Planning Administrator uploading the data source file into the data source directory. Once the data source file is uploaded, an Import record is used to load the data source into CCH Tagetik Supply Chain Planning so the data source can be used by other records and forecasts. The static data source is typically configured during a CCH Tagetik Supply Chain Planning implementation and subsequent data is added using Data Entry and Data Table records. The data in the file used as the data source is loaded into CCH Tagetik Supply Chain Planning each time a user session is started. See Data Entry Record. See Data Table Record. |
| Dynamic | Import record points to dynamic external SQL databases that are updated frequently. The CCH Tagetik Supply Chain Planning Administrator creates an Import record that contains ODBC connection information and a SQL query to the database. Connecting CCH Tagetik Supply Chain Planning to a SQL Server or other dynamic data source involves the following steps: 1. Create an ODBC connection using the ODBC Data Source Administrator on the Windows Server running CCH Tagetik Supply Chain Planning. 2. Create a new Import record in CCH Tagetik Supply Chain Planning and reference the ODBC connection. 3. In the Import record, include a SQL query to the data source. |
Note: To ensure data sources are added correctly, have the CCH Tagetik Supply Chain Planning Administrator configure CCH Tagetik Supply Chain Planning for both dynamic and static data sources.
You can also reference an Import record in other records in the Historical data formula property field.
To configure an Import record
- In the Import record properties, in the Name field, type a name for the record that identifies the data source.
The other fields vary according to the CCH Tagetik Supply Chain Planning configuration. 2. In the Import section, in the Type list, select one of the following:
Import Record Setup Options
| Option | Description |
|---|---|
| ODBC | Connect to a database application using Open Database Connectivity and SQL. |
| CSV | Establish a dynamic link with a comma-separated value (.csv) text file. |
| TXT | Establish a dynamic link with a tab-delimited text file. |
| VDB | Connect to the database in CCH Tagetik Supply Chain Planning's native format. |
| Checkpoint | Allows you to configure checkpoints as comparison lines. |
| 3. If you selected ODBC, do the following: |
ODBC Setup Options
| Option | Procedure |
|---|---|
| Connection String | Type the database connection string. This string is usually a reference to a System or File DSN (Data Source Name) created in the Windows ODBC Data Source Administrator (in the Control Panel). However, it can be a DSN-less connection string. Examples: - DSN=myDsn;Uid=myUsername;Pwd=myPassword; - FILEDSN=c:\myDsnFile.dsn;Uid=myUsername;Pwd=myPassword; - ``` DBQ=C:\TEST\QUERY |
| FILES;DefaultDir=C:\TEST\QUERY FILES; | |
| Deleted=1;Driver={Microsoft dBase Driver | |
| (*.dbf)};DriverId=277;FIL=dBase | |
| IV;PageTimeout=600;Statistics=0 ``` | |
| SQL Query | Type a SQL query that returns a list of records where each record contains transaction data as date, value pairs. For example: ``` SELECT Date, Value |
| FROM Table WHERE Item = '##ITEM##' AND Date < {d'##DATE##'} ``` Before submitting the query to your database, the server makes the following substitutions: - ##ITEM## is replaced with the name of the requested item. - ##DATE## is replaced with the current date if the server is running in normal mode, or with the rollback date if the server is running in rollback mode. You do not need to create a query using ##DATE## to support the rollback feature. If your query returns records representing data after the rollback date, the extra records are ignored. Use ##DATE## in your query if your database contains version information, and if you want to refer to a table as it existed at a prior date. | |
| 4. If you selected CSV, TXT, VDB, or Checkpoint in the Filename field, type the | |
| location of the file that contains your data. To point to a list of files, use a wildcard character (*) in the filename. For example, |
to access all checkpoints in Jan 2024.
The file is loaded each time a CCH Tagetik Supply Chain Planning session starts. Keep the data in the source file current.
In CSV and TXT files, format the data in a table with each item on a separate row. The first column in each row must contain the item name. All subsequent columns contain the periodic values for each item. The first row must contain dates associated with each column.
CSV/TXT File Example
| 1/31/2024 | 2/28/2024 | 3/31/2024 | 4/30/2024 | |
| SKU101 | 100 | 109 | 121 | 136 |
| SKU102 | 56 | 78 | 60 | 98 |
| SKU103 | 891 | 885 | 763 | 790 |
| 5. In the Options section, set the following parameters as necessary: |
Option Parameters for an Import File
| Parameter | Description |
|---|---|
| Period | You can convert data from a fine resolution, such as transaction data or daily totals, to another period that is more suitable for forecasting. You can select any period that is equal to, or more coarse than, your source data (for example, you can convert daily data to monthly, but you cannot convert monthly data to daily). |
| First period | Use this option to specify what to do with the first period’s data: - Drop: Always drop the first period. If your source data is transactional, or is in a form that is finer than your chosen period (for example, daily data that is binned into monthly periods), the first period may contain partial totals that degrade forecast accuracy. - Keep: Always keep the first period. |
| Last period | Specify what to do with the last period's data when the source data is transactional, or is in a form that is more fine than your chosen period: - Auto: Keep the data if the last transaction is at least one full period before today. - Drop: Always drop the last period. If your source data is transactional or is in a form that is finer than your chosen period (for example, daily data that is binned into monthly periods) the last period may contain partial totals that would degrade forecast accuracy. - Keep: Always keep the last period. - Extend: Pad data with trailing zeros up until the last full period. |
| Keep connection open (ODBC) | You can significantly improve the performance of the ODBC connection by keeping the connection open between database requests. Note: Depending on your database and structure of your query, you may encounter cursor state errors if you use this option. |
| Drop leading zeros (CSV, TXT, or VDB) | If your data file contains zeros to indicate missing initial data, check this option to drop all leading zeros automatically from the data set. |
| Cache lifetime | If you type a value greater than zero, CCH Tagetik Supply Chain Planning caches historical data locally and accesses your data source only if the cached data is older than the lifetime value you specify. This can significantly improve performance and reduce loading of your data source. To turn all caching off, type 0 (zero). |
| Test item | To test your data source connection, type the name of an item to load and click Fetch. CCH Tagetik Supply Chain Planning requests the data using the settings you selected. It displays a list of date and value pairs (indicating success), or an error message. |
| Offset | When Checkpoint is chosen, this option allows you to select which checkpoint to use. Most often you want this set to Latest so that you are always comparing your data to the latest checkpoint file. |
| 6. To save the record, in the upper-right of the screen, click OK. | |
| 7. To save your changes to the database, on the menu, click Save All Changes. |