Data mart is a capability released in Support Package 9 for SAP BusinessObjects Access Control’s risk analysis and remediation (RAR) and compliant user provisioning (CUP) capabilities. It allows you to export SAP BusinessObjects Access Control tables into another reporting tool. (For more information, see SAP Note 1369045.) With this feature, SAP BusinessObjects Access Control can use the extraction, transformation, and loading (ETL) process to fill some RAR and CUP tables (all tables that start with GRC_DM_) and allow you to read and then correlate the data of these tables.

Using the data mart feature allows you to better involve the business in the analysis, remediation, mitigation, and continuous compliance phases of risk management by representing the more technical data in the classical output of RAR as a business-readable layout created by you. We’ll show you how to configure the data mart feature in your RAR system and how to connect it to a reporting tool, using Microsoft Access as an example. We’ll start with a general data mart job process and move through the steps from there.

Data Mart Job Process  

Figure 1 shows the data mart job process. VIRSA_CC and VIRSA_AE are tables of RAR and CUP capabilities; these tables contain technical-level information. A data mart job reads and correlates, with the ETL process, these tables to fill GRC_DM_CC and GRC_DM_AE tables with more business-oriented information. Table 1 shows some simple examples of how the business-oriented information can differ.

Figure 1

SAP BusinessObjects Access Control data mart job

Table 1

Technical information versus business information

How to Configure Data Mart Functionality  

There are five steps to take before using data mart features. Figure 2 shows these steps graphically. Now let’s go through them in more detail.

Figure 2

Steps to perform before using data mart functionality

Note
For more basic information about data mart features, see SAP Notes 1168120 and 1168508.

Step 1. Enable offline risk analysis. In RAR, follow Risk Analysis > Additional Options and in the Configuration tab, select Yes next to Enable Offline Risk Analysis (Figure 3). (For more information, see SAP Note 1126251.) Offline Risk Analysis is a prerequisite for data mart functionality because all data inserted in table VIRSA_CC_PRMVL (which contains the result of risk analysis at permission level) is used to populate data mart tables. If this table is empty, after data mart job execution data mart table GRC_DM_CC_SOD is not populated.

Figure 3

Enable offline risk analysis

Step 2. Confirm synchronization has successfully completed. In RAR, click the Informer tab and follow Management View > Risk Violations (Figure 4). If the summary data and graphics are populated, it means the batch risk analysis job has worked. (For performance problems, you can also exclude critical roles and profiles — see SAP Note 1034117.)

Figure 4

The Informer tab with management report populated

Step 3. Define and configure a data source to access the database on which SAP BusinessObjects Access Control runs. On J2EE Engine Visual Administrator you must configure the data mart data source on JDBC Connector services (Figure 5). (For more, see this SAP Help link.) In addition to the SAPSR3DB standard data source you must create another data source, in this example named GRC. On the Oracle database the driver class is oracle.jdbc.OracleDriver. (For other driver class database vendors, see this wiki.) You can find a detailed data source configuration guide at this link.

Figure 5

Visual Administrator JDBC connectors — data mart data source

Step 4. Enable the data mart job. Support Package 10 introduced a configuration parameter — go to the Configuration tab, then follow Risk Analysis > Additional Options (Figure 6). Select Yes so that VIRSA_CC_CONFIG is enabled. (For more on this setting, see SAP Note 1397653, and to address any troubleshooting issues, see SAP Note 1434170.)

Figure 6

Enable the data mart job

Step 5. Schedule the data mart job. In the About tab of RAR, you can see the release and Support Package deployed (Figure 7). You can reach the data mart interface in the Configuration tab by clicking the Background Job node on the left (Figure 8). Here you can schedule the data mart job by clicking the Schedule button. RAR tables are included by default. To include CUP tables, select the Include Compliant User Provisioning Data option.

Figure 7

RAR About tab

Figure 8

Data mart interface

Keep in mind that as of Support Package 10, the data mart feature had not yet been fully verified with all supported databases with large record sets. For more, see SAP Note 1417345.

Now that you’ve completed setting up the data mart in RAR, let’s look at how to connect it to a reporting tool such as Microsoft Access.

Connect Microsoft Access to RAR

Once you enable the data mart feature, try your connection with your reporting tools. In this example, we’ll use Microsoft Access 2010. Keep in mind the technical limit of your database. (For more information see this Microsoft Access link and SAP Note 1446126.) There are two key steps for configuring your data mart on the client side: first defining your open database connectivity (ODBC — see this wiki) on the client side, and then opening your external reporting tools.

In your client, define a data source. In this example, we are using Oracle client 11 (see SAP Note 1399116 for MaxDB database). To define an ODBC entry in Microsoft Windows 32 bit, you can use the program odbcad32 (Figure 9). You can find this program on the C:WindowsSystem32 directory; with a 64-bit operating system at the C:WindowsSysWOW64 directory.

Figure 9

Program odbcad32.exe Microsoft OS ODBC Data Source Administrator

Then click the Add... button to create a new data source based on the database driver for which you want to set up a data source (Figure 10).

Figure 10

Add a new data source

Configure the Data Source Name, TNS Service Name, and User ID on the Oracle Driver based on your installation (Figure 11). You can ask your system administrator for more about this. The TNS Service Name is based on a file in Oracle client installation directory C:app$WinsowsUserID$product11.2.0client_1networkadmin.

