SAP Bank Analyzer rapid- deployment solution for financial reporting with SAP HANA (v35)

SAP Bank Analyzer rapid- deployment solution for  financial reporting with  SAP HANA (v35)

Building Block Configuration Guide

1         Purpose

2. Preparation

2.1 Prerequisites

3         Configuration

3.1                Creation of Aggregated Data Table in SAP Bank Analyzer

3.2                 Implementation of Rapid-Deployment Solution Specific Programs (SAP Note   1614443) in SAP BANK Analyzer

3.3                          SAP HANA – Data Model Overview

3.4                          SAP HANA – Used Tables

3.5                          SAP HANA – Use of Tables in Views

3.6                         SAP HANA – Creating and Filling the REPORTING_STUCTURE Table

3.7                          Balance Sheet Model Overview

3.8                          Profit & Loss Model Overview

3.9                        Sub ledger Models Overview

3.10                       SAP Business Objects information Space Creation

4. Extension of Delivered Package and Associated Reporting Objects

4.1.1                      Add Additional Attributes to Views in SAP HANA Package

4.1.2                      Universe Enhancement

4.1.3                      SAP Crystal Report Enhancement

5. Appendix

SAP Bank Analyzer Financial Reporting with SAP HANA: Configuration Guide

1         Purpose

The purpose of this document is to describe the general configuration steps required to manually set up the configuration within the system landscape that has already been installed using the corresponding installation or configuration guides for installation.

The configuration guide provides you with guidance on the contents of the SAP HANA model SAP.fs.ba and  sap.fs.ind.ba to support you with your decision making process for app modifications you may wish to make to the package. This guide also provides your will guidance on how to make changes to views in the package as well as the reporting object that use these views.

• This configuration guide does not include a full description on how to manually configure the views included in package sap.fs.ba and sap.fs.fnd. ba from scratch. You are expected to adjust and import these using the processes described in the quick guide for the SAP Bank Analyzer rapid-deployment solution for financial reporting with SAP HANA, which is attached to the SAP Note 1626729. Likewise, you are expected to import the universes and crystal reports as described in the quick guide.
• Due to technical restriction on the inclusion of an information space in a FIAR file, this configuration guide does provide you with guidance on how to create the information space from scratch, as well as general guidance on how to carry out you own modifications to the information space.
• The Content of this guide is based on SAP HANA™ 0

2         Preparation

2.1 prerequisites

Before you start the configuration of this scenario, the SAP HANA Studio needs to be installed. Furthermore, you must ensure the connection between the following:

• SAP HANA system and SAP Business Objects Explorer
• SAP HANA system and SAP Business Objects Crystal Reports

For more information, see the quick guide for the SAP Bank Analyzer rapid-deployment solution for financial reporting with SAP HANA, attached to the SAP Note 1626729.

3         Configuration

3.1    Creation of Aggregated Data Table in SAP Bank Analyzer

• In the quick guide, the identification of the relevant tables is described. You need to create the aggregated data table in case it does not exist yet as the aggregated table is included in the Analytic view AN_BALANCE_AGG (see chapter SAP HANA – Use of Tables in views.)

Procedure

1. Create a new Result view:

• Access the transaction using the following navigation path:

IMG     Financial Services  Bank Analyzer     Result Data Layer  Basic settings    Edit Data Structures in the Result Data Area.

• Select the Result Data Area (for example, SBA) where the new Result view is used for.
• In the dialog structure, right-click Result Views and select Choose Sub dialog.
• Choose F5 (or New Entries) t create a new result view. You should use the following setup:

• Double-click Result views: Result Types.
• Choose F5 (or New Entries) to assign a result type.

  which was already identified for the flow data lable described in the quick guide (for example, SF).

• Double-click Result Views: Characteristics.

  Please insert all at least all required please double-click Filter Conditions and maintain the filter conditions.

• Save the entries.

2. Create a new node counter (counter key figure):

• Access the transaction using the following navigation path:

