Ranking vs Dense Ranking Functions in BO

RANK

RANK calculates the rank of a value in a group of values. RANK calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. Rows with equal values for the ranking criteria receive the same rank. Database system adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.

DENSE_RANK

DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank.

RANK function in Universe Designer

RANK() OVER (PARTITION BY () ORDER BY ())

PARTITION BY () – you can mention any of your dimension object for which you wanted Rank and should be grouped for some of measures. This is an option parameter in RANK.

ORDER BY () – RANK will be calculated based on Measure / Dimension object along with ASC / DESC order. This parameter is must for RANK function.

Example: To get the Ranking for State wise Sales amount, below is the sample data as is.

Rank and Dense Rank1Create a Ranking report for State wise based on Sales amount.

Example 1: RANK() OVER (ORDER BY (Sum(SalesAmount)) DESC)

RANK function would consider the SalesAmount in Descending order for each State and return the RANK numbers. Distinct amount values are considered as same RANK numbers. In the below example, your report SQL would be,

Select Country_name, State, Sum(salesAmount), RANK() OVER (ORDER BY (Sum(SalesAmount)) DESC) rank from SalesTable group by Country_name, State
Rank and Dense Rank2RANK function won’t display the numbers consecutively if two or more values are same and those values are considered as same RANK. Remaining RANK numbers will be skipped for the number of Rows which has same values. In the above example, RANK 3 is having 2 rows due to same amount and RANK 4 is skipped and next RANK 5 is displayed.

Example 2: RANK() OVER (ORDER BY State)

RANK function would consider the State object as order and return the RANK numbers for each State & each row. Each row will have an separate RANK and same State repeats multiple rows, those rows will have same RANK numbers.

Select Country_name, State, Product_category,Sum(salesAmount), RANK() OVER (ORDER BY State) rank from SalesTable group by Country_name, State, , Product_category
Rank and Dense Rank3Example 3:  RANK() OVER  (PARTITION BY (Sate, Product_Category) ORDER BY (Sum(SalesAmount)) DESC)

In this case RANK will be calculated based on State & Product Category for the Sum of Sales Amount.

DENSE RANK function in Universe Designer

DENSE_RANK() OVER (PARTITION BY () ORDER BY ())

PARTITION BY () – you can mention any of your dimension object for which you wanted Rank and should be grouped for some of measures.

ORDER BY () – DENSE_RANK will be calculated based on Measure / Dimension object along with ASC / DESC order.

Example 1: DENSE_RANK() OVER (ORDER BY (Sum(SalesAmount)) DESC)

DESNE_RANK function would consider the SalesAmount in Descending order for each State and return the RANK numbers. Distinct amount values are considered as same RANK numbers and next rows will have a consecutive RANK numbers. In the below example, your report SQL would be,

Select Country_name, State, Sum(salesAmount), DENSE_RANK() OVER (PARTITION BY (Sate) ORDER BY (Sum(SalesAmount)) DESC) D_Rank from SalesTable group by Country_name, State
Rank and Dense Rank4DENSE_RANK function will display the numbers consecutively if two or more values are same and those values are considered as same RANK. Remaining RANK numbers will be considered for next Rows which has same values. In the above example, RANK 3 is having 2 rows due to same amount and RANK 4 also having 2 rows and these won’t be any RANK number skipped in DENSE RANK and next RANK 5 is displayed.

DENSE_RANK can be used whenever you r report need consecutive RANK numbers to identify the range of PRODUCTs. DENSE RANK wont skip nay RANK numbers if there is same values are occurred and you will get consecutive numbers in you report and you can decide either to use RANK or DENSE_RANK based on your needs.

Advertisements

4 thoughts on “Ranking vs Dense Ranking Functions in BO

  1. Hi Madesh,
    Kindly help me to sort the issue out.I have a requirement of displaying certain fields which matches multiple patterns.To ellaborate:
    “There is a field called -“Legacy Name1” which has to be displayed according to the pattern which contains ““C/O”, “CARE OF”, “ATTENTION “…etc.
    As an instant solution i have included multiple filters,which is working fine.But i was trying the other way round by creating a variable using the “Match() function”,but its not working.Is there any possible way to include multiple matching patterns seperated by commas like:

    =If Match([LEGACY NAME1];”*CO*”,”*CareOf*”,”*ATTN*”) Then [LEGACY NAME1]

    the above is not working although.

    • Hi,

      You can try creating universe object using decode or case function. Example =Case when ([LEGACY NAME1]) in (“*CO*”,”*CareOf*”,”*ATTN*”) Then [LEGACY NAME1] end.

  2. Is there a way to break a table once it has been ranked? For example I’ve ranked my data based on highest values to lowest and put alerters on the top 4 to identify them (18 ranks in total). I would like to now break the data to view 1-4 then 5-18 below it. Is this possible?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s