Skip to Content

Data Federator is a multi-source query engine and its optimizer and SQL engine can be used in 2 different scenarios

–          Delegate computations to sources (avoid data transfer)

–          Execute operations not supported by the sources (compensate sources deficiencies)

 

It’s a good time to familiarize with Data Federator as it provides already the solution of choice for:

–          Multi-source Universes

–          SAP BW

–          SAS data access

–          Real-time access to On-demand information via parameterized Web Services

 

So let’s install and get familiar with Data Federator XI 3.0, Universes and WebI.

 

This post will guide you step by step from the installation to the WebI report design.

    • Install database vendors JDBC drivers

    • Launch the Data Federator Designer

    • Create a project

    • Create a datasource

    • Create a target table

    • Update the target table schema (data model)

    • Update the default mapping rule

    • Deploy the DF project

0.1. Create a Universe connection

0.2. Create a business layer

0.3. Export the Universe to the repository

0.1. Create a WebI Document

 

Install the last version of DF

Install Data Federator XI 3.0 or higher. Ask your account manager an evaluation key then download Data Federator from the market place here:

http://service.sap.com/swdc  

In the left tree view click on:

Download > Support packages and Patches > Entry by application group

 

In the right pane

BusinessObjects packages and products > BOBJ DATA FEDERATOR > BOBJ DATA FEDERATOR XI 3.0 > ** > BusinessObjects Data Federator XI 3.0 SP1

!https://weblogs.sdn.sap.com/weblogs/images/251908199/1.install.jpg|height=437|alt=image|width=566|src=https://weblogs.sdn.sap.com/weblogs/images/251908199/1.install.jpg|border=0!”

 

 

 

 

Start with the 32 or 64 bits architectures depending on your server machine, then launch the Drivers install if you want to install the ODBC bridge.

 

Enter the evaluation key.

Install database vendors JDBC drivers

For legal reason, we are not allowed to ship the database vendors JDBC drivers with Data Federator.

This means that before to connect to sources, you have to copy the DB vendors JDBC drivers in the following folder:

C:\Program Files\Business Objects\BusinessObjects Data Federator 12\LeSelect\drivers

 

You can find these drivers on the DB vendor’s websites.

image

At this stage, Data Federator Designer and Query Server have been installed and you are ready to create a federation project.

Launch the Data Federator Designer

On windows, go to:

Start Menu > All Programs > BusinessObjects XI 3.0 > BusinessObjects Data Federator > Data Federator Designer

 

Login using:  sysadmin/sysadmin<br />

image

 

Create a project

 

Click on “Add project

 

image

 

Enter the name of the project (Post in our case) and click on Save

 

 

image

 

Now, we have a new DF project and we are ready to define the sources we want to federate.

 

Create a datasource

 

Click on “Add” then “Add datasource

image

 

Define the name of the datasource (CRM in our case); select the type of source (SQL Server in our case) and click Save

 

 

image

 

Here, you have to enter the datasource parameters

 

The resource (SQL Server 2000 or 2005)

The hostname running the database

The name of the database

The credentials

A schema (optional)

 

Then you can click on “Test the connection” to test if everything is fine.

If the test is successful, you can click on “Update all tables…” to import the tables you want to federate in your project.

 

image

 

Select the tables you are interested in (customers in our case) and click “Update

 

 

image

If you are satisfied with your source definition, you have to click on “CRM” in the treeview, and then click “Final” to finalize the datasource connection and use it for the mappings.

 

image

 

I repeat this process to create a 2nd

source called “Support”.

 

At this stage, our project contains 2 sources (CRM & Support) containing customer information.

 

image

 

This project is already ready to use and you can decide to deploy this data foundation (customers and CUSTOMERS_VIEW tables).

 

However, we want to go a step further and leverage the target tables and mapping rules mechanisms provided by Data Federator.

 

Create a target table

In our case, we want to leverage the data modeling capabilities of DF and create a mapping rule to federate (join) our 2 sources.

Let’s create a Customers target table based on the CRM customers schema.

 

Click on “Add” and select “Add target table from existing table

 

image

 

Select the schema of the source table (customers) you want to copy.

Change the target table’s name and check “Create default mapping rule”, then click on “Save

 

image

 

Now, we have a Customers target table which is the exact copy of the customers table coming from the CRM.

 

 

image

 

Let’s modify the schema of this table to add the customer’s name coming from the Support source.

 

Update the target table schema (data model)

Click on the “Customers” target table in the treeview, select the column position where you want to insert the new columns.

Click “Add Columns” and select “1 column

image

 

Enter the new column name (NAME in our case) and click “Save

 

 

 

image

 

Update the default mapping rule

Click on the “Default mapping” in the treeview to edit the mapping rule. The mapping rule is the mechanism used by Data Federator to map the sources information to the target table.

You can see that the new NAME column doesn’t have any mapping formula.

Let’s fix this problem by adding a new source and use this source to map the customer’s name.

