Generate Scenario and Scheduling job in odi

 

A Scenario is compiled or executable object like .exe; we can generate a scenario for packages, interfaces or variables, procedures.

Scenarios generated for procedures, interfaces or variables are single step scenarios that execute the procedure, interface or refresh the variable.

Scenario variables are variables used in the scenario that should be set when starting the scenario to parameterize its behavior.

Once generated, the scenario is stored inside the work repository. The scenario can be exported then imported to another repository and used in different contexts. A scenario can only be created from a development work repository, but can be imported into both development and execution work repositories.

Generating a scenario:

Go to designer navigator -> open project -> First folder ->interface_name then right click on the interface and select generate scenario.

Enter the name and version of the scenario.

If we have done some changed based on the requirement, then we need to regenerate the scenario else the previous version will gets executed, so we need to regenerate the scenario.

Regenerating the scenario: Right click on the scenario which is already been generated.

Generating group of scenarios:

When a set of packages, interfaces, procedures and variables grouped under a project or folder is finished and tested, you can generate the scenarios.

 

 

 

Steps:

Right click on the project or folder containing group of objects then click generate all scenarios. 

Then it will show one window to choose the mode of generation

Generation mode:

·         Replace: Overwrites the last scenario version of each object with a new one but with the same ID, name and version. Sessions, scenario reports and schedules are deleted. If no scenario exists for an object, a scenario with version number 001 is created by default.

·         Re-generate: Overwrites the last scenario version for each object with a new one with the same id, name and version. It preserves the schedule, sessions and scenario reports. If no scenario exists for an object, no scenario is created using this mode.

·         Creation: Creates a new scenario for each object with the same name as the last scenario version and with an automatically incremented version number. If no scenario exists for an object, a scenario named after the object with version number 001 is created.

Scheduling:

In order to schedule any object first we need to create a scenario for that and then schedule the object.

Scenarios are mainly used to schedule the objects like interfaces, variables, procedures.

Expand the scenario->scheduling->Right click on the scheduling->new scheduling

Scheduling window will open, where we need to configure the scheduling. To schedule an object, agent is mandatory.

 

Definition:

Context: Context into which the scenario or Load Plan is started.

Agent: Agent executing the scenario or Load Plan.

Log Level: Level of logging information to retain

Status:

Active: The scheduling will be active when the agent is restarted or when the scheduling of the physical agent is updated.

Inactive: The schedule is not active and will not run.

Active for period: Activity range of the schedule. A schedule active for a period of time will only run within this given period.

Execution:

Frequency of execution option (annual, monthly,... simple). This option is completed by a set of options that depend on this main option.

 

 

Execution Cycle:

Properties	Description
None (Execute once)	The scenario or Load Plan is executed only one time.
Many times	The scenario or Load Plan is repeated several times. Maximum number of repetitions: The maximum number of times the scenario is repeated during the cycle. Maximum Cycle Duration: As soon as the maximum time is reached, the scenario is no longer restarted, and the cycle stops. Interval between repetitions: The downtime between each scenario execution.
Constraints	Allows limitations to be placed on one cycle iteration, in the event of a problem during execution. Number of Attempts on Failure: Maximum number of consecutive execution attempts for one iteration. Stop Execution After: Maximum execution time for one iteration. If this time is reached, the scenario or Load Plan is automatically stopped.

 

Variables: On the Variables tab, unselect Latest Value for variables for which you want to provide a Value. Only variables used in the scenario or Load Plan and flagged as parameters for this scenario or Load Plan appear in this tab.

 

 

After defining the schedule, we need to update the agent which is accomplished with the schedule. So, go to topology->agents->double click the agent -> update the schedule

 

 

 

 

If we want to monitor the schedule, then we need to click view schedule

 

Then below window will be displayed

 

 

We can even schedule the object from console also,

In order to execute the scenarios for ( interfaces/packages ) from the web, we have an console named:

ODICONSOLE

url: 100.9.80.90:8001/odiconsole/

select the relevant repository and give the credentials to login followed by execution of scenarios.

 

 

Setting up default agent:

 

Load data from Flat File to Oracle DB

Advantage:

Using this, we can load data from  flat file to Oracle easily,

To export Flat File to Oracle DB, we have to follow below steps.

Ø  For that, we have to Create project to create a interface

Ø  We have to   import knowledge modules

Ø  we need to define the 2 Data Servers, the 2 physical schemas and associate them with the relevant logical schemas in ODI Topology Navigator.  

Ø  Need to define 2 data models & create 2 data stores.

	Create project to create interface: Go to Designer Navigator. Select new project
	Import Knowledge modules:
	For this interface, It has to import a flat file into the SQL staging area, and then export the file to a different flat file. The Knowledge Modules (KMs) that are required for this are LKM File to SQL and IKM SQL to SQL Control Append. To import the KMs, click the Project tab in the left panel and expand the Knowledge Modules folder. Right-click Loading (LKM) and select Import Knowledge Modules.  Note: In this example, the generic SQL KMs are used. However, specific KMs for the RDBMS technology can be used as well.
	1.     On the screen that follows, in the File Name, navigate to xml reference directory as shown in the following screenshot. Click Open. The files to import should appear in the Import Knowledge Modules window. Press and hold the CTRL key to select IKM SQL to SQL Control Append and LKM File to SQL. Click OK.
	On Import Report window, click Close. Expand the Loading (LKM) and Integration (IKM) folders, and view each imported KM in the tree view as shown below.     Create a Data Server for FILE DB in Topology Manager Step: 1) Open up Topology Manager and go to Physical Architecture. Right click on the File technology and select New Data Server. In the Data Server window enter a name in the Name field. Step: 2) Go to the JDBC tab. Select the com.sunopsis.jdbc.driver.file.FileDriver  in the JDBC Driver List and jdbc url for jdbc:snps:dbfile  Step: 3) Click on Test and make sure you get a successful connection.

