ODI 12C – Slowly Changing Dimensions

In this blog we will explore how we can setup Slowly Changing Dimension SCD2 in ODI 12c.

Slowly Changing Dimensions: Attributes that changes slowly over the period of time, rather than changing frequent. In order to generate the historical reports in DW we need to track the history of dimension.

Type 1: When there is no need to store historical data in the dimension table. So process overwrites the old data with the new data in the dimension table. This is normal incremental update.

Type 2: When there is need to stores the entire history of the attribute in the dimension table in the form of separate rows. Using dates & current flag you can identify which records is currently active. See below example.

scd2

Type 3: When there is need to store current as well as previous value of the attribute. In this scenario two separate columns required in table to store current as well as previous value. See below example.

scd3


Import the IKM: Oracle Slowly Changing Dimension

IKM

Step 1: Create the tables & reverse engineered the same in your model. I have created below tables in localhost database.

 

  CREATE TABLE "SRC_EMP_ASSIGNMENT"
   (	EMP_ID VARCHAR2(10 CHAR),
	ASSIGNMENT_ID VARCHAR2(150 CHAR)
   );
  CREATE TABLE "TRG_EMP_ASSIGNMENT"
   (  ROW_WID NUMBER,
      EMP_ID VARCHAR2(10 CHAR),
      ASSIGNMENT_ID VARCHAR2(150 CHAR),
      ASSIGNMENT_TYPE VARCHAR2(5 CHAR),
      CURRENT_FLAG CHAR(1 CHAR),
      EFF_START_DATE DATE,
      EFF_END_DATE DATE
   );
 CREATE SEQUENCE SEQ_TRG_EMP_ASSIGNMENT START WITH 1;

 

Step 2: Open the target data-store and change the OLAP type to “Slowly Changing Dimension“.

1

Step 3: Now you have to set the SCD behavior of columns for target SCD2 data-store.

  • Surrogate Key – Unique key for table which does not have any business meaning.
  • Natural Key – Unique Key from the source data.
  • Current Record Flag – Indicate whether the record is active or old.
  • Starting Timestamp – Indicate the starting date of that entry
  • Ending Timestamp – Indicate the ending date of that entry
  • Overwrite on Change – If you don’t want to enable on SCD2 on any column.
  • Add Row on Change – If you want to enable on SCD2 on any column.

In this example, for target table : TRG_EMP_ASSIGNMENT

  • Surrogate KeyROW_WID
  • Natural KeyEMP_ID
  • Current Record FlagCURRENT_FLAG
  • Starting TimestampEFF_START_DATE DATE
  • Ending TimestampEFF_END_DATE DATE
  • Overwrite on ChangeASSIGNMENT_TYPE (Don’t want to track history for this)
  • Add Row on Change ASSIGNMENT_ID

Open the data-store, attributes & select the SCD behavior for every column.

8

Step 4: Create Mapping: See below execute on hints for columns.

mappig

Executing Mapping 1st Time: Initial Run

Source Date:

src

Target: See the end dates of all the records, default 01-01-2400 EFF_END_DATE.

trg

Executing Mapping 1st Time: Incremental Run

Source Data:

src2

Target: 

trg2

In above screenshot you can notice that previous assignment for EMP_ID 100 has been updated with CURRENT_FLAG=0 and EFF_END_DATE updated. New assignment for EMP_ID 100 has been created with CURRENT_FLAG=1 and default 01-01-2400 EFF_END_DATE.

odiopr


Thanks!

Happy Learning! Your feedback would be appreciated!

ODI 12c | Version Control System Setup – Git

In this blog we will see step by step how we can setup the version control in ODI 12c using git. Git – open source version control system for tracking changes in source code during software development. I have used github.com/ to create a repository for my ODI 12c objects versioning.


Step 1: Created a repository using the github.com/

1

Copy the git url for you repository.

https://github.com/shobhit-singh/odi12c-repo.git

2

Step 2: Open the ODI Studio & change your “Version Control Management System” to git by default it was set to ODI.

3

4

