Skip to Content

Introduction

From SAP BI 7.0 onwards, in DTP we have an option for semantic groups.  We have different perception about Semantic Groups in DTP and its usage. Here I am summarizing specific usage of semantic group with examples.

A1.jpg

Case 1. Calculation on group of records

Example: I would like to provide discount to each customer based on total sales value of Customer No, Country and Product category.

Discount should be assigned to each row i.e. for each product. I am having below sample records.

Product No

Customer No

Country

Product Category

Sale Value (In US $)

P001

C001

US

Home

5000

P002

C002

UK

Electronics

3000

P003

C003

Germany

Kids

2000

P004

C004

India

Home

4000

P005

C001

US

Electronics

1000

P006

C003

Germany

Kids

6000

P007

C005

France

Home

5000

P008

C004

India

Electronics

3000

P009

C002

UK

Kids

2000

P010

C001

US

Home

1000

Discount Criteria based on Sales value as per grouping (Customer No + Country + Product Category)

Total Sales (In US $)

Discount %

>=1000 AND <5000

5

>=5000 AND <10000

10

>=10000

20

Data Upload from PSA to Standard DSO.

DTP Package size is 5 Records.

End routine is calculating total sales which are used for discount calculation of each customer based on Sales value and assigning it to each product row.

Without Semantic Group Data Upload

Total sales value and Discount amount when data loaded without semantic group is as below

Product No

Customer No

Country

Product Category

Sale Value (In US $)

Total Sales (In US $)

Discount Amount (In US $)

P001

C001

US

Home

5000

5000

500

P002

C002

UK

Electronics

3000

3000

150

P003

C003

Germany

Kids

2000

2000

100

P004

C004

India

Home

4000

4000

200

P005

C001

US

Electronics

1000

1000

50

P006

C003

Germany

Kids

6000

6000

600

P007

C005

France

Home

5000

5000

500

P008

C004

India

Electronics

3000

3000

150

P009

C002

UK

Kids

2000

2000

100

P010

C001

US

Home

1000

1000

50

Here if you notice about Customer C001 having same country and product category but Total sale is assigned as 5000 and 1000 respectively.

In such case for Product P010, customer received only 5% discount where as actually he/she should get 10%.

Similar case is for Customer C003 and product P003.

This is because Data uploaded into 2 equal packages of 5 records each, first 5 records into 1st package and rest 5 records in 2nd package.

Since in First package 5 records are as below,

Product No

Customer No

Country

Product Category

Sale Value (In US $)

P001

C001

US

Home

5000

P002

C002

UK

Electronics

3000

P003

C003

Germany

Kids

2000

P004

C004

India

Home

4000

P005

C001

US

Electronics

1000

 

Total Sales Value is calculated as below

Product No

Customer No

Country

Product Category

Sale Value (In US $)

Total Sale (In US $)

P001

C001

US

Home

5000

5000

P002

C002

UK

Electronics

3000

3000

P003

C003

Germany

Kids

2000

2000

P004

C004

India

Home

4000

4000

P005

C001

US

Electronics

1000

1000

So Discount values for first 5 rows are stored into Target DSO as below

Product No

Customer No

Country

Product Category

Sale Value (In US $)

Total Sale (In US $)

Discount Amount (In US $)

P001

C001

US

Home

5000

5000

500

P002

C002

UK

Electronics

3000

3000

150

P003

C003

Germany

Kids

2000

2000

100

P004

C004

India

Home

4000

4000

200

P005

C001

US

Electronics

1000

1000

50

For next 5 rows as below

Product No

Customer No

Country

Product Category

Sale Value (In US $)

Total Sale (In US $)

Discount Amount (In US $)

P006

C003

Germany

Kids

6000

6000

600

P007

C005

France

Home

5000

5000

500

P008

C004

India

Electronics

3000

3000

150

P009

C002

UK

Kids

2000

2000

100

P010

C001

US

Home

1000

1000

50

With Semantic Group Data Upload

Key fields are Customer no, Country and Product category

Total sales value and Discount amount when data loaded with semantic group are as below

Product No

Customer No

Country

Product Category

Sale Value (In US $)

Total Sale (In US $)

Discount Amount (In US $)

P001

C001

US

Home

5000

6000

500

P002

C002

UK

Electronics

3000

3000

150

P003

C003

Germany

Kids

2000

8000

200

P004

C004

India

Home

4000

4000

200

P005

C001

US

Electronics

1000

1000

50

P006

C003

Germany

Kids

