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
| 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
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:
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:
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:
| 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 "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... - 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>" o...
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
- SAP Managed Tags:
3 Comments