Step 3: Setup the git, go to setting & edit connection.

5

Provide the git url, github repository. Change the authentication to SSL.

6

Accept the server certificate.

7

Step 3: Clone remote repository with local folder.

8

9

10

Step 4: Click on configure to setup the branch. I have selected the master branch & save the settings.

11

12

Adding ODI Object First Time in VCS:

I have created the Model Folder, Sub-Model Folder, Model & Data-store in ODI.

datastore

Right Click on the Model Folder & Add to VCS, as it was new object & going first time.

13

You can select object also if you want to move child objects also. Add comments & Click on OK.

14

15

You can check the ODI Objects in GitHub.

Check the objects

Re-Adding ODI Object in VCS:

I have changed the data-store ‘TEST_TABLE’ in ODI. I have added the column in data-store. Now I have create a new version for this object. See below steps to create new version.

16

Add comments & Click on OK.

17

18

Check the version in GitHub: Now this object has 2 version.

19

 


Thanks!

Happy Learning! Your feedback would be appreciated!

ODI 12c | Master & Work Repository Setup

ODI 12c | Master & Work Repository Setup

In this blog we will see step by step how we can setup the ODI 12c Master & Work Repository:


Prerequisite: Setup the repository schema first in database(Supported by ODI). I have created the schema in the Oracle database. Refer the blog for creating new schema in Oracle schema Oracle Database 11g | Create User

CREATE USER ODI_REPO IDENTIFIED BY welcome;
GRANT CONNECT, RESOURCE, DBA TO ODI_REPO;
GRANT CREATE SESSION TO ODI_REPO;
GRANT DBA TO ODI_REPO;
GRANT ALL PRIVILEGES TO ODI_REPO;

You can create separate schema for master repository as well as work repository, but in this blog we going to use only one schema for both master as well as work repository. Refer this blog for more understanding of ODI Repositories | Master & Work Repository

Checkout this blog for understanding of relationship between ODI Master & Work Repository.

Setup Master Repository

Open the ODI 12c Studio. Go to File -> New. Select “Create  New Master Repository“.

1

Step 1. Provide all the details for repository connection.  Like schema name, password, JDBC details, DBA credential details etc. See below screenshot.

jdbc:oracle:thin:@localhost:1522/orcl.168.56.1

2

After providing all the details, test the connection. Click next.

Step 2: Provide authentication mode using ODI authentication, provide supervisor password & note it down somewhere as it is required for login setup.

3

Click on finish after providing supervisor password. It will start the setup of the master repository. It will take few minutes to complete the setup of master repository.

4

5

master done

Here the master repository has been created. Now you have create login for the master repository: Follow below steps


Setup Master Repository Login

Step 1: Open the ODI 12c Studio. Go to File -> New. Select “Create  New ODI Repository Login“.

master login

Step 2. Provide the detail like login name (provide any name), supervisor name & supervisor password you provided at the time of repository setup.

For Database Connection: Provide details for master reposotory schema details. Select radio button master repository. Test the connection.

login2

Click on ‘Ok’. You are done with the master repository login setup. Now you have to login your master repository & create the work repository inside it.

In ODI 12c Studio. Click on connect repository.

connect

Below screen will come with all pre-populated data, you just have to click ‘Ok’. Now you are inside the master repository. Check the repository details ODI->’Repository Connection Information’

ml2

ml3

Here you have logged in ODI Master Repository. Notice here the designer navigator is by default disabled. Basically you cannot do development work in master repository, you have to create work repository for this master to start the development work.

Now you have to setup work repository under this master repository: Follow below steps


Setup Work Repository:

Checkout this blog for understanding of relationship between ODI Master & Work Repository.

Go to Topology Navigator, Repositories -> New Work Repository

work1

Notice here I have provided the same schema, you can create separate schema for work schema which is recommended.

work2

Provide the password for work repository& select development type. As you are going to develop ETL in this work repository. For productions & UAT generally we select ‘Execution‘ as work repository type where Designer navigator is by default disabled. It means you can develop object in Execution type work repository.

work3

Click on Finish.

