How to implement time series function without time hierarchy

3123015

Hi All,

Please let me how to implement time series functions(Ago,TO_DATE) without time hierarchy with case when or with other methods ?

Thanks,

Aritra Sen

Gianni Ceresa

Hi,

Without a time hierarchy in your business model forget the OBIEE timeseries functions, they can't work.

You can simulate the same result by modelling things differently to generate a YTD or things like that.

So the question is more : why don't you want to tag a dimension as time? It makes no sense to talk about timeseries functions if you don't have a time dimension in your model ...

Joel

As Gianni said, it will be much easier and much more beneficial to your model to have a time dimension.

Trying to simulate a time series without a time dimension and then a time hierarchy is possible but is prone to have all sorts of issues down the line.

3123015

I agree with you have said...

Just wanted to know is there any other way to perform this with case when statement ....

Gianni Ceresa

Well ...

Not sure how do you want to use a CASE STATEMENT for that as it's row by row ...

So it's more something you can achieve by filtering your analysis ...

Like if you add filter on YEAR = 2016 and MONTH IN (Jan, Feb, Mar, Apr) and you don't add the "Month" column in the analysis you end up with April YTD figures, but it's not at all close to timeseries functions as you generally use them to compare current with past or YTD figures.

But as said you can do it modelling things in the BMM, if you join your fact with the calendar table using a fact.date between Jan-1 and calendar.date you are producing a YTD figure, but in that case it's easier to configure the time dimension ...

[Deleted User]

With a ..... CASE statement? How should that work?

Time series functions are there for a reason. Time hierarchies are there for a reason.

Only alternative (but which does not cover everything) is to model things over LTSs with AGO joins.

cesar.advincula.o

Hello,

There is a possibility however you have to get an OLAP OPTION (Its part of Oracle DB from 9i to 12c - extra cost option for Oracle DB) and you can do the calculation in the OLAP ENGINE (not in the Oracle BI), check these posts are old but could help you. You could do some measure like a YTD, QTD, etc (TO_DATE), and Comparision (AGO) from OBIEE.

How to create OLAP Cubes? Check this link:  Building OLAP 11g Cubes

How to create the Metadata in OBIEE? Check this link Creating OBIEE Metadata for OLAP 11g Cubes

Hope this helps you

Kind Regards,

[Deleted User]

Not sure that a multidimensional cube is a clever way of "preventing the need for a time hierarchy".

It's a bit like saying: "Hey don't use that one check-box and those approximately 5 chronological keys and....learn a new technology, pay more license fees, populate a cube rather than using what's in your database etc etc etc." Not a bit. It IS saying that. About ten thousand times more effort than having a time hierarchy.

Edit: Friday night not enough creativity left but I found a good one.

Problem: "Hey I want to cook indian-style food without using curry."

Answer: "Here's how you build a new kitchen."

cesar.advincula.o

I dont say what you try to pretend.

The question was: "Please let me how to implement time series functions(AGO,TO_DATE) without time hierarchy with case when or with other methods ?",

The Answers was: Yes, OLAP Option, however there is a another points like a cost , business, new technology, some who do a maintaning, etc, for that reason I mentioned Cost and give an articles about the technology. Could you do AGO/TO_DATE in OBIEE without a Dimension Time? Is a quite difficult right?

Gianni Ceresa

Hi,

I had to read the previous posts twice to find out if I was dreaming or I saw it correctly ....

cesar.advincula.o wrote:Could you do AGO/TO_DATE in OBIEE without a Dimension Time? Is a quite difficult right?

No, not really difficult, actually lot easier than building an OLAP cube.

Repeating myself: you simply model it in OBIEE, using an alias or 2 and adapting physical joins for these aliases to produce TO DATE figures or AGO figures. It's actually really easy and take 10 minutes in the RPD for each single different view. The cons is that if you want a "1 month ago" and a "1 year ago" figure you need to build it separately (not a unique setup for all the timeseries calculations but a different alias with it's own joins for each different timeserie needs).

It's the best fit answer staying only in OBIEE, it takes 10 minutes, it's often better in performance than the embedded time series functions because you clearly control how things are joined and calculated, doesn't require any new technology or knowledge than OBIEE and only OBIEE.

Now if you really want to bring in an external calculation at least keep it simple: precalculate it in the DB and add the new columns in the RPD. Same DB technology as used already, simple SQL to generate by ETL the timeseries calculated values.

I would say the question try to explore options by curiosity and because it's good to challenge things from time to time, but still applying some common sense and not going into something overkilling for the need.

As conclusion: timeseries in OBIEE without a time dimension possible? Yes, use some adapted physical joins. That's simple standard SQL logic.

PS: it's weekend, so let's enjoy the free time and wait for Aritra Sen to come back and tell us if found enough options or there are maybe more info to take into consideration for that open and generic question.

[Deleted User]

Writing 500 views also can do that. Or LTS modelling with AGO joins. Or Pre-calculating everything so you don't even need to join anymore.

Or...which is part of most licenses of OBIEE now...Hey, Essbase! Every OBIEE+ license, every OBIF license contains it. Multidimensional cubes. Yay!

And while we're at it. You can also - with the same justification as you mentioned above - say "You can also do it using Cloudera". Why yes you can but what's the point?