In this article, we will learn how to use SAP Data Services Pivot Transform. The Pivot transformation allows us to change how the relationship between rows is displayed. For each value in each pivot column, Data Services produces a row in the output data set. We can create pivot sets to specify more than one pivot columns. It basically convert Columns to Rows.

This article is part of our comprehensive data services tutorial "Learning SAP Data Services - Online Tutorial", you may want to check that tutorial first if you have not already done so.

Implementation

Let us consider we have source data of sales for different stores based on quarters. We may want to transform the sales data based on the quarters for each of the stores. Find below the implementation Data Flow.

Data Flow Pivot

To solve the problem we are using the SAP Data Services Pivot transform. We first set the Pivot sequence column value as PIVOT_SEQ. So the sequence value will be 1 for QUARTER1 column, 2 for QUARTER2 column .. when being transformed to multiple rows. Select the STORE as the Non-Pivoted column. In this example we are dealing with only one Pivot set for QUARTER. Hence the Pivot set is set to 1. Next include all the input QUARTER columns as the Pivot Columns. Set the output Header column name as QUARTER and the corresponding Data fields as SALES.

Pivot Transformation

Finally we map the quarter to the Pivot Sequence in the Query transform. So for the first quarter sales value the corresponding QUARTER will be 1 and so on.

Data Flow- Query

Transform Options

  • Pivot sequence column : The name we assign to the sequence number column. For each row created from a pivot column, Data Services increments and stores a sequence number. Data Services resets the sequence to 1 when creating an output row from an original row. For example, if the row corresponds to the first pivoted column, the sequence number for the row is 1.
  • Non-pivot columns : The input schema columns that appear in the output schema without modification.
  • Pivot set : The number that identifies a pivot set. For each pivot set, we need to define a group of pivot columns, a pivot data field, and a pivot header name. Each pivot set must have a unique Data field column and the Header column. Data Services automatically saves this information.
  • Pivot columns : A set of columns to be rotated into rows. Describe these columns desired output name in the Header column and corresponding data in these columns in the Data field column.
  • Data field column : The name of the column that contains the pivoted data. This column contains all of the Pivot columns values. The data type of this column is determined by the data type of Pivot columns. If two or more Pivot columns contains different data types, Data Services converts the columns to a single data type i.e. the data type of the first column we added to the pivot set.
  • Header column : The name of the column that contains the pivoted column names. This column lists the names of the source columns where the corresponding data originated.


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Getting Started SAP BODS

    This article is a step by step guide to learn the basic of SAP BODS. Starting from the Basic we will cover the essential topics like SCD implementation, Fact loading, CDC Mechanisms, Persistent Cache, Substitution Parameters, Variables and...

  • How to use Data Services Pivot Transformation

    In this article, we will learn how to use SAP Data Services Pivot Transform. The Pivot transformation allows us to change how the relationship between rows is displayed. For each value in each pivot column, Data Services produces a row in the...

  • Table Comparison Transform to Implement Slowly Change Dimension (SCD) in Data Service

    In this tutorial we will learn a new SAP Data services transform, known as Table Comparison Transform and we will see how we may use this transform to implement "slowly changing dimension" (SCD) Type - I. Like before, we have added a video tutorial at the...

  • Why SAP Data Services

    SAP BusinessObjects Data Services delivers a single enterprise-class solution for Data Integration, Data Quality, Data Profiling, and Text Data Processing that allows us to integrate, transform, improve, and deliver trusted data to critical...

  • GROUP RANK in Data Services

    In this article, we will learn how to implement RANK and DENSE RANK operations on GROUP in SAP Data Services.

  • XML file generation using SAP Data Services

    This article will demonstrate how to generate XML target files using SAP Data Services. Here our objective is to generate XML file with employee and department information.

  • SAP BODS Cluster Installation

    This article is a step by step guide on how to configure SAP BODS for High Availability using Windows Cluster services. To take advantage of fail-over support for SAP BusinessObjects Data Services services in a Windows Clustering Environment,...

  • Data Services Scripting Language

    We can use the Data Services Scripting Language to write scripts and custom functions to address complex logical expressions.

  • Data Services Metadata Query Part 1

    Data Services provides full access to the repository metadata tables and views for metadata analysis. To access this metadata either we can use SQL SELECT statements or use the metadata reporting from Management Console.

  • Top 50 SAP Business Objects Data Services (BODS) Interview Questions with Answers

    Learn the answers of some critical questions commonly asked during SAP BO Data Services interview.