Extract Data from Data Sources

This topic covers the initial data extraction steps. For entity-wise job and global variable details, refer to the following chapters.

During extraction, connect to your data source and extract a data snapshot using ETL jobs.

Prerequisites:

1. Ensure your database administrator has created a user or schema in the ERP source database and granted the required permissions to run scripts. The created user must be able to access the application tables.
2. (Oracle E-Business Suite only) Ensure you have permission to run the DBMS_CDC_PUBLISH package. For Oracle E-Business Suite Release 12.2.9, 12.2.8, and 12.2.6, you must also have permission to access the editioning views.
3. (Oracle E-Business Suite and JD Edwards EnterpriseOne on Oracle Database only) Log on to the ERP source database and configure Oracle Native CDC using the queries available at Content/EB_11i|EBS_R12 or Content/JDE_9.1|JDE_9.1.
4. For source system and entity compatibility, refer to the insightsoftware SourceConnect Master Data Replication Product Availability Matrix.xlsx.
5. For the password required to import .atl files, contact insightsoftware Support.

Extract Data:

1. Log on to a schema that has access to all application tables and run the SQL queries available at these locations:
   1. Deltek Costpoint: Content/CSPT/
   2. Epicor iScala: Content/ISCALA/
   3. Infor Baan: Content/BAAN/
   4. IFS Applications: Content/IFS/
   5. Infor CloudSuite Industrial (SyteLine): Content/SYTELINE/
   6. Microsoft Dynamics GP: Content/MSFTGP/
   7. Microsoft Dynamics SL: Content/MSFTSL/

   Note: SourceConnect Management Console supports the following source systems: Oracle E-Business Suite, JD Edwards EnterpriseOne, Dynamics 365 for Finance and Operations, Dynamics 365 Business Central (on-premises and cloud), Oracle PeopleSoft, Microsoft Dynamics AX, Microsoft Dynamics NAV, SAP ECC, and Universal Connector (UC)/Flat file. For detailed instructions, refer SAP Central Finance Master Data Replication by insightsoftware Installer and Management Console Guide.

2. Run the queries to create tables, packages (if available), views, and then procedures (if available). You may need to modify the user name while running the SQL queries depending on your environment.
3. The database tables, views, and stored procedures are created in the source database for master data extraction.

For 1C: Enterprise data sources:

1. Import the SourceConnectExtracts.cf configuration file available at Content/1C/ into your 1C:Enterprise instance. The insightsoftware SourceConnect Configuration link appears in the left pane, and Extracting plan and Log Journal links appear in the right pane. For information about importing and using the configuration file, contact insightsoftware Support.
2. Select Extracting plan to open the Extracting plan window.
3. Double-click SAP to open the Magnitude SAP (Extracting plan) window.
4. Select the required extraction options (Business Partners, Materials, or both) and specify the folder location for the extracted data.
5. Select either Extract all data or Extract data (changes) based on your requirements. The source data is extracted into a .csv file in the specified folder location.

For QAD, Workday, or 1C: Enterprise sources:

1. Ensure the file name in the File name(s) field matches the name of the .csv file containing the source data.
2. Open SAP Data Services Designer as an administrator and sign in to your repository.
3. Go to the Formats tab and right-click MDR_FF_<EntityName><DataSourceName>SOURCE to open the MDR_FF<EntityName><DataSourceName>_SOURCE window.
4. In the File name(s) field, rename the file to match the extracted .csv file name.

Configure lookup values and entity objects:

1. Go to Tools > Import From File and import the MDR_MASTER_LOOKUP.xml available at Content/ETL/ into your repository. The project MDR_MASTER_LOOKUP is created.
2. To update the lookup values in the MDR_MASTER_LOOKUP staging table:
   1. In the MDR_MASTER_LOOKUP project, open the MDR_JOB_MASTER_LOOKUP_LOAD job.
   2. Update the $FILE_PATH to indicate the location of the MDR_MASTER_LOOKUP.xls file containing the lookup values for your entity.
   3. Run the job to update the staging table with the lookup values.
3. Go to Tools > Import From File and import the MDR_<EntityType>.xml file located at Content/ETL/ into your repository. Repeat this step for every entity in your environment.
4. The following objects are created in the local object library:
   1. Data store MDR_STAGE for staging database
   2. Data stores MDR_<DataSourceName> (Project) and MDR_SOURCE (all entities) for each supported data source except QAD, Workday, and 1C:Enterprise data sources and flat file data
   3. Data store MDR_S4H for Projects and MDR_<EntityType> for each entity

Configure staging database connection:

1. In the Local Object Library > Datastores tab, right-click MDR_STAGE and update the connection details to point to your staging database:
   1. For SQL Server:
      1. In the Edit Datastore MDR_STAGE window, select Advanced, then Edit.
      2. Set the default configuration to Yes in the MDR_DC_SQL_SERVER_STAGE column.
      3. Enter the server name where the staging database is installed in the Database server name or Hostname box.
      4. Enter the staging database name in the Database name or SID or Service Name box.
      5. If using SQL Server Authentication, select No for Use Windows Authentication and provide the user name and password.
      6. Select Apply, then OK.
   2. For SAP HANA:
      1. In the Edit Datastore MDR_STAGE window, select Advanced, then Edit.
      2. Set the default configuration to Yes in the MDR_DC_HANA_STAGE column.
      3. Select Yes or No for Use data source name (DSN) or TNS based on your environment. If Yes, enter the server name in the Database server name or Hostname box and the port number in the Port box.
      4. Enter the user name and password.
      5. Select Apply, then OK.
2. In the Local Object Library > Datastores tab, connect to your data source using the Edit option.