Skip to Content

How to configure the SQL BW connector

h3. Introduction   In my last post  (Do you want to query SAP BW using SQL?), I discussed about the new Data Federator SQL connector for SAP BW.  The performances, the architecture and its relational capabilities are extremely well aligned with the Universes (Business Objects semantic layer) and our ad-hoc Web Intelligence reporting tools.   In this post, I will explain the step required to create a DF project pointing to an SAP BW Infoprovider (*Infocube*, *Multiprovider* or a *DSO*), generate the associated relational Universe and how to create a WebI report using this Universe.  h3.  Data Federator project Open Data Federator Designer and create a DF project (Webinar in our case).  In this project, click on “*Add datasource*” imageAnd select the SAP BW source imageEnter your SAP BW instance connection parameters  image Then click on “*Get gateway parameters*” button, to retrieve the SAP gateway parameters.    Once the gateway parameters have been retrieved, you can click on the “*target*” icon to list the Infoproviders available in your BW instance.   Select the one you want to connect to (*I0D_DX_C03* InfoCube in my case) imageAt this stage, the Infoprovider is exposed as a star schema containing 3 type of tables: ** Master Data tables, prefixed by “D” (Dimension) ** Text tables, prefixed by “T” (Text)   Click on “*Make final*” to finalize the connection   imageNow, the Webinar DF project has to be deployed in the Data Federator Query Server. The DF Query Server is the component in charge of the queries optimizations and executions.  To do that, you have to deploy the current project into a DF Query Server catalog. 0.1.  Click on “*Deploy*” in project toolbar0.1. Enter a name for the deployed project 0.2.  In the “*Deployment address*” section, reuse ‘sysadmin’ for password0.1. Keep the default values for other parameters 0.2.  Click on “*OK*” image At this stage, the Webinar project has been deployed in the Query Server */WB* catalog and can be queried using SQL.Start the “*Quick Design Wizard*” and check the “*Click here to choose strategies*” option  image Define a new JDBC connection to to point to the Data Federator Query Server */WB* catalog.   Click on Next 0.1.  Enter a connection name for example “*SAPBW*”0.1.  Select  *Business Objects > Data Federator Server > JDBC Drivers*0.1.  Click on *Next* image  Enter the Data Federator Query Server credentials for this connection. Note that: 0.1.  The Query Server credentials (*sysadmin*/*sysadmin*)0.1. The hostname or the IP address of the machine running the DF Query Server (cdi in our case) 0.2.  The name of the catalog used during the DF project deployment post fixed by “/*sources*”. For instance, “*WB/sources*” in our case image0.1.  Test the connection by clicking on  “*Test Connection*”0.1.  Click on “*Next*”0.1.  Click on “*Finish*”Enter a new Universe name and select the Universe Connection pointing to DF(SAPBW)image Select the “*Data Federator Strategy*” strategy for the objects and joins image Click on “*Next*”   Select the generated Classes and Objects (select the root node of the tree view) then click on “*Add >>*”   image Click on “*Next*”   image Click on “*Finish*” to generate a relational SAP BW Universe automatically.    imageYou end up with a ready to use Universe.  The star schema is visible on the right as well as the aliases to avoid the join loops. The business layer (tree view on the left) has been generated for you leveraging the metadata stored in SAP BW.imageOf course, you can customize the Universe if required then Export it to the CMS in order to create WebI documents. At this stage, you can start Web Intelligence to create your own queries and reports. In WebI start the Query Panel to define your query using the business objects available in the Universe we created. For instance, I created the following query to compare sales and billed plans.image By clicking on “*SQL*” icon of the Query Panel, you can have a look to the generated SQL image Click on “*Run Query*” to execute the query and create a default report layout.  In my case, I updated the layout to create sections, breaks and add extra calculations including a ratio between billed and sales plan.  In addition, I use the multi-block capability of WebI to associate graph with my table.image
You must be Logged on to comment or reply to a post.
  • Great blog!

    Is it possible to achieve SSO with SAP BW backend system? In the blog you had the userid and password hardcoded within the DF project, can we do SSO similar to the SSO used for SAP BW OLAP Universes?

      • what are the performance improvements you will getusing data federator with SAP BW as compared to use integration kit with SAP BW.

        How much time it takes to retrieve the data with 2 million records at a time

        i would really appreciate if you could throw some light on this

        Abid Paul

        • Time to retrieves data depends of many things but ideally, for a query accessing only the fact table, with DF server near BW server, and with a few tuning tips, time to retrieve 2 million of small rows is 30-60 seconds.
      • Hi Fred,

        Our architecture is BOBJ_DF-BW. The DF version is XI 3.0 SP2 connected to a BW 7.0. I know from existing documentations that DF leverages the BI authorizations.
        The question is how does DF leverage/use BI authorizations. I don't know exactly how the interface works. For example, if user1 brings up a kpi which is directly map through a relational universe defined for a BW provider, how will the existing roles defined for that user1 come into play? Will DF honor the authorizations granted to user1 in BW. I think it should. Kindly let me know.


        • 1) To leverage BI authorizations set in BW, you have to use DF XI 3.0 SP3 and SAP BW with the note 1500945 installed.

          2) DF tells to DF Facade in BW that BI authorizations must be respected. The DF Facade adds filter to the query like an authorization variable does with a BEx query. This filter is dependent of BW user currently used (user1 in your example).

          To have more details, you can read the user guide of DF XI 3.0 SP3, section "14.7.5 Leveraging SAP analysis authorizations to filter data automatically". You can find it here:

          3) If you get an error like "No InfoProvider exists with name 0CALYEAR", then you have to install also the SAP note 1541649.

  • Hi I have problems with Data Federator and Sap NetWeaver BI Datasource.

    perform the following step:
    -add new project "Demo".
    -add new datasource and but do not show me SAP NetWeaver BI datasource.

    I can do?

    SAP Business Object XI 3.1
    SAP Data Federator XI 3.0
    SAP  NetWeaver BI 7.10

    which is your email?

  • Hi,

    I have installed BOE XI 3.1, DF 3.0 SP2 and NW BI 7.01 SP7.

    I created a new project DF Designer and add a new data source but I cannot find the options SAP NetWeaver BI in the data source type meny. 

    Would you please tell me what can be the problem and how to proceed.

    Thank you for your attention.

    Best regards,

  • Hi Fred,

    We have implemented Data Federator on BW cubes, ODS and Master data tables. We have a landscape where we have a Accelerator in Production and not on Dev and QA servers. We would like to know while transporting DF objects from Dev to QA,PRD, how does they get the data..Is it hits Accelerator and gets the data or does it goes into underlying cube tabels.

    Kindly let us know


  • I have so many questions ... !

    Can you comment on the ability to run reports with filters based on system date with this architecture? How limited would we be if we need to run "Last Six Weeks" reports, etc?

    How are week and month values treated in this architecture, as strings or dates? Can we apply SQL string and date functions to BW values, so that weeks (for example "23.2010" and "22.2011") can sort correctly?

    These seem to be very common problems for WebI users on SAP BW universes.