Img                    Financial Services                  Bank analyzer          Basic Settings           Settings for Meta Data                             Key figures          Edit Key figures.

• Make the following entries:

4. Identify the table name of the data table for the aggregated level via selecting the created aggregation view:

The table names of the different levels are listed in the Generated Objects for Aggregation Level section. The relevant data table of the aggregation level is stored in the data table. The data table name is, for example, ?1BA/HM_IITJ_830

5. The aggregation table structure is created. Now, the structure must be filled with data. In this, you can use the transaction.
6. Access the transaction using one of the following navigation options: Transaction code

   /BA1/HW_PREAGG2 or Financial Service Bank Analyzer   Result Data Layer Pre-aggregation Run   Preaggregation

• Make the following entries:

9. Choose Execute.

• All flow data with a timestamp lower or equal to the used data and time in pre-aggregation are aggregated and stored in the aggregated data table (for example /1BA/HM_IITJ_830).

3.2 Implementation of Rapid-Deployment Solution Specific programs (SAP Note 1614443) in SAP Bank Analyzer

Implement the programs listed and described in SAP Note 1614443 into your SAP Bank Analyzer system.

1. Exchange Rate Copy Program (ZMR_FX_Rate_Copy)

   As stated in the quick guide, this program is for an initial copy of exchange rates and exchange rate customizing from SAP Bank Analyzer to standard exchange rate tables only. For automatic update of the TCURR table after the initial copy choose the following navigation option: IMG    Financial Services  Foundation   Market Data  Exchange Rates  Map   Exchange Rate Category to Exchange Rate Type

