The Query Browser feature differs from the other data connection methods in that we can create a query from a Universe or a BEx Query without leaving SAP BusinessObjects Dashboards. Also, with Query Browser we can bind the result data not only to the spreadsheet but also directly to the components on the canvas.
Open a new SAP BusinessObjects Dashboards file and activate the Query Browser pane from the View menu. You will also need a SAP BusinessObjects BI Platform environment with a Universe that you want to connect to.
If you want to connect to a BEx Query, you need an OLAP Connection to a SAP BW system. In the recipe we will follow the workflow for the Universe. In the There's more... section of this recipe, we will take a look at connecting to a BEx Query.
- Go to the Query Browser pane and click on the Add Query button.
- If you are not already connected to the SAP BusinessObjects BI Platform, a popup will appear asking you to log in. Enter the system and user credentials to log in.
- You can choose between selecting a Universe or a BEx query. Choose Universe.
- A list with available Universes appears. Select the Universe you want to use and click Next.
- In the Build Query screen you can define the Universe query. Drag a dimension and a measure to the Result Objects section.
- Drag a dimension you want to filter into the Filter Objects Area.
- Set this filter to Equal to Prompt. The Edit Prompt screen will pop up. Check Optional prompt. Click OK.
- Click the Next button to go to the Preview Query Results screen.
- Since we added a prompt, we now need to select a value to fill this prompt to retrieve some preview data. Select a value from the list and click Run.
- The result of the query will be shown now. Click Next.
- The final screen is the Usage Options screen, which looks exactly the same as the Usage tab in the Data Manager. Leave everything with the default values selected and click OK.
- The query will be added to the Query Browser pane, including the Result Objects, Filters, and Prompts you selected.
- Bind each dimension and measure of the Result Objects area to a column in the spreadsheet.
- Add a Spreadsheet Table component to the canvas. Bind its Display Data field to the cells you bound the dimensions and measures to in the previous step.
- Add a Query Prompt Selector component from the Universe Connectivity category and select the prompt you just created from the Source Prompt menu.
- At the bottom of the General tab you can define whether you want the query to be refreshed after the selection has been made, or after clicking the button. Here you can also change the Button Label.
- Preview the dashboard!
The Query Browser gives us the opportunity to connect a dashboard to a Universe without leaving SAP BusinessObjects Dashboards to set up QaaWS, Live Office, or a BI Webservice. Also, we don't have to use the spreadsheet in SAP BusinessObjects Dashboards since we can bind the query results directly to the components.
We used the Query Prompt Selector component to filter the dataset. The component shows a list of values, and after hitting the Apply button the data is refreshed.
From the Query Browser we inserted the output of the query into the spreadsheet, from which we used the data to be displayed in a Spreadsheet Table component. Of course, you can also add, for example, a chart component and use the Query Data option to connect to the result data.
In this section, we will look into one more related component and discuss several connectivity options (SAP BW BEx Query, SAP HANA, and SAP ECC).
With the Query Refresh Button component, which is also located in the Universe Connectivity category, you are able to manually refresh the query. After adding this component to the canvas you need to select the queries that should be refreshed after clicking the button.
In step 3 of the primary recipe we chose to connect to a Universe instead of a BEx query. The workflow with a BEx query is almost the same:
- Instead of choosing Universe, choose BEx query as a data source.
- The OLAP Connections that are configured in your SAP BusinessObjects BI Platform are shown now. Select the OLAP Connection to the SAP BW system.
- Now you can select the BEx query by either browsing through the InfoArea or using the search option. As you can see in the following screenshot, only the first BEx query (0PT_MP01_Q0003) is available. The others are grayed out since the External Access option isn't switched on yet.
- You can now continue from step 5 of the primary recipe.
It is possible to use SAP HANA views (attribute, analytical, or calculation view) as a data source for your dashboard. The only thing you need to do is to create a Universe based on a connection to a SAP HANA system. From there you can reach the Universe in the Query Browser in SAP BusinessObjects Dashboards and use it just like any other Universe.
SAP ECC contains its own little SAP BW Client within itself. This provides us with the opportunity to use data directly from SAP ECC tables in SAP BusinessObjects Dashboards. This topic falls a bit outside the scope of this book, so we won't go into detail, but the general steps you have to take are as follows:
- Activate the SAP BW client within SAP ECC using the RSRTS_ACTIVATE_R3IS program.
- Create an InfoSet using t-code
SQ02. - Activate the BI Release property for the InfoSet using t-code
SQBWPROP. - Create a BEx Query on this InfoSet with BEx Query Designer.
- Create an OLAP Connection in the SAP BusinessObjects Dashboards BI Platform to this BEx Query.
- Use the Query Browser to connect to the BEx Query.