No AGO in fragmentation - alternatives with Period

3129453

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

Hi,

First I would like to say AGO working with fragmentation (there some limitations but it would mean you have a really special fragmentation rule).

You can just model it in the RPD to have some extra LTS returning you the previous month data (changing the join between the fact and the calendar table).

3129453

Hello Gianni,

thanks for the suggestion on remapping with an alternative calendar join, this might be too big an ask with the scale of the repository, but at least I have options to present.

On your comment that AGO does work with fragmentation can you point me at a reference source which details the exact requirement for this to be the case(?), as for me it does not.

My two sources are fragmented without a gap, both come from Oracle databases of the same and latest version, but they are on different databases. All fields without exception have been fragmented for both fact and dimension logical tables.

Many thanks,

Robert.

Gianni Ceresa

But fragmented by what?

What is the fragmentation rule?

3129453

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.

3129453

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.

Gianni Ceresa

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.