How to implement the below logic in obiee 11g?

AbinashBehera

Hi All,

please go through my requirement and suggest any way to implement the same.

I have the sales Table like below:

Month	Sales
FEB-2014	5000
MAR-2014	6000
APR-2014	500
MAY-2014	1000
JUN-2014	1500
JUL-2014	2000
SEP-2014	2500
OCT-2014	3000
JAN-2015	3500
FEB-2015	4000
MAR-2015	4500
APR-2015	500
MAY-2015	1000
JUN-2015	1500

As you can see, the sales is cumulative for one financial Year only. ie  For APR of each year, sales starts once again and keep adding  till MAR of the same financial year.

For my case, Prompt is a Month Range. Suppose user gives Prompt Between APR-2014 to JUN-2015(across financial year), Then the output should come as below:

(Sales on MAR-2015)+(Sales on JUN-2015)

For this case, Sales=(4500+1500)=7000

How this can be achieved in obiee 11g.

Joel

Hi @Abinash2707

If you have a Fiscal Year date hierarchy setup (which in your case runs from April to March) in your repository, then you can use this alongside the TODATE time series function to achieve this.

AbinashBehera

Hi Joel,

Thanks for your update.

I have time dimension hierarchy set up as shown below...

Where should I use the ToDate function, In RPD or in BI answer? And how to relate Prompt values to this Function?@Joe Fin

Joel

To use this in your report, you'll need to have your Fiscal Date Hierarchy exposed in the Presentation Layer so that you can reference the Fiscal Year level in the TODATE function. An example of it usage can be found here. Alternatively, you can create a YTD column in the BMM layer and directly reference the hierarchy in your screenshot.

AbinashBehera

Hi Joel,

I went through your link and also some oracle docs for Time Series Functions.

I found ToDate function is used to calculate the running sum till current date which does not apply for my case.

For me, the data(sales amount) is already in cumulative nature.So if I apply ToDate function, it will again add up all the sales within the range. I want to fetch only MAR-2015 data and JUN-2015 data.

Particularly I am facing the issue when user gives the prompt across financial Year. ie APR-2014 to JUN-2015.

But if the Prompt range is within one financial year then there is no issue showing sales only on END month. For Example, if Prompt Between: APR-2014 and SEP-2014,Then Sales will come for only SEP-2014. (As per above Table its 2500).

Regards,

Abinash

cesar.advincula.o

Hello,

I had this requirement the solution it's:

1.crete a new column in your dimension time table in your database called "total"(whatever name that you want) and put in for default value "total time" ( alter table dim_time add total varchar2(15) default "total time"),

2. Map this new column in your physical layer. In.your rpd

3. Map this in your business.model in your rpd.

4. Map in.your presentation layer.

5. Create your dashboard.prompts fort your date ranges and use your time series function function to_date.

Kind regards,

AbinashBehera

Hi Cesar,

Thanks for your reply.

I created the "Total" column in DIM_TIME table. but m confused how can a dummy column will help me to solve this. With which column I have to map the "Total" column in RPD.

And Also while using Time series function, Heirarchy details are not coming in the Edit Formula box, so i am unable to use ToDate function.

please suggest.

Regards,

Abinash

cesar.advincula.o

Confused about the dummy.column:

It's a artifice to tell to your.model that you want to_date in.acumulative way through years, remember obiee

has this levels in.A hierarchy.this column help to get the acumulative through years

To_date in edit formula:

for to_date metrics(or ago / rolling time series functions) you could create in.two ways this metric,

Oracle bi answers via or your rpd via, I believed that you got the hierarchy of the dimension time.In your presentation layer if you don't have it or.you have any restriction with it, you could.Create that in your rpd.

Please check.how I could accumulated the metric between 2015 and 2016, December 2015, January 2016

Kind regards,

AbinashBehera

Hi Cesar,

I have created TIME hierarchy in RPD, alos having it in Presentation layer. But in BI answer I am unable to use Todate function. Time hierarchy is not coming in Edit Formula Box so unable add "time_level" for ToDate Function.

as shown below...

  

Please suggest...

regards,

Abinash

AbinashBehera

Hi Cesar,

I tried with creating new logical column(Total Sales) in RPD with Todate Function with time level as the "Total" column created in DIM_TIME.

What I found is... Even if I give Month ranges in prompt, Lets say for Two year :APR-2014 to DEC-2015, It will show me the data of previous financial years also, for 2012-13 and 2013-14,(if data Exist)

But for my case if User gives Prompt for Month Range: APR-2014 to DEC-2015, The sales should be Sum (sales only for 2014-15 FY i.e data on MAR-2015 + DEC-2015 )

please suggest.

Regards,

Abinash

cesar.advincula.o

1.Please go inside your rpd,check your levels in dimension hierarchy in.your business model and mapping layer, the.level.of the time.It's.the same.name.of the level that you use in your bmm layer.

.In your screenshot I could see the "AD_DIM_TIME Total", this is the dimension level that you have.to.put in your to_date function

2. ensure in.your business model layer that your.dimension time is.market as.time dimension(check the flag of your dimension) and set the chronological keys by each level of your time.dimension.

3. In.your dimension hierarchy.In.your business model you have to add the logical column total to your logical level of your hierarchy dimension time level "AD_DIM_TIME"

Kind regards,