Getting Started with Crystal Reports and SAP HANA

Former Member · ‎2013 Mar 18

Overview

This document is for report developers who are new to SAP HANA modeling and reporting. It covers connectivity options to SAP HANA from SAP Crystal Reports for Enterprise and Crystal Reports 2011, differences, use cases, best practices, and resources.

The information applies to Crystal Reports’ 14.0.4 (i.e. 4.0 SP4) and higher service packs.

Related Product Availability Matrices (PAM):

SAP Crystal Reports 2011	http://scn.sap.com/docs/DOC-21609
SAP BusinessObjects BI 4.0 SP04 (including FP3) includes Crystal Reports for Enterprise	http://scn.sap.com/docs/DOC-29175

What is SAP HANA?

SAP HANA™ is a platform that leverages in-memory computing and columnar data storage to enable reporting and business decisions in real-time on massive amounts of data. HANA can aggregate and process millions of rows of data at sub-second speed. The speed depends on a variety of factors like the extent to which the HANA model (Analytical View) has been optimized, the type of queries used, and the volume of data that has been requested. Data is fresh and live; there is no need to wait for the next ETL load.

Some Benefits

Real-time access with real-time replication services and data integration capabilities
Flexible data calculation engines, reporting and analytics models
Simplified IT landscape with a multi-purpose in-memory appliance and fewer layers

What is a typical use case for Crystal Reports off SAP HANA?

Reports that aggregate and perform calculations on huge volumes of data returning a small result set make best use of HANA’s power. An individual report that answers a specific question, executes a query to answer it, and enables subsequent execution of reports and queries to answer follow-on questions is a workflow that makes good use of HANA’S capabilities.

Connectivity Options

This document is focused on the right-hand column of the connectivity scenarios pictured below: Crystal Reports’ connectivity to SAP HANA.

Crystal Reports to SAP HANA - Connectivity Options

Crystal Reports version	Connection	Notes
Crystal Reports 2011 SP4	JDBC ODBC	Command objects and SQL Expressions are available
Crystal Reports for Enterprise 4.0 SP4	JDBC ODBC	Direct-to-data connections are available with FP3 and higher
Crystal Reports for Enterprise 4.0 SP4	Universe (.unx)	Relational universe

HANA ODBC and JDBC drivers

JDBC connectivity is available from Crystal Reports for Enterprise with Service Pack 4.

Install the HANA client to get ODBC and JDBC drivers. (Neither are installed with Crystal Reports 2011.) SAP HANA client software is available for customers from Service Marketplace. (Search for “SAP HANA Client”). The release cycle for HANA is frequent so installing the client enables you to benefit from updates more often.

Crystal Reports for Enterprise – JDBC Connections

JDBC connections from Crystal Reports for Enterprise toSAP HANA are easily created using by entering credentials along with your server host and port. (The second and third digits of the port number correspond to your HANA instance number.)

Crystal Reports 2011 - JDBC Connections

Use the information below to configure JDBC connections from Crystal Reports 2011 to SAP HANA.

XI documentation for JDBC connections : http://scn.sap.com/docs/DOC-11028
Main configuration is done from CRConfig.xml, Default location is C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java
<ClassPath> must point to the ngdbc.jar file (HANA JDBC driver): default path on Windows is C:\Program Files\sap\hdbclient\ngdbc.jar
<JDBCURL> is: jdbc:sap://<IMCE_host>:<IMCE_port> where IMCE_port is 3xx15 and xx the instance number
<JDBCClassName> parameter: com.sap.db.jdbc.Driver

32-bit ODBC Administrator (Windows 7)

Creating a DSN from the ODBC Administrator of the Control Panel of a 64-bit system will not expose your DSN to 32-bit Crystal Reports.

Create your DSN in the 32-bit ODBC Administrator; found here:

C:\Windows\SysWOW64\odbcad32.exe (in Windows 7).

Catalog Query Results from SAP HANA, and Attribute, Analytic, and Calculation Views

When you first connect to HANA, Crystal Reports launches a catalog query to determine what repository content you are permitted to view and report against. You may see a large number of schemas, tables, views, and, in Crystal Reports 2011, stored procedures, from which to select. The objects you can report off and the results you will obtain differ between Crystal Report for Enterprise and Crystal Reports 2011.

In most if not all scenarios, you will be seeking a “Column View” from the schema “_SYS_BIC”. This is the schema to which activated HANA views are published. There may be thousands of objects returned when you browse the _SYS_BIC schema, so knowing the name of the view you need is important.

Modelers of data in HANA within your organization create views that can be used for reporting and decision-making. Three types of information views can be created: attribute views, analytic views, and calculation views. Limited descriptions of these models follow. For more detail refer to http://help.sap.com/hana/hana_dev_en.pdf

