Browse Prior Art Database

Algorithm to determine if members selected from SAP BW characteristics will result in no data being extracted

IP.com Disclosure Number: IPCOM000246229D
Publication Date: 2016-May-18
Document File: 5 page(s) / 70K

Publishing Venue

The IP.com Prior Art Database

Abstract

When extracting data from SAP BW it is easy to unknowingly select members which cause the extraction to return no data. The algorithm disclosed can identify this situation and state which members caused it.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 44% of the total text.

Page 01 of 5

Algorithm to determine if members selected from SAP BW characteristics will result in no data being extracted

Disclosed is an algorithm to determine if members selected from SAP BW characteristics will result in no data being extracted. This issue has not been addressed before.

Background

SAP BW is a multi dimensional database. It stores fact data about elements from several dimensions, like Customer and Product for instance. These elements can have multiple characteristics, like Name and Address for Customer or Brand and Line for Product.

Data is stored internally as a snowflake schema. The fact table (shown in blue in the Figure) holds dimension IDs, which are keys in dimension tables (shown in orange in the Figure). A dimension holds SIDs (Surrogate IDs), which are keys in characteristic SID tables (shown in green in the Figure).

Example tables:

There are 2 customers, with the names Alex and John located in Bucharest and London. Fact data exists for both customers.

Fact table:
Customer
Dimension ID

Product Dimension ID

Cost

Quantity

1


Page 02 of 5

CUST1

PROD1

123

456

CUST2

PROD2

124

457

Customer dimension table: Customer Dimension ID NameSid

AddressSid

CUST1

NameSid1

AddrSid1

CUST2

NameSid2

AddrSid2

Name characteristic SID table:

NameSid

Name

NameSid1

Alex

NameSid2

John

Address characteristic SID table:

AddressSid

Address

AddrSid1

Bucharest

AddrSid2

London

SAP BW provides a way to extract fact data by providing characteristic values. However, it is easy to select incompatible values and get no data back.

Below are two examples of extractions:
Selecting
Bucharest from characteristic Address produces AddrSid1 from the Address SID table. AddrSid1 produces CUST1 from the Customer dimension table. CUST1 produces the data row CUST1 PROD1 123 456 from the fact table.

Selecting John and Bucharest produces NameSid2 for Name andAddrSid1 from Address . Because there is no row in the Customer dimension table that has both AddrSid1 AND NameSid2 on the same row row no data is retrieved.

These situations are hard to detect in case there are many characteristics and members selected or the SAP BW user is not familiar with the domain.

The disclosed algorithm detects these situations and returns the members causing them.

Description of the algorithm

The algorithm takes as input:
an SAP dimension table for a dimension
the SID tables for characteristics in that dimension
a list of member selections for each characteristic in the dimension

The algorithm outputs a list of incompatible member selections for characteristics in that dimension, if any exist.

2


Page 03 of 5

For each characteristic it produces a list of pairs

and stores it.

Then, it iterates over each characteristic with member selections keeping only the indices in the list for the members selected from that characteristic. If at any point during the iterations 0 indices remain, then it means that the member selections are incompatible.

In other words:


It starts off with the list of indices....