image

Click on “Add table” and select the “CUSTOMERS_VIEW” table coming from the Support source.

image

 

The 2 tables (S1:customers and S2:CUSTOMERS_VIEW) are visible in the treeview but also represented as vertical bar in the relationships pane.

The table is red as a relationship between the 2 tables has to be created to indicate to DF how to join the table sources.

image

 

Let’s click on “Add relationship” to define a relationship (join) between these 2 tables.

 

Double click on “CUSTOMEID” from the Support, click on “=”, double click on “CUSTOMERID” from the CRM.

 

Note: You can click on “Edit” if you want to define more advanced relationships (joins) between the sources.

image

 

The relationship between the tables has been updated and we are ready to enter the mapping formula for the NAME column.

 

You can click on “Edit” to use the wizard or simply click on “<Control> + <Space>” to activate the code completion.

Select the source column you want to map to the NAME target table column (CONTACTNAME in our case)

 

Click “Save

image

 

Now the Customers target table federates the CRM & Support sources transparently for the end user.

 

Deploy the DF project

At this stage, we have to deploy the data model to the DF Query Server.

The DF Query Server is the component which optimizes and processes the queries at runtime.

 

Click on “Deploy” then enter the deployment options.

 

In this case, Post is used for the deployment name, sysadmin as the Password and /PP as a catalog name.

 

Note: Don’t forget the catalog name (PP) as it will be used later to define the connection between the Universes and Data Federator.

!https://weblogs.sdn.sap.com/weblogs/images/251908199/23.deployed.jpg|height=478|alt=image|width=695|src=https://weblogs.sdn.sap.com/weblogs/images/251908199/23.deployed.jpg|border=0!</body>

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Witalij Rudnicki
    “Install Data Federator XI 3.0, you can click on the install HTML page. \BusinessObjects_Data_Federator_XI_R3\RTM\en\release\install.htm”

    Is it supposed to be a public link?

    Thanks,
    -Vitaliy

    (0) 
  2. Claudio Sanft
    Can DF help me on choosing the right table among a set of homogeneous tables?
    I have 3 tables, and I would like to “UNION ALL” them all, but applying a pre-filter so that two of them will come up empty and only the right one will come full of data.
    Is this possible? As far as I read (I have no chance to play with DF now), it is NOT possible, as DF strictly requires to join tables from different datasources. Am I wrong?

    Thank you
    Claudio

    (0) 
    1. Frederic VANBORRE Post author
      Hi Claudio

      This use case is fully supported by Data Federator and no join between the tables is required.

      I will post about this use case next week if you want. In addition, we have a mechanism called intelligent data connection which smartly create connections to sources according to your SQL filters.

      Fred

      (0) 
      1. Claudio Sanft
        ok, please do. I’m looking forward to reading it. I thnk it’s a wonderful alternative to moving huge amount of data, even in those cases where a DWH is in place.
        (0) 
  3. Irma Nugroho
    Frederic,

    From my understanding, Universe Designer (UD) has a drawback of being able to connect to only one database in a single Universe. There will be times when I need to integrate multiple databases into a single Universe. In this case, can you confirm that DF is the solution to that UD’s drawback?

    (0) 
  4. Irma Nugroho
    Frederic,

    From my understanding, Universe Designer (UD) has a drawback of being able to connect to only one database in a single Universe. There will be times when I need to integrate multiple databases into a single Universe. In this case, can you confirm that DF is the solution to that UD’s drawback?
    Also, my company has purchase BOE XI R3.1. If I want to utilize DF, do we have to purchase it as a separate product?

    (0) 
    1. Frederic VANBORRE Post author
      Hi

      Yes, I confirm that Data Federator is the solution of choice in this case.

      Data Federator is now part of the BI package, so if your company pruchases BOE XI 3.1 BI package you will get Data Federator.

      Hope this helps,

      Fred

      (0) 
  5. srinivas reddy
    Hi Fred,

    Its a great blog.Thanks for posting .
    However I am not able to find DF in service market place. can you please guide me ..

    any help appreciated.

    Thanks
    srinivas

    (0) 
    1. Frederic VANBORRE Post author
      Thanks.

      You can download the latest version of Data Federator here:
      http://service.sap.com/swdc

      In the left tree view
      Download > Support Packages and Patches > Entry by Application Group

      In the right pane
      SAP BusinessObjects packages and products > SBOP DATA FEDERATOR > BOBJ DATA FEDERATOR XI3.0 > *

      (0) 
  6. Sreenadh Sanam
    Hi Fred,

    Thank you for this blog. It really helped in getting started. I have a question with DF Universe.

    Can I create a caliculated column in the existing target table? For Example, I want to create a new column “Days between 2 Dates” from 2 different tables? Why I am asking this question is, I am not able to create an (Data Federator Universe) Universe object. Can you please answer my question?

    Thanks & Regards,
    Peter

    (0) 

Leave a Reply