Archiv für Februar 27, 2017

Oracle Analytic Functions III: RANK() & DENSE_RANK()

Today I’ve prepared a lesson in Oracle analytic functions. Basicly these functions are an enhancement of the aggregation functions like SUM, MAX, MIN, etc. With the help of this enhancement it’s possible to calculate different aggregations within the same query. With a classic group by you can only have one aggregation per query. With analytic functions you can have more than this. It’s also possible to calculate windows, i.e. to access rows before or after the current row which gives you the ability to create dynamic window sums, etc. This basic functionality is described in part 1 and part 2 of this tutorial. Beside these basics functions there are a lot of additional functions in the database.

Today I want to demonstrate the use of the functions RANK() and DENSE_RANK(). These are used to calculate rankings in lists. The ranking depends on the sorting order which you can define within each calculation, i.e. you can create one ranking based on turnover descending and another on turnover ascending within the same query. As with all analytical functions you can also use partitioning with the ranking functions. This gives you the ability to start a new ranking for each partition. We will see that in the example. Basicly RANK() and DENSE_RANK() work in the same way but the difference lays in the way how they deal with rows that have the same ranking. If you have two rows with the same turnover they receive the same rank. The function RANK() then skips the next rank and continues with the following one. In comparision with that DENSE_RANK() continues with the next rank. Here comes the first example to demonstrate the basic stuff. This example is based on the TBL_TEST table (it consists of four columns: DAY, PRODUCT_NO, PRODUCT_NAME, TURNOVER. See also the example files):

SELECT x.*,
       RANK() OVER(ORDER BY PRODUCT_NO) AS RANK_NO,
       DENSE_RANK() OVER(ORDER BY PRODUCT_NO) AS DRANK_NO
FROM (
      SELECT to_char(DAY,’YYYY-MM’) AS MONTH,
             PRODUCT_NO,
             SUM(TURNOVER) as TURNOVER
      FROM TBL_TEST
      GROUP BY to_char(DAY,’YYYY-MM’), PRODUCT_NO
) x

Result:

BLOG_0028_PIC01_Example_RANK_DENSE_RANK

This example works with a sub-query to aggregate the data to a month based level. Then in the main query RANK() and DENSE_RANK() are used to calculate a ranking based on the product-no column. For that PRODUCT_NO appears in the ORDER BY of the RANK and DENSE_RANK functions. In the results you can see that the first four rows have the same rank. The function RANK() continues with rank 5 then but DENSE_RANK with 2. That’s the difference between these two functions when rows have the same rank.

I’ve mentioned that it’s possible to create a new ranking which each partition. The next example deals with that:

SELECT x.*,
       RANK() OVER(PARTITION BY MONTH ORDER BY PRODUCT_NO) AS RANK_NO
FROM (
      SELECT to_char(DAY,’YYYY-MM’) AS MONTH,
                    PRODUCT_NO,
                    SUM(TURNOVER) as TURNOVER
      FROM TBL_TEST
      GROUP BY to_char(DAY,’YYYY-MM’), PRODUCT_NO
) x;

Result:

BLOG_0028_PIC02_Example_RANK_PARTITION_BY

Again we have a sub-query to aggregate daily values in the table to monthly values. In the main query we just added a PARTITION BY MONTH to the definition of the RANK_NO column and that changes the list in that way that we have a ranking per month now.

To finish todays article I’ve created a small task. The solution can be found in the download area:

Produce a list with products, days and turnover. Create a ranking based on the turnover (of product and day). The data should be filtered to January 2017. And in the result the 3 top days should be displayed. The list should look like this one:

BLOG_0028_PIC03_Task1_Result

That’s it for RANK and DENSE_RANK. I hope you enjoyed today’s lesson.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Download

BLOG_0028_RANK_DENSE_RANK_Examples_TASK.zip

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter

Macro functions in Cognos Report Studio

Hi,

today I will deal with macro functions. I had a customer who wanted to switch between two dimensions to drill up & down in a dimensional report. That means you have to dynamically change the MUN(=member unique name) for this element to decide whether to use dimension A or dimension B.

The two MUNs for the dimensions looked like this on the highest level:

  1. [Dimensional].[PG].[PG].[Product Group]
  2. [Dimensional].[AX].[AX].[Target Group]

To solve this task I used the following macro functions:

  • Operator +
  • Function substitute (<search pattern>, <replace text>, <orig text>)
  • Function prompt (<p_name>, <data type>, <default>)

To use macro functions you have to define a  macro block like this:  # ….… #. Between the # symbols you will place the macro functions. The macro then defines the expression throughout the macro functions. In our case it will result in one of the two MUNs described above.

Example: You could create a query with a query item that contains the following macro function: # ‘Test1‘+‘Hello‘ #. This will result in the string Test1Hello at runtime in this query item. It’s like you would place this string in the query item by yourself but with macro functions you have the opportunity to dynamically define such expressions.

If we take a look at our example. We’ve create a text: Test1Hello. If we placed this into the query item we would receive an error because of the syntax. We need to put ‘  ‘ around it to  make it a string. For this we can use the macro function sq( … ). Sq stands for single quotes. We will enhance our little example to this: # sq(‘Test1‘ + ‘Hello‘) #. This will result in the string ‘Test1Hello‘ and no error will occur. By the way the operator + concats two strings.

To get back at my customer’s problem. The function substitute(…) will replace the string <search pattern> within the string <orig text> by the string <replace text>. Prompt(…) will ask for parameters and returns the value of a parameter. First argument of this function is the name oft he parameter, second the datatype and third a default value. The third argument is optional. Regarding the datatype. There is the string which is text with single quotes around it and there exists token which is a text too but without single quotes. If you have a prompt page with this parameter it won’t be asked for it again but the value is returned here as well.

With the knowledge of these functions we can now come to the solution for my problem:

# ‘[Dimensional].[' +  prompt('p_proddim', 'token' ,'PG')  +  '].['  +  prompt('p_proddim', 'token' ,'PG')  +  '].['  +  substitute('AX', 'Target Group',  substitute('PG', 'Product Group', prompt('p_proddim', 'token' ,'PG')))  +  ']‘   #

The parameter p_proddim contains the selected dimensions (‘PG’ or ‘AX’). First we take the constant text at the beginning (‘[Dimensional.[‘) then we concat the content of p_proddim then the string ‘].[‘ and then again the content of p_proddim. The result is: [Dimensional].[PG].[PG].[ Instead of PG could also be AX depending on the parameter’s selection. The next part is something like an if-then-else. But it’s realized via a nested substitute call. The inner substitute takes the parameter value of p_proddim and checks the value for ‘PG’. If it is found it’s replaced by ‘Product Group’. If not nothing happens. The result of this substitute is the text to be checked in the outer substitute. This one is checked for ‘AX’ and is then replaced by ‘Target Group’.

This is one example how to use macro functions. There are a lot others. In future articles I will deal with macro functions again.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter