BusinessObjects Universe Design Tool: Each calculated column must have an explicit name

In SAP BusinessObjects Universe Design Tool, you might get the following error – “Each calculated column must have an explicit name”.

Each though the query runs fine in the underlying database, but Universe Design Tool doesn’t accept it. The reason is it expects ‘AS’ keyword between the calculated column and it’s alias. So that query should be re-written as:

select to_char(statusdate, 'DD-MMM-YYYY') as datetext 
from person

 

 

Completed SAP BusinessObjects course on ‘BI Clients and Applications’ from openSAP

BI Clients and Applications - Record of Accomplishment

This is my first openSAP course where I managed to stick around till the end. Generally I found the content on openSAP to be a bit dull. At times it seems that you watching a marketing video rather an educational one. Anyway, there is a lot of useful content also.

This six-week online course was held from October 29 through December 17, 2014. Following topics were covered:

  • SAP HANA as an Analytics Platform
  • Self-Service
  • Dashboards and Applications
  • Semantic Layer and Reporting
  • Mobility and Cloud
  • User Experience Tools and Features

SAP BusinessObjects Universe: Using ANSI92 SQL syntax

When writing SQL queries, I prefer the JOIN clause syntax over (+) in where clause for creating table joins. It is more readable and maintainable as join logic is separated from the where clause.

BusinessObjects Universe can be configured to generate JOIN clause (ANSI92) syntax by setting a parameter in ‘Universe Parameters’ dialog available from File menu.

 

image

 

We get some extra benefits by using ANSI92 syntax, for instance:

Full Outer Join

The functionality to define Full Outer join is available with ANSI92 syntax only.

image

Advanced Join Properties

The Advanced Join Properties give additional control over the generation of SQL queries from the universe. It determines whether a conditional expression should be included in where clause or join/on clause. These options are only supported with ANSI92 syntax.

image

 

The above applies to ‘Universe Design Tool’ version 14.0.7.  More at Official SAP BusinessObjects Universe Design Tool Guide.

BusinessObjects Universe: Defining Complex Outer Join

A complex join contains more than one condition in the join expression. Consider the following expression where one line of sales order joins with a line on invoice,

INVOICE_LINE.ORDER_NUM=ORDER_LINE.ORDER_NUM
and INVOICE_LINE.ORDER_LINE_NUM=ORDER_LINE.ORDERLINENUM

If we define a complex join in BusinessObjects Universe Design Tool, than the option to make it outer becomes disabled.

clip_image002

There are three possible ways to get around this problem (that I found), which are described below.

 

1) Create two separate joins

Create two separate joins to split the complex expression into simple ones. This will enable the ‘Outer join’ check box. While creating the query, the universe will automatically combine the two joins into one complex expression.

clip_image006

 

2) Set the Outer Join first, then specify complex expression (ANSI92 only)

Change the expression to simple temporarily; this will enable the ‘Outer join’ checkbox. Select the ‘Outer join’ and change the join expression back to the designed complex expression. Universe Designer will not only remember that ‘Outer join’ flag but also generate the right query at runtime with outer join clause.

Note: This solution only works if ANSI92 parameter is set to ‘Yes’. That is, join/on clause syntax is used for creating SQL query.

clip_image004

The universe will generate SQL similar to the following:


SELECT DISTINCT
INVOICE_LINE.INVOICE_NUM,
INVOICE_LINE.INVOICE_LINE_NUM,
ORDER_LINE.ORDER_NUM,
ORDER_LINE.ORDERLINENUM
FROM
ORDER_LINE RIGHT
OUTER JOIN INVOICE_LINE
ON (INVOICE_LINE.ORDER_NUM=ORDER_LINE.ORDER_NUM(+)
and INVOICE_LINE.ORDER_LINE_NUM=ORDER_LINE.ORDERLINENUM(+))

 

3) Directly specifying the complex expression with (+) syntax

We can directly specify the complex join expression and make it outer by using (+) syntax. Off course, this will only work when ANSI92 parameter is set to ‘No’.

clip_image008

Generated SQL will be something like:


