Checking Disk Size of Infocube and DSO in Hana Database

This document is to share the steps on how to check the disk size of Infocube and DSO in HANA database.

Getting size of data target in dso or infocube from Joseph Tham

Read More

How to use Data Services Pivot Transformation

How to use Data Services Pivot Transformation

The Pivot transformation allow the developer to change how the relationship between rows is displayed. For each value in each pivot column, Data services produces a row in the output data set. We can create pivot sets to specify more than one pivot columns. It basically convert Columns to Rows.

Implementation:

Let us consider we have source data of sales for different stores based on quarters. we may want to transform the sales data based on the quarters for each of the stores. Find below the implementation Data Flow.

To solve the problem we are using the SAP Data Services Pivot transform. We first set the Pivot sequence column values as PIVOT_SEQ. So the sequence value will be 1 for QUARTER1 column, 2 for QUARTER2 column. When transforming to multiple rows, select the STORE as the NON-Pivoted column. In this example, we are dealing with only one Pivot set for QUARTER. Hence the Pivot set is set to 1. Next include all the input QUARTER columns as the Pivot Columns. Set the output Header Column name as QUARTER and the coresponding Data fields as SALES.

Finally we map the quarter to the Pivot Sequence in the Query transform. So, for the first quarter sales value that corresponding QUARTER will be 1 and so on.

Read More

Analysis Authorization in SAP BW 7.3

Analysis Authorization in SAP BW 7.3

Objective:
Describe the principle of analaysis authorization checks
Explain the differences between analysis authorizations and previous reporting authorization.

Business Example:
The user want to display transaction data from a navigation attributes in a query. Therefore, there is a need for analysis authorizations. As a result of this, new navigation attributes has to be added in the respective InfoObject that are tied to this requirement that set by the user. Only authorize user are able to view the the specific transaction data.

Authorization Check in the InfoObject

Authorization Check OK : Query results will be shown if query selection is a proper subset of the authorization.

Authorization Check Not Ok: Query results will not be shown at all ('not authorized') even if the parts of the authorizations are met.

Analysis authorizations are not based on authorizaton objects. You create authorizations that include a group of charateristics instead. You restrict the values for these characteristics.

The authorizations can include any authorization-relveant chracteristics, and great single values, intervals and hierachy authorizations in the same way. Navigation attributes can also be flagged as authroization-relevant in the attribute maintenance for chracteristics and can be added to authroization as separate chracteristics.

Working with Authorization-Relevant Chracteristics
 

 

 Before restricing authorizations on characteristics, remember to mark the Authorization relevant as shown above.

Creating analysis authorization for attributes:

Business Example:

To provide additional information in your reports, you have defined attributes. You need to ensure that your users have the neccesary authorization to display the attributes.

Creating analysis authorization as Navigational Attributes

In authorizations, navigation attributes are handled as ordinary characteristics. You can mark navigation attributes as authorization relevant independently of the assigned basic characteristics.

To mark a navigation attribute as authroization relevant, select the relevant indicator on Attributes tab page in the InfoObject maintenance.

Using Authorization variables

There are two different ways variables are used for authorization processing. It is important to understand the differences.

Using Variable

When you have maintained analysis authorizations in transaction RSECADMIN, you can craete variables in your queries that are filled automatically with the value of the user's authorization. When the user open a query, the data is selected automatically according to his or her authorization.

This type of variable is callled an authorization variable.

Creating an Authorization Variable - Example

 Note: Note that when variables are filled automatically, they do not have to be input ready, which means a variable screen does not need to appear when you execute the query or Web application. The user opens the query with the authroizaton variable, and only the data that corresponds to his or authorizations is displayed.


The variable needs to be set up to have the Processing by parameter set to Authorization. Additionally, the Ready for Input checbox should NOT be selected if the query user should automatically receive data for all values for which they are authorizied. If the Input Ready checkbox
　
　
　
　
　
　

Read More

Understanding the fundamental of Data Service Design

In order to understand how to design the workflow, data flow and ETL in BODS. The following illustion is use to show the hierarchical relationship for the key object types within Data services.

 

Read More

What is Derived Table in Universe Designer?

A Derived Table is a logical table created in Business Objects Universe using an SQL query. A derived table in a universe is analogous to a view created in database with a SELECT statement.

How to Implement Derived Table

1. Open/Import a universe.
2. Click Insert -> Derived Tables OR right click on an empty space in the universe canvas and click derived Tables.
3. Write the SQL statement which is to be used for the derived table in the "Enter SQL Expression" area. Name the derived table with an appropriate name.
4. If the SQL of the derived table contains any calculation, enter a valid alias for that column. e.g. SELECT calculated_column AS CAL_COL FROM TABLE
5. Click "Check Syntax". If the SQL gives error, debug it. If the SQL parses correctly, click OK.
6. The Derived Table can be seen in the universe canvas with columns as named in the SQL query. These columns can be dragged to the "Universe Window" (on left side of the canvas) to create Objects of the derived table.
7. Link the derived table with the database tables if required using appropriate joins and contexts.

Advantage of using Derived Tables

