Skip to Content

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)

Without Set - First Query.png

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.

Without Set - Second Query.png

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

Procedure - Set Containers Management.gif

Information and options available in Set Containers Management

Set Containers Management tabs Description
Subjects Subjects detected in the universe are listed. These are based on business layer objects with primary key columns. Click Synchronize with universe to update the list with the latest version of the universe.
Calendars Calendars available to all sets are listed. Click Add a new Calendar to create a new calendar or click the ellipsis button to modify an existing one in the list.
Deployment Current set containers associated with the universe are listed. Click Add a new Container to create a new container.

Example

Example - Set Containers Management.gif

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.

Procedure - Retreive Set Container.gif

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

Set Type Description
Static The set members are fixed at a moment in time. A static set is like a snapshot of your data that captures a state of the population that you are considering for analysis.
Temporal The set members are dynamic, so can change over multiple calendar periods used to build the set. A temporal set is a collection of members who joined, left or stayed over each calendar period.

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:

Example - Insert Static Set.gif

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.

Defining a Temporal Set.gif

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.

Select members directly from set lists.gif

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.

Combining Sets.gif

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:

Set Step Description
Start This is the default step. It is the first list of members that you create, and starts the sequence of lists, each one acting on the previous one to refine your set members.
Add The list members are added to the set.
Subtract The list members are subtracted from the set.
Keep Performs an intersection between the set and the selected members. Removes those members from the set that do not exist in your selected members.

Step “Add”

Step Add.gif

Step “Subtract”

Step Subtract.gif

Step “Keep”

Step Keep.gif

Building a Set

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

Build Set.gif

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.

DB change after buiding set.gif

Publishing a Set Container

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

Publish Set.gif

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

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:

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

Schedule Set refresh time.gif

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

Best regards,

Xiwen

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. ning wang

    Very informative post, client might need time to realize the power of Sets. Please keep posting more use cases!

    Thanks

    (0) 

Leave a Reply