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*” 
And select the SAP BW source 
Enter your SAP BW instance connection parameters 
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) 
At 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 
Now, 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*” 
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 
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* 
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 
0.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)
Select the “*Data Federator Strategy*” strategy for the objects and joins 
Click on “*Next*” Select the generated Classes and Objects (select the root node of the tree view) then click on “*Add >>*” 
Click on “*Next*” 
Click on “*Finish*” to generate a relational SAP BW Universe automatically. 
You 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.
Of 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.
By clicking on “*SQL*” icon of the Query Panel, you can have a look to the generated SQL 
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.
And select the SAP BW source Enter your SAP BW instance connection parameters 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) At 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 Now, 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*” 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 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* 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 0.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) Select the “*Data Federator Strategy*” strategy for the objects and joins Click on “*Next*” Select the generated Classes and Objects (select the root node of the tree view) then click on “*Add >>*” Click on “*Next*” Click on “*Finish*” to generate a relational SAP BW Universe automatically. You 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.Of 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. By clicking on “*SQL*” icon of the Query Panel, you can have a look to the generated SQL 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.
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?
Fred
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
Regards
Abid Paul
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.
Nes
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:
http://help.sap.com/businessobject/product_guides/boexi3SP3/en/xi3_sp3_df_userguide_en.pdf
3) If you get an error like "No InfoProvider exists with name 0CALYEAR", then you have to install also the SAP note 1541649.
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?
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,
Zabrina
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
Thanks,
Satya
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.