According to this document SAP BI 4.2 SP3: What’s New In Semantic Layer,  the BI Sets support is the main enhancement in SAP BI 4.2 SP3 for Semantic Layer and the information design tool (IDT). Here, I will show you how to understand, create set in IDT and then use it in Web Intelligence.

Introduction

A Set is a structure that you define to group lists of values, which is very useful to query data using set logic and operators rather than SQL.

For example, you may be planning a marketing campaign to boost sales of a line of your products. As you don’t want to waste money targeting the wrong customer group, you would look at more limited lists to those customers who: located in certain countries(or regions/cities), belong to a certain age group,

have bought related products in specific period(i.e., Year), Annual Average Revenue, and so on.

Without BI Set, you may create a first query to get the customer list who : located in certain countries(or regions/cities), belong to a certain age group,

have bought related products in specific period(i.e., Year)

and then create another query to get the revenue values of those customers per each year by filtering customers to the list returned by first query.

Is it easy for business user who may only be responsible for creating webi report?

Will the performance be good when the condition to get the list of customers is more complex and the list is very long?

No, I don’t think so.

So is there any way to simplify the query and improve the performance?Yes, it is BI Set.

Starting with a Set Container

A new object is available in the CMS repository: the Set container. This set container is based on a universe (UNX) and contains definition of sets created on top of objects of this universe.

ℹ For this release, you can only create one set container for each universe.

You create and manage a set container using the Set Containers Management dialog box, accessed by right clicking a single source relational universe (.UNX) in the repository. Before you can create a set container, ensure that the following pre-requisites are met:

• The universe has been updated to version 4.2 SP3.
• You have the appropriate permissions required for the Set Container Administrator role.
• The universe has been set up to allow the use of sub-queries.
• Dimensions in the business layer that you want available as subjects have Primary Key specified in the Keys tab for the object properties.

Procedure to create a new Set Container

Information and options available in Set Containers Management

Example

A set container published to the repository has the file extension .SETS. Before you start authoring sets, you initially need to retrieve the set container from the repository and save it to a local project in IDT.

Please note that Only the following databases are supported for 4.2 SP3 BI Set:

• SAP HANA
• IBM Netezza
• SQL Server
• Oracle

If using other databases, you may get error “‘Update Sets data from Database’ has encountered a problem” as described in KBA note:

2366369 – BI4.2 SP3: Error “Updating Sets data from DataBase” occurs when Retrieving a Set Container in Information Design Tool

Designing and building Sets

A set is made up of one or more lists of values that are linked as a series of steps, each list restricting the values available for the following ones. When published to the repository, the set appears as a filter object available to the Query Panel in Web Intelligence.

You create a set based on a subject defined in a set container. The subject is typically built on a business layer object, a dimension. The members of the dimension make up the data available to build the set.

Two Types of Set

Defining a Static Set

After retrieving a set container, you could select one set folder and then click the arrow to the right of the Insert Static Set icon in the menu bar of the Sets tab to create a static set as below:

Defining a Temporal Set

If the set container has calendars defined, you could define a temporal set based on a calendar by clicking the arrow to the right of the Insert Temporal Set icon in the menu bar of the Sets pane.

More information about temporal set can be found from IDT User Guide.

Once the set is created, you can choose the set members either from a list of object members, or as a subset of combined sets that are already available in the set container.

Selecting Members Directly for Set Lists

You select set members from a list of objects available via the subject.

Combining Sets to Select List Members

You can select new set members by adding multiple sets for a subject into a collection, which applies an intersect or union operation on all of its sets.

You can then add sets to more collections, combining them all using intersect or union operations to define a new set with members resulting from the complex combination of values.

Only sets that have been built and are based on the same subject are available for inclusion into collections.

Set Steps

The value of building a set is to combine a sequence of lists that respond to questions allowing you to refine your data set.

Each additional list of members can be combined with the previous one, using the following properties:

Step “Add”

Step “Subtract”

Step “Keep”

Building a Set

Click the Build button available on the Steps or History tabs in the Sets Editor to build a set.

When a set is built, its listsof values (primary keys of the dimension) are compiled into a database column immediately making the new count available to Web Intelligence, if this set was already published.

Publishing a Set Container

You publish a set container to update the set container in the repository.

This ensures that the latest definitions of sets are available to Web Intelligence as set filters in the Query Panel.

Is it easy for business user to get their expected result without designing a complex query? I hope so.

Please note that APS hosting Set Materialization service and write access to DB are required to build set. Otherwise, you may get the following issues:

• Error “Engine not connected” occurred when building a Set
  => Cause: The Adaptive Processing Server (APS) hosting Set Materialization Service is not available (stopped or disabled).
  =>KBA: 2367484 – BI4.2 SP3: Error “Engine not connected” occurred when building a Set in Information Design Tool
• Error “The INSERT permission was denied on the object <set membership table>” occurs when building a Set 
  => Cause: Missing appropriate data permission (write access)
  => KBA: 2367528 – BI4.2 SP3: Error “The INSERT permission was denied on the object <set membership table>” occurs when building a Set in Information Design Tool

Moreover, you could schedule a refresh time for your sets from the home page of the Central Management Console.

Thank you for reading and I hope you enjoyed this blog.

Best regards,

Xiwen