How to create a Database links in Data Services using SQL Server

[former_member186897]

Sometimes you need to use multiple databases in a project where source tables may be stored into a database and target tables into another database. The drawback of using two different databases in BODS is that you cannot perform full pushdown operation in dataflow which may slow down the job execution and create performance issue. To overcome this we can create a database link and achieve full push down operation. Here is step by step procedure to create a database link in BODS using SQL Server on your local machine.

Pre-Requisite to create a database Link:

1. You should have two different datastores created in your Local repository which are connected to two different databases in SQL Server (Ex: Local Server).
   Note: You may have these databases on a single server or two different servers.It is up to you.
2. These two different databases shall exits in your Local SQL Server.

How to create a Database Links:

Step 1: Create two databases named DB_Source and DB_Target in your Local SQL Server.

SQL Server Code to create databases. (Execute this in your query browser)

CREATE Database DB_Source;

CREATE Database DB_Target;

Step2: Create two datastores in your local repository named DS_Source and connect this to DB_Source database. Create another datastore named DS_Target and connect this to DB_Target database.

Now, I want to link DS_Target datastore with DS_Source datastore so that it behaves as a single datastore in data services.

Use below details in screenshot to create your Datastores:

a) Create DS_Source Datastore as shown under

b) Create DS_Target Datastore as shown under

Before we go for third step lets create a Job and see what will happen without using a database link when we use the tables from these datastores in a dataflow. Will it perform full pushdown?

Step 3:

Follow the below screen shot to create your Project, Job and Dataflow in Designer.

Now go to your Sql Server database and open a query browser and use the below sql code to create a table with some data in DB_Source database.

a)

--Create a Sample Table in SQL Server
Create table EMP_Details(EmpID int identity, Name nvarchar(255));

--Inserting some sample records

Insert into EMP_Details values (1, 'Mohd Shahanshah Ansari');
Insert into EMP_Details values (2, 'Kailash Singh');
Insert into EMP_Details values (3, 'John');.

b) Once table is created import this table EMP_Details into your DS_Sourcedatastore.c) Drag a table from the datastore in your dataflow and use it as source table. Use a query transform then drag a template table and fill it the data as shown in the screen shot below. So, you are creating a target table int DS_Target datastore.

Once target table created your dataflow will look as under.

d) Map the columns in Q_Map transform as under.

Now you have source table coming from one database i.e. DB_Source and Target table is stored into another database i.e. DB_Target. Let’s see if the dataflow is performing full pushdown or not.


How to see whether full pushdown is happening or not?Go to Validation Tab in your designer and select Display Optimized SQL…. Option. Below is the screen shot for the same.

http://2.bp.blogspot.com/-EbP7mLrxp4U/UdkiBhd1iVI/AAAAAAAABxM/VruWWofGQ88/s1600/6.png

Below window will pop up once you select above option.

If optimized SQL Code is starting from Select Clause that means Full pushdown is NOT performing. To perform the full pushdown your SQL Query has to start with Insert Command.Step 4:
How to Create a Linked Server in SQL Server

Now go to SQL Server Database and Create a linked Server as shown in the screen below.

Fill the details as shown in the screen below for General Tab

Now, go to Security tab choose the option as shown in below dialog box.

Click on OK Button. Your link server is created successfully.Step 5:
Now It is time to create a datastore link and then see what optimized SQL it will generate.Go to advance mode of your DS_Target datastore property and Click on Linked Datastore and choose ‘DS_Source’ Datastore from the list and then click OK Button.

Below dialog box will appear. Choose Datastore as DS_Source and click Ok.
Then Click on the browser button as shown below.Then, select the option as show in dialog box below and then Click OK button.Now you have successfully established a database link between two datastores i.e. between DS_Source and DS_Target.Now Save the BODS Job and check the Optimized SQL from Validation Tab as done earlier. Go to the dataflow and see what code is generated in Optimized SQL.Below optimized code will be shown.

You can see that SQL has insert command now which means full pushdown is happening for your dataflow.

This is the way we can create a database link for SQL Server in DS and use more than one databases in a Job and still perform full pushdown operations.