No AGO in fragmentation - alternatives with Period

Question

Hi,

the client had a working analysis with an AGO function that presented multiple Periods data against the previous period thus (simplified)

Period   Value    Value Last Month

01        10          0

02        15         10

03        5           15

Their requirement was to have fragmentation of their data, but this has broken all analyses that use AGO.

I can see any eas workaround for a simple period where you can use logic along the lines of; -

Period in (@{SelectedPeriod}, @{SelectedPeriod} -1) (simplified) - for the filter

And use a filter around the Value to emulate Current And Previous period using similar logic, BUT, I cannot see a workaround to reinstate it with a multiple period presentation in a moving timeframe, and some of the analyses have a @{Period}{%} dashboard based filter.

Has anyone found a flexible alternative for AGO?

Note, I do not think the pviot table based analyses will work for me, some of the analyses are extremely complex?

Would this be possible using evaluate to run a nested piece of SQL as the equivalent of a function?

The version is 11.1.1.9

thanks for any input,

Robert.

Gianni Ceresa · Answer

Hi Robert,

I couldn't try your scenario (lack of time), but I have projects where I used a partitioned FACT table and just that, nothing else is partitioned. In other projects I have some dimensions partitioned and only that and finally other projects with a some facts partitioned and some not, some dims partitioned and some not.

It always worked all the time, the only issue I had was that with a strange partitioning on the time dimension when using AGO it was calculating wrongly the number of periods to move back in the past (because forgot to apply some of the filters of my analysis which was actually a way to select the partition I wanted to hit and so end up with OBIEE thinking my time dimension had 2 times more periods than what it really had).

But in general never had to do what you says and the error you got smells more like some joins are missing somewhere than related to partitioning itself.

3129453 · Answer

The error that I receive is 'Ago Function may not be used when the time dimension source is horizontally partioned'

Functionally I have no need for a partioned time dimension, but how can I make it work with a single time dimension but everything else partioned, from my experiments with fragmentation even though all I am trying to achieve is fragmented FACT tables I have found that you get an error alluding to no dimension existing at the same level of detail existing if you do not partition the dimensions in the same way, which I achieved via Aliases pointing at the same dimensions as I had no requirement here, just reflecting the same fragmentation condition as the FACT tables, this works, but gives me the AGO error (cf above) - is there any way around this???

thanks for any input,

Robert.

3129453 · Answer

The fragmentation is based on the calendar dimension year, using a repository variable as the comparator; thus; -

Year >= VALUE_OF(CURRENT_YEAR)

Year < VALUE_OF(CURRENT_YEAR)

This same condition is referenced on the fragmentation content of ALL FACTs and DIMs

All have combined ticked.