work4

It will ask you to setup the login for work repo. Click on yes & provide the login name.

worklogin

worklogin2

workdone

Here the work repository setup &  login has been done & disconnect from master repository and login the work repository.

connect

Check the repository details ODI->’Repository Connection Information‘.

Notice here the designer navigator is not disabled. As this is as development type work repository, you can start the development work by creating new project.

Dataserver, Physical Schema, Logical Schema & Context | Oracle Data Integrator 11g

Model & Datastore| Creating and Reverse-Engineering| Oracle Data Integrator 11g

worklogin3


Thanks!

Happy Learning! Your feedback would be appreciated!

ODI 12C Installation & Setup | Windows 10 64-Bit

In this blog we will see how we can setup the ODI 12c on your Windows 10 – 64 Bit PC. First you have to download the latest ODI 12c using below link. I have downloaded the Oracle Data Integrator 12c (12.2.1.4.0) version.

Step 1. Lets begin the installation by  downloading & unzipping the file.

Oracle Data Integrator Download Link

Instal1

Step 2. Prerequisite: Check you java version & set the java variables.

2

java -version
set JAVA_HOME="C:\Program Files\Java\jdk1.8.0_221"
set path=C:\Program Files\Java\jdk1.8.0_221\bin;%path%

Step 3. Start the installation using below commands:

cd C:\Users\shobhit\Downloads\fmw_12.2.1.4.0_odi_Disk1_1of1
java -jar fmw_12.2.1.4.0_odi.jar

1

It will automatically start the installation.

Screenshot 23-02-2020 12_05_44

Installation steps are self explanatory you just have to check all the details like path & click on next.

3

4

Installation is completed. Go to windows start,  type ODI Studio and open the tool.

Step 3. Setup the Master Repository,Work Repository & Logins. Refer below blog to setup the master repository, work repository & logins.

Oracle Data Integrator 11g (ODI) Repositories | Master & Work Repository | Connection & Setup

tool

Repository configuration is done & now you can start building the project using ODI 12c.

If you encounter such issue while opening the ODI 12c Sudio. Please follow below steps.

issue

Delete all the temp files present in the below directory & try opening the ODI studio again.

C:\Users\<<Username>>\AppData\Roaming\odi\system12.2.1.4.0


Thanks!

Happy Learning! Your feedback would be appreciated!

 

Model & Datastore| Creating and Reverse-Engineering| Oracle Data Integrator 11g

Model & Datastore| Creating and Reverse-Engineering| Oracle Data Integrator 11g

Folks,

In this blog we will explore:

  • What is Model & how to create a new Model using Oracle or file technology.
  • What is Datastore & how to reverse-engineer a model to populate it with all new & existing datastores.
  • How to create manually data-store of a model.

Model:

Model is a group of tabular data structures stored in a data server defined in the topology.

  • Model is based on a Logical Schema which is defined in the topology. In a given Context, Logical Schema is mapped to a Physical Schema which contains physical data structure: such as tables, files etc.
  • Models only contain Metadata Information, i.e. description of the data structures, they do not contain actual data.

Datastores:

A datastore represents a data structure. It describe data in tabular form i.e rows & columns.  It can be a table, a flat file, XML file or any other data structure which is supported by ODI as technology.

Reverse-engineering:

Reverse-engineering is the process that populates the Model by retrieving metadata from the data server containing the data structures.


Creating and Reverse-Engineering –  Model & Data-store 

Refer this video for Technology – File System:

Refer this video for Technology – Oracle:

In next blog we will explore how to create a ODI Interface to load data from file to Oracle.


Thanks!

Happy Learning! Your feedback would be appreciated!

Dataserver, Physical Schema, Logical Schema & Context | Oracle Data Integrator 11g

Dataserver, Physical Schema, Logical Schema & Context | Oracle Data Integrator 11g

Folks,

In this blog we will explore some areas of ODI Topology Navigaor.

  • We will explore Dataserver, Physical Schema, Logical Schema & Context.
  • Relationship between Physical, Logical Schema & Context.

