In this article we will show you, How to import data from relational Databases such as Microsoft SQL Server, Oracle, Teradata etc and use them as Database source in Informatica Source Analyzer.
Relational Database Source in Informatica
Before we start doing anything, First connect to Informatica repository service with your Admin credentials and then Navigate to Source Analyzer. Please refer Informatica Source Analyzer article to understand the Source Analyzer. From the below screenshot you can observe that, we are in the Source Analyzer.
NOTE: Source Menu in the Menu bar will be available only when you are in Source Analyzer section otherwise, it will be hidden
Once you are in Source Analyzer, Please navigate to Source menu and select the Import from Database.. option as shown below
Once you select the Import from Database.. option, a new window called Import tables will be opened as shown below. Please select the ODBC connection that we already created in our previous article from the drop down list. In order to create new one, please refer Informatica ODBC Connection article.
Next, We have to provide the credentials required to connect with respective database. Here we are providing the Username and password of our Microsoft SQL Server 2014 and owner name as dbo.
Once you fill the details, please click on the connect button. This will connect the database and display the tables and Views present in the database.
If you want to see the data from all owners then, Please change the owner name from dbo to default All. This can be done by clicking the All button below the Show Owners section
From the below screenshot you can observe that, we are selecting the DimProducts table from our Adventure Works DW 2014 database. Here you can select more than one table also.
TIP: If you find difficult to track your required table(s) then, you can type the table name under the Search for tables named and click on search button
From the below screenshot you can observe that, under the Source sub folder you can see our newly created database source in Informatica and the table definition (Column Names and appropriate data types) inside our work space.
Preview Source Data
Although we imported our source from the SQL database, before we proceed further we should preview our data. In order to preview the source data, Right click on the table definition and select the Preview data.. option from the context menu.
Once you select the Preview data.. option, Preview data window will be opened. here you have to provide the credentials (Username and password) required to connect with respective database and click connect button. Once you successfully connected to your database, data inside the table (here DimProducts) will be displayed as shown below
Edit existing Database Source in Informatica
Although we selected the DimProducts, we may not require all the columns present in that table. To resolve this, we can edit the table by Right click on the table definition and selecting the Edit.. option from the context menu.
Once you select the Edit.. option, a new window called Edit tables will be opened as show below. In order to alter or change the existing table, we have to navigate to Columns Tab. For example, if you want to add new columns then, Please select the below shown button and add Column Name, Data Type, Precision (if required), Scale (if required), If it is primary key then change the Key type to primary and check mark the Not Null option (if your column doesn’t allow null values)
If you want to delete few columns then select the unwanted columns and click the scissors button as shown below. Here we are removing few columns for the demonstration purpose. Once you done editing, Click OK to close the Edit tables window
From the below screenshot you can observe that, we successfully deleted the unwanted columns from the source definition.
NOTE: We are removing the columns only from the Informatica Source Analyzer. This will not reflect the underlying database data.
Thank You for Visiting Our Blog