Structuring SAP BW Queries for Re-Use

Structures

When dragging Characteristics from the InfoProviders into the Columns section of the Query Designer, new objects are created which are named Key Figures. The icon next to the Key Figures section represents a Structure. This is how basic structural components of a BEx Query are grouped together.

clip_image002[4]

Key Figure Structures allow the user to combine any number of Key Figures, whether they are regular, Calculated, or Restricted.

clip_image004[4]

This also means that it is possible to save Structures and re-use them in other Queries. After giving the Structure a Description and a Technical Name in the Properties section, right-clicking and selecting Save As will save the Key Figure Structure.

clip_image006[4]

The saved Structure will then reside in the InfoProvider section, under the Structures and With Key Figures folders. This means that even if the fields are removed from the Query, it takes a single drag-and-drop to restore the Structure, and it can also be used in any Query that uses the same InfoProvider.

There are also Characteristic Structures. These differ in that they are not created automatically; they are optional components of a Query. Also, unlike Key Figure Structures which allow the user to group together Key Figures, a Characteristic Structure is principally used to restrict data for the Characteristic.

clip_image008[4]

Right-clicking in the Row section and selecting New Structure from the context menu creates a blank Structure. This will require a Selection, which will allow the adding of Characteristic Selections. Right-click on the new Structure and select New Selection, and then double-click the blank Selection, which will bring up the Select Selection window.

clip_image010[4]

Here, we can drag across a Characteristic to the Selection and select values to filter it by.

When a Query is run, every value in a Characteristic which meets the Query selection criteria is then displayed. What the Selection does is filter to the specified values, but only for that specific row. For example, the above screenshot shows a Selection set-up that will filter the Query’s ‘Personnel Area’ row, showing only the values for the four cities determined in the Selection.

Multiple Characteristics can be added to further refine the Selection.

The resulting Structure will look like a pre-determined Dimension, but instead is a customised Restriction, and any number of these can be created within a Query. This gives a lot of flexibility in a report, which means that individual rows can show different Characteristics.

clip_image012[4]

Characteristic Structures can be saved in the same way as Key Figure Structures, by right-clicking and selecting Save As. Make sure a Description and a Technical Name have been entered (the Save As window will give an opportunity to do this if nothing has been entered in Properties). They will appear under the Structures and Without Key Figures folders in the InfoProvider section.

clip_image014[4]

Running a report with Characteristic Structures show how combining different Characteristics with restrictions is possible, all within the same report. This shows that we are not restricted to dragging and dropping fields into a Query and requiring a new column each time.

Cell Definitions

Using Structures in a Query brings the opportunity to use Cell Definitions. This means that formulas can now be created for individual cells in a report. This function adds another layer of flexibility to your Queries.

A report that is only made up of dragged in Characteristics would show the values that come from the Key Figures, or the Characteristics as displayed in the report. But, with Structures in place, there is the facility to define individual formulas for every cell, just as in Excel.

clip_image016[4]

The above report is the result of two Structures: a Key Figure Structure that includes the Key Figures ‘Age in Years’ and ‘Length of Service’, as well as a Characteristic Structure that filters ‘Personal Data’ by Male, Female (from ‘Gender’) and German, American (from ‘Nationality’). The resulting values are showing the totals.

If we wanted to change the totals for ‘Male’ into averages, then we can create a Formula which will divide ‘Age in Years’ by ‘Number of Employees’.

clip_image018[4]

So that ‘Number of Employees’ can be used for a formula without being visible on the report, it can be made invisible by adding it to the Structure, and selecting “Always Hide” in the Properties section, under the Display tab. The icon will change from green to grey to signify this.

clip_image020[4]

Then, right-clicking on the Structure and highlighting New Formula will create a blank formula. Double-clicking on that will bring up the Change Formula window where a formula that will divide ‘Age In Years’ by ‘Number of Employees’ can be created.

clip_image022[4]

After creating another Formula to create an average ‘Length of Service’, this Structure has invisible fields for both, which will ensure that only the averages will be shown in the report.

clip_image024[4]

The resulting report shows the averages for each row of the Characteristic Structure.

clip_image026[4]

As a result of having two Structures in the Query, the Cells button on the tool bar is no longer greyed out, as seen previously. Clicking on it changes the Query Designers to a Cell view. (This also adds a Cell tab to the window, next to Filter and Rows/Columns.)

clip_image028[4]

This view shows a grid of all the Key Figures and Characteristics with Restrictions that have been defined, and formulas can be defined in each cell. It is important to note that, by default, the Query automatically works out what needs to go into each cell, based on the Selections and Key Figures present. Once a formula is in place in a cell, it will override the default Query action.

One use for this function is to add special factors into certain cells. Right-clicking and selecting New Cell Reference, adds a reference which describes what formula is currently active. Then, selecting New Selection makes the cell editable, so that additional Selections can be added to the cell. Selecting New Formula works in the same way.

Re-using Structures

We have seen how Structures can be re-used by dragging and dropping from the InfoProvider section into Rows and Columns. When creating and re-using Structures, there are however a few things you need to remember.

clip_image030[4]

Firstly, when Structures are created and saved, they are greyed out in the Query view, which means that they become display only. To edit them, the Change icon must be made active in the Properties section of the Structure. Bear in mind that any changes will be applied to every Query that uses the Structure within the InfoProvider, so caution is advised, especially if they are used by other users across the system/company, affecting their Queries. Not to say that they shouldn’t be used, considering the time-saving qualities of saving Restrictions and Formulas within a Structure!

clip_image032[4]

Secondly, if you wish to alter a Structure for a specific Query, then there is the option to Save As a separate Structure. However, it will still be available to all users in the InfoProvider. If you wish to alter the Structure without making it available, then selecting Remove Reference will unlink the Structure from the global Structure reference, which means that any changes made will only apply to the individual Query.