6000

8000

600

P007

C005

France

Home

5000

5000

500

P008

C004

India

Electronics

3000

3000

150

P009

C002

UK

Kids

2000

2000

100

P010

C001

US

Home

1000

6000

100

In this case records will be grouped based on Semantic key definition and even if there are 2 packages each will have records as below.

Records in First package grouped based on are Customer No, Country and Product Category

Product No

Customer No

Country

Product Category

Sale Value (In US $)

Total Sale (In US $)

Discount Amount (In US $)

P001

C001

US

Home

5000

6000

500

P002

C002

UK

Electronics

3000

3000

150

P005

C001

US

Electronics

1000

1000

50

P009

C002

UK

Kids

2000

2000

100

P010

C001

US

Home

1000

6000

100

Records in Second package

Product No

Customer No

Country

Product Category

Sale Value (In US $)

Total Sale (In US $)

Discount Amount (In US $)

P003

C003

Germany

Kids

2000

8000

200

P004

C004

India

Home

4000

4000

200

P006

C003

Germany

Kids

6000

8000

600

P007

C005

France

Home

5000

5000

500

P008

C004

India

Electronics

3000

3000

150

Due to this semantic grouping we have correct values for the Total Sales and our discount values are also calculated as expected.

Please note to accommodate records as per semantic group, package size will be automatically adjusted. Hence, it will not be constant through out the load.

Case 2. Error handling

Similarly if Error handling is enabled and error DTP is available, error handling functionality will work as below based on semantic group.

Without Semantic Group Data Upload

In case there is an error in the record having product no P001 then it will go into error stack and rest of the records will be uploaded. In such case for Product P010, total sales value will be 1000 and hence discount will be 5% i.e. 50$.

Even later if we will correct first record (P001) into error stack and upload it through error DTP, Discount value will not change for record P010.

With Semantic Group Data Upload

In case there is an error in the record having product no P001 then it will go into error stack along with record having product id P010 and rest of records will be

uploaded into target. It is due to semantic definition, so even one record is having error, both records are transferred into error stack to maintain semantic grouping.

In such case, when product P001 will be corrected into error stack and error DTP will run, it will upload both records with correct total sales and discount value.

Additional Note

Here I have used only 2 cases for illustration purpose. In real life there can be more use cases as per specific needs.

Please note above example is to explain the concept of semantic groups. There can also be other ways to achieve this calculation.

Hope it will help to get better insight about semantic groups into DTP for beginners.

To report this post you need to login first.

26 Comments

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

  1. Suman Chakravarthy K

    Nice demonstration with real-time examples…But this example is to upload to DSO(W-Optimized) One , Am i right? If you upload these 10 records to Standard DSO, then the behavior will change based on the Key fields and settings in your DSO.

    Thanks,

    Suman

    (0) 
    1. Sangita Kumari Post author

      Hello Suman,

      Thanks for comments. Even in Std DSO, if you have defined only Product id as key field it will behave as I mentioned in the above example. So here assumption is in Std DSO only product id is key field.

      But you are right if you have already defined Customer No, Country and Product category as key field in Std DSO behavior will be different.

      Best Regards,

      Sangita

      (0) 
  2. Anshu Lilhori

    Really helpful documnet to understand the concept of semantic keys in grouping the same data and processing it in similar package.

    It automatically increases the Package size based on records i believe.

    Regards,

    Anshu Lilhori

    (0) 
  3. anirban mukhopadhyay

    Hi Sangita,

    Firstly, this is a top notch article with crisp explanations. ๐Ÿ™‚

    However, I just had a little thought. I don’t think that the DTP package size increases anytime. For the same set of semantic keys, standard aggregation takes place and thus there is one record for the same set of semantic keys. These records have been aggregated during extraction from the source. When semantic keys are used, internally sorting of the source data takes place during extraction on the basis of the semantic keys.

    Let me know what you think! ๐Ÿ™‚

    Regards,

    Anirban.

    (0) 
    1. Sangita Kumari Post author

      Hello Anirban,

      Thanks for appreciation. To answer your question, I will suggest try to load data with semantic key and without semantic key and observe each DTP package size. It will be self explanatory and shall answer your question.

      Thanks.

      Regards,

      Sangita

      (0) 
  4. Anup Singh

    Hi ,

    Do we have similar option while extracting through BODS of semantic grouping of specific fields ..?

    Thanksย Sangitaย – Good documentation

    Regards,

    Anup Singh

     

     

    (0) 

Leave a Reply