The development of an organizational Business Intelligence (BI) platform is an ongoing process. Many organizations run extremely heterogeneous OLTP platforms (ERP, Legacy Apps, CRM etc’), and consequently different BI systems. Moreover, business events, such as mergers and acquisitions, further increase heterogeneity across the enterprise BI landscape. Consequently, BI end users need to be trained on a plethora of front-end tools in order to be able to analyze data from different BI sources, resulting in increased costs and productivity loss. BI Standardization (i.e. migrate the entire enterprise BI platform to a single vendor) is often touted as the silver bullet; however we should acknowledge that it’s a painful process, and its ROI is in question.
The challenge we therefore face, as SAP BI developers and architects, is to be able to surface our data to the end users using a variety of front-end tools and not only using those supplied by SAP.
In this blog post, I would like to demonstrate how we can distribute SAP BI data to end users of the Microsoft BI platform tools. More specifically, we will leverage
Microsoft SQL Server Reporting Services
(hereafter SSRS) 2005 as the front end tool for our SAP BI data. SSRS is a reporting platform that enables the creation and delivery of reports over the web (in that sense it’s similar to our beloved Web Application Designer (WAD), but it features also some BEX Query and Report Designer like functionality). More on SSRS can be found at the Microsoft SSRS page .
I assume that (1) you are familiar and have access to a SAP BI system (including to the Query Designer Tool) and (2) that you have access or intend to install a SSRS 2005 SP1 system. No previous SSRS 2005 reporting knowledge is assumed.
The integration between SAP BI and SSRS 2005 is facilitated by the
Microsoft .NET Data Provider 1.0 for SAP NetWeaver BI
(not to be confused with the SAP connector for .NET, which enables SAP R/3 data access from Microsoft platforms). This tool ships as part of Service Pack 1 for SSRS 2005, released back in April 2006. It leverages MDX (Multi Dimensional Expressions) and XMLA (XML for Analysis), in order to access BI InfoCubes, MultiProvides and Queries. Note that DataStore access for XMLA is not allowed. In case you would like to access DataStore data, you should first build a query on top of that DataStore and then release the query for external XMLA access (procedure is shown below).
In order to implement the scenario you will need the following software components:
One of the following SAP BI systems with the following minimum service pack level:
o SAP BW 3.0B with SP 30
o SAP BW 3.1 with SP 24
o SAP BW 3.5 with SP16
o SAP NW2004S BI with SP 6 (The scenario doesn’t work on NW 2004S ABAP Sneak Preview Systems)
o Windows 2003 SP1 or Windows XP SP2 (if you are using the developer edition of SSRS 2005)
o Microsoft .NET Framework 2.0 (If you are not sure whether it’s installed, go to Start>>Settings>>Add/Remove Programs and verify)
o SQL Server 2005 with Service Pack 1 (Enterprise, Standard or Developer editions)
o SSRS 2005 with Service Pack 1 (included in the SQL Server 2005 SP1)
Note: For test and demo purposes, it’s possible to run this scenario on a single lap top (with at least 2GB of RAM) running MS SQL Server, the Netweaver ABAP stack and SSRS 2005.
Build your SAP BI query and release it for XMLA access
o Using your SAP Bex Query Designer, build a query you would like to surface in SSRS 2005. For the sake of simplicity, your query should contain no variables. Note that the XMLA service (and thus SSRS) doesn’t support the execution of queries containing mandatory variables (only optional variables are supported). The implementation of variables in SSRS 2005 reporting, exceeds the scope of this post (as it requires a deeper SSRS 2005 hands-on experience), and might be covered in next installments of this blog.
o Open your query properties and go to the ‘Extended’ tab.
o Check the “Allow External Access to this query”. This will make the query visible through the XMLA interface.
Activate and test the XMLA web service configuration settings in your BI system.
You’ll need those settings in order to configure the connection between SSRS 2005 and BI.
o In your BI system, run transaction SICF.
o From your web services tree, expand the following node: /sap/bw/xml/soap.
o Highlight the ‘xmla’ node and right click.
o If the ‘Activate Service’ is highlighted – click on it.
o Click on ‘Test Service’.
o You will be prompted for your SAP BI user name and password.
o The XMLA definition will be open in your default browser.
o Copy the URL displayed in your browser address bar and then paste it in a notepad file. This is actually the URL prefix and port for the XMLA web service that runs on your BI server. You’ll need that URL for defining the connection from SSRS 2005 to the SAP BI server.
Note: In a SAP white paper, Mike Eacrett proposed an alternative method that you can use in order to find out your URL prefix and port for the XMLA service:
a. Run transaction SE37
b. Execute function module RSBB_URL_PREFIX_GET
c. In the import parameters set the value CL_RSR_MDX_SOAP_HANDLER for parameter I_HANDLERCLASS
d. Set the value X for parameter I_MESSAGESERVER
e. Execute the function module
f. Look into your export parameters. The E_URL_PREXIF and E_URL_PATH are what you are looking for.
Build and Deploy your report in SSRS 2005
o Login to your SQL Server Business Intelligence Studio
o Click on ‘New’>>’Project’
o Select ‘Report Server Project Wizard’
o Click on ‘Next’
o Create a new Data Source. Fill the required fields as shown below. Important: Paste the connection string you copied in Step 2 in the connection string text box.
: In case the value ‘SAP Netweaver BI’ is not listed as an option in your ‘Type’ combo box, you are either not running SSRS 2005 SP1, or the provider wasn’t registered with either your SSRS design or runtime environments.
o Click the ‘Credentials’ button and mark the “Prompt for Credentials” checkbox.
o Hit ‘OK’, and then ‘Next’.
o Open the Query builder to build a new Reporting Services query. Note that you can also generate an MDX query from transaction MDXTEST and paste it into the ‘Query string’ field.
o Drag the relevant Characteristics and Key figures to the query builder panel
o Once you are done, hit ‘OK’.
o Your XML query was auto-generated and it’s visible in the ‘Query string’ field.
o Hit ‘Next’.
o Run the wizard to determine your report layout.
o Choose a deployment folder to contain your SSRS report.
o Provide your Report a meaningful name.
o Save your project.
o Go to Start>> Programs>> Microsoft SQL Server 2005>> Configuration Tools and start your SQL Configuration manager. Make sure that the required services for deploying the report to the SSRS 2005 server are started:
• SQL Server Agent
• SQL Server Reporting Agent
o Return to your SQL Server Business Intelligence Development Studio.
o From the ‘Solution Explorer’ pane, expand the ‘Reports Folder’, right click your report name and click on ‘Deploy’. In case you don’t see the ‘Solution Explorer’ pane, hit ‘View’ and ‘Solution’ Explorer’.