Attribute views contain descriptive data only, such as Customer ID, city, and country. They are used to model an entity based on attribute data contained in multiple source tables. For example, Customer ID is an attribute that describes measures, and the address, relationship, status, and hierarchy information may also be included, and may also include filters that restrict data. Attribute views do not include any measures but can be used in the definition of analytic or calculation views (where measures may be used). In Crystal Reports, the data from an attribute view may be useful in a listing report.

Analytic views are used to model data that includes measures and may include attributes. Measures are attributes for which an aggregation must be defined. The data foundation of an analytic view may contain multiple tables however measures must originate from only one of these tables (a single fact table). Analytic views may combine tables that contain attribute and measure data, or a combination of tables and attribute views. Various properties are available to support modeling in analytic views including but not limited to calculated and restricted columns, variables and input parameters, filters and aggregation type of measures.

Calculation views are generally used when advanced logic not possible in other views is required. Calculation views can have layers of calculation logic, can include measures from multiple source tables (multiple fact tables), and can include any combination of tables, column views, attribute views, and analytic views. You can create joins, unions, projections, aggregation levels and hierarchies, variables, input measures (and more).

Attribute, analytic, and calculation views are authored in packages, and, when complete, activated to expose them for reporting and analysis. The process of activating a view creates a column view representation of the information view in _SYS_BIC along with other supporting column views.

The format for the name of activated views in _SYS_BIC is packageName/ViewName or, in cases where packages are nested packageName1.packageName2/ViewName.

Other views that are generated for each attribute, analytic, or calculation view that is activated, also available in _SYS_BIC, are structured like: packageName/ViewName/ColumnFieldName/hier/ColumnFieldName

Or

packageName/ViewName/olap

(It is unlikely that any of these views will be of interest to report developers.)

Modelers of data in HANA within your organization may use naming conventions to reflect the type of HANA view they build.

“AT…” for Attribute Views
“AN…” for Analytic Views
“CA…” for Calculation Views

There is some variation in catalog query results and their order in Crystal Reports for Enterprise and Crystal Reports 2011. The following table and screen shots identify and show differences.

	Crystal Reports for Enterprise	Crystal Reports 2011
Initial catalog query	[JDBC & ODBC] Lists users and schemas whether or not they contain any tables or views Results are sorted according to Unicode sort order: “A” precedes “_” which precedes “a” [JDBC & ODBC] (Returns _SYS_AFL but exposes none of the objects within)	[JDBC] Lists users and schemas whether or not they contain any tables or views [ODBC] Lists only schemas containing Tables, Views, or Procedures [ODBC] Returns schema “PUBLIC” [JDBC & ODBC] Returns “SYS_AFL” Schema names are sorted alphabetically: “_” precedes “A” which precedes “a”
Tables, Views, and Stored Procedures?	Returns tables and views only Views and tables appear in dedicated folders – “Views” and “Tables” Objects are not sorted alphabetically	Returns tables, views, and stored Procedures Tables, views and stored procedures are sorted alphabetically Right-click in the Data window and choose “Options” or use File à Options à Database to filter objects from results

Crystal Reports 2011
Catalog query	List of Views in _SYS_BIC

Crystal Reports for Enterprise 14.0.4

Catalog query

List of Views in _SYS_BIC

Reporting Off Attribute, Analytic, and Calculation Views

Crystal Reports can return data from an attribute view, calculation view, or an analytic view that contains a calculated attribute. When used with an analytic view that contains no calculated attribute, the query must contain a GROUP BY expression.

If there is no GROUP BY expression, you will see a message. In Crystal Reports for Enterprise the message is “…Failed to execute query”. In Crystal Reports 2011 it is “Failed to retrieve data from the database...feature not supported: not allowed over OLAP VIEW : search without aggregation or grouping…”.

The creation of a simple Calculated Attribute within the view (whether necessary, or unnecessary and hidden) should ensure a GROUP BY clause and enable the return of data to Crystal Reports.

Alternately, in Crystal Reports 2011 a GROUP BY expression can be generated with the following steps:

From the design view of the report insert a group.
Suppress the Details section.
Modify the Report Options to “Perform Grouping on Server”
Display data from group header or footer sections.
Then try showing the details and see if data can also be displayed from this section if need be.

SAP HANA Parameters and Variables (CR2011)

Parameters and variables may be defined in HANA views. For end users there is no distinction between variables and parameters. However neither of these inputs is exposed by Crystal Reports when reporting directly off a View that has one. If the view can execute from Crystal Reports, it will use default values.

It is possible to expose variables by using a Command object in Crystal Reports 2011, or by using a derived table in a universe. Refer to the following document for detailed information on accessing SAP HANA variables and parameters.

