How can we add multiple year hierarchies in OBIEE pivot table?

3545306

I have a report requirement as below :

Report should have respective Year Hierarchy on the respective measure column as below.

Image 1:                                         

But when I try to add the Years(2016,2017,2018) as hierarchies, it is showing up as below. All measure columns are getting added under every hierarchy as below.

Report should have 2016 hierarchy only on 2016 Budget measure, 2017 with 2017 Budget and 2018 with 2018 Budget.

Image 2:

Can anyone please help me, how can we add 2016 year hierarchy on 2016 Budget measure and 2017 year hierarchy on 2017 budget and 2018 on 2018 Budget

as shown in the Image 1 above.

Thanks,

Vijaya

Martin van Donselaar

That's because you have different measures for each year instead of one budget measure. Your Design is incorrect. At least that is what I understand from your description.

Year Budgets should be only distinguished by the period hierarchy, they should not be separate columns.

So, what you should do is merge the year budget measures into just one budget measure. This should be done in your ETL.

Gianni Ceresa

Definitely agree with Martin, your model is "weird".

You do not need 3 years hierarchies, with a properly built time dimension you just add the time dimension once and add a selection step to automatically take the 3 years instead of the total.

And your budget definitely need to be a single measure, not sure how you end up with a measure per year, I hope you duplicated the column in the analysis yourself and they aren't really there in the RPD (or do you update the RPD every year to add new measures for the new year?).

Keep it simple: LOB, time dimension (open on the years), budget = 3 columns in your analysis

[Deleted User]

+2 to Martin and Gianni. Why 3 measures?!

3545306

the thing is, if we do in that way,  we have to select previous, next year's in the dashboard prompt, the way we designed it is it shows previous, next year values automatically if we select the current year. but we are unable to enable respective year hierarchy on that as I said above.

Thanks,

Vijaya

3545306

no we actually need them separately so that we can display all the measures for previous and next year automatically though the default selection is current year.

if we make it one measure then we have to select multiple years in dashboard prompt.

Thanks,

Vijaya

Gianni Ceresa

I still don't see why you end up with such a complicated analysis ...

Your time hierarchy will be filtered by the selected year and with a super simple logic you can add previous one and next one too.

So for me it's still: LOB, time hierarchy, budget : 3 columns and that's it (with selection steps to start with the year level expanded and no upper level, and a filter to select current, previous and next year based on your prompt).

[Deleted User]

No you don't. You just have to use the tool differently. A filter can be made to say "between curr_year-1 and curr_year+1". Take a step back, look at what you are trying to achieve and think of how you could achieve it rather than getting hung up on a detail of a wrong solution path you have chosen. Change the way you look at the issue.

3545306

client requirement is like that, so we had to design the analysis that way.

Thanks,

Vijaya

3545306

I agree on what you all are saying, but client requirement is in that way.

Thanks,

Vijaya

WVanSluys1

So, my first question is what is your data source?

if it is Essbase you can easily do this with a Scenario Dimension and a Time Dimension.

Think about it.

-w