It has recently been announced that ” Microsoft and SAP are jointly delivering business intelligence (BI) interoperability in Microsoft Excel, Microsoft Power BI for Office 365, and SAP BusinessObjects BI.” Microsoft Power Query for Excel will seamlessly connect to SAP BusinessObjects BI Universes, ee Excel and Power BI connectivity to SAP BusinessObjects Universes | Power BI for more details
I had a spare hour this morning so wanted to give this connectivity a try out and gain a quick understanding of what this really means to users.
The System Requirements are:
- Microsoft Power Query preview add-in installed in MSOffice Excel 2010 or Excel 2013, alternatively
- SAP Business Objects Platform version 4.1 with SP2
- SAP BI 4.1.2 REST Web Service installed in BI4.1 SP2
I went through the steps that the videos suggested on Excel and Power BI connectivity to SAP BusinessObjects Universes | Power BI, and yep it does take a while as the video suggests. But... I believe it’s not necessary to register for the trial and provision Power BI for Office 365 as we will be working in a local addin install in Excel on your laptop.
Install Microsoft Power Query
1. Download exe from Excel and Power BI connectivity to SAP BusinessObjects Universes | Power BI
2. Run the appropriate 32bit or 64bit installer
3. After installing Power Query, there will be a Power Query ribbon in Excel.
Connecting to a universe
To connect to an SAP BusinessObjects BI Universe, select From Other Sources in the Power Query ribbon. Navigate to the bottom and select the From SAP BusinessObjects BI Universe data source.
When prompted, enter the URL for the SAP BI 4.1 system you’d like to connect to (e.g.: http://<host>:<port>/biprws).
CARE: This URL took me a while to get right, you would assume it’s the Host and Port Number you use to access BI Launchpad, alas not. After a number of failed attempts and a large mug of tea for inspiration I had a moment of inspiration. The system requirements stated SAP BI 4.1.2 REST Web Service installed in BI4.1 SP2 are needed, this lead to a bit of research, and these are done automatically, which is great.
You can use the Windows installer to add RESTful web services to your custom BI platform deployment. RESTful web services requires an instance of the Web Application Container Server (WACS), which is installed with RESTful web services if it does not already exist. RESTful web services was introduced in BI platform 4.0 to Feature Pack 3. • If your BI platform 4.0 FP3 is a new installation, RESTful Web Services is automatically included in the installation. If you choose custom install, RESTful Web Services is selected in the feature tree by default. If you are upgrading from 4.0 SP2 to 4.0 FP3, after completing the upgrade, use the Programs and Features Windows Control Panel, Uninstall/Change feature to add the RESTful web service.
But, the REST Web Service installed in BI4.1 SP2 are installed by default using port 6405, so the URL needed to log in is:
Select the Universe from the other data source droplist
Enter the URL discussed earlier with port 6405
Next Enter your credentials for the service. Use the options in the dialog to view all possible credential types. Choose the credential type supported by your service and enter the necessary credentials. Once connected, you may see some progress information in the dialog.
After successfully connecting to the service, you will see a list of the available SAP BusinessObjects BI Universes in the Navigator pane. You can drill into the items in the Navigator and select SAP objects to transform. In my demo system I have a UNX universe accessing data on UK Road Traffic accidents since 2005, this UNX is now visible in the Navigator panel and I can select objects to display in Excel.
Click on LOAD DATA and you now have Data from your trusted universe inside Excel very simply
This was a quick technical how to get started with Microsoft Power Query and connecting to SAP BusinessObjects Universes.
But, just stop and think …… Live Office from SAP has been around for more years than I care to mention, I believe it came out of SAP Labs originally and is the current primary way to access universe data within the MSOffice suite of products. Live Office offers integration of both new queries and report parts from Crystal and Web Intelligence into Excel, Word, PowerPoint and Outlook, this new integration with Power Query is understandably limited to Excel but is also limited to new queries only. Some way to go to have parity of functionality if that is the vision from SAP and Microsoft.
I am left with this question in my mind .. Could this be a replacement to Live Office going forward? I personally think not, but I see this as a great complementary use of technologies.
What do you think ?