Regarding setup & configuration of Dataserver, Physical Schema, Logical Schema in ODI using Oracle Technology please refer below video.


Let’s explore Topology Navigator in detail.

topology section
Topology Navigator

In Topology Navigator we have ‘Physical Architecture‘ section where we define the physical connection details of any Instance ( either Oracle DB, SQL Server,  IBM DB2, PostgreSQL, MySQL etc.) or any file system.

What is Dataserver?

A ODI object that defines the physical connection to any database instance or any file system. It basically store the host, username & password related details of an instance.

To define any physical connection in ‘Physical Architecture‘, you have to create a Data Server. Just click on your desired technology in Physical Architecture & click on ‘ New Data Server’.

NEWDATASERVER
Physical Architecture

After that you have to provide instance details. In case of any Database instance provide host, user/password & JDBC details.

Database server new
Data Server: DS_ORACLE_DEV

JDBC Details in case of oracle database as instance:

JDBC details
JDBC Details Data Sever: Localhost

In case of any file. Just provide the folder path from where you pick raw files in interface.

file1
Data Server File Type

JDBC Details in case of file:

file2
JDBC Details File Type

Now after adding the Data-server, next step is to add the ‘Physical Schema‘ in Data-server.

What is Physical Schema?

Physical Schema represents the actual connection to the data source (any database instance or any file system).

ODI Physical Schema corresponds to a pair of schema:

  • Main Schema (Data) – In which ODI look for the source and target objects for the interfaces.
  • Work Schema – Generally used by ODI for staging purpose, here ODI create temporary data objects associated to the sources and targets. Example C$, I$, E$ tables. It is always preferred to create & use separate work schema.

To define any physical schema, go to Data Server you created and then just click on new ‘New Physical Schema’.

PS.png
New Physical Schema

After that you have to provide Schema Name & Work Schema name using drop down. It will show you all the schema present in selected data-server instance.

PS02
Physical Schema Configuration
  • RAW_SCHEMA – schema where my source/target tables lies.
  • ODI_WORK_SCHEMA – Created for ODI staging purpose.
ps03.PNG
Physical Schema – RAW_SCHEMA present in Dataserver DS_ORACLE_DEV

Now after adding the Physical Schema, next step is to create Logical Schema & Context.

What is Logical Schema & Context?

  • Logical schema represents the logical name associated to that source/target objext.
  • One logical schema can be associated with multiple physical schema along with context, i.e. one logical schema is associated with different physical schema using different context.

Create Context:

To add any new context, go to Context Section and then just click on new ‘New Context’.

contextContext 02

Create Logical Schema:

To define any logical schema in ‘Logical Architecture‘ . Just click on your desired technology & click on ‘New Logical Schema’.

LA

LS02
Add New Logical Schema

After that you have to provide the logical schema name & Physical Schema mapping with context.

LS03
Logical Schema

So in Dev Context, Logical Schema LS_RAW_SCHEMA is pointing to Physical Schema DS_ORACLE_DEV.RAW_SCHEMA.

It may be possible that in Test Context, Logical Schema LS_RAW_SCHEMA is pointing to different Physical Schema DS_ORACLE_TEST.RAW_SCHEMA.

lS_Context.PNG

Refer this video clip for better understanding of Logical Schema & Context relationship.


Thanks!

Happy Learning! Your feedback would be appreciated!

 

Oracle Data Integrator | Master & Work Repositories Relationships

Oracle Data Integrator | Master & Work Repositories Relationships

Folks,

In this blog we will explore the relationship between ODI Master & Work Repository.

You can refer this blog  for better understanding of ODI Master & Work Repositories, how they differ from each other & how to setup new repository in ODI Studio using Oracle technology.


Master & Work Repositories Relationships

Master Repository is usually associated with multiple Work Repositories.

Each Work repository can associate with only one Master Repository.

Case 1)  When single Work Repository is attached to the Master Repository.

  • When we have to isolate any environment from the rest of the environments.
  • Usually case of Production/Pre-Production environment.

