Need a way to add a partition as a field in a query

[patrice_cote]

Hello,

We are using Toad for Oracle to develop our sql views, which we bind to Sisense using ElastiCube. For this case, we have an external source of data, I created a table using an excel sheet and we have a custom table using a query created directly in the ElastiCube so that we can work with the excel sheet. The situation would be a list of projects (PROJ_NO) with their cost (COST) for each date (PERIOD). A period would consist of a year and a month, like 202311. I need to rank each project according to it's cost by period and also get the max rank for each period. For the rank I used RankDenseAsc(PERIOD, COST)  and it seems to do the trick. But I am struggling to find a way to get the max rank for the current period. In Toad for Oracle it is accomplished by doing MAX(RK_PROJ) OVER (PARTITION BY PERIOD), RK_PROJ being the rank calculated previously for the current period. There has to be a way to do this in the ElastiCube. I'm all ears if you have any idea.

Thanks

[Liliia_DevX · Accepted solution]

Hi Patrice, 

Please note that the following rank functions are currently supported on the ElastiCube level in Sisense:

RANK(), RANKASC(order_by_field), RankCompetitionAsc(partition fields list, order field), RankCompetitionDesc(partition fields list, order field), RankDenseAsc(partition fields list, order field), RankDenseDesc(partition fields list, order field), RANKDESC(order_by_field).

The detailed description of these functions could be found in this guide - "Mathematical Functions".

If these functions do not allow you to achieve your goal, you can run the query with "max() over partition" in the custom import query so that it will be executed on the datasource side where this function is supported and then return the result dataset to the table in ElastiCube. 

Best Regards, Lily

[Liliia_DevX · Accepted solution]

Hi Patrice, 

Please note that the following rank functions are currently supported on the ElastiCube level in Sisense:

RANK(), RANKASC(order_by_field), RankCompetitionAsc(partition fields list, order field), RankCompetitionDesc(partition fields list, order field), RankDenseAsc(partition fields list, order field), RankDenseDesc(partition fields list, order field), RANKDESC(order_by_field).

The detailed description of these functions could be found in this guide - "Mathematical Functions".

If these functions do not allow you to achieve your goal, you can run the query with "max() over partition" in the custom import query so that it will be executed on the datasource side where this function is supported and then return the result dataset to the table in ElastiCube. 

Best Regards, Lily

[patrice_cote]

Thanks for your reply Lily,

Unfortunately I cannot do "max() over partition" because I must create this field in the ElastiCube. I import an excel sheet as a table and it's with this data that I must get the max rank by period. I tried the mathematical functions that you mentioned, it works to get the rank of each project for a given period, but I am not able to get the max rank for this period. If I was in Oracle I would simply use something like MAX(RK_OPP) OVER (PARTITION BY PERIODE), but I'm looking for a way to do this in Sisense.

I'll keep searching for a way to accomplish this, if you ever think about a way to do it I'd be grateful.

Thanks

[Liliia_DevX · Accepted solution]

Hi,

Thanks for the detailed explanation. Since the function over partition is not currently supported in ElastiCube manager within a custom table, please try to use an alternative SQL queries to achieve the same goal. An example SQL code that might work is added below:

SELECT t1.partition_column, t1.value_column
FROM your_table t1
JOIN (
    SELECT partition_column, MAX(value_column) AS max_value
    FROM your_table
    GROUP BY partition_column
) t2
ON t1.partition_column = t2.partition_column AND t1.value_column = t2.max_value

 This query will return rows with the maximum "value_column" within each partition defined by "partition_column" without using the "MAX" function over the partition directly. 

Best Regards, Lily

[patrice_cote]

Hello,

This solution should work, I need to make some adjustments but I'm onto something!

Thanks a lot