In this article, we’re going to switch attention away from the BEx Analyzer and focus on the BEx Query Designer. First, I will give an overview of where the BEx Query Designer fits into the SAP BW landscape, taking a look at the System Architecture to understand how reports are created and where they can be used.
Overview of the SAP BW Tools Landscape
First of all, let’s go into a bit of detail about InfoProviders. These are the objects, similar to a database, that hold data that reports are built upon. Instead of being called tables, they are given the specific name because an InfoProvider can be an InfoCube (a multi-dimensional set of tables), a Data Store Object (referred to as a DSO), or Info Objects within a BW System.
InfoProviders can also be classed as Logical InfoProviders, which instead of holding data, they hold the mapping rules to where the data resides. For example, a Multi-Provider combines the mapping rules to multiple InfoCubes or DSO objects in one place which then can be used in the Query Designer as though it were just one InfoProvider.
There are also Virtual InfoProviders, which map data connection rules to allow connections to remote data sources. Think of a normal SAP ERP System, instead of bringing the data into a BW System, Mapping Rules can be setup via a Virtual InfoProvider to go straight through to the source system where the data sits and gets updated in real-time, enabling direct reporting from that system.
Once we have the InfoProvider that the Query is going to be built upon, this is where the BEx Query Designer steps in, where the Query is built using the InfoProviders data. Once the Query is defined using drag-and-drop techniques, setting up calculations and so on, the Query can then be used by multiple applications.
This book focuses on the BEx Analyzer, as it is the most used application, but these Queries can also be used in the BEx Web Application Designer (where applications are built using Queries), BEx Web Analyzer (a web-based version of the Excel powered Analyzer) and the BEx Report Designer (for creating printed reports). With these tools that analyse the BEx Queries, we have the choice of either Web (including the SAP Portal) or Excel platforms to carry out reporting activates, and once either of these is set up, users are free to use them to their heart’s content.
One important note: the BEx Report Designer is no longer actively developed by SAP. After purchasing Business Objects a few years ago, Business Objects Tools have become the preferred tool for web reporting. However, the Web Application Designer and Web Analyzer will continue to be supported for quite some time.
Another thing to keep in mind is that although Business Objects is being implemented in many companies, the majority of Business Objects reports that use SAP BW data actually use Queries as a data source, which means the BEx Query Designer and BEx Analyzer continue to be key fundamental tools within SAP’s Business Intelligence offering, and will be supported for a long time to come.
Starting the BEx Query Designer
To start the Query Designer, click on the Start button, select All Programs, and then navigate to Business Explorer. Click on Query Designer option.
You will be presented with a login window. Log into your BW system and the Query Designer will open.
When the program opens, you are presented with a screen consisting of seven sections.
The first section on the left is the InfoProvider section. Every Query must be built using an InfoProvider, and this is the section in which they appear, listing all the available fields that can be added to a Query. This includes all the Characteristics, Key Figures, Attributes and everything else such as calculated key figures.
The next section is Filters, which covers two window areas for Characteristic Restrictions and Default Values.
Whenever a Query has to be restricted to a certain number of records (for instance, if it was required to restrict to four Organisational Units), the Characteristic Restrictions window is where fields would be dragged and the restrictions applied.
The Default Values window is where default values for Characteristics are entered, which are the first set of filters to be applied to a Query. The user will be given the opportunity to override or remove these filters as and when they wish.
The Rows/Columns section can be accessed by clicking the tab underneath the Filter windows, and replaces them with three further sections.
This section is where a Query is built. For instance, to include five fields into a Query they would be dragged from the InfoProvider window into either the Free Characteristics, Rows or Columns sections depending on where the user wants them. The Preview section shows a representation of how the Query would look in the BEx Analyzer. The fields included will then appear by default.
The next section is Properties. Just about every component that can be added into a Query will contain properties, and this is where you can view and edit the properties as you need. The properties include things like field description, display settings, how a Characteristic may be aggregated or totalled and so on.
This section can be switched to the Tasks section (again, by clicking the tab at the bottom). This is an area that changes depending on the Query object that is currently highlighted, displaying different tasks or actions for the different types of Query elements. Any errors in a Query will appear here and tasks will appear to enable you to fix them.
The next section is Messages. When a Query is saved, the BW System checks the Query for completeness and ensures that there are no errors. Any messages relating to this will be shown here.
Toolbars & Menus
The Menu bar
The Query menu acts in a similar way to the File menu in Microsoft Word: New, Open and Save act as you would expect.
Publish is for when a Query needs to be published to a specific Role that has been set up for users to access Queries, or by using the BEx Broadcaster to publish reports via e-mail or another broadcast mechanism that has been setup.
The Edit menu also contains typical menu commands, such as Cut, Copy and Paste. The Display/Change option toggles between the Display mode, where no changes can be made, and Change mode where everything is editable.
Next, there is the View menu. Inside this menu are options to display different screen areas as well as the toolbars you can see. Predefined lets you change between the Standard view and the old edition’s view called SAP BW v3.5.
Technical Names brings up the option to display names as Text, Key or a combination of both. Just like data in the BEx Analyzer, Technical Names have both a Key and a descriptive name (e.g.: ‘Employee’ and the Key, ‘PERNR’).
There are also menu items for the sections of the window detailed in the last section; clicking on these does the same thing as selecting a tab. Exceptions and Conditions are used for advanced reporting and will be detailed later in the book, and at the bottom of the menu there is a Refresh function.
The Tools menu is rather small and just allows the saving of the Object edited within the Query, and Help contains an option for Application Help and the About screen.
As you can see, many icons on the toolbar are repeated in the menu, very much like in the BEx Analyzer. People have their own preference; I prefer to use the controls on the actual page.
Cells, referring to Cell Definitions will covered later in the book.
One final part of the Query Designer interface to note is the bottom-right corner of the screen, where there is the Connection icon. Just like the BEx Analyzer, it shows you if the software is connected to the BW System. If the connection is broken, the icon will show this too.
Creating Our First BEx Query
Now the interface has been introduced, this section will focus on getting down to business and creating our first BEx Query. To start off, I will show how to create a simple Query, to get a feel of how to use the Query Designer.
Adding an InfoProvider
The first step is to find the InfoProvider that holds the information we want to create a report on. Click New Query and a dialog box will appear, giving the option of choosing whichever InfoProvider we want.
The Select InfoProvider window is similar to those seen before: there is a History of recently seen Objects, a Find section, and also the opportunity to use the system hierarchy of the Info Areas to navigate and find the information we want.
This example Query will keep to the HR theme used in the BEx Analyzer article, so this section will create a Report with an ‘Organisation Unit’, a few Free Characteristics, and a Key Figure.
Clicking on Info Areas causes the system to present all the different Info Areas contained in the BW System.
After navigating through the Info Areas, there will be a number of InfoCubes, signified by the cube icons. (DSOs would be represented with cylinder icons, and MuliProvider would have an icon showing cubes stacked upon each other). In the screenshot above, there are three identically named Cubes, and so the Technical Names need to be visible.
To do this, select the Display Object Name As icon (the wrench), and select from the options available. With the Technical Names visible, it becomes easier to determine which InfoCube is required. Double click to Open it.
The Find tool on the sidebar looks and acts just as in the BEx Analyzer, and allows wildcard searches that search both Descriptions and Technical Names.
Once loaded, the InfoProvider will be loaded into the Query Designer. You can see there are three high level folders: Structures, Key Figures and Dimensions.
Adding a Dimension
Dimensions is the name given to high level folders in InfoProviders. Once opened, a list of Descriptors (with the ‘three triangle’ icon) appears. These are not Fields, but still high-level Containers containing Fields.
For instance, opening up ‘Employee’ in the screenshot above reveals two Fields (‘Employee’ and ‘Person’). Every Field listed under Dimensions is a Characteristic that belongs to the InfoProvider.
As mentioned before, this example Query will contain ‘Organisational Unit’, which in this InfoProvider is listed under Dimensions, and the ‘Organisational Assignment’. To add this Characteristic to the Report, you must first find it. Click on Rows and Columns, and drag the Characteristic across to where you want it. Once the mouse is released, it is added into the Report.
Once added, a small Preview appears showing the layout of the Report as it currently stands. This is all that is required to create a listing of ‘Organisational Units’ and ‘Employees’ inside, and run it.
Clicking Save for the first time will open a window asking for a Description and a Technical Name. For the latter, your technical team should let you know the naming conventions of Queries saved into the System. (Spaces are not allowed in a Technical Name.)
Click Save and you have your first defined BEx Query.
You can then open the BEx Analyzer and see how it displays. In this case, it displays a simple list of ‘Organisational Units’.
When field are added to the Rows/Columns section, they will also appear in the Filters section, giving the opportunity to add Default Values to them if you wish. This will be explained in detail later in the book.
Adding a Key Figure
Next, we will add a Key Figure to this Query.
Expanding Key Figures reveals a list of Base Key Figures as well two folders. A Calculated Key Figure is a Base figure with a calculation or formula applied to it, whereas a Restricted Key Figure is a Base figure with an applied filter. These will be expanded on later on.
To add a Key Figure, find the one you want to include in the list and drag and drop into the Columns window.
The Preview will update, showing a column where the Key Figures will reside.
Adding another Dimension, for instance ‘Gender’, adds further breakdowns of the selected Key Figure. The Preview screenshot shows how this builds up the Query, so that each ‘Organisation Unit’ is then broken down by ‘Gender’, and the ‘Number of Employees’ Key Figure is divided accordingly.
Viewing an Edited Query in the BEx Analyzer
When opening a newly saved Query in the BEx Analyzer, it is important to note that if the report was left open, refreshing the Query will bring back the old results. The system will not recognise that a Query has changed, because when a Query is run it is held in the computer’s memory. This means that the design of the Query will only be refreshed if it detects the user is logging on for the first time, or if the session is closed and reopened.
So, if you have made a change to the Query and want to view it in the BEx Analyzer, always make sure to disconnect from the system, connect up again and then refresh your data.
Here, the above screenshot shows our Query as it looks in the Analyzer: we have each ‘Organisational Unit’s ‘Number of Employees’, broken down by the ‘Gender’ dimension with a subtotal.
Accessing The Query Designer from the BEx Analyzer
If you want to make a change to the Query without opening a separate session, it is possible to access the Query Designer through the BEx Analyzer.
Once you have run a report within the Analyzer, the Query can be edited by selecting the Edit Query menu option, either within the Tools icon on the toolbar or through the menu bar. This will open the Query Designer in a window that is linked to the current BEx Analyzer session.
Once it is opened, it is not possible to go back to the Analyzer without closing the Query Designer. While this can be an occasional pain, it does ensure that every time the Query is run after editing through this option, the whole page is refreshed.
Hierarchies are a feature that can help structure Queries, add handy features, and alter the look and feel of a report.
Key Figures can be structured to form a hierarchy. An example scenario which would require this could involve a ‘Headcount’ field which was broken down by a ‘Number of Employees’ field.
By dragging ‘Headcount’ onto ‘Number of Employees’, it will be moved across to the right to show that a hierarchy has been made.
To arrange Key Figures within a hierarchy, drag the fields up or down into the order that you require. A horizontal line will appear to show where the field will appear when dropped.
When loaded into the BEx Analyzer, it shows ‘Number of Employees’ along with the other two fields, but notice the different colours of the headings. The colour coding represents that the two right fields belong to the other field’s level. There is also an ‘expanded’ triangle icon visible in the higher level field. Clicking on it hides the lower level Key Figures, changing the ‘expanded’ icon so that it faces right. This can be a useful tool if you want to have columns that break-down a Key Figure further.
Some Characteristics can also have a hierarchy attached to them. In the Query Designer, clicking on a Characteristic and opening the hierarchy tab in the Properties section gives this option.
This screenshot shows there are currently no active hierarchies within the Query. (They may exist within the BW Tables, but the Query might not be able to see it within the report.) Clicking on the button within the Selected Hierarchy section brings up a window where one can be selected.
A hierarchy can be chosen by name by clicking on the drop down menu, which displays a list of active hierarchies. (Within a BW or SAP system, a field can be placed into any number of hierarchies where they can be set up into certain structures.) After clicking OK, the Properties pane will show that the Active Hierarchy Display check-box is checked, and the Characteristic’s icon will also change.
The above table shows the ‘Organisational Unit’ field structured within an ‘Organisational Plan’ hierarchy. This shows that the top level Unit contains two levels, which can be further expanded to reveal even more levels by clicking on the triangle icons next to them.
Right-clicking on a level provides menu options to expand the table. ‘Not Complete’ brings up a menu that allows all levels to be expanded at the same time, saving time clicking – just select the lowest level to expand all the way down.
Each different level of hierarchies shows a subtotal of the lower levels. For instance, in the above table, ‘Human Resources’ has 13 employees, which are divided into 7 in ‘HR North’ and 6 in ‘HR South’.
When a hierarchy is active, it is still possible for the end-user to alter the view themselves. By turning it on while designing the Query, we are just making the hierarchy functionality available to the end-user, and they have the option of turning it off altogether, or altering the order.