Accessing Data through the Business Layer

Objectives

After completing this lesson, you will be able to:

  • Create a Business Layer

Business Layer

A business layer is a collection of metadata objects that map to specific data in a database or an OLAP cube. The metadata objects include the following:

  • Dimensions

  • Measures

  • Attributes

  • Predefined conditions

  • Navigation paths

Each object corresponds to the business information that users need for their reporting and dashboarding requirements. Business layers can be created directly on an OLAP cube, or on a data foundation that is built on a relational database.

When a business layer is complete, it is published to the SAP Business Intelligence Platform as a universe. A universe is a published .unx file that includes all the components of the universe.

The principle role of the business layer is to define and organize the reporting data in a way that is logical to the business users.

Business Layer Editor

You use the Business Layer Editor to create and edit business layer objects and properties. Similar to the data foundation editor, the Business Layer Editor is divided into a business layer view and browsing panes.

The browsing panes allow you to work with different elements of the business layer. Access the panes by clicking the corresponding tab:

  • Business Layer

  • Queries

  • Parameters and Lists of Values

  • Navigation Paths

Each of these tabs are explained in detail as you proceed through this course.

Business Layer Properties

The following properties and restrictions are defined for the entire business layer. The system applies the restrictions in the published universe.

Business Layer Properties and Restrictions

PropertyRestrictionDescription
Name Identifies the business layer and the universe when the business layer is published.
Description Describes the purpose and content of the universe. This description is available to display in the query and reporting tools that use the published universe.
Summary Displays the number of each type of object defined in the business layer. Also displays the number of data foundation objects defined in the underlying data source.
Change Data FoundationData SourceSpecifies the data source for the business layer; either a data foundation or OLAP connection. For OLAP data sources, other properties are defined.
SQL Parameters (apply to business layers based on data foundations) Specifies custom values for SQL generation parameters that override the default value or any customized value in the data foundation properties.
Query LimitsLimit result set sizeSpecifies the number of rows that are returned in a query. This property limits the number of rows returned, but does not restrict the RDBMS from processing all the rows in the query. This property only limits the number once the RDBMS has started to send rows.
 Limit execution timeSpecifies the time limits in minutes, for query execution but does not stop the process on the database.
 Warn if estimate exceededProvides a warning to send a message when an estimate of the execution time exceeds the specified number of minutes.
Query Options (apply to business layers based on data foundations)Allow use of subqueriesPermits subqueries in a query.
 Allow use of union, intersect, and minus operatorsEnables universe user to combine queries using the operators data set of union, intersect, and minus to obtain one set of results.
 Allow complex operands in Query PanelAllows complex operands in the list of operands available when defining a filter in the Query Panel.
Comments Comments about the business layer.

Business Layer Objects

In BusinessObjects products, an object is a named component in a universe that represents a column or function in a database. The objects that you create in the business layer are the objects that business users see and use in the end-user reporting tools.

Watch this short video to learn about the primary types of objects.

Depending on the type of data source for the business layer, you can create and edit the following types of objects in a business layer:

  • Folders

  • Dimensions

  • Measures

  • Attributes

  • Filters

  • Analysis dimensions (OLAP only)

  • Named member sets (OLAP only)

  • Calculated members (OLAP only)

Each object in the business layer is comprised of SQL statements and has properties that can be defined and modified at any time. The system applies the properties that you set for objects in the business layer in the published universe.

SQL Definition

The SQL definition tab in business layer object properties lets you define the SQL statement for the selected object. You can enter the following properties:

SQL Definition Properties

PropertyDescription
SelectLets you enter the SELECT statement directly in the text box. You can also click the SQL Assistant button and use the SQL editor to build the statement.
WhereLets you enter the WHERE clause directly in the text box. You can also click the SQL Assistant button and use the SQL editor to build the statement
Extra TablesLets you select tables related to the object to include in the query at runtime. Click the button at the end of the text field to open a list of related tables. Select or clear the extra tables.

Validating Objects

Always validate the syntax of your business layer objects.

Keys Properties

On the Keys tab in business layer dimension object properties, you specify which database columns are primary and foreign keys. This specification allows the query to take advantage of indexes on key columns. Defining keys speeds up data retrieval by optimizing the SQL that is generated for the query.

For example, in a star schema database, if you build a query that filters on a value in a dimension table, the filter can be applied directly on the fact table by using the dimension table foreign key. This filter eliminates unnecessary and inefficient joins to dimension tables.

Business Layer Object Properties

PropertyDefinition
NameThe name of the object. Choose names that correspond to the business terminology of the target users.
DescriptionComment describing the object.
Active/Hidden/DeprecatedActive: Object is visible in the Query Panel

Hidden: Object is valid but not available in the Query Panel.

Deprecated: Object is hidden and not valid; for example, when the target database field no longer exists but you want to keep the object for possible future use.

Advanced Properties

In addition to the SQL definition properties that define an object, the Advanced tab allows you to perform the following actions:.

Business Layer Object: Advanced Properties

PropertyDescription
Access levelDefines the security access level of the object. You can select a security level that restricts use of the object to end users with the appropriate security level set by the SAP BusinessObjects administrator in the SAP BusinessObjects Business Intelligence Platform.
Object can be used in ResultWhen selected, the object can be used in a query.
Object can be used in ConditionWhen selected, the object can be used in the condition of a query.
Object can be used in SortWhen selected, the object can be specified in an ORDER BY clause of a SELECT statement.
Database formatAvailable for date objects. By default, the date format for the object is defined in the Regional Settings Properties dialog box of the MS-Windows Control Panel. You can modify the date format to use the target database format for storing dates. For example, the date format can be US format or European format.
List of ValuesLets you associate a list of values (LOV) to the object. The LOV applies when defining a filter on the object in the Query Panel.
DisplayLets you set display options for the data returned by the object in a query. Click Edit display format to select a predefined format or define a custom format.

You can also select to display the data returned by the object as HTML or Hyperlink.

Source Information Properties

The Source Information tab in business layer object properties contains descriptive fields that only apply to objects created by SAP Data Services.

Business Layer Object: Source Information Properties

PropertyDescription
Technical InformationDisplays information about a column, for example, the original database name of the concerned column for the object.
MappingDisplays initial formula information describing how a column has been specified (used in Data Integrator), for example revenue = column calculated from several sources.
LineageDisplays source columns for the formula used to calculate the column in the database.

Custom Properties

Custom properties provide a place for universe developers to communicate. Any properties, such as internal comments or descriptions are not visible to business users and cannot be accessed outside of the Information Design Tool.

Log in to track your progress & complete quizzes