Dense Ranking in SAP Business Objects Web Intelligence

[former_member45129]

Introduction

This Document will provide an Idea on how we can achieve Dense Rank at Web Intelligence report.

I Prepared this scenario when our Customers wants a Standard report that should have a provision for end user to select the Base of analysis(i.e. the KPI that they want to see) and Ranking criteria(Top 5,Top 10,Bottom 5.. etc.)

Currently we are having normal Ranking in WEBI Report as, the Rank values will be Specifies that the rank of a row is defined as 1 plus the number of rows that strictly precede the row ,if the rows are distinct . Thus, if two or more rows are not distinct with respect to the ordering, there will be one or more gaps in the sequential rank numbering.

Dense Rank

Dense_Rank() over(Partition by  partitioning-expression 1 Order by sort-key-expression 2 ASC/DESC)

• 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. Therefore, there will be no gaps in the sequential rank numbering. Rows with equal values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.

      PARTITION BY (partitioning-expression,...)

Defines the partition within which the OLAP operation is applied. A partitioning-expression is an expression that is used in defining the partitioning of the result table. Each column name that is referenced in a partitioning-expression must unambiguously reference a column of the result table of the subselect that contains the OLAP specification. A partitioning-expression cannot include a scalar-full select or any function that is not deterministic or has an external action.

This document is not explained with the partition option,since its not the part of our scenario.

ORDER BY (sort-key-expression,...)

Defines the ordering of rows within a partition that is used to determine the value of the OLAP specification. It does not define the ordering of the result table.

Use the your Key figure(KPI) over here.

ASC

Specifies that the values of sort-key-expression are used in ascending order.

DESC

Specifies that the values of sort-key-expression are used in descending order.

System Requirements

• SAP Business Objects 4.0 SP2 FP13 (Web Intelligence Rich Client or BI Launchpad)
• DB2 C Express V9.0 (free from IBM).
• Business Objects Data Services Designer 4.0 SP2 Patch5.

Database Ranking(Dense Rank) at query filter:

We can add database Rank (dense rank) at query filter, but the limitations is that we need to mention the measure at query filter and ranking criteria as a static input.

We can achieve the following scenario through this document.

• End User can be able to select the KPI on which they going analyze the Business.
• User can also able to select the Ranking criteria.

Please see above image for reference

Step to Achieve Dense Rank

Prerequisite:

1. Create a Fact Table with Amount Income and Amount Outgo(KPI’s) with Dimension id as State in an appropriate Table Space in DB2.(Please see table script for reference)

create table DATAMART.BUSINESS_FACT_MART

(

URN                  INTEGER                       PRIMARY KEY NOT NULL,

PERIOD_ID         INTEGER                       NOT NULL,

STATE_ID          INTEGER                       NOT NULL,

UNIT_ID                        INTEGER                       NOT NULL,

AMOUNT_INCOME          DECIMAL(31,3)  ,          

AMOUNT_OUTGO           DECIMAL(31,3)  ,          

CREATE_BY       VARCHAR(25)    ,          

MOD_DT            TIMESTAMP                   ,

ETL_INSERT_DT TIMESTAMP                  

)    IN USERSPACE1 ;

2. Load the Table in such a way that Two States should have same values to demonstrate the dense rank functionality (with Amount Income and Amount Outgo).

Steps to Reproduce:

1. Create a Universe based on the Fact Table.

( i) Create the measure objects Amount Income and Amount Outgo.

(ii) Create an object Rank Amount Income(as a measure) as mentioned in below snapshot.

(iii) Create an object Rank Amount Outgo(as a measure) as mentioned in below snapshot.

Please see above image for reference

(iv) create two Universe level prompts as below

          -Base of Analysis with Static LOV’s as per the List of KPI you are going use.

          -Number of states with Static LOV’s as per your ranking criteria

(v) Publish the business layer to the repository.

2. Create a webi Report on top on that Universe.

Please see below steps for Achieving Dense Rank at report level.

(i)Drag the necessary objects(State name, Base of Analysis, Amount Income, Amount Outgo, Rank Amount Income and Rank Amount Outgo),Please see the below snapshot for reference

     Please see above image for reference

     The Following SQL will be generated.

SELECT

"DATAMART"."BAP_MA_STATE_MART"."STATE_NAME",

@prompt(Base of Analysis),

sum("DATAMART"."BUSINESS_FACT_MART"."AMOUNT_INCOME"),

Case @prompt(Number of states)

When 'Top 5' Then

dense_rank() Over(Order by sum("DATAMART"."BUSINESS_FACT_MART"."AMOUNT_INCOME") DESC)

When 'Top 10' Then

dense_rank() Over(Order by sum("DATAMART"."BUSINESS_FACT_MART"."AMOUNT_INCOME") DESC)

When 'Bottom 5' Then

dense_rank() Over(Order by sum("DATAMART"."BUSINESS_FACT_MART"."AMOUNT_INCOME") ASC)

When 'Bottom 10' Then

dense_rank() Over(Order by sum("DATAMART"."BUSINESS_FACT_MART"."AMOUNT_INCOME") ASC)

end,

sum("DATAMART"."BUSINESS_FACT_MART"."AMOUNT_OUTGO"),

Case @prompt(Number of states)

When 'Top 5' Then

dense_rank() Over(Order by sum("DATAMART"."BUSINESS_FACT_MART"."AMOUNT_OUTGO") DESC)

When 'Top 10' Then

dense_rank() Over(Order by sum("DATAMART"."BUSINESS_FACT_MART"."AMOUNT_OUTGO") DESC)

When 'Bottom 5' Then

dense_rank() Over(Order by sum("DATAMART"."BUSINESS_FACT_MART"."AMOUNT_OUTGO") ASC)

When 'Bottom 10' Then

dense_rank() Over(Order by sum("DATAMART"."BUSINESS_FACT_MART"."AMOUNT_OUTGO") ASC)

end

FROM

"DATAMART"."BAP_MA_STATE_MART",     /* Table to store the States dimension*/

"DATAMART"."DIM_BAP_TIMETABLE",     /* Table to store the time dimension*/

"DATAMART"."BUSINESS_FACT_MART"    /*Fact table where is our KPI will be stored*/

WHERE

( "DATAMART"."BUSINESS_FACT_MART"."PERIOD_ID"="DATAMART"."DIM_BAP_TIMETABLE"."PERIOD_ID"  )

AND  ( "DATAMART"."BUSINESS_FACT_MART"."STATE_ID"="DATAMART"."BAP_MA_STATE_MART"."STATE_ID"  )

GROUP BY

"DATAMART"."BAP_MA_STATE_MART"."STATE_NAME",

@prompt(Base of Analysis)

          (ii) create a Variable KPI_Show as shown below.

          (iii) create a variable KPI as shown below.

          (iv) create an another variable KPI_Rank as shown below.

          (v) Drag objects State Name,KPI,KPI_Rank and add a block level filter to filter out the states names as per the user selection for Ranking.

          (vi)Add the Report filter to the above block as mentioned below

          (vii)The Result reporting block will be like below.

3. Please refer below blocks for comparison of normal ranking from our WEBI reports and dense rank which achieved through this document.

          (a)  When user wants to see Top 5 Ranking States.

          (b)  When user wants to see Bottom 10 Ranking States.