Loading Data from One File into Staging
To load data from one file to Staging, follow these steps:
1. Create an EDS of the type File.
2. Create EDD by selecting pre-defined EDS. Provide all required information while creating the EDD. If post-loading reconciliation is required, then go to the Control tab and provide a control record.
3. Create a Connector for loading data into staging. Select both the EDDs, establish a join, and click Lookup. If the SQLLDR option is enabled, then the file must be available in the server where the ODI agent is running. If the External Table option is selected, then the file must be available in the target database server.
4. Publish the Connector.
5. Execute the Connector.
Loading Data from Two Files Joined into Staging
To load data from two files which are joined to Staging, follow these steps:
1. Create EDS of type File.
2. Create two EDD by selecting pre-defined EDS. Provide all required information while creating EDD. If post-loading reconciliation is required then go to the Control tab and provide a control record.
3. Create a Connector for loading data into staging. Select both the EDDs, establish a join, and click Lookup. If the SQLLDR option is enabled, then the file must be available in the server where the ODI agent is running. If the External Table option is selected, then the file must be available in the target database server.
4. Publish the Connector.
5. Execute the Connector.
Loading Data into Staging from File and Performing Lookup into a Table
To load data from a file to Staging, and perform a lookup into a table, follow these steps:
1. Create one EDS of type File and another EDS of type Database.
2. Create two EDD by selecting predefined EDS. Provide all required information while creating the EDD. If post-loading reconciliation is required, then go to the Control tab and provide a control record. Post loading reconciliation is only applicable for file type Data Loading.
3. Create a Connector for loading data into staging. Select both the EDDs, establish a join, and click Lookup. If the SQLLDR option is enabled, then the file must be available in the server where the ODI agent is running. If the External Table option is selected, then the file must be available in the target database server.
4. Publish the Connector.
5. Execute the Connector.
Loading Data into Staging from an XML File
To load data to Staging from an XML file, follow these steps:
1. Create an EDS of type XML.
2. Create EDD by selecting predefined EDS. Provide all required information while creating the EDD. To get the XML structure, use the XSD file. If post-loading reconciliation is required then go to the Control tab and provide a control record. The control record must be in a separate file.
3. Create a Connector for loading data into staging. Select both the EDDs, establish a join, and click Lookup. If the SQLLDR option is enabled, then the file must be available in the server where the ODI agent is running. If the External Table option is selected, then the file must be available in the target database server.
4. Publish the Connector.
5. Execute the Connector.
Loading Data into Staging from EBCDIC
To load data to Staging from EBCDIC, follow these steps:
1. Create an EDS of type EBCDIC.
2. Create EDD by selecting predefined EDS. Provide all required information while creating the EDD. To define the EBCDIC structure, use the Cobol Copy book file. If post-loading reconciliation is required then go to the Control tab and provide a control record. The control record must be in a separate file.
3. Create a Connector for loading data into staging.
4. Publish the Connector.
5. Execute the Connector.
Loading Data from File into Multiple ADI/Subtypes
To load data from File into multiple ADI/Subtypes, follow these steps:
1. Create an EDS of type File.
2. Create EDD by selecting predefined EDS. Provide all required information while creating the EDD. To define the file structure, you can use an Excel template. If post-loading reconciliation is required then go to the Control tab and provide a control record.
3. Create a Connector for loading data into staging. Select multiple ADIs/Subtypes. Set filter against each ADI/Subtype selected to identify record status.
4. Publish the Connector.
5. Execute the Connector.
Loading Data into Results from File
To load data into Results from File, follow these steps:
1. Create an EDS of type File.
2. Create EDD by selecting predefined EDS. Provide all required information while creating the EDD. To define the File structure, you can use the Excel template for defining the columns. If post-loading reconciliation is required then go to the Control tab and provide a control record. The control record must be in a separate file.
3. Create a Connector for loading data into results. Select EDD and ADI (is displayed only relevant for the Result area). During mapping, code values that are available in the file must be mapped to the attributes (that are internally number columns). While loading it converts the code value to the surrogate key by performing a lookup in the related dimension table. Dimension data must be populated before this loading occurs using the SCD process.
4. Publish the Connector.
5. Execute the Connector.
Loading Data from HIVE
The following Jars must be copied to /ficweb/WEB-INF/lib:
1. commons-logging-1.1.3.jar
2. guava-15.0.jar
3. hadoop-auth-2.6.0-cdh5.8.0.jar
4. hadoop-auth.jar
5. hadoop-common-2.6.0-cdh5.8.0.jar
6. hadoop-common.jar
7. hive-common-1.1.0-cdh5.8.0.jar
8. hive-jdbc-1.1.0-cdh5.8.0.jar
9. hive-metastore-1.1.0-cdh5.8.0.jar
10. hive-service-1.1.0-cdh5.8.0.jar
11. HiveJDBC4.jar
12. httpclient-4.3.jar
13. httpcore-4.3.jar
14. libthrift-0.9.0.jar
15. slf4j-api-1.7.5.jar
For example, to connect to the Cloudera Hive server with JDBC 4.0 data standards, specify “com.cloudera.hive.jdbc4.HS2Driver” as the driver. See the Cloudera document for more details about Cloudera JDBC drivers.
NOTE |
Contact Oracle support for more information. |
Loading data into Partitioned Hive Table
If data is loaded into the partitioned table in Hive, the following configuration must be done in ODI by logging into ODI studio:
1. Navigate to Designer Tab > Models > <INFODOM_NAME>.
2. Right-click the target table to be loaded and click Open.
3. Navigate to Attributes Tab.
Select the “Partition by” checkbox for the column on which partition is defined.
Extracting Data from Staging into File
To extract data from Staging into File, follow these steps:
1. Create an EDS of type File.
2. Create EDD by selecting predefined EDS. Provide all required information while creating the EDD. To define the File structure, you can use the Excel template for defining the columns. If post-loading reconciliation is required then go to the Control tab and provide a control record. The control record must be in a separate file.
3. Create a Connector for extracting data from staging. ADI becomes the source and EDD becomes a target. The file structure is according to EDD.
4. Publish the Connector.
5. Execute the Connector.
Extracting Data from Result into File
To extract data from Results into File, follow these steps:
1. Create an EDS of type File.
2. Create EDD by selecting the predefined external data store. Provide all required information while creating the EDD. To define the File structure, you can use the Excel template for defining the columns. If post-loading reconciliation is required then go to the Control tab and provide a control record. The control record must be in a separate file.
3. Create a Connector for extracting data from results. ADI becomes the source and EDD becomes a target. The file structure is according to EDD. During extract, internal surrogate keys are converted into code values by performing lookup into the dimension table.
4. Publish the Connector.
5. Execute the Connector.
OFSAA out of the box dimension tables as lookup entity
Out of the box, dimension tables are used in the following scenarios:
· Loading Data into results
· Extracting data from results
While defining mapping, if the results ADI surrogate key column is mapped, then the corresponding dimension table is used as a lookup.
For Example:
· Loading data into Results
If N_Product_Skey of Common Account Summary ADI is mapped to an EDD product code column, then DIM_PRODUCT Table is used as a lookup for getting the N_Product_Skey value and V_PROD_CODE is used in the join expression. The EDD does a left outer join with the DIM_PRODUCT.
· Extracting Data from Results
If N_Product_Skey of Common Account Summary ADI is mapped to an EDD product code column, then DIM_Product Table is used as a lookup for getting the V_PROD_CODE value and N_Product_Skey is used in the join expression. The Common Account Summary lookup does a left outer join with DIM_PRODUCT.
1NOTE |
The lookup is established identifying the Surrogate key relationship between Results ADI and Dimension table automatically. You do not need to define the lookup in the connector. |
How to connect to External Source Server such as Db2/SQL Server/Teradata/Sybase?
DIH uses the JDBC mechanism to connect to source such sources. Hence the JDBC driver for the source server must be made available in the deployed location for DIH and ODI agent.
Navigate to $FIC_WEB_HOME/webroot/WEB-INF/lib and copy the JDBC driver.