Using SAP HANA Variables with SAP BusinessObjects BI 4.0

http://www.saphana.com/docs/DOC-1688

Other Parameters, Prompts, and Lists of Values

Parameters with lists of values, and parameter groups with cascading lists of values can be created in Crystal Reports and implemented to filter data (generating a WHERE clause in the report’s query) with SAP HANA data sources as with other sources. Lists of values (including those based on a custom hierarchy) and parameters off SAP HANA data sources can also be defined in universes, and used in a query from Crystal Reports.

Command Objects

Command Object functionality enables users to write custom SQL to query their database from Crystal Reports 2011 without having to create views or stored procedures. A Command Object will return a result set that appears as a table in the Field Explorer. Command objects may be particularly useful when users want to execute a view with parameters or variables but pass non-default values into the query.

SAP HANA Reference – SQL Reference Manual

Html: http://help.sap.com/hana/html/sqlmain.html

Pdf: http://help.sap.com/hana/hana_sql_ref_en.pdf

Crystal Reports and Hierarchies from SAP HANA Data Sources

Crystal Reports cannot expose hierarchies that have been defined in SAP HANA.

In HANA there are two types of hierarchies. Level hierarchies exist across attributes (i.e. Country-State-City). Parent Child hierarchies exist within a single attribute (i.e. employee manager and employee direct report) where one column identifies each dimension member, and a second column, the parent column, identifies the parent of each dimension member.

Grouping, and Cascading Parameters (with Lists of Values) defined in Crystal Reports can be used to replicate level hierarchies defined in SAP HANA. Refer to in-product help file information for creating cascading parameter groups.

Lists of Values based on a “custom hierarchy” (i.e. a level hierarchy) can also be defined in the business layer of a universe and used with prompts in Crystal Reports.

These following articles cover how to create cascading lists of values in a universe:

http://scn.sap.com/community/bi-platform/blog/2012/08/13/creating-a-prompt-with-hierarchical-display...

http://wiki.sdn.sap.com/wiki/display/BOBJ/How+to+create+a+cascading+List+of+Values+%28LOV%29+in+UNX+...

Performance

The same performance best practices that apply to other data sources apply to Crystal Reports built on SAP HANA data sources.

Design reports to leverage SAP HANA’s strength:

Understand the purpose of the report, and return only those records and fields (dimensions) necessary to meet that need. (Don’t use BI clients as data extraction tools.)
Consider using multiple smaller queries (subreports) rather than one large one.
Make use of on-demand subreports to answer follow-on questions, and execute additional queries when necessary.

HANA views and universes built on HANA data sources must also incorporate performance best practices in order for Crystal Reports to perform well. Ensure you design your HANA model for optimal performance. Answer relevant business questions and avoid querying unnecessary data which taxes the database and the network.

Performance can be broken into 3 parts: HANA database, Network, and client tool (Crystal Reports). The quick way to gain insight into database performance versus Universe or BI client performance is to install HANA studio on the same machine as Crystal Reports (or as the Crystal Reports processing servers if deployed in a BI 4.x system) and then compare the query performance between them.

“Best Practices for Optimal Data Models in SAP HANA and analytics in SAP BusinessObjects 4.0”

http://scn.sap.com/docs/DOC-22475

“Best Practices: Creating a universe on SAP HANA”

http://scn.sap.com/docs/DOC-20569

When to use universes

If one is available
If a HANA view with a variable (a parameter) is required (CR will use the default value)

When to use Crystal Reports direct to SAP HANA

If a universe with the data required is not available
If you are using Crystal Reports 2011 rather than Crystal Reports for Enterprise.
If you want to write custom SQL using a Command Object or SQL Expression
If you want to access tables and views directly (and stored procedures using Crystal Reports 2011)

When to choose Crystal Reports for Enterprise

If you are a new user of Crystal Reports (benefit from streamlined interface and improved user-experience)
If you will be reporting off universes

When to choose Crystal Reports 2011

If you want to create Command objects
If you want to call a HANA stored procedure from Crystal Reports
If you want to execute a view with parameters or variables and submit non-default values (using a Command object)
If you want to create SQL Expressions
If you want to have more control over the SQL that is generated – for example, to generate a GROUP BY clause from Crystal Reports.

Resources

HANA Academy Implementation Resources – front page

http://www.saphana.com/community/implement

Using SAP HANA Variables with SAP BusinessObjects BI 4.0

http://www.saphana.com/docs/DOC-1688

Using a command to find Top N values

http://www.saphana.com/docs/DOC-2708

Using a SQL Expression

http://www.saphana.com/docs/DOC-2709

Creating Crystal Reports using HANA tables

http://www.saphana.com/docs/DOC-2430

Creating Crystal Reports using Hana views