• It can be created using an SQL statement with complex expressions, joins and prompts which are not possible to create in BO Universe using normal approach.
• It acts just like a normal table in the universe and can be used to join with other database table and derived tables present in the universe.
• Multiple level of nested dervied tables can be created, in which one derived table is used in another derived table query.
• Since a derived table is based in a universe and is not dependent on any DDL, there’s no need for any interaction from the DBA or ETL team for its creation.
• Changing the structure of the derived table is as easy as changing the SQL statement it is made up of.

Disadvantages of using Derived Tables

• Derived tables do not store data and hence every time a report using this table is run, the whole SQL query is executed. This may cause poor performance of the report.
• If the database tables used in the derived tables are huge and the query is complex, it may cause memory issues on the server.
• Since the derived table is a logical table, indexes cannot be used for faster data retrieval.

 

Read More

Stored Proceudre in Universe Designer

Stored Procedure Universe Best Practice Guide

Purpose

This is to describe how a stored procedure universe is created at the semantic layer and how web intelligence will use this type of universe. Why use a stored procedure, and what are the limitations of using this type of universe.

Overview

A stored procedure can be a used in a UNV universe and provides some performance improvement. The feature itself has many restrictions that should be considered before designing a universe with them. This is an explanation of this feature and some of the benefits and limits of its use.

What is a Stored Procedure?

Since version XI3.1 it is possible to create a universe on a stored procedure. A stored procedure is an encapsulated set of sql statements that is stored and run on a database. It creates the possibility of manipulating the data before it is used in the reporting tool. The sql statements are not stored or visible from the BusinessObjects application.

Benefits of Using a Stored Procedure

Since stored procedures encapsulate the sql statements the database operation appears only once within the stored procedure. Comparatively in Web Intelligence the sql may be generated and passed to the database three times. This makes it easier to maintain a set of code. Changes in the database are a database administrator task and not BI. Security is maintained by the database and code can only occur on the database. Users have no access to view or change sql. Because stored procedures are compiled and stored outside the client application, they can use more sensitive variables within the SQL syntax, such as passwords or personal data. An added benefit is that it can reduce network traffic, which can be the greatest source of bottlenecks.

Restrictions for Using a Stored Procedure

It is important to understand, that while it is efficient to use a stored procedure, they limitations and restrictions in the BusinessObjects Universe.

• Can only be created in the Universe Design Tool. Not in Information Design Tool.
• No joins can be made between a stored procedure and a table in a universe
• No Query Filters can be used
• No predefined conditions
• The procedure itself may contain a variable that will prompt, but it cannot be manipulated.
• They cannot be used in Linked Universes.
• Not All Databases support stored procedures
• These SQL Commands are not ALLOWED: COMPUTE, PRINT, OUTPUT or STATUS
• The stored procedures do not support OUT or dynamic result sets parameters
• An IF statement cannot be used in the where clause.
• You can only create a new universe based on the stored procedure. You cannot add it to an existing universe.
• The stored procedure creates all objects in the universe automatically. If there is a long text object it will not generate an object.
• If a change is made on the database to the stored procedure. The universe view will not update the schema. The stored procedure must be re-inserted. (This causes the object id to change!)
• In order to avoid parsing errors on stored procedures columns, it is recommended that you alias result columns based on complex SQL, for example using the aggregate functions - sum, count. The creation of aliased objects cannot be constrained.
• Teradata Cannot use Stored Procedure at all - Only use Macros

Supported parameters in a Stored Procedure

Without parameters
With parameters (IN)
With multi-result sets (this means it creates more than one virtual table by use of the select statement)

Creating a Universe on a Stored Procedure

The universe design tool generates one table per selected stored procedure (or many in a multi-result set) one object per column in the select statement. The structure of the table is determined by the definition in the stored procedure.
Quick design Wizard, available from the toolbar.
1. Click the Quick Design Wizard toolbar button. The welcome pane appears.
2. Click the check box Click here to choose stored procedures universe at the bottom of the pane.
3. Click Begin. The Define the Universe Parameter panel appears.
4. Type in a universe name in the Enter the universe name field.
5. Choose the database connection from the dropdown list in the Select the database connection list.
6. Click Next.
The Create initial classes and objects panel appears.
7. Click on a stored procedure.
8. Click Add.
The stored procedure is created in the Universe classes and objects pane.
9. Click Next.
10. Click Finish. The Congratulations panel appears.
OR

1. File new universe
2. Check on under the connection the check box for Stored Procedure Universe. This Adds a parameter to the universe parameter list STORED_PROC_UNIVERSE is set to YES.

Prompts or Parameterized Query in a Stored Procedure

This is a prompt that is defined within the stored procedure and when the procedure is executed will prompt the user. The prompt display name is defined in the procedure. It is possible to associate a list of values to this prompt in the Universe. A standard database table is inserted in the universe and it can contain only simple values. In front of each parameter in the stored procedure parameter dialog box there is a button to open an advanced dialog box.

Troubleshooting and current issues

Review the Sql – is the sql supported? Check the Data Access Guide and the UDT Guide.
Did this work in a previous version and stop working after applying a patch or migrating to BI4 from 3.1?
Does the sql contain use of global temp tables?
Check the odbc.sbo file and add this parameter:
Add the following under all libraries needed: Always

Read More