nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Time Series Performance Issue

Naeem Akhtar

Dear All,

May be this question is asked several times as I can see on older posts like below but still I could not find the answer.

https://community.oracle.com/thread/971923?db=5

https://community.oracle.com/thread/3617846

I have requirements to calculate MTD, YTD, LY YTD, LY MTD etc in one OBIA report and getting huge performance issues. The reports is taking aroudn 3 minutes with builtin time series functions like Ago, Todate.

Below is report. (There are two fact tables involved.)

I was able to tune Ago function following below note which has increased the performance a bit for Ago but still reports is taking more then 2 minutes.

Configuring the Oracle BI Repository - 11g Release 1 (11.1.1)

I can see in physical query that YTD calculation (with TODATE function) is generating a query which is scanning full table (having million of rows) and since no time dimension in where clause.

Can you please share expert opinion on the same to have alternative of TODATE function?

Regards

Naeem K.

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

If you want to increase performance reduce flexibility ... your best performing approach to this is to built a physical database object that has the data pre-aggregated and pre-calculated.

^ especially if you have large row-sets;

Naeem Akhtar

Hi Thomas,

Thanks for your response.

I understand that approach will solve the problem but interested to know how we can model such type of scenarios. May be the old way can help where we used to create stuff with Time Series wizards but not sure how I can achieve the same.

Regards

Naeem K.

Thomas Dodds

Having implemented OBIA over and over and been given these kinds of requirements ... my default way of getting performance is via the physical structure - yes, it's inflexible (a change in requirements is a restart); but it performs. Ask your client what is more important performance or flexibility; outline the trade off. If you know 3 mins is unacceptable - I'm thinking they want performance over flexibility. But safer to have this discussion -- they have to support it after you are gone too! Also have them consider the future - how fast is the row-count going to grow? Might work now to be flexible and do it logically -- 18 months from now it doesn't.

Usually a restart isn't all that bad (cost in terms of labor) as you are typically using finished fact/dimension tables -- just changing the presentation 'layer' of the EDW.

Naeem Akhtar

Hi Thomas,

I am convinced to implement this through database. However I have spent number of hours to populate a newly created YTD column but couldn't get success with logic itself. Can you please share your thoughts to populate the same through SQL so that later I translate that in ODI. The table name in picture is W_ACCT_BUDGET_F (OBIA)

Regards

Naeem K.

Thomas Dodds

YTD is

SELECT SUM(measure)

FROM table

WHERE date between trunc(sysdate,'YEAR') and trunc(sysdate)-1

SELECT trunc('06-JUN-2017','YEAR') FROM DUAL; <-- returns '01-JAN-2017'

MTD would be:

SELECT SUM(measure)

FROM table

WHERE date between trunc(sysdate,'MM') and trunc(sysdate)-1

SELECT trunc('06-JUN-2017','MM') FROM DUAL; <-- returns '01-JUN-2017'

as a bonus, LY YTD is

SELECT SUM(measure)

FROM table

WHERE date between add_months(trunc(sysdate,'YEAR'),-12) and add_months(trunc(sysdate)-1,-12)

[Deleted User]

Thomas Dodds wrote:Having implemented OBIA over and over and been given these kinds of requirements ... my default way of getting performance is via the physical structure - yes, it's inflexible (a change in requirements is a restart); but it performs. Ask your client what is more important performance or flexibility;

Generally I agree with the "do it physically" but especially in cases like the one mentioned above I'd definitely choose Essbase over a relational storage because of calculation performance and the added flexibility of measure hierarchies - a new calc could simply become a new member in the measure rather than a named column.

Thomas Dodds

Absolutely, the cube provides the best case -- if available. Most of the clients I have been at they didn't have ESSBASE as part of the mix.

Currently working on a Microsoft BI stack project (shhhh!) for a new EDW and have made great use of cubes for these types of cases!

[Deleted User]

Hey as long as it's multidimensional the storage technology is irrelevant

Just having the possibilities is what counts for most financial reporting.

Naeem Akhtar

Dear,

Thanks for your help.

I was able to update YTD field using below statement (lowest grain)

SUM (

SUM (BUDGET_LOC_AMT))

OVER (

PARTITION BY SUBSTR (W_ACCT_BUDGET_F.PERIOD_END_DT_WID, 4, 4),

W_ACCT_BUDGET_F.BALANCING_SEGMENT_WID,

W_ACCT_BUDGET_F.LEDGER_WID,

W_ACCT_BUDGET_F.GL_SEGMENT3_WID,

W_ACCT_BUDGET_F.COST_CENTER_WID,

W_ACCT_BUDGET_F.BUDGET_LEDGER_WID,

W_ACCT_BUDGET_F.BUDGET_WID,

W_ACCT_BUDGET_F.GL_SEGMENT6_WID,

SQ_W_GL_ACCOUNT_D.GROUP_ACCT_WID

ORDER BY PERIOD_END_DT_WID)

Now report results in less then 20 seconds.

Regards

Naeem Akhtar