Data Mart Reporting with SAP BusinessObjects Access Control 5.3
Applies to: Risk Analysis and Remediation (RAR) and Compliant User Provisioning (CUP) capabilities of SAP BusinessObjects Access Control 5.3.
Summary This document details the “Data Mart” functionality of SAP Business Objects Access Control 5.3. The Data Mart functionality allows end users to extract data from RAR and CUP and load it to any reporting tool, such as Crystal Reports. Author:
Sirish Gullapalli GRC Regional Implementation Group
Company:
Governance, Risk, and Compliance SAP BusinessObjects Division
Created on:
30 November 2009
Updated on:
30 August 2010
SAP COMMUNITY NETWORK © 2010 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
Document History Document Version
Description
1.00
First official release of this guide
SAP COMMUNITY NETWORK © 2010 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
Typographic Conventions
Icons
Type Style
Description
Icon
Example Text
Words or characters quoted from the screen. These include field names, screen titles, pushbuttons labels, menu names, menu paths, and menu options.
Description Caution Note or Important Example Recommendation or Tip
Cross-references to other documentation Example text
Emphasized words or phrases in body text, graphic titles, and table titles
Example text
File and directory names and their paths, messages, names of variables and parameters, source text, and names of installation, upgrade and database tools.
Example text
User entry texts. These are words or characters that you enter in the system exactly as they appear in the documentation.
EXAMPLE TEXT
Variable user entry. Angle brackets indicate that you replace these words and characters with appropriate entries to make entries in the system. Keys on the keyboard, for example, F2 or ENTER.
SAP COMMUNITY NETWORK © 2010 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
Table of Contents 1.
Business Scenario............................................................................................................... 1
2.
Background Information ..................................................................................................... 1
3.
Prerequisites ........................................................................................................................ 1
4.
Configuring the Data Mart .................................................................................................. 1
5.
6.
4.1
Enable “Data Mart” functionality in RAR ....................................................................... 1
Note .............................................................................................................................. 3
4.2
Create and Upload Database Driver ............................................................................ 3
4.3
Create DataSource ....................................................................................................... 4
4.4
Schedule Data Mart Background Job in RAR .............................................................. 6
Generating Reports in Crystal Report 2008 ...................................................................... 7 5.1
Creating ODBC Connection ......................................................................................... 8
5.2
Create DataSource Location ...................................................................................... 12
Generating Reports in Microsoft Access ........................................................................ 15 6.1
Creating ODBC Connection ....................................................................................... 15
6.2
Create DataSource Location ...................................................................................... 16
7.
Limitations.......................................................................................................................... 18
8.
References ......................................................................................................................... 20
9.
Comments and Feedback ................................................................................................. 20
10.
Copyright ............................................................................................................................ 21
SAP COMMUNITY NETWORK © 2010 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com
1.
Business Scenario
Data Mart is a new reporting functionality introduced in SP09 of Access Control 5.3. Data Mart functionality is in addition to existing reporting functionalities available in Access Control 5.3. Data Mart is used to extract data from Risk Analysis and Remediation (RAR) and Compliant User Provisioning (CUP) and load it into Data Mart tables. By connecting to Data Mart tables one can generate custom reports on RAR and CUP data.
2.
Background Information
This is an overview of step-by-step configuration provided in this document.
Check all prerequisites to configure Data Mart
Configure your AC 5.3 NetWeaver Application Server Java system o o
Create and Upload the appropriate Data Base Driver for the database where Data Mart tables are deployed. Create a DataSource and add Alias “SAPGRC_DM_ADM” to the DataSource.
Load data from the AC database into Data Mart tables
If the system is successfully configured, you will be able to load RAR and CUP data into Data Mart tables and generate custom reports using your standard reporting tools. This guide will only focus on the steps necessary to configure Data Mart functionality. General requirements for connecting to your reporting tool need to be verified with the respective vendor.
3.
Prerequisites
The following pre-requisites need to be met before configuring and using the “Data Mart” funct ionality:
Installation of Access Control 5.3 SP09 or higher.
RAR capability is deployed (the Data Mart interface resides in RAR)
4.
Single Launchpad for Access Control is deployed (since all “Data Mart” tables are bundled with the “Single Launchpad”). “Batch Risk Analysis” in RAR has completed successfully in “Full Sync” mode.
Configuring the Data Mart
This section provides the steps involved in configuring the Data Mart functionality. It is important to read the prerequisites and background information before you begin.
4.1
Enable “Data Mart” functionality in RAR
Follow steps below t o enable “Data Mart” functionality in RAR 1.
Logon to RAR as administrator
2.
Go to Configuration -> Risk Analysis -> Additional Options
3.
Set “Enable Data Mart Job” option as “ Yes” and click “Save”.
4.
Make sure to restart the server to get the change effective.
Figure 1. To ensure “Data Mart” is enabled, go to “Background Job” and click “Data Mart” which brings up the “Data Mart” screen as shown in the Figure 2.
Figure 2.
Note If your AC application is lower than SP 10 please follow steps 4.2 & 4.3. If your AC application is SP 10 or higher, please ignore steps 4.2 & 4.3 and continue with step 4.4.
4.2
Create and Upload Database Driver
It is a recommended practice to use “Vendor SQL” to populate data into Data Mart tables. To use “Vendor SQL”, upload the respective database drivers provided by the database vendor. In this example Data Mart tables exists in a n “SQL 2005” database. Therefore, we will be creating and uploading drivers related to “SQL 2005” . 1. Logon to Visual Admin and select “Drivers” under “JDBC Connector”. Click Provide drive name and click “OK”.
to upload drivers.
Figure 3.
2.
During the creation of “Drivers”, Visual Admin will ask to load database drivers. Select all files that are related to database drivers provided by the vendor. For the “SQL 2005” database we will be uploading following three “.jar” files.
msbase.jar
mssqlserver.jar
msutil.jar
Once all files are loaded, click OK.
Figure 4.
4.3 1.
Create DataSource Once the “DataSource” is created, add alias to the datasource definition. Click on “Add Alias” and provide an alias name, such as “SAPGRC_DM_ADM”.
Figure 5.
2.
Once the “Main” tab information of the DataSource is configured, click on the “Additional” tab and enter the following information related to “GRCDMDB” DataSource. a. Configure “Connection Pooling” information. b. Make sure “SQL Engine” is set to “Vendor SQL”.
Figure 6. 3.
Once the “Additional” tab information of the DataSource is completed, test the connectivity of the DataSource created. Click on the “DB Initialization” tab and add a sample query statement. Click the “Execute” button and a successful message should be returned as shown below.
Figure 7. Once the “DataSource” is successfully defined, the next step will be scheduling a “Data Mart” job in RAR to populate all “Data Mart” tables.
4.4
Schedule Data Mart Background Job in RAR
Logon to RAR and select “Data Mart Job” listed under Configuration
On the “Data Mart” screen check boxes for:
Background Job.
o
Extract master data (which is always extracted as a full synchronization)
o
Extract transactional data o
o
Select “Full Sync” of transactional data for this first execution. (Incremental synchronization may be performed after the initial execution with full synchronization performed periodically.
Include Compliant User Provisioning data (If you want to report on CUP)
Figure 8.
After successful completion of the “Data Mart” job, Access Control data will be populated in all “Data Mart” tables and is ready for reporting .
5.
Generating Reports in Crystal Report 2008
Once “Data Mart” tables are populated, we can run reports against them. In this document we will be detailing how to generate a sample report using “Crystal Report 2008”.
Pre-requisites:
“Data Mart” tables are populated with Access Control data.
Crystal Reports 2008 SP1 or higher is deployed and configured.
Procedure: 1.
Create ODBC connection between Crystal Reports system and the Data Mart database.
2.
Create a New Data Source location in Crystal Reports.
3.
Generate reports in Crystal Reports
5.1
Creating ODBC Connection
The ODBC data source will need to be created before using the sample reports. Following are steps to create an ODBC data source connection. 1. From the Start Menu → Select Control Panel → Administrative Tools → Data Sources (ODBC). 2. Select Tab 2 → System DSN. 3. Click on “Add”. 4.
Select the datasource driver as “SQL Server”.
5. Click “Finish”.
Figure 9. 6.
Enter the data required to create the ODBC connection and click “Next”. Example: Name:
nspah226
Description: SQL Database having DM tables Server:
10.48.144.126
Figure 10. 7.
In the authentication screen select the radio button for “SQL Server Authentication” , check the box “Connect to SQL Server…” and provide login credentials
Figure 11.
8.
Change the default database to the correct database. Check boxes for
and click “Next” .
•
Use ANSI quoted Identifiers.
•
Use ANSI nulls, paddings and warnings.
Figure 12. 9.
Select the appropriate language and click “Finish”
Figure 13. 10. The new ODBC screen will appear and at this point you can test the connection by selecting “Test Data Source”.
Figure 14. 11. If successful then you will receive a “T est Completed Successfully”
Figure 15.
5.2
Create DataSource Location
Once the ODBC connection is established, the next step is to set the “Data Source” location in Crystal Reports. 1. Open Crystal Reports 2008: Start Menu → All Programs → Crystal Reports 2008 → Crystal Reports 2008 2.
Select a Sample Report: From the Crystal Reports Toolbar – Select File → Open → then select a Sample Report
3.
Set Data Source Location: From the Crystal Reports Toolbar – Select Database → Set Datasource Location
Figure 16.
4.
In the “Available Data Sources” screen, select “ODBC” under “Create New Connection”.
5.
In the pop-up screen, select the Data Source “nspah226” that was configured earlier and click “Next”.
6.
Enter login credentials of the Data Source where the “Data Mart” tables exist and click “Finish”.
7.
Once the connection is established you can see Data Source “nsaph226” under “My Connections”. Drill down to GRCDMDB4 dbo, to make sure all “Data Mart” tables are available to generate reports.
Figure 17.
Once the connection to the “Data Source” is established a nd you are able to see Data Mart tables, custom reports can be generated by selecting respective table(s) and associated field in the selected table(s).
6.
Generating Reports in Microsoft Access
In this section we will be detailing how to generate a sample report using “ Microsoft Access”. Pre-requisites:
“Data Mart” tables are populated with Access Control data.
Microsoft Access is deployed and configured.
Procedure:
6.1
1.
Create ODBC connection
2.
Create Data Source location in Microsoft Access
3.
Generate reports in Microsoft Access.
Creating ODBC Connection
The ODBC data source will need to be created before using the sample reports. Follow steps mentioned in section 5.1 to create an ODBC data source connection.
6.2
Create DataSource Location
Once the ODBC connection is established, the next step is to set the “Data Source” location in Microsoft Access. 1.
Open Microsoft Access: Start Menu → All Programs → Microsoft Office → Microsoft Access
2.
From the Microsoft Access menu bar choose External Data → More → then select ODBC Database
Figure 18.
3.
In the pop-up select the radio button “ Import the source data into a new table in the current database” and click OK.
Figure 19. 4.
In the pop-up screen, select the Data Source “nspah226” that was co nfigured earlier and click “OK”.
Figure 20.
5.
Enter login credentials of the Data Source where the “Data Mart” tables exist and click “Finish”.
Figure 21. 6.
Once the connection is established you can see all “Data Mart” tables and can generate reports against these tables.
Figure 22.
7.
Limitations
Following are some limitations regarding the Data Mart functionality:
MaxDB is not supported for Data Mart as of SP09. Refer to SAP Note # 1399116.
You must use these steps to rerun any aborted or errored data mart synchronization job in RAR: 1. Delete all entries from the GRC_DM_ETL_LOCK table. 2. Rerun Data Mart - ETL in full-sync mode
8.
References
SAP Note # 1369045 AC SP09 Data Mart Design Description SAP Note # 1243085 Available Documentation for GRC Access Control
9.
Comments and Feedback
Your comments and feedback are welcome. You may contact the Regional Implementation Group of SAP BusinessObjects Governance, Risk and Compliance solutions by one of the following methods.
SAP Corporate Portal: https://portal.wdf.sap.corp/go/grc-rig SDN/BPX:
https://www.sdn.sap.com/irj/bpx/grc
Email:
[email protected]
10. Copyright © 2010 SAP AG. All rights reserved. No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Microsoft, Windows, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation. IBM, DB2, DB2 Universal Database, OS/2, Parallel Sysplex, MVS/ESA, AIX, S/390, AS/400, OS/390, OS/400, iSeries, pSeries, xSeries, zSeries, System i, System i5, System p, System p5, System x, System z, System z9, z/OS, AFP, Intelligent Miner, WebSphere, Netfinity, Tivoli, Informix, i5/OS, POWER, POWER5, POWER5+, OpenPower and PowerPC are trademarks or registered trademarks of IBM Corporation. Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated in the United States and/or other countries. Oracle is a registered trademark of Oracle Corporation. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group. Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc. HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of T echnology. Java is a registered trademark of Sun Microsystems, Inc. JavaScript is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape. MaxDB is a trademark of MySQL AB, Sweden. SAP, R/3, mySAP, mySAP.com, xApps, xApp, SAP NetWeaver, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary. These materials are subject to change without notice. These materials are provided by SAP AG and its affiliated co mpanies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP Group products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty. These materials are provided “as is” without a warranty of any kind, either express or implied, including but not limited to, the implied warranties of merchantability, fitness for a particular purpose, or non-infringement.
SAP shall not be liable for damages of any kind including without limitation direct, special, indirect, or consequential damages that may result from the use of these materials. SAP does not warrant the accuracy or completeness of the information, text, graphics, links or other items contained within these materials. SAP has no control over the information that you may access through the use of hot links contained in these materials and does not endorse your use of third party web pages nor provide any warranty whatsoever relating to third party web pages. Any software coding and/or code lines/strings (“Code”) included in this documentation are only examples and are not intended to be used in a productive system environment. The Code is only intended better explain and visualize the syntax and phrasing rules of certain coding. SAP does not warrant the correctness and completeness of the Code given herein, and SAP shall not be liable for errors or damages caused by the usage of t he Code, except if such damages were caused by SAP intentionally or grossly negligent.
