Connect to SAP BusinessObjects BI Universes with Power Query

Note: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. To learn more, see Get & Transform in Excel 2016.

In this tutorial, you learn how to connect, navigate, and shape a data set from dimensions and measures in an SAP BusinessObjects BI Universe with Power Query for Excel.

What you will need

The following components are required; however, each component may have additional system requirements.

Procedure title (can be blank)

• Microsoft Power Query for Excel

• SAP BusinessObjects 4.1 SP2 or higher

• SAP BI 4.1.2 or higher REST Web Service

Note: The following is not supported by REST Web Service 4.1.2 or higher:

• Direct access to SAP systems

• Universes created with Universe design tool

• Multidimensional Universes created with information design tool

• Relational Universes created with information design tool and containing prompts, parameters, or contexts

• Advanced filters (such as ranking)

Introduction to Power Query

Microsoft Power Query for Excel enhances the self-service Business Intelligence experience by simplifying data discovery, data transformation and enrichment for the desktop to the cloud. With Power Query, you can easily discover, combine, and refine data for better analysis in Excel. Power Query also includes a public search feature that is currently intended for use in the United States only.

After installing Power Query, you will see a Power Query ribbon in Excel. Using items on the Power Query ribbon, you can connect to a variety of data sources including web, file, database, and other data sources. After you connect with one or more data sources, you can shape the data into new data sets.

To learn more about Power Query, including samples and tutorials, see Microsoft Power Query for Excel Help.

Connect to an SAP BusinessObjects BI Universe

To connect to an SAP BusinessObjects BI Universe:

1. Select From Other Sources in the Power Query ribbon.

2. Navigate to the bottom and select the From SAP BusinessObjects BI Universe data source.

3. In the From SAP BusinessObjects BI Universe dialog, enter the URL for the SAP BusinessObjects BI 4.1 SP2 or higher system you'd like to connect to as http://<host>:<port>/biprws.

Note: The default port of the service is 6405.

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 your credentials. Once connected, you may see some progress information in the dialog.

After successfully connecting to the service, you will see a list of SAP BusinessObjects BI Universes in the Navigator pane. You can drill into the items in the Navigator and select dimensions and measures to transform.

Navigate through a Universe

For an SAP BusinessObjects BI Universe data sources, you can navigate business views, dimensions and measures that are categorized using folders from the Universe. You can drill down through each of the folders to see the dimensions, attributes and measures.

You can select items using the check box next to the object, selecting a parent item will automatically include all of its children. The icon next to the item indicates the type of object you're selecting. As you select objects, you will see your selections in the Selected items list. To see a preview of your selected objects, you can hover over the entry.

One you have selected objects, you can edit or shape them, such as applying a filter or combining objects to other data sets, before you import the results. To learn more about how to edit or shape objects, see Edit Query.

Load Settings are available at the bottom of the navigator. In Excel 2013, there are options to Load to worksheet and Load to Data Model. These options determine if a data set is loaded to the worksheet and/or into the Data Model. If neither are selected, a connection will be retained to the data source, however no data will be loaded.

These settings can be changed after the data is initially loaded in the Query Editor as shown in the Edit Query section.

In Excel 2010, there is no Load to Data Model option. After selecting the objects and choosing Load, you can go through Power Pivot to pull the data into the Data Model explicitly. Use the name of the connection to determine the correct query.

After selecting objects and choosing Load, a query is created in the workbook. When the query is selected, you will see a Query ribbon that offers query-related actions.

The Workbook Queries pane includes all of the queries currently included in the workbook. If the query was loaded to the worksheet, selecting a query will take you to the sheet it is located on. Double click the query to open the Query Editor to edit the query. You can also over or right click the query to take other actions on the query.

Edit Query

After you select objects, you can edit the query to perform other query related actions. To show a preview of the query, hover over the query in the side pane. This peek also offers some query actions including edit or share a query.

You can also access these options in the Query ribbon if the query is selected, or by right-clicking the query.

Select Edit Query to bring up the Query Editor. The Home ribbon offers a variety of transformations that you can apply to any data source. When possible, these transformations are done on the server or service side. There is information about the specific query in the Query Settings pane. The Load Settings for the query may also be changed in this Query Settings pane.

There are specific actions for the SAP BusinessObjects BI Universe data source, such as adding new dimensions and measures. This is accessible through the small cube icon to the left of the column names and from Add Items and Collapse Columns on the Manage ribbon tab.

There are also actions available when you right click a column, such as Collapse and Remove a dimension.

Note: When you collapse a dimension, it is removed from the current view of the cube, which affects the applied measures. Whereas, removing the column will simply hide it from the display and not affect the measures.

Once you've completed editing your query, choose Apply & Close to re-load the data with the new transformations and continue working with the data in Excel.