How To Create Bex Query

Microsoft Power BI is known for its ease of usage and powerful functionalities across the tool. Power BI is becoming one of the most widely used Self Service BI tool primarily because of its extensive data connectivity and its user-friendly reporting interface.

SAP BW is one of the most extensively used data warehousing technologies by big businesses. Connecting to SAP BW from Power BI allows the reporting of business cases in a very effective way and this serves the purpose of this blog.

Data from SAP BW can only be consumed in the format of Business Explorer(BEx) queries.

So, let us look at how we could connect MS Power BI to SAP BW data sources and how we can consume BEx queries from BW and build visualization on top of it.

Heads Up Activity :

As a prerequisite, use SAP Note No. 1025361 and the installation wiki for the SAP NetWeaver RFC Library to get the NW RFC library in place, so that we can have the BW connector for Power BI Installed.

Connecting to SAP BW

Step #1: Go toGet Data under Home tab in the Ribbon. TheGet Data dialogue has an option calledSAP Business Warehouse Application Server in theDatabase Category.

A Deep Dive into Creating Microsoft Power BI Visualizations on BEx Queries

Step #2: Select the option and Click on Connect. You will see a pop-up which will ask you the connection details to connect MS Power BI to SAP BW

Step #3: Enter the Server name, System number, and Client ID. At times, we might miss out on entering the full name of the server, which could possibly throw an error.

Step #4: Choose the Data Connectivity mode that you want to connect to. You can either choose to Import or also use the DirectQuery option.

Import – You can import data into Power BI (into the Power BI cache) using this option which is a most common way to get data. If any changes are made to the underlying data, it will not reflect in any visualizations. It is necessary to Refresh all the tables to get the latest data.

DirectQuery – You can directly connect to the source. No data will be imported into Power BI. The queries will be sent to the underlying data source which will retrieve the necessary data. The time taken to retrieve the data and refresh the visualization will depend on the performance of the underlying data source.

You also haveAdvanced options which allow you to write an MDX statement(optional) to get data.

Multi-Dimensional Expressions(MDX) is BI query language developed by Microsoft Inc. It is used to query Multi-dimensional models (cubes) and returns multidimensional datasets that contains the data available on the cubes.

The syntax of multi-dimensional expressions is defined in the Microsoft specification OLE DB for OLAP.

ClickOK to proceed to the next step.

Step #5: Enter your credentials andclick on Connect.

A Navigator dialogue will show up with the available cubes and queries.

This lets you easily connect to any data source that is available on the particular SAP Business Warehouse system.

Now that we have connected to SAP BW, let us look into how we can consume the BEx queries in Microsoft Power BI

Consuming BEx Queries

Prerequisites:

In order to consume the queries designed on Power BI, we need to enableAllow External Access to this Query property.

Follow the below steps to enable the property.

The Science behind the connection

We are consuming BEx queries from Microsoft Power BI which is a third party front-end tool. The following image illustrates the integration of the interfaces,

TheMDX processor gets query request from third-party front-end tools and it forwards the request toOLAP Processor.TheOLAP processor accesses Info Providers. MDX (multidimensional expressions) is a language for queries using multi-dimensional data. MDX is developed by Microsoft. The syntax of multi-dimensional expressions is defined in the Microsoft specification OLE DB for OLAP.

The science of integration between the interfaces would have given you an idea about how the data is getting into Power BI from the BEx queries. Now let's get into the how-to part of consuming BEx queries on Microsoft Power BI.

Steps to SAP BEx (SAP Business Explorer) Queries

Step#1: Connect to SAP BW connection that we have created earlier in the blog. The Navigator dialogue will show up.

You will be able to see all the fields of a cube by expanding the particular cube and choose required key figures (measures) and Dimensions.

You can collapse the data provider just by clicking on the same.

Data Preview

The Preview of the data you have consumed will be available on the right side of the screen after you expand the provider.

Working on the data

Just like any other data that we consume on Microsoft Power BI, we will also be able to perform any kind of data calculations and transformations on the data from the BEx queries too.

You can click on Edit if you want to do data transformation or you can just click on the Load button to get the data loaded on to the interface.

Performance

SAP BW is usually implemented to bring in data from systems with huge amounts of transactional data being updated very frequently. It is also capable of handling historic data for years together.

So, the time taken for the data to load entirely depends on the volume of the data you are trying to pull into Power BI.

However, the impact on performance is not going to be much. Power BI takes a few seconds more than usual.

Conclusion

SAP BW being one of the most extensively used data-warehousing technologies amidst huge businesses and also, many have been using it for decades now.

Though, Power BI does not have an option to directly consume data from SAP BW, SAP provides us with a library which facilitates the consumption of data from BW using a special connector, about which we have discussed earlier in the blog.

Once we have the SAP BW system connected to Power BI, the data from the system can be consumed in the form of Business Explorer(BEx) queries and can be visualized like data imported from any other system. We can manipulate the data according to the requirements of the Business Users.

Learn more about Microsoft Power BI services offerings from Visual BI solutions here.