In below figure we can see that production/pre-prod master repository is isolated from other repositories. Its like one to one relationship. No information sharing.

 

Case 2)  When multiple Work Repository is attached to same Master Repository.

  • See below Figure 2, both work repositories are sharing same master repository.
  • Basically both are sharing the same Topology and Security data.
sharing
Figure 2

 

In real word production environment is isolated from rest of the environments.

practical.PNG


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Data Integrator 11g (ODI) Repositories | Master & Work Repository | Connection & Setup

Folks,

In this blog we will learn about Oracle Data Integrator Repositories.

You can refer this blog for ODI 11g Installation & Setup on Windows OS.


Suppose you are just done with the ODI installation on your machine:

Now you are going to use the ODI Studio to develops, operate & administrate some ELT project work. But you need some space/location to start working & to save your all work. Basically you need ODI repository to store the information you are going to develop, configure or used by the ODI.

There are two types of ODI repositories: Master & Work Repository

  • Objects developed, configured, operated/used by the ODI stored in one of these two types of repositories.
  • Repositories are accessed in client/server mode by various components of the ODI architecture.
  • Master Repository is usually associated with multiple Work Repositories.
  • Each Work repository can associate with only one Master Repository.
  • Work repository can either be Development Work Repository or Execution Work repository. (We will discuss this point later in this blog)

repo1

Refer this YouTube video to setup Master & Work Repositories in ODI 11g using Oracle.


Master Repository:

Master Repositories are used to store:

  • ODI Connection Information: JDBC URLs, LDAP information, user names and passwords for source/target connections.
  • ODI Security Information: ODI users names and passwords, ODI users privileges and profiles information, if security is handled by ODI.
  • Version Information: When a new version of an object is created in the ODI, that information is saved in the Master Repository.

Information contained in the Master Repository is maintained with the Topology Navigator and the Security Navigator in ODI Studio.

  • Data contained in master repository is mostly static data and will be going through minimum changes, whenever any topology or security information added/updated.
  • ODI components access the Master Repository, whenever they need the topology and security information data stored in it.

Work Repository:

Work Repositories are used to store:

 

  • Project Information: Folders/Sub-folders, packages, interface, procedure, variables, sequences, functions,  knowledge module etc.
  • Models: Data Store structures and metadata, fields, columns, constraints etc.
  • Load Plans & Scenarios: Load plans information & scenarios.
  • Operations:  Execution details, scheduling information, and logs.

The contents of a Work Repository are managed by using Designer and Operator. They are also accessed by the agent at run time.

When a Work Repository is used only to store execution, operations logs information (typically for production environment), it is called an execution work repository. Designer Navigator is disabled in this execution repository.

Work repository consists of two sub-types: Development Work RepositoryExecution Work repository.

Development Work Repository:

  • Repository which contains all the objects which are used for developing the development project i.e. packages, interface, procedure, variables, sequences, load plans, data stores etc.
  • Designer Navigator & Operator Navigator both are available for Development Work Repository.
  • Usually, used for development projects.

2.PNG

Execution Work Repository:

  • Repository which stores only the scenarios (executable files) & execution information, no source code & designer information available here.
  • Usually, used for production and test environments to make sure that source code will not be modified in a live environment.
  • Operator Navigator is available, but Designer Navigator is restricted for Execution Work Repository.

3

Checkout this blog for understanding of relationship between ODI Master & Work Repository.

Refer this YouTube video to Install ODI 11g on Windows OS.


Thanks!

Happy Learning! Your feedback would be appreciated!

 

 

Oracle Data Integrator 11g (11.1.1.9) | Installation & Setup |Microsoft OS

Oracle Data Integrator 11g (11.1.1.9) | Installation & Setup |Microsoft OS

Folks,

In this blog we will learn how to download & setup ODI – Oracle Data Integrator 11g (11.1.1.9) on Windows 10.

Step by step installation guide:

You can download the Oracle Data Integrator 11g (11.1.1.9) for Microsoft Windows (x64) using this Link


Thanks!

Happy Learning! Your feedback would be appreciated!

shobhitsingh.in