Create physical schema for file DB (technology):

 

	Click Topology tab. In Topology navigator, click the Physical Architecture tab, select Technologies->> File. Right-click FILE_GENERIC, and then select New Physical Schema. Enter the path to the directory, where your input flat files are located (C:\Users\Administrator\Desktop).   Create logical schema for file DB (technology): Click Topology tab. In Topology navigator, click the Logical Architecture tab, select Technologies->> File. Right-click and then select New Logical Schema. Specify the name and map it with Contexts.       Create a Data Server for Oracle DB in Topology Manager Step: 1) Open up Topology Manager and go to Physical Architecture. Right click on the Oracle technology and select New Data Server. In the Data Server window enter a name in the Name field. Step: 2) Go to the JDBC tab. Select the oracle.jdbc.OracleDriver in the JDBC Driver List and JDBC URL for jdbc:oracle:thin:@190.160.0.85:1521:SAPTRAINING Step: 3) Click on Test and make sure you get a successful connection. Create physical schema for Oracle DB (technology): Click Topology tab. In Topology navigator, click the Physical Architecture tab, select Technologies->> Oracle. Right-click SAP_TRAINING, and then select New Physical Schema. Enter the schema details. Create logical schema for Oracle DB (Technology): Click Topology tab. In Topology navigator, click the Logical Architecture tab, select Technologies->> Oracle. Right-click and then select New Logical Schema. Specify the name and map it with Contexts.
	Create Data model for source DB (FILE Technology):   Step: 1) Go to the Designer Navigator select model tab and create new model folder for Files ,  used for easily identification. Step: 2) Create new model for Files, used for storing tables & data stores. Specify the Name, Technology and Logical schema while creating new model.   Create data stores for source DB (FILE Technology)
Source: Flat File
		Step: 1) Expand the Flat Files folder. Select text file folder, right-click and then select New Datastore. On the screen that appears, set Name to source. Click the button  next to the Resource Name field. Select the Source_File.txt file and click Open.
		Step: 2) Click the Files tab. Set the File Format to Delimited & File Separate to comma(,) and then click the Columns tab.
		Step: 3) Check the Columns in the data store. Clicks save.   Step: 4) Expand flat file folder>text file >source (Source_File.txt) >Columns   to view your source model. Verify that the columns were successfully created as shown in the screenshot.     Create Data model & Data store for Target(Oracle DB)   Target: Oracle DB Step: 1) Go to the Designer Navigator select model tab and create new model folder for Oracle DB, used for easily identification.   Step: 2) Create new model for Files, used for storing tables & data stores. Specify the Name, Technology and Logical schema while creating new model.   Step: 3) Create data stores for target DB. Expand Oracle DB folder>SAP_TRAINING. Right click & select new data store   . Then enter the data store name & add the column specified data type. Click Save.     Step: 4) Check the columns in Data store. Expand Oracle DB folder>SAP_TRAINING> Columns   to view your source model. Verify that the columns were successfully created as shown in the screenshot.

 

Create interface for loading data from source to target table:

Step: 1) Go to Designer, click the Projects tab. Expand your project SAMPLE _WORK_ON_INTERFCAES_VTH_DIFF DB, and then expand First Folder.  Right-click interfaces and selects new Interface.

	Step: 2) Click the Models tab. Drag the source & target data store into interface mapping. When Designer asks "Do you want to perform an Automatic Mapping?" click Yes. The Diagram tab should look as follows. Click the Quick edit tab & set primary key.   Step: 3) Go to flow tab. Click the source table. This makes the properties for the source appear below. In the LKM Selector section, select LKM File to SQL from the LKM drop-down list if not already selected Step: 4) Click the target table. This makes the properties for the target appear below. This makes the properties for the target appear below. For IKM, select IKM SQL to SQL Control Append from the IKM drop-down list if not already selected. Set the IKM options create target table to true & Flow control to false as shown below. Click Save icon to save your interface. Step: 5) To test your interface click Execute icon. Then following screen appears.              Step: 3) To verify that your interface was executed successfully, you need to open ODI Operator Navigator. Click the ODI Operator tab. In ODI Operator, click Session List. Expand Sessions > FLAT TO Oracle, and view the execution results for each step. Double-click any step and click the Description and Execution tabs to see the execution results. Double-click step 7. View the number of rows that are inserted into the target flat file. Click OK. Close the tabs.   Step: 3) Go to Oracle DB model and select target data store(Flat to Oracle).  Next right click the target data store and select the view data for output. Note: First Target table have empty records. After execute the interface target table have 14 records