Oracle Analytics supports the localization of attribute columns. This enables users to report and filter by their locale, but effortlessly share content without modification. When an attribute column is localized, it’s defined to use a descriptor ID column. Instead of having an attribute table with locale specific descriptions, a code column is used. The code column is the same across all locales. When the attribute column is selected, the code column is used in conjunction with a localization session variable in a LOOKUP function. The LOOKUP function queries a localized description table for the correct translation to use.
When the attribute is used in the ‘select’ clause or as a regular report filter, the logical SQL contains DESCRIPTOR_IDOF values used to generate efficient database SQL. But a word of caution, DESCRIPTOR_IDOF must be used judiciously. If used in the wrong place, filters won’t be applied in the most efficient place in the SQL. Instead of a filter being applied to a query against a fact table, that query will be unfiltered and the filter will be applied after the data is returned. This results in many more rows being returned than necessary and longer query times.
To begin, let’s go through how DESCRIPTOR_IDOF columns are used. Users aren’t aware that descriptor columns are being used unless they look at the session logs. In this example, we’re selecting a list of customers and their status, where status is localized.
From a prompt, all the user sees is the list of customer status values in their locale. There’s nothing to indicate the column is localized.
When you look at the session log, column ‘C2 Customer Status’ appears twice, once by itself, and once in the DESCRIPTOR_IDOF clause. More importantly, the values of the filter are not those shown in the filter box, but are the codes associated with the descriptions.
Logical SQL:
SET VARIABLE QUERY_SRC_CD=’Visual Analyzer’,SAW_SRC_PATH='{“viewID”:”view!1″,”currentCanvas”:”canvas!1″}’,ENABLE_DIMENSIONALITY=1;SELECT
0 s_0,
“A – Sample Sales”.”Customers”.”C1 Customer Name” s_1,
“A – Sample Sales”.”Customers”.”C2 Customer Status” s_2,
DESCRIPTOR_IDOF(“A – Sample Sales”.”Customers”.”C2 Customer Status”) s_3,
“A – Sample Sales”.”Base Facts”.”1- Revenue” s_4
FROM “A – Sample Sales”
WHERE
(DESCRIPTOR_IDOF(“A – Sample Sales”.”Customers”.”C2 Customer Status”) IN (‘STATUS 1’, ‘STATUS 2’, ‘STATUS 4’, ‘STATUS 3’))
ORDER BY 2 ASC NULLS LAST, 3 ASC NULLS LAST, 4 ASC NULLS LAST
FETCH FIRST 500001 ROWS ONLY
Database SQL
WITH
SAWITH0 AS (select sum(T5398.Revenue) as c1,
T5356.Name as c2,
T5356.Status_Key as c3,
T5356.Cust_Key as c4,
T5356.Type_Key as c5
from
BISAMPLE.SAMP_CUST_SEGMENTS_D T5365 /* D61 Customer Segments */ left outer join BISAMPLE.SAMP_CUSTOMERS_D T5356 /* D60 Customers */ On T5356.Segment_Key = T5365.Segment_Key,
BISAMPLE.SAMP_REVENUE_F T5398 /* F10 Billed Rev */
where ( T5356.Cust_Key = T5398.Cust_Key and (T5356.Status_Key in (‘STATUS 1’, ‘STATUS 2’, ‘STATUS 3’, ‘STATUS 4’)) )
group by T5356.Cust_Key, T5356.Name, T5356.Status_Key, T5356.Type_Key),
SAWITH1 AS (select 0 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c1 as c4,
D1.c4 as c5,
D1.c5 as c6
from
SAWITH0 D1),
SAWITH2 AS (select /*+ no_merge */ T5541.Lookup_Dsc as c1,
T5541.Lookup_Key as c2
from
BISAMPLE.SAMP_LOOKUPS_D T5541 /* L2 Other Lookups */
where ( T5541.Lookup_Type = ‘Customer Status’ and T5541.Language_Key = ‘en’ ) ),
SAWITH3 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7
from
(select D1.c1 as c1,
D1.c2 as c2,
nvl(D2.c1 , D1.c3) as c3,
D1.c3 as c4,
D1.c4 as c5,
D1.c5 as c6,
D1.c6 as c7,
ROW_NUMBER() OVER (PARTITION BY D1.c2, D1.c3, D1.c5, D1.c6, nvl(D2.c1 , D1.c3) ORDER BY D1.c2 ASC, D1.c3 ASC, D1.c5 ASC, D1.c6 ASC, nvl(D2.c1 , D1.c3) ASC) as c8
from
SAWITH1 D1 left outer join SAWITH2 D2 On D1.c3 = D2.c2
) D1
where ( D1.c8 = 1 ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5
from
SAWITH3 D1
order by c2, c3, c4 ) D1 where rownum <= 500001
The SQL contains four query blocks. The first query block queries the attribute and dimension tables. The third query block queries the localized description table performing the LOOKUP function. The fourth query block joins the results of the base query with the results of the lookup function. The filter on ‘C2 Customer Status’ is in the first query block, filtering the query that’s run against the database.
Be careful when using localized attribute columns in places other than a select statement or filter. When the attribute is used in an expression filter, column filter, case statement or other SQL function, the logical SQL doesn’t include the DESCRIPTOR_IDOF. This can result in inefficient database SQL. Changing the report definition can fix this problem. The best course of action is to rewrite the function in terms of the DESCRIPTOR_IDOF. If that isn’t possible, then we recommend adding report filters on the DESCRIPTOR_IDOF fields to the report. Depending upon the scenario, you may need to create parameters or presentation variables to set the correct values.
If the code column isn’t exposed in the presentation layer, you can still use this method by filtering on the DESCRIPTOR_ID column instead of the code itself.
Am I Using Columns That Have Descriptor IDs Defined?
One way to tell if the columns you’re using have descriptor IDs defined is to look at the logical SQL generated from your query. The logical SQL can be found in session logs in Manage Sessions in the Classic Administration page or under Developer in DV.
The logical SQL below shows the column you are selecting, in this case ‘C2 Customer Status’, followed by the same column name wrapped in DESCRIPTOR_IDOF. Note that this simple example shows the base column and the descriptor ID one after the other in the logical SQL, but they are often some distance apart.
SELECT
0 s_0,
“A – Sample Sales”.”Customers”.”C2 Customer Status” s_1,
DESCRIPTOR_IDOF(“A – Sample Sales”.”Customers”.”C2 Customer Status”) s_2
FROM “A – Sample Sales”
ORDER BY 2 ASC NULLS LAST, 3 ASC NULLS LAST
If you have access to the repository or semantic model, you can check the column properties to see if a descriptor ID column has been defined.
Here’s a repository example of a column having a descriptor ID defined:
Here’s a semantic model example of a column with a descriptor ID defined:
The Lookup function is defined as:
LOOKUP( SPARSE “01 – Sample App Data (ORCL)”.””.”BISAMPLE”.”L3 Direct Physical Lookups”.”Lookup_Dsc”, “01 – Sample App Data (ORCL)”.””.”BISAMPLE”.”D60 Customers”.”Status_Key”, VALUEOF(NQ_SESSION.USERLOCALE) , “01 – Sample App Data (ORCL)”.””.”BISAMPLE”.”D60 Customers”.”Status_Key”, ‘Customer Status’)
For more information on the LOOKUP function, see the documentation.
Filtering
A common use case for descriptor IDs is filtering. This applies to both using a filter function to filter a measure by an attribute value and using the attribute column as part of an expression filter. Dashboard filters automatically use the DESCRIPTOR_IDOF column.
When creating a filter based on a descriptor column, it’s important to note what the possible code values are. An easy way to do this is to create a calculation using DESCRIPTOR_IDOF on the descriptor field and create a report to return the description field and the code field.
The calculation is similar to:
The report will display as shown below:
In the first example, a filter function is used to filter a measure by an attribute value:
Logical SQL:
SELECT
0 s_0,
“Financials – GL Profitability”.”Scenario”.”Scenario Description” s_1,
DESCRIPTOR_IDOF(“Financials – GL Profitability”.”Scenario”.”Scenario Description”) s_2,
FILTER(“Financials – GL Profitability”.”GL Balance (AC)”.”Activity Amount” USING “Financials – GL Profitability”.”Scenario”.”Scenario Description” =’Journal updates actual balances.’) s_3
FROM “Financials – GL Profitability”
ORDER BY 2 ASC NULLS LAST, 3 ASC NULLS LAST
FETCH FIRST 500001 ROWS ONLY
Database SQL:
WITH
SAWITH0 AS (select sum(T596032.GBL_CRNC_PRD_NET_ACTIVITY) as c1,
T4287.CODE as c3
from
OAX$OAC.DW_ACCOUNTING_SCENARIO_D_TL T4287 /* Dim_DW_GL_ACCOUNTING_SCENARIO_D_TL */ ,
OAX$OAC.DW_GL_VARIANCES_ALL_CA_SEC T596032 /* Fact_Agg_DW_GL_VARIANCE_CA */
where ( T596032.CURRENCY_TYPE not in (‘ENTERED’) and T4287.CODE = T596032.ACCOUNTING_SCENARIO_CODE and T4287.LANGUAGE = ‘US’ and (T596032.CURRENCY_TYPE in (‘T’, ‘TOTAL_LEDGER_CURRENCY’)) )
group by T4287.CODE),
SAWITH1 AS (select /*+ no_merge */ T4281.DESCRIPTION as c1,
T4281.CODE as c2
from
OAX$OAC.DW_ACCOUNTING_SCENARIO_D_TL T4281 /* Lookup_DW_GL_ACCOUNTING_SCENARIO_D_TL */
where ( T4281.LANGUAGE = ‘US’ ) ),
SAWITH2 AS (select D1.c1 as c1,
nvl(D2.c1 , D1.c3) as c2,
D1.c3 as c3
from
SAWITH0 D1 left outer join SAWITH1 D2 On SYS_OP_MAP_NONNULL(D1.c3) = SYS_OP_MAP_NONNULL(D2.c2)
where ( nvl(D2.c1 , D1.c3) = ‘Journal updates actual balances.’ ) ),
SAWITH3 AS (select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
(select 0 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c1 as c4,
ROW_NUMBER() OVER (PARTITION BY D1.c3 ORDER BY D1.c3 ASC) as c5
from
SAWITH2 D1
) D1
where ( D1.c5 = 1 )
order by c2, c3 ) D1 where rownum <= 500001)
select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
SAWITH3 D1
There are four query blocks. The first queries the scenario and fact tables. This is where you would expect the scenario filter to be. The second query block queries the lookup table. The scenario filter isn’t applied until the third query block when the results of the first two query blocks are combined. As a result, the fact table query is not being filtered by scenario.
The calculation is rewritten to use the scenario DESCRIPTOR_ID column instead:
Logical SQL:
SELECT
0 s_0,
“Financials – GL Profitability”.”Scenario”.”Scenario Description” s_1,
DESCRIPTOR_IDOF(“Financials – GL Profitability”.”Scenario”.”Scenario Description”) s_2,
FILTER(“Financials – GL Profitability”.”GL Balance (AC)”.”Activity Amount” USING Descriptor_IDOF(“Financials – GL Profitability”.”Scenario”.”Scenario Description”)=’A’) s_3
FROM “Financials – GL Profitability”
ORDER BY 2 ASC NULLS LAST, 3 ASC NULLS LAST
FETCH FIRST 500001 ROWS ONLY
Note the DESCRIPTOR_IDOF in the filter function.
Database SQL:
WITH
SAWITH0 AS (select sum(T596032.GBL_CRNC_PRD_NET_ACTIVITY) as c1,
T4287.CODE as c3
from
OAX$OAC.DW_ACCOUNTING_SCENARIO_D_TL T4287 /* Dim_DW_GL_ACCOUNTING_SCENARIO_D_TL */ ,
OAX$OAC.DW_GL_VARIANCES_ALL_CA_SEC T596032 /* Fact_Agg_DW_GL_VARIANCE_CA */
where ( T4287.CODE = T596032.ACCOUNTING_SCENARIO_CODE and T4287.CODE = ‘A’ and T4287.LANGUAGE = ‘US’ and T596032.ACCOUNTING_SCENARIO_CODE = ‘A’ and (T596032.CURRENCY_TYPE in (‘T’, ‘TOTAL_LEDGER_CURRENCY’)) )
group by T4287.CODE),
SAWITH1 AS (select /*+ no_merge */ T4281.DESCRIPTION as c1,
T4281.CODE as c2
from
OAX$OAC.DW_ACCOUNTING_SCENARIO_D_TL T4281 /* Lookup_DW_GL_ACCOUNTING_SCENARIO_D_TL */
where ( T4281.LANGUAGE = ‘US’ ) ),
SAWITH2 AS (select D1.c1 as c1,
nvl(D2.c1 , D1.c3) as c2,
D1.c3 as c3
from
SAWITH0 D1 left outer join SAWITH1 D2 On SYS_OP_MAP_NONNULL(D1.c3) = SYS_OP_MAP_NONNULL(D2.c2) ),
SAWITH3 AS (select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
(select 0 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c1 as c4,
ROW_NUMBER() OVER (PARTITION BY D1.c3 ORDER BY D1.c3 ASC) as c5
from
SAWITH2 D1
) D1
where ( D1.c5 = 1 )
order by c2, c3 ) D1 where rownum <= 500001)
select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
SAWITH3 D1
There are still four query blocks and the lookup function is still present in the second query block. This is because the scenario description is part of the logical query. But now, scenario code is added as a filter in the main query block, resulting in fewer rows returned by the database.
Similar behavior is seen when a description is used in an expression filter.
Expression filter using description field:
Logical SQL:
SELECT
0 s_0,
“Financials – GL Profitability”.”Scenario”.”Scenario Description” s_1,
DESCRIPTOR_IDOF(“Financials – GL Profitability”.”Scenario”.”Scenario Description”) s_2,
“Financials – GL Profitability”.”GL Balance (AC)”.”Activity Amount” s_3
FROM “Financials – GL Profitability”
WHERE
(“Financials – GL Profitability”.”Scenario”.”Scenario Description” = ‘Journal updates actual balances.’)
ORDER BY 2 ASC NULLS LAST, 3 ASC NULLS LAST
FETCH FIRST 500001 ROWS ONLY
Database SQL:
WITH
SAWITH0 AS (select sum(T596032.GBL_CRNC_PRD_NET_ACTIVITY) as c1,
T4287.CODE as c3
from
OAX$OAC.DW_ACCOUNTING_SCENARIO_D_TL T4287 /* Dim_DW_GL_ACCOUNTING_SCENARIO_D_TL */ ,
OAX$OAC.DW_GL_VARIANCES_ALL_CA_SEC T596032 /* Fact_Agg_DW_GL_VARIANCE_CA */
where ( T596032.CURRENCY_TYPE not in (‘ENTERED’) and T4287.CODE = T596032.ACCOUNTING_SCENARIO_CODE and T4287.LANGUAGE = ‘US’ and (T596032.CURRENCY_TYPE in (‘T’, ‘TOTAL_LEDGER_CURRENCY’)) )
group by T4287.CODE),
SAWITH1 AS (select /*+ no_merge */ T4281.DESCRIPTION as c1,
T4281.CODE as c2
from
OAX$OAC.DW_ACCOUNTING_SCENARIO_D_TL T4281 /* Lookup_DW_GL_ACCOUNTING_SCENARIO_D_TL */
where ( T4281.LANGUAGE = ‘US’ ) ),
SAWITH2 AS (select D1.c1 as c1,
nvl(D2.c1 , D1.c3) as c2,
D1.c3 as c3
from
SAWITH0 D1 left outer join SAWITH1 D2 On SYS_OP_MAP_NONNULL(D1.c3) = SYS_OP_MAP_NONNULL(D2.c2)
where ( nvl(D2.c1 , D1.c3) = ‘Journal updates actual balances.’ ) ),
SAWITH3 AS (select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
(select 0 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c1 as c4,
ROW_NUMBER() OVER (PARTITION BY D1.c3 ORDER BY D1.c3 ASC) as c5
from
SAWITH2 D1
) D1
where ( D1.c5 = 1 )
order by c2, c3 ) D1 where rownum <= 500001)
select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
SAWITH3 D1
As with the filter function example, there are four query blocks. The first queries the attribute and fact tables and the second queries the lookup table. The filter for scenario isn’t applied in the first query block but again is applied when the first two query blocks are combined.
When the expression filter is changed to use DESCRIPTOR_IDOF the description instead of the description itself, we see the same behavior as the filter function.
Expression filter using DESCRIPTOR_IDOF:
Logical SQL:
SELECT
0 s_0,
“Financials – GL Profitability”.”Scenario”.”Scenario Description” s_1,
DESCRIPTOR_IDOF(“Financials – GL Profitability”.”Scenario”.”Scenario Description”) s_2,
“Financials – GL Profitability”.”GL Balance (AC)”.”Activity Amount” s_3
FROM “Financials – GL Profitability”
WHERE
(Descriptor_IDOF(“Financials – GL Profitability”.”Scenario”.”Scenario Description”) = ‘A’)
ORDER BY 2 ASC NULLS LAST, 3 ASC NULLS LAST
FETCH FIRST 500001 ROWS ONLY
DESCRIPTOR_IDOF is now seen in the ‘WHERE’ clause.
Database SQL:
WITH
SAWITH0 AS (select sum(T596032.GBL_CRNC_PRD_NET_ACTIVITY) as c1,
T4287.CODE as c3
from
OAX$OAC.DW_ACCOUNTING_SCENARIO_D_TL T4287 /* Dim_DW_GL_ACCOUNTING_SCENARIO_D_TL */ ,
OAX$OAC.DW_GL_VARIANCES_ALL_CA_SEC T596032 /* Fact_Agg_DW_GL_VARIANCE_CA */
where ( T596032.CURRENCY_TYPE not in (‘ENTERED’) and T4287.CODE = T596032.ACCOUNTING_SCENARIO_CODE and T4287.CODE = ‘A’ and T4287.LANGUAGE = ‘US’ and T596032.ACCOUNTING_SCENARIO_CODE = ‘A’ and (T596032.CURRENCY_TYPE in (‘T’, ‘TOTAL_LEDGER_CURRENCY’)) )
group by T4287.CODE),
SAWITH1 AS (select /*+ no_merge */ T4281.DESCRIPTION as c1,
T4281.CODE as c2
from
OAX$OAC.DW_ACCOUNTING_SCENARIO_D_TL T4281 /* Lookup_DW_GL_ACCOUNTING_SCENARIO_D_TL */
where ( T4281.LANGUAGE = ‘US’ ) ),
SAWITH2 AS (select D1.c1 as c1,
nvl(D2.c1 , D1.c3) as c2,
D1.c3 as c3
from
SAWITH0 D1 left outer join SAWITH1 D2 On SYS_OP_MAP_NONNULL(D1.c3) = SYS_OP_MAP_NONNULL(D2.c2) ),
SAWITH3 AS (select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
(select 0 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c1 as c4,
ROW_NUMBER() OVER (PARTITION BY D1.c3 ORDER BY D1.c3 ASC) as c5
from
SAWITH2 D1
) D1
where ( D1.c5 = 1 )
order by c2, c3 ) D1 where rownum <= 500001)
select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
SAWITH3 D1
Again, the filter is moved from the third query block to the first query block, where it’s applied to the query running against the scenario table and fact table, reducing the number of rows returned by the database.
Case Statements
Another common use case is using a case statement to change the description coming from the database to something else:
Logical SQL:
SELECT
0 s_0,
CASE WHEN “Financials – AP Liabilities”.”Party”.”Party Type”=’Person’ THEN “Financials – AP Liabilities”.”Party”.”Party Name” WHEN “Financials – AP Liabilities”.”Party”.”Party Type” =’Organization’ THEN “Financials – AP Liabilities”.”Supplier”.”Supplier Name” ELSE “Financials – AP Liabilities”.”Party”.”Party Type” END s_1,
“Financials – AP Liabilities”.”Facts – Analytics Currency”.”Liability Distribution Amount” s_2
FROM “Financials – AP Liabilities”
ORDER BY 2 ASC NULLS LAST
FETCH FIRST 500001 ROWS ONLY
Database SQL:
WITH
SAWITH0 AS (select sum(T582534.AP_GLOBAL_AMOUNT) as c1,
T3544.PARTY_TYPE as c4,
T3544.PARTY_NAME as c5,
T3428.PARTY_NAME as c6
from
OAX$OAC.DW_PARTY_D T3428 /* Dim_DW_PARTY_D_SUPPLIER */ ,
OAX$OAC.DW_PARTY_D T3544 /* Dim_DW_PARTY_D */ ,
OAX$OAC.DW_AP_SLA_LIAB_PMT_CA_SEC T582534 /* Fact_DW_AP_SLA_LIAB_PMT_CA_FiscalYear */
where ( T3428.PARTY_ID = T582534.SUPPLIER_PARTY_ID and T3544.PARTY_ID = T582534.SUPPLIER_PARTY_ID )
group by T3428.PARTY_NAME, T3544.PARTY_TYPE, T3544.PARTY_NAME),
SAWITH1 AS (select /*+ no_merge */ T7070.NAME as c1,
T7070.PARTY_TYPE_CODE as c2
from
OAX$OAC.DW_PARTY_TYPE_LKP_TL T7070 /* Lookup_DW_PARTY_TYPE_LKP_TL */
where ( T7070.LANGUAGE = ‘US’ ) ),
SAWITH2 AS (select D1.c1 as c1,
case when nvl(D2.c1 , D1.c4) = ‘Person’ then D1.c5 when nvl(D2.c1 , D1.c4) = ‘Organization’ then D1.c6 else nvl(D2.c1 , D1.c4) end as c2
from
SAWITH0 D1 left outer join SAWITH1 D2 On D1.c4 = D2.c2)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
(select 0 as c1,
D1.c2 as c2,
D1.c1 as c3,
ROW_NUMBER() OVER (PARTITION BY D1.c2 ORDER BY D1.c2 ASC) as c4
from
SAWITH2 D1
) D1
where ( D1.c4 = 1 )
order by c2 ) D1 where rownum <= 500001
There are three query blocks in the SQL. The first queries the attribute and fact tables and the second queries the lookup table. Similar to the other examples, the case statement isn’t performed in the main query block but instead in the query block that combines the results.
Rewrite the case statement to use DESCRIPTOR_IDOF:
Logical SQL:
SELECT
0 s_0,
CASE WHEN DESCRIPTOR_IDOF(“Financials – AP Liabilities”.”Party”.”Party Type”)=’PERSON’ THEN “Financials – AP Liabilities”.”Party”.”Party Name” WHEN DESCRIPTOR_IDOF(“Financials – AP Liabilities”.”Party”.”Party Type”)=’ORGANIZATION’ THEN “Financials – AP Liabilities”.”Supplier”.”Supplier Name” ELSE DESCRIPTOR_IDOF(“Financials – AP Liabilities”.”Party”.”Party Type”) END s_1,
“Financials – AP Liabilities”.”Facts – Analytics Currency”.”Liability Distribution Amount” s_2
FROM “Financials – AP Liabilities”
ORDER BY 2 ASC NULLS LAST
FETCH FIRST 500001 ROWS ONLY
Database SQL:
WITH
SAWITH0 AS (select sum(T582534.AP_GLOBAL_AMOUNT) as c1,
T3544.PARTY_TYPE as c4,
T3544.PARTY_NAME as c5,
T3428.PARTY_NAME as c6
from
OAX$OAC.DW_PARTY_D T3428 /* Dim_DW_PARTY_D_SUPPLIER */ ,
OAX$OAC.DW_PARTY_D T3544 /* Dim_DW_PARTY_D */ ,
OAX$OAC.DW_AP_SLA_LIAB_PMT_CA_SEC T582534 /* Fact_DW_AP_SLA_LIAB_PMT_CA_FiscalYear */
where ( T3428.PARTY_ID = T582534.SUPPLIER_PARTY_ID and T3544.PARTY_ID = T582534.SUPPLIER_PARTY_ID )
group by T3428.PARTY_NAME, T3544.PARTY_TYPE, T3544.PARTY_NAME),
SAWITH1 AS (select /*+ no_merge */ T7070.NAME as c1,
T7070.PARTY_TYPE_CODE as c2
from
OAX$OAC.DW_PARTY_TYPE_LKP_TL T7070 /* Lookup_DW_PARTY_TYPE_LKP_TL */
where ( T7070.LANGUAGE = ‘US’ ) ),
SAWITH2 AS (select D1.c1 as c1,
case when nvl(D2.c1 , D1.c4) = ‘Person’ then D1.c5 when nvl(D2.c1 , D1.c4) = ‘Organization’ then D1.c6 else nvl(D2.c1 , D1.c4) end as c2
from
SAWITH0 D1 left outer join SAWITH1 D2 On D1.c4 = D2.c2)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
(select 0 as c1,
D1.c2 as c2,
D1.c1 as c3,
ROW_NUMBER() OVER (PARTITION BY D1.c2 ORDER BY D1.c2 ASC) as c4
from
SAWITH2 D1
) D1
where ( D1.c4 = 1 )
order by c2 ) D1 where rownum <= 500001
Because we aren’t querying the description field separately, there isn’t a need to query the lookup table. As a result, there’s only one query block resulting in a much more streamlined SQL statement.
CONCATENATION
Our final example is a filter applied to a field that’s a concatenation of two other fields. Often a customer creates a calculation that combines a code value with a description value and in the course of reporting, applies a filter to the calculation.
Calculation to concatenate a code field with a description field:
Expression filter to filter concatenation calculation by a specific value:
Logical SQL:
SELECT
0 s_0,
“Financials – GL Profitability”.”Scenario”.”Scenario”||’~’||”Financials – GL Profitability”.”Scenario”.”Scenario Description” s_1,
“Financials – GL Profitability”.”GL Balance (AC)”.”Activity Amount” s_2
FROM “Financials – GL Profitability”
WHERE
((“Financials – GL Profitability”.”Scenario”.”Scenario”||’~’||”Financials – GL Profitability”.”Scenario”.”Scenario Description”) = ‘Actual~Journal updates actual balances.’)
ORDER BY 2 ASC NULLS LAST
FETCH FIRST 500001 ROWS ONLY
The concatenation function is both in the body of the SQL statement and the filter clause.
Database SQL:
WITH
SAWITH0 AS (select sum(T596032.GBL_CRNC_PRD_NET_ACTIVITY) as c1,
T4287.CODE as c4
from
OAX$OAC.DW_ACCOUNTING_SCENARIO_D_TL T4287 /* Dim_DW_GL_ACCOUNTING_SCENARIO_D_TL */ ,
OAX$OAC.DW_GL_VARIANCES_ALL_CA_SEC T596032 /* Fact_Agg_DW_GL_VARIANCE_CA */
where ( T596032.CURRENCY_TYPE not in (‘ENTERED’) and T4287.CODE = T596032.ACCOUNTING_SCENARIO_CODE and T4287.LANGUAGE = ‘US’ and (T596032.CURRENCY_TYPE in (‘T’, ‘TOTAL_LEDGER_CURRENCY’)) )
group by T4287.CODE),
SAWITH1 AS (select /*+ no_merge */ T4281.VALUE as c1,
T4281.CODE as c2,
T4281.DESCRIPTION as c3
from
OAX$OAC.DW_ACCOUNTING_SCENARIO_D_TL T4281 /* Lookup_DW_GL_ACCOUNTING_SCENARIO_D_TL */
where ( T4281.LANGUAGE = ‘US’ ) ),
SAWITH2 AS (select D1.c1 as c1,
concat(concat(nvl(D2.c1 , D1.c4), ‘~’), nvl(D2.c3 , D1.c4)) as c2
from
SAWITH0 D1 left outer join SAWITH1 D2 On SYS_OP_MAP_NONNULL(D1.c4) = SYS_OP_MAP_NONNULL(D2.c2)
where ( concat(concat(nvl(D2.c1 , D1.c4), ‘~’), nvl(D2.c3 , D1.c4)) = ‘Actual~Journal updates actual balances.’ ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
(select 0 as c1,
D1.c2 as c2,
D1.c1 as c3,
ROW_NUMBER() OVER (PARTITION BY D1.c2 ORDER BY D1.c2 ASC) as c4
from
SAWITH2 D1
) D1
where ( D1.c4 = 1 )
order by c2 ) D1 where rownum <= 500001
There are three query blocks in this query. The first query block queries the fact and attribute tables. The second query block queries the lookup table. The third query block performs the concatenation and filters the result set. Again, the filter is not applied in the initial query.
Instead of filtering on the concatenated column, filter on the DESCRIPTOR_IDOF of the base column. We discussed how to find the code values for a localized column earlier in this article:
Logical SQL:
SELECT
0 s_0,
“Financials – GL Profitability”.”Scenario”.”Scenario”||’~’||”Financials – GL Profitability”.”Scenario”.”Scenario Description” s_1,
“Financials – GL Profitability”.”GL Balance (AC)”.”Activity Amount” s_2
FROM “Financials – GL Profitability”
WHERE
(DESCRIPTOR_IDOF(“Financials – GL Profitability”.”Scenario”.”Scenario Description”) = ‘A’)
ORDER BY 2 ASC NULLS LAST
FETCH FIRST 500001 ROWS ONLY
Database SQL:
WITH
SAWITH0 AS (select sum(T596032.GBL_CRNC_PRD_NET_ACTIVITY) as c1,
T4287.CODE as c4
from
OAX$OAC.DW_ACCOUNTING_SCENARIO_D_TL T4287 /* Dim_DW_GL_ACCOUNTING_SCENARIO_D_TL */ ,
OAX$OAC.DW_GL_VARIANCES_ALL_CA_SEC T596032 /* Fact_Agg_DW_GL_VARIANCE_CA */
where ( T596032.CURRENCY_TYPE not in (‘ENTERED’) and T4287.CODE = T596032.ACCOUNTING_SCENARIO_CODE and T4287.CODE = ‘A’ and T4287.LANGUAGE = ‘US’ and T596032.ACCOUNTING_SCENARIO_CODE = ‘A’ and (T596032.CURRENCY_TYPE in (‘T’, ‘TOTAL_LEDGER_CURRENCY’)) )
group by T4287.CODE),
SAWITH1 AS (select /*+ no_merge */ T4281.VALUE as c1,
T4281.CODE as c2,
T4281.DESCRIPTION as c3
from
OAX$OAC.DW_ACCOUNTING_SCENARIO_D_TL T4281 /* Lookup_DW_GL_ACCOUNTING_SCENARIO_D_TL */
where ( T4281.LANGUAGE = ‘US’ ) ),
SAWITH2 AS (select D1.c1 as c1,
concat(concat(nvl(D2.c1 , D1.c4), ‘~’), nvl(D2.c3 , D1.c4)) as c2
from
SAWITH0 D1 left outer join SAWITH1 D2 On SYS_OP_MAP_NONNULL(D1.c4) = SYS_OP_MAP_NONNULL(D2.c2) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
(select 0 as c1,
D1.c2 as c2,
D1.c1 as c3,
ROW_NUMBER() OVER (PARTITION BY D1.c2 ORDER BY D1.c2 ASC) as c4
from
SAWITH2 D1
) D1
where ( D1.c4 = 1 )
order by c2 ) D1 where rownum <= 500001
There are still three query blocks and they each still do the same thing, but the code filter is in the first query block reducing the number of rows returned by the query.
Call to Action
If you use localization in your Oracle Analytics deployment and experience slow queries, check if you are using description columns in any calculations or filters. If so, you may see improved query performance by using DESCRIPTOR_IDOF to filter on code fields instead of description fields.
For more information on localization of attribute columns, see the documentation for Oracle Analytics Cloud and Oracle Analytics Server.
Now that you’ve read this article, try it yourself and let us know your results in the Oracle Analytics Community, where you can also ask questions and post ideas.