SELECT DISTINCT
INVOICE_LINE.INVOICE_NUM,
INVOICE_LINE.INVOICE_LINE_NUM,
ORDER_LINE.ORDER_NUM,
ORDER_LINE.ORDERLINENUM
FROM
INVOICE_LINE,
ORDER_LINE
WHERE
( INVOICE_LINE.ORDER_NUM=ORDER_LINE.ORDER_NUM(+)
and INVOICE_LINE.ORDER_LINE_NUM=ORDER_LINE.ORDERLINENUM(+) )

 

The above applies to ‘Universe Design Tool’ version 14.0.7.  More at Official SAP BusinessObjects Universe Design Tool Guide

Designing BusinessObjects Universe: Join problems in schema and resolution techniques

An essential aspect of creating a BusinessObjects universe is to define joins among tables in ‘Universe Design Tool’. Even if the joins are correctly defined from relational point of view, the universe might not generate the correct results in certain cases. There are few pitfalls to avoid which are specific to the business of designing universe.  These pitfalls, generally referred as join problems, come in three major varieties: loops, chasm traps and fan traps.

Loop

Loop is the existence of more than one join paths between two tables. In other words, if we move among tables following joins and we can reach back to the original table using a different path, then we have a loop.

Loops will cause less number of records to be returned than expected.

BO Universe Loop

The Universe Design Tool can automatically detect loops and suggest solutions (aliases and contexts).

Chasm Trap

We are in Chasm Trap when a table joins with two others in one-to-may relationship. Learn more about them here.

Chasm Trap

 

Fan Trap

Fan trap is a less severe problem than other two. It does *not* affect the number of records returned  from the query, rather it affects the aggregation of an attribute.

Consider, an invoice record with three lines. While summing invoice total if it gets accounted thrice (once for each invoice line), than we are in Fan Trap.

Fan Trap

Techniques for resolving join problems

As universe designer, the toolbox consists of following to overcome the join problems:

Alias

A table can be duplicated in schema by giving it an alias (Both table and its alias refers to the same underlying table in database). This is the most common way to resolve join problems. Use of an alias can break loops or undesirable join paths.

Contexts

Context is a set of joins. It divides the universe in overlapping sets of tables and joins. If your table is part of more than one context, the report designer tool might ask the user to select the context if it can’t be inferred.

Self Restricting Join

Join can be defined as condition on a single table that limits the records returned from it. Such a join (which is not actually a join at all) is called Self Restricting Join.

Derived Table

Derived Table is like a database view. It allows us to create new tables in Universe based on a SQL query.

Shortcut Join

As the name says, it is shortcut to a longer join path. It is important to identify a shortcut join, otherwise we may have a loop.

Techniques to avoid

Joins could potentially cause issues which might motivate us to do without them, for instance, we can have sub-query in select clause to fetch data from another table. This is not recommended as query generated by universe might group by columns, in such a case query will not be valid.

So, the general rule is not to have sub-queries in select clause.

 

The above applies to ‘Universe Design Tool’ version 14.0.7.  More at Official SAP BusinessObjects Universe Design Tool Guide

Chasm Trap: Designing SAP BusinessObjects Universe

While designing SAP BusinessObjects universe with Universe Design Tool, Chasm Trap is one of the major join problems that one can come across.

We are in Chasm Trap when a table joins with two others in one-to-many relationship. Chasm Trap produces more data than expected.

Example

Consider the following ER diagram where DEPARTMENT is having 1-to-many relationships with EMPLOYEE and CONTRACT_STAFF tables.

Let’s assume we have following data in these tables.

image

BusinessObjects universe will produce something similar to the following query to join the above 3 tables:

select depart_name, employee_name, name "contract staff"
from department d
  inner join employee e on d.department_no = e.department_no
  inner join contract_staff c on c.department_no = d.department_no

Result of the query will have more rows than expected.

image

Resolution

Universe Design Tool doesn’t detect chasm traps automatically. They can be identified by visually inspecting the ER diagram.

Using Contexts

The recommended way of resolving Chasm Trap is to use contexts which will separate the two tables into separate contexts (employee and contract_stafff in our case). If both tables are used in the report, separate queries will be generated resulting in two tables in Web Intelligence report.

Using Derived Tables

