Implementing Rank Functionality in SAP Data Services SAP Data Services is a versatile and powerful enterprise information management tool that can extract, transform, and load data between many different systems and databases. In this post, I will detail how to implement a rank functionality using tools available to you within SAP Data Services. The RANK function is becoming a more and more common requirement in today’s business world because it is able to limit the number of records that are displayed on a report or dashboard down to a meaningful set. Typically, ranking is used to limit records to those that are most recent. This is helpful in dashboards and mobile reports where a user certainly would not want to be accessing every piece of data that is available. A rank function simply should assign a numeric rank to a value that is part of a set of values based on your ranking criteria. For example, if you wanted to assign ranks to the set of dates {2/5/2013, 12/25/2012, 3/15/2013, 1/2/2013, 11/27/2012} in order of descending values, your output would look like: Table Key 1 2 3 4 5

Date 2/5/2013 12/25/2012 3/15/2013 1/2/2013 11/27/2012

Rank 2 4 1 3 5

Higher ranks are given to the dates with a higher value. A more realistic example would be a request to view the most recent sales transactions by outlet number. Let’s start with some sales data: Sales Order 1 2 3 4 5 6 7 8 9 10 11 12 13

Outlet A B C C A B B B A C C A C

Date 1/2/2013 1/3/2013 1/3/2013 1/4/2013 1/4/2013 1/4/2013 1/5/2013 1/6/2013 1/6/2013 1/6/2013 1/7/2013 1/7/2013 1/8/2013

A new sales dimension with a rank column will allow us to show the most recent sales records per outlet and dictate how many records are displayed. Please note that I am simplifying here by assuming that only one sales transaction can be received by an outlet per day. This is obviously not a real world example, so you will have to apply the principles of this post to achieve your desired rank goal with your data. The solution to implement rank functionality via SAP Data Services is a combination of utilizing the ORDER BY clause in one Query transform and then using the gen_row_num_by_group function in a second Query transform. A detailed example follows. Step one is to create a dataflow and place two query transforms between your source and target.

Next, open up the first Query and specify the fields you would like to bring over from the source. Drag the column(s) you would like to rank by into the Order By tab. Here, I order by the Outlet ID first and then Sales Date second. The order does matter; I need the records ordered by Outlet ID first since that is the first criteria for my ranking. Once they are ordered by Outlet ID I would like them in order by date with the most recent first. Specify ascending or descending for the sort of each based on your requirements. I picked ascending for the Outlet ID sort although it does not matter in this particular example. I picked descending for the Sales Date sort so that the most recent records (largest date values) would be placed before those further in the past (smaller date values).

The output of this query with our sample data would look like: Sales Order 12 9 5 1 8 7 6 2 13 11 10 4 3

Outlet A A A A B B B B C C C C C

Date 1/7/2013 1/6/2013 1/4/2013 1/2/2013 1/6/2013 1/5/2013 1/4/2013 1/3/2013 1/8/2013 1/7/2013 1/6/2013 1/4/2013 1/3/2013

In the second Query transform, bring over the columns from the first query and make a new column that will utilize the gen_row_num_by_group function. This function generates row numbers starting at 1 and incrementing by 1 for each record in the group that is defined by the expression list. Once a new group is reached the function will restart back at 1. Note that multiple expressions can be passed into the list to create the group. Here I named the new column RANK and passed the Outlet ID into the expression list of the function. In our example, the function will generate row numbers (our rank values) for the sales records and restart for each Outlet ID that is come across, since the group is composed solely of the Outlet ID. Also, we are sending in ordered data with the most recent sales records first, so the more recent records will be given a higher value by this function.

The output of this query will be: Sales Order 12 9 5 1 8 7 6 2 13 11 10 4 3

Outlet A A A A B B B B C C C C C

Date 1/7/2013 1/6/2013 1/4/2013 1/2/2013 1/6/2013 1/5/2013 1/4/2013 1/3/2013 1/8/2013 1/7/2013 1/6/2013 1/4/2013 1/3/2013

Rank 1 2 3 4 1 2 3 4 1 2 3 4 5

I am also passing the output of this query into a dimension table to illustrate how to use the data. We can later join this dimension table to a sales fact table where we can utilize the RANK column to display the most recent N number of records desired per outlet. A little creativity and ingenuity can go a long way in SAP Data Services. The many transforms and functions available to you will ensure that you will always be able to meet your enterprise information management needs and goals.

Rich Hauser, Business Intelligence Consultant Decision First Technologies [email protected] Rich is a business intelligence consultant specializing in Enterprise Information Management. He has delivered customized SAP BusinessObjects solutions for customers of all sizes across a variety of industries. With Decision First Technologies, Rich utilizes SAP Data Services and SAP Information Steward.