Figure 11

Oracle ODBC driver configuration

This file can contain the syntax shown in Figure 12. This syntax gives you a real example of how this file should be. A common error is to set the port as 1521 (Oracle standard port) instead of 1527. See this wiki, where you can find the syntax of the tnsnames file.

Figure 12

Sample syntax

Open Microsoft Access and select the newly created connection. Click External Data and then ODBC Database. You can get external data by two ways: importing the source data into a new table in the current database or linking to the data source by creating a linked table (Figure 13). We recommend the second choice because data mart tables often contain a large amount of data. With the import choice, all data is transferred from the server to the client locally.

Figure 13

Select how to import data from your external tool

Click OK and you see the screen in Figure 14. Here you can see the data sources. Select the appropriate one (e.g., GRC).

Figure 14

Select the data source

Then you must authenticate with a user ID and password defined at the JDBC Visual administrator level (Figure 5). Ask your system administrator to set it as read only and to be available only on GRC_DM tables, which is the permission level of this user ID. If the connection works, you can see and then choose all data mart tables for CUP and RAR capabilities (Figure 15).

Figure 15

Data mart tables for CUP and RAR

At the end of this authentication process, you are connected to the database and you can select all tables and link them in your reporting tool (Figure 16).

Figure 16

All linked RAR data mart tables

The configuration phase is finished. Now we’ll show how to produce a customized report to involve business in the process.

Define a Set of Queries to Involve Business

Once you’ve linked all data mart tables, there are no queries or reports that are already prepared. You should create your report based on these data mart tables because they have the business-oriented information in addition to the technical-level information. The ETL processes this data and produces the tables (that start with GRC_DM) that contain RAR data into a business-oriented form.

You can also import other SAP data in an Access database. For example, table USR02 in SAP ERP Central Component (SAP ECC) contains the user master records. Here you can find the TRDAT field, which you can use to know the date of the last logon. The number of data mart tables involved is small. All risk analysis data (at permission level) is contained in a single table (Figure 17).

Figure 17

Risk analysis data at permission level

As shown in Figure 18, in the table you can see 23 fields. (For more information, see SAP Note 1369045). This table contains the risk analysis for users, roles, and profiles. The GENOBJTP field is used to distinguish among users (value 1), roles (value 2), and profiles (value 3). The GENOBJID field is used to fill the object type. This means that if GENOBJTP equals 1, GENOBJID contains the user ID, when GENOBJTP equals 2, it contains the role analyzed, and when GENOBJTP equals 3, it contains the profile.

Figure 18

GRC_DM_CC_SOD schema

In our experience, the standard SAP BusinessObjects Access Control reports are too large to be managed. In the query, we can put a filter to split the output in many Excel files. One other issue we have discovered in standard GRC reports is that some fields are not separated. In the example in Figure 19, there is a risk analysis on a user at permission level. You can see that the transaction code is concatenated into a long string. The same result is in the exported Excel file (Figure 20).

Figure 19

Toolbox - Article Manager - Improve Business Reporting on Your RAR Data Using Data Mart Functionality

Figure 20

RAR ad hoc risk analysis report into Excel

Using data mart functionality, you can create a query named 09 – SOD010 (Figure 21). In this query, we have joined the risk analysis results with user information and statistics use.

Figure 21

Example of query with data mart table and SAP ECC standard tables

Table 2 describes the tables and queries used to create the final query. In the Source column, anything that says GRC is from the SAP BusinessObjects Access Control database and entries with ECC represent back-end data. ACCESS identifies a standalone table that is inserted for better management of the data.

Table 2

List of tables and queries used to create the final query

Once the query is ready, you can easily prepare the output in a report (in Access) as shown in Figure 22.

Figure 22

Report of query 09-SOD-010

When to Use It

The risk management process is formed by three phases:

• Phase 1, which includes risk recognition and rule building and validation
• Phase 2, which includes analysis, remediation, and mitigation
• Phase 3, which is continuous compliance

You can use data mart features in phases 2 and 3. You can use it in phase 2 when you see results of the first analysis. This result is important because it’s the first dashboard that shows data analysis, SoD conflicts cleaning, mitigations, and so on. This report is more accepted by the business because it contains important business information such as the cost centers, HR information, and business units. In phase 3 you can use it to manage continuous compliance, as each person responsible can see all risks related to a process or business unit and then decide what to do about it.

Massimo Manara

Massimo Manara is an SAP-certified security and compliance consultant at Aglea s.r.l. (www.aglea.com), the only Italian company whose core business is SAP security and compliance. He has nearly 10 years of experience in IT security and a bachelor’s degree and master’s degree in security computer science and on SAP projects.

You may contact the author at mmanara@aglea.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.

Andrea Cavalleri

Andrea Cavalleri is an SAP-certified security and compliance consultant. He founded Aglea s.r.l. in 2003. He has 12 years of experience in IT and more than 15 years experience as developer in Visual C++ and Microsoft Access. Andrea has been team leader in more than 30 SAP security projects and is a teacher for SAP Italy courses since 1999.

You may contact the author at acavalleri@aglea.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.