SUMMARY

• I have added a fact to GL Balance Sheet subject area.
• This fact is joined to the dimensions in the subject area, and simple metrics work as expected.
• I have a measure similar to Balance Amount.
• When it aggreates, it's grouping by Fiscal Period Set and Fiscal Day, which is inefficient because it's calculating for every day instead of the period (365 instead of 12 periods).
• The Balance Amount metric aggregates by Fiscal Period Set and Fiscal Period Sort Key.
• How do I create a formula that does the same thing as the delivered Balance Amount?

— More Details —

I have been trying to recreate GL Balance Sheet for constant dollar. 

There are 4 base metrics in GL Balance Sheet: Balance Amount, Activity Amount, Credit Amount, and Debit Amount. The last 3 are simple but Balance Amount has an aggregation I'm trying to recreate.

 I have a view of DW_GL_BALANCE_CA that I'm using for my measures.

I join the view with Dim - Date Fiscal Calendar as follows:

"OAX_USER"."#prefix#DW_GL_BALANCE_CA_CD_V"."FISCAL_PERIOD_SET_NAME" = "Oracle_Data_Warehouse".."OAX$OAC"."Dim_DW_FISCAL_DAY_D"."FISCAL_PERIOD_SET_NAME" and

"OAX_USER"."#prefix#DW_GL_BALANCE_CA_CD_V"."FISCAL_PERIOD_NAME" = "Oracle_Data_Warehouse".."OAX$OAC"."Dim_DW_FISCAL_DAY_D"."FISCAL_PERIOD_NAME" 

 For my base measure, I move the filtering of the fact to the column:

CASE WHEN DW_GL_BALANCE_CA.CURRENCY_TYPE in ('T', 'TOTAL_LEDGER_CURRENCY', 'TOTAL_TRANSLATED') THEN TRXN_CRNC_PRD_END_BALANCE ELSE NULL END AS  BALANCE_TEST 

In my branch, I bring this in several times, with different aggregations set.

This is the SQL generated by Balance Amount when viewed on a table with no attributes.

 WITH 

SAWITH0 AS (select sum(T1330.TRXN_CRNC_PRD_END_BALANCE) as c3,

     T8216.FISCAL_PERIOD_SET_NAME as c4,

     T8216.FISCAL_PERIOD_SORT_KEY as c5

from 

     OAX$OAC.DW_FISCAL_PERIOD_D T8216 /* Dim_DW_FISCAL_PERIOD_D */ ,

     OAX$OAC.DW_GL_BALANCE_CA T1330 /* Fact_Agg_DW_GL_BALANCE_CA */ 

where  ( T1330.FISCAL_PERIOD_NAME = T8216.FISCAL_PERIOD_NAME and T1330.FISCAL_PERIOD_SET_NAME = T8216.FISCAL_PERIOD_SET_NAME and (T1330.CURRENCY_TYPE in ('T', 'TOTAL_LEDGER_CURRENCY', 'TOTAL_TRANSLATED')) and T8216.FISCAL_TRAILING_PERIOD_NUMBER between 17 and 25 ) 
group by T8216.FISCAL_PERIOD_SORT_KEY, T8216.FISCAL_PERIOD_SET_NAME),

SAWITH1 AS (select LAST_VALUE(D1.c3 IGNORE NULLS) OVER ( ORDER BY D1.c4 NULLS FIRST, D1.c5 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c2

from 

     SAWITH0 D1),

SAWITH2 AS (select max(D1.c2) as c1

from 

     SAWITH1 D1)

select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,

     D1.c1 as c2

from 

     SAWITH2 D1 ) D1 where rownum <= 500001

I can get similar results by summarizing TRXN_CRNC_PRD_END_BALANCE using Time Based option

Other: Sum; Fiscal Calendar: Last

This generates the following SQL, 

 WITH 

SAWITH0 AS (select sum(T422841.BALANCE_TEST) as c3,

     T2811.FISCAL_PERIOD_SET_NAME as c4,

     T2811.FISCAL_DAY_DATE as c5,

     T2811.ADJUSTMENT_PERIOD_FLAG as c6

from 

     OAX$OAC.DW_FISCAL_DAY_D T2811 /* Dim_DW_FISCAL_DAY_D */ ,

     OAX_USER.DW_GL_BALANCE_CA_CD_V T422841 /* Fact_DW_GL_BALANCE_CA_CD_V_18128576439148733 */ 

where  ( T2811.FISCAL_PERIOD_NAME = T422841.FISCAL_PERIOD_NAME and T2811.FISCAL_PERIOD_SET_NAME = T422841.FISCAL_PERIOD_SET_NAME) 
group by T2811.ADJUSTMENT_PERIOD_FLAG, T2811.FISCAL_DAY_DATE, T2811.FISCAL_PERIOD_SET_NAME),

SAWITH1 AS (select LAST_VALUE(D1.c3 IGNORE NULLS) OVER ( ORDER BY D1.c4 NULLS FIRST, D1.c5 NULLS FIRST, D1.c6 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c2

from 

     SAWITH0 D1),

SAWITH2 AS (select max(D1.c2) as c1

from 

     SAWITH1 D1)

select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,

     D1.c1 as c2

from 

     SAWITH2 D1 ) D1 where rownum <= 500001

 My aggregation is slower than the delivered one. In the group by clause it generates 16,190 rows while the delivered one generates 555 rows. I was looking for a way to aggregate by Fiscal Period (12 rows per year) instead of Fiscal Calendar (365 rows per year). I tried adding a join to FISCAL_PERIOD_D. I also tried adding a level aggregation for Fiscal Period (this one looks much slower).