Another approach which I have found handy in many cases is to use Derived Table functionality. Derived Table in universe designer can merge the two tables into one. Additional column containing record type can be introduced to still be able to tell the records apart (in our case, record type would be employee or contract_staff). This simplifies the design and avoids the Chasm Trap.

More at Official SAP BusinessObjects Universe Design Tool Guide

SAP BI Suite – BI Designer or Consumer Tools Overview

SAP BusinessObjects BI Platform 4 contains a number of designer or consumer tools which connect to various data sources and perform data analysis, monitoring and reporting. Each tool has its own strengths and there is overlap in their functionality. It can be a bit confusing to decide which one to use for a particular task. Following is the list of client tools with a brief overview that may help in figuring out how suitable they are for a particular BI need:

SAP Crystal Reports 2011
  • Crystal Reports is used to develop highly formatted reports which are printer-friendly and largely static.
  • Supports parameters
  • Newer version of SAP Crystal Reports 2008
SAP Crystal Reports for Enterprise
  • Crystal Reports for Enterprise is the next generation of Crystal Report designer which will be foundation for future Crystal Report development.
  • Currently it doesn’t support all data sources as compared to ‘SAP Crystal Reports 2011’.
  • Crystal Reports for Enterprise should be used for new reports if the required features are available.
SAP BusinessObjects Dashboards
  • SAP BusinessObjects Dashboards is a data visualization software that allows you to create interactive dashboards.
  • Dashboard designer contains an embedded excel workbook which can be used to load data directly or data from other sources can be bind to the excel sheet.
  • Data from other sources can be directly bind to dashboard components by-passing the embedded excel workbook.
  • Dashboard design tool is generally meant for IT department which deploy the created dashboards to BI platform to be consumed by users.
SAP BusinessObjects Web Intelligence
  • Web Intelligence supports powerful adhoc query and reporting. The tool is also callled interactive analysis.
  • The reports created in Web Intelligence are much more interactive than Crystal Reports. It supports drill down , outlining and input control to filter records.
  • Web Intelligence is available both as a client tool of the SAP BusinessObjects Business Intelligence platform, and as a stand-alone product.
SAP BusinessObjects Analysis
  • There are two tools in SAP Analysis: Excel Addon called ‘SAP BusinessObjects Analysis, Edition for Microsoft Office’ and ‘SAP BusinessObjects Analysis, Edition for OLAP’.
  • Allows to design workbooks applications and perform multidimensional ad-hoc analysis of OLAP data sources in Microsoft Excel.
  • ‘SAP BusinessObjects Analysis, Edition for OLAP’ is an alternative to Excel which duplicates the enhanced pivot table functionality.
SAP BusinessObjects Design Studio
  • SAP BusinessObjects Design Studio enables application designers to create analysis applications and dashboards for Web browsers and mobile devices based on SAP NetWeaver BW or SAP HANA data sources.
  • It is the product of choice when full support is required for data models and engine capabilities in SAP NetWeaver BW and SAP HANA.
SAP BusinessObjects Explorer
  • SAP BusinessObjects Explorer is a data discovery application that allows you to retrieve answers to your business questions from corporate data quickly and directly.
  • Through search, you can find relevant data that is held within consistent, meaningful datasets known as Information Spaces.
Visual Intelligence
  • Visual Intelligence is a data discovery and visualization tool.
  • It is the go to tool for self service BI.

Reports, dashboards and workspaces from above tools can be saved to SAP BI platform. Documents saved on the BI platform are accessible through BI Launchpad. BI Launchpad provides an interface to the SAP BusinessObjects Business Intelligence platform repository that enables you to navigate to, view, and manage your business intelligence documents.

Official SAP tutorials for above tools are available at www.sap.com/learnbi. The tutorials are in the form of How-To videos which explain how to accomplish common tasks in these tools.

For a more detailed analysis of the capabilities of these tools, check out the webcast ‘How to select the right BI tool for your environment‘. The presenter categorizes the BI portfolio capabilities into four areas: reporting, monitoring, analyze and discover. He than evaluates the BI client tools with respect to these categories and required technical expertise.

For a webinar on Visual Intelligence tool, visit http://scn.sap.com/docs/DOC-31427. Here are the slides for the webinar.