2.    Master Data Text Copy program (Z_R_AL_IM_CHARTXT

For more information, see the chapters implement and Run Exchange Rate copy program and implement and Run Master Data Text Copy program of the quick guide.

3.3. SAP HANA –Data Model Overview

The Attribute, analytic and Calculation views used for this Rapid-Deployment solution are as follows:

Attribute Views (Package: sap—fs—fnd—ba)

Analytic Views (Package: sap-fs-ba)  

Calculation View (Package: sap-fs-ba)

The tables required for this Rapid-Deployment solution are as follows:

3.5 SAP HANA – Use of Tables in Views                       

Calculation Views, Analytic Views and attribute Views need to be adjusted according to the tables used on customer side. You can replace the table name with the Excel macro Table_name_Replacement_Template.xls. The Excel macro helps automate the table name replacement in the XML files of the different views.

For more details, see chapter adjust the SAP HANA Models According to Customer-Specific Tables Naming of the quick guide.

Use of customer specific transactional tables in views

Use of master data and generated text tables in views    

3.6 SAP HANA  - Creating and filling the REPORTING_ STRUCTURE Table           

To set up a three hierarchy levels reporting structure for aggregation on top of G/L accounts, a REPORTING_STRUCTURE table is created and filled in schema SAP_FS_BA of the SAP HANA Studio.

• This Rapid-Deployment solution focuses on a three hierarchy levels structure for aggregation on top of the G/L account. The use of another hierarchy structure (for example, more than three levels) would require an adjustment of the predelivered content of this Rapid-Deployment solution.

Procedure

1. To create the REPORTING_STRICTIRE table in the schema SAP_FS_BA, you

   must enter and start the following script in the SQL Editor. Copy and paste

   this script into the SQL Editor (in SAP HANA Studio) and execute it:

Create column table  “SAP_FS_BA” . “REPORTING_STRUCTURE” (

“CLIENT”   CHAR    (3)    not null,

“GL_ACCOUNT”    VARCHAR  (10) not null,

“VALID_FROM”     NCHAR   (8)    not null,

“VALID_TO”       NCHAR     (8)  not null,

“REPORT_TYPE”     CHAR  (3),

“ORDER_ACCOUNT”   INTEGER,

“HIERARCHY1”    VARCHAR     (130) not null,

“ORDER_H1”     INTEGER,

“HIERARCHY2”    VARCHAR    (130)   not null,

“ORDER_H2” INTEGER,

“HIERARCHY3”   VARCHAR     (130)   not null,

“ORDER_H3”   INTEGER,

Primary key

(“CLIENT”, “GL_ACCOUNT”, “VALID_FROM”, “VALID_TO”, “HIERARCHY1” ,

“HIERARCHY2” , “HIERARCHY3”))

WITH PARAMETERS    (

‘SESSION_TYPE ‘-‘ SIMPLE’   )

• For more details, see chapter SQL Script for creation of SAP HANA Specific Table (REPORTING_STRUCTURE) of the quick guide.

2. To fill the REPORTING_STRUCTURE table, use the Chart of Accounts Upload

   Macro Template file (Chart_of_Accounts_Upload_Macro_Template.xls)

   The Excel macro is filled out according to the customer-spacific chart of accounts                       structure. Running the macro generates an SQL file. The SQL code of the

   generated file is copied and afterwards pasted in the SQL editor of the SAP HANA

   Studio.

• For more details, see chapter Generate Chart of Accounts Structure (REPORTING_STRUCTURE) of the quick guide.

3.7 Balance Sheet Model Overview

3.8 Profit & Loss Model Overview

3.9 Subledger Models Overview

3.10 SAP Business Objects Information Space Creation

Use

In this activity, you build your own Information Spaces on the basis of the available Calculation and analytic Views.

Prerequisites

SAP Business Object Explorer must be installed and accessible via the BI Launch-pad.

Procedure

1. Access the SBO Explorer via the following address to the BI Launch-pad:

2. Input your username and password and choose Log on.
3. Under My Applications, launch the SBO Explorer.
4. On top right menu, choose Manage Spaces.
5. Expand the node SAP High-Performance Analytic Appliance (SAP HANA) 1.0 <your

   hana connection name>

6. Select the view sap.fs.ba/<technical source name>
7. Choose New Information Spaces.
8. In the Create Information Space dialog box, make the following entries:

8. On the Objects tab page, drag and drop the attributes and key figures from the info cube

   panel to the Facets, Measures and Filters area.

9. Choose OK to finish creating the report.
10. Choose Index Now to create an index for the created information Select.

Result

You have created an information space. You can use it for the reporting and evaluations.

See the following two examples of information Spaces:

1. SUBLEDGER Information Space based on InfoCube name sap.fs.ba/AN_SUBLEDGER:

4 Extension of Delivered Package and Associated Reporting Objects

4.1.1          Add Additional Attributes to Views in SAP HANA Package

If necessary, it is possible to add additional attributes to view. When you do this, you must also take into account the customer specific name space. Before you start the modeling, make sure you know the business logic of the attribute you want to add.

• The general installation and deployment process of SAP HANA™ is not included in this document. For more information, see the SAP HANA™ master Guide.

Procedure

1. Start the SAP HANA Studio.
2. Go to the attribute view in which the attribute is located in.

To add an attribute to an attribute view, proceed as follows:

• Select the required field from the table.
• From the context menu, do the following:
• To set a field as a key attribute, choose Add as Key Attribute.
• You have to define at least one key attribute for the output structure.
• To set a field as an attribute, choose Add as Attribute.
• You can define any number of non-key attributes.
• To define static filter values for field, choose Apply Filter.
• In the Apply Filter dialog box, choose the required operato.
• Enter the filter value.
• To set description mapping, select the attribute from the Output pane.
• Go to the Properties tab page.
• In the Description Mapping property. Choose the relevant value.

4. Activate the attribute view as follows:

Before a view is available for reporting purposes after adding an additional attribute, you have to deploy and activate the view. The activation of the view translates the metadata defined in the information model to

base object.

• To save the attribute view, choose File Save
• From the context menu of the attribute view choose Activate.

5. Add an attribute to an analytic view as follows:

• Select the required field from the table.
• From the context menu, do the following:
• To set a field as a key attribute, choose Add as Key Attribute.

  • You have to define at least one key attribute for the output

• To set a field as an attribute, choose Add as Attribute.

  • You can define any number of non-key attributes.

6. Activate the analytic view as follows:

Before a view is available for reporting purposes after adding an additional attribute, you        have to deploy and activate the view. The activation of the view

• To save the attribute view, choose File
• From the context menu of the attribute view, choose Activate.

7. Add an attribute to a calculation view as follows:

• To make the calculation view work, you need to define the function and execute the SQL SCRIPTS:
• Define the structure of the function output using Output Parameter.

  The Output Parameter can be a locally defined table or an existing table.

• Define the Input Parameter, which is optional, with the two values:
• Scalar value to pass parameters from the frontend tools, which is used to filter the results.
• Table Type to pass the result of one function to the other.
• To make the Column View based on the scripting done, you need to execute the script using the green arrow button in the SQL editor page. On execution the metadata of the calculation view gets translated to a database object.
• You have to do the following in order to define the metadata of the view’s out put structure in the form of attributes and measures.

  • Navigate to the Logical View tab page.
  • Select the required field from the table.
  • From the context menu, choose Add as Attribute to set a field as attribute.

8. Activate the view as follows:

Before a view is available for reporting purposes after adding an additional attribute, you

have to deploy and activate the view: The activation of the view translates the metadata defined in the information model to a database objects

• To save the attribute view, choose File
• From the context menu of the attribute view choose activate

9. After activating the Calculation View, the attribute is added to the report.

4.1.2          Universe Enhancement

• You can find an overview of measures, dimensions and filters included in the two universe templates Balance_Universe and Profit_Loss_Universe in the appendix of this document.

Use

In this activity you enhance universes according to the attribute of the available Calculation and analytic vies.

Prerequisites

• SAP_FS_BA_Connection mut be connected successfully.
• SAP Business Object Information Design Tool must be installed.
• For details about the information design tool, see the information Design tool user Guide.

Procedure

1. Access the Information Design Tool using the following navigation path:

2. In the Repository Resources pane, choose insert Session.
3. In the Add session dialog box, make the following entries and choose OK.

4. In the Repository Resources pane, expand the server and choose Universe             SAP        SAP.FS.BA              Balance_Universe.unx or Profit_Loss_Universe.unx.
5. Right-click a universe and choose Retrieve Universe. The imported universe is retrieved to a local project.
6. Expand te retrieval folder in the local project and open the Data Foundation which should be enhanced (Balance_DataFoundation.dfx or Profit_Loss_DataFoundation.dfx).
7. In the Master pane, chose Detect           Refresh structure, (Check Missing Tables)        Next (Check Missing Columns)                      Next (Added Columns, in this step, all enhanced attributes are displayed; you can select which attributes to add)                      Next (Modified Columns)            Next (Summary of Changes) – Finish. Save the Data Foundation.
8. Open the Business Layer (Balance_Universe.blx or Profit_Loss_Universe.blx) that is the basis of the Data Foundation to be enhanced.

• In the Business Layer pane, choose insert            Enter the Name and Description of the Dimension.
• In the Select area, choose SQL assistant.
• In the dialog box, go to the Tables Pane area. Expand Model (That is, SAP HANA mdoel: for example sap.fs.ba/AN_PROFIT_LOSS). Choose the attribute field that should be enhanced in the model. Choose OK and save the Business Layer.

9. Right-Click the Business Layer (Balance_Universe.blx or Profit_Loss_Universe.blx) that you

   enhance and choose publish       To a Repository. In the dialog box, choose Check all and choose Check integrity. Choose Next and go to SAP                   SAP.FS.BA folder and choose Finish to overwrite the universe (Balance_Universe.unx and Profit_Loss_Universe.unx)

4.1.3          SAP Crystal Report Enhancement

• You can find an overview of measures, dimensions and filters included in the two SAP Crystal Report templates Balance Report and profit & Loss Report in the appendix of this document.

Use

In this activity, you anhance the SAP Crystal Report on the basis of the unverses.

Prerequisites

SAP Crystal Reports for Enterprise XI 4.0 must be installed and accessible via the BI Launch pad.

• For details about how to use the SAP Crystal Reports for enterprise see the SAP Crystal reports for Enterprise User’s Guide.

Procedure

1. Access SAP Crystal Reports for Enterprise via the following address to the BI Launch pad:

2. Input your username and password and choose Log on.
3. Under My Applications, launch the SAP Crystal Reports for Enterprise.
4. In the File Download dialog box, choose Open (if a security warning appears, choose

   Allow). Open the SAP Crystal Reports for Enterprise. In the left bottom corner, the message Logged on to <server> : <port> is displayed.

5. Choose File             Open Report From <server>:<port>, All Folders           Public folders         SAP         SAP.FS.BA. select the SAP Crystal Report (Balance Report and Profit & Loss Report) that you want ot enhance, and choose open.

6.Select the fields that you need from the Result Objects area of the Data Explorer side panel to the Structure mode area of the report.

7. Choose File      Save.

5 .Appendix

Universes

Balance_Universe (Data Foundation Sap.fs.ba/CA_BALANCE)

Measure:

• Amt Balance Currency
• Amt Object Currency
• Amt Local Currency
• Cm Amt Adhoc Balance Currency

Dimension:

• Value Data
• Chart of Accounts
• Chart of Accounts Descr
• GI Accoutn
• GI Account Descr
• Move Type
• Move Type Descr
• Posting Key Fig
• Posting Key Fig Descr
• Debit Credit Ind
• Debit Credit Ind Descr
• Legal Entity
• Legal Entity Descr
• Org Unit
• Org Unit Descr
• Business Segment
• Profit Center
• Fiscal Year
• Fiscal year period
• Balance Currency
• Object Currency
• Local Currency
• Hierarchy1
• Hierarchy2
• Hierarchy3
• Order H1
• Order H2
• Order H3
• Order Account
• Posting Date
• Report Type

Filter:

• Legal Entity
• Reporting Period

Profit_Loss_Universe (Data Foundation sap.fs.ba/AN_PROFIT_LOSS)

Measure:

• Amt Balance Currency
• Amt Object Currency
• Cm Amt Adhoc Balance Currency

Dimension:

• Hierarchy1
• Hierarchy2
• Hierarchy3
• Order1
• Order2
• Order3
• Order Account
• Object Currency
• Balance Currency
• GI Account
• GI Account Description
• Legal Entity
• Legal Entity Description
• Posting Date
• Business Segment
• Chart of Accounts
• Fiscal Year period
• Fiscal year
• Row Count
• Profit Center
• Filter:
• Legal Entity
• Reporting Period

Sap Crystal Reports

Balance Repot

Measure:

• Amt Balance Currency
• Amt Object Currency
• Cm Amt Adhoc Balance Currency

Dimension:

• GI Account
• GI Account Description
• Legal Entity
• Legal Entity Description
• Balance Currency
• Object Currency
• Order Account
• Hierarchy1
• Hierarchy2
• Hierarchy3
• Posting Data
• Business Segment
• Chart of Accounts
• Fiscal year
• Fiscal year Period
• Profit Center

Filter:

• Report Period: From Posting Date
• Report period: To Posting Date
• Legal Entity

Profit & Loss Report

Measure:

• Amt Balance Currency
• Amt Object Currency
• Cm Amt Adhoc Balance Currency

Dimension:

• GI Account
• GI Account Descr
• Legal Entity
• Legal Entity Descr
• Balance Currency
• Hierarchy1
• Hierarchy2
• Hierarchy3
• Order H1
• Order H2
• Order H3
• Order Account
• Posting Date
• Report Type

Filter:

• Report Period: From Posting Date
• Report Period: To Posting Date
• Legal Entity

SAP Best Practices

SAP HANA 1.0

January 2012

English