SAP BW Security
SAP Business Information Warehouse or SAP BW or SAP BI is the Business Intelligence and Data Warehousing Product in SAP Netweaver Suite. Being an OLAP system, it has its own security requirements which are often different from a standard OLTP system like SAP ECC and hence this separate discussion. However, before getting into the nitty-gritties of BW security, let us first take some time off to discuss data warehousing in general and how SAP implements it the SAP BW solution.
A data warehouse (DW) is a database used for reporting. It's usually separate from a database used to store transactional data as the goals of reporting is significantly different from OLTP systems. While OLTP systems are optimized for the preservation of data integrity and speed of recording of business transactions through the use of database normalization and an entity-relationship model, OLAP systems are optimized for speed of data analysis. Frequently data in data warehouses are denormalized via a dimension-based model. Also, to speed data retrieval, data warehouse data are often stored multiple times—in their most granular form and in summarized forms called aggregates. Data warehouse data are gathered from the operational systems and held in the data warehouse even after the data has been purged from the operational systems.
Having a separate data warehouse system is beneficial in many respects.
A data warehouse provides a common data model for reporting irrespective of the source of data. It's common that data from multiple operational systems are loaded into a central data warehouse.
Data warehousing solutions help in the implementation of efficient decision support systems where key users get access to key data about the enterprise to understand past histories and make future forecasts.
It's typical that reporting on historical data is time-intensive. A separate data warehouse allows the execution of complex queries without unduly affecting the performance of operational systems.
The structure of the SAP BW solution can be divided into the four general layers given below.
- The extraction, Transformation, and Load (ETL) layer are used for extracting data from one or more sources, applying transformation rules on extracted data, and loading it into the Data Warehouse Area.
- The Data Warehousing layer consists of various SAP BW specific data structures (e.g. DataStore Objects, InfoObjects, InfoCubes, etc) to store information.
- Presentation layer comes with tools to analyze data stored in the data warehouse and allows the creation and presentation of reports for end-users.
- Planning and analysis capabilities – In addition to reporting, the latest version of SAP BW provides capabilities for the user to create planning scenarios and perform tasks such as budget calculations.
A related development in the history of SAP BW was SAP’s acquisition of Business Objects (BO). BO has introduced a number of new presentation tools into the SAP Business Intelligence landscape like Crystal Reports, Xcelcius, InfoView. Other than Crystal Reports which introduces some new security concepts, most of the other tools allow the use of the current security model used for BW in general.
BEX Analyzer or Business Explorer Analyzer or Simply BEX is the core reporting tool in SAP BW. It can be launched from the Analyzer icon from the SAP GUI menu or through the transaction RRMX. It is Add-On to Microsoft excel and allows the executing of reports (queries) on BW data. It has links for the creation/change of queries as well, though the actual update is done in a separate BW application, the Query Designer. The screen below shows the BEX toolbar inside Add-Ins and the result of running a query. The different icons in the toolbar allow us to open, save, refresh, and change a query. This is a simple test query that shows the amount, price, and quantity of a material type sold to different customers. The results are filtered for the calendar year/month – AUG, 2010. In the report below, material and customer are characteristic info objects in the cube while amount, price, and quantity are the key figures.
In addition to queries, Bex Analyzer can also be used to execute or display Workbooks. Workbooks are basically the results of the execution of the query. For example, we can save the report obtained above as a workbook. In fact, the different tabs in a workbook can store the results of different queries obtaining data from completely different InfoProviders.
We can readily appreciate that since BEX is really a single application launched through a single transaction, the general transactional-based security model might not be as effective to secure the multitude of different queries that can be run through it. A more logical security model for queries will be one that allows us to secure individual info providers, characteristics, and key figures. SAP provides us two methodologies to do just that, reporting authorization objects as used traditionally till BW 3.5 and the newer Analysis Authorizations which were introduced as part of BI 7.
Query Designer, as the name suggests, is an application within SAP BW which allows us to create new queries or display/ change existing ones. It can be launched by trying to change a query in the BEX analyzer or by a separate link in the SAP GUI menu. The options in the query designer have changed quite a bit between BW 3.5 and BI 7. However, the essential functionality remains the same. Let's start our discussion by displaying a query in the BW 3.5 designer.
The leftmost bar displays a list of InfoObjects, both characteristics and key figures, which are defined for the InfoProvider. The rest of the Designer displays the different design areas in the query. Thus we have separate areas for filters, free characteristics, rows, and columns. The bottom right area gives a preview of the query output. This is how the result from the query will look like once it's executed through Bex. We now selectively drag InfoObjects into the different areas of the query depending on our reporting needs. In general, characteristics appear as filter criteria, free characteristics, or rows while keyfigures appear as columns. Characteristics also should be restricted to particular values as otherwise all data for them will be pulled into the query result and result in long execution times for the query. Characteristics can be restricted to actual values or to input variables which prompt user for values during query execution. In the displayed query, the filter criteria calendar year/month is restricted to Aug 2010 while the material is restricted to an input variable. We also have an option of using authorization variables, where an input variable is automatically filled by the authorized values for the executing user.
Query Designer also allows the use of calculated key figures, restricted key figures and provides many different options for controlling the display of the InfoObjects. We would not get into these details as our intention is only to concentrate on the security aspects of query design. We end our brief introduction to Query Designer by opening the same query opened in Query Designer for BI 7. Though the look and feel, and certainly the features, is different it has the same basic areas. The difference which is readily observed is a separate tab to contain all the filter criteria. One important factor to note is while queries designed in the old designer can be opened in the new version, the reverse is not true.
Select Authorization Concept
SAP provides two different ways of securing OLAP data in BW. The first is traditional and till BW 3.5 the only way, using Z authorization objects for reporting. The second way, which was introduced as part of BI 7, uses analysis authorizations. So the first step in BW security should always be to choose the concept which we want to use in our BW landscape. We can reach the switch settings option through the transaction SPRO as shown below
Once inside the transaction, we just choose the appropriate authorization concept setting and save our entry. Though it appears to be a single setting, we should give some thought about which authorization concept to use as migrating from one concept to another takes a significant amount of time for design, testing and implementation
SAP recommends that for new projects at least we use the concept of analysis authorizations for security as it provides significant advantages over the old concept. There is also the possibility that in the future SAP might stop supporting the old authorization concept completely in their products.
Before going into the detailed configuration for analysis authorizations, it might be worth looking at a few of the advantages of analysis authorizations over-reporting authorization objects.
Reporting authorization objects are Z or Y objects of the RSR class (SAP Business Information Warehouse – Reporting). As authorization objects, they are limited to a maximum of 10 authorization fields per object. Analysis authorizations have no such restrictions.
The new concept allows us to separately secure the navigational attributes used in an InfoProvider. For example, the authorization object 0COSTCENTER can have different security when it appears as an InfoObject in an InfoProvider and when it appears as a navigational attribute for another InfoObject. In the old concept, both these cases will have the same security. SAP Business Information Warehouse – Reporting