EPM 10.1 QTD and YTD issue

[Former Member]

Hi all,

I am receiving an issue when we are running any reports on any other measure apart from Periodic, lets say QTD or YTD, we are getting an error.

One or more errors occurred.

The execution of report Default Report failed. Please contact your administrator.

Bad request.

Server message:

code: UJO_READ_EXCEPTION_018

severity: error

description: MDX statement error: Value 2016.JAN 0HIER_NODE for characteristic /CPMB/WPDKBDQ unknown

log id: 051Mb53H7jMzxR5rdt{t7m

DATAVALUE:Value 2016.JAN 0HIER_NODE for characteristic /CPMB/WPDKBDQ unknown

MDX:  SELECT NON EMPTY  {[/CPMB/WPDGKQH].[1201] } ON 0  from  [$/CPMB/WPMRH9H]  WHERE ( [Measures].[/CPMB/SDATA] ,[/CPMB/WPDI6M9                 PARENTH1].[NETINCOME                       /CPMB/WPDI6M9] ,[/CPMB/WPDCR84                 PARENTH1].[ALL_CC                          /CPMB/WPDCR84] ,[/CPMB/WPD0JJC                 PARENTH1].[TOTALDATASRC                    /CPMB/WPD0JJC] ,[/CPMB/WPDE09U                 PARENTH1].[ALL_FA                          /CPMB/WPDE09U] ,[/CPMB/WPDJ0WM                 PARENTH1].[ALL_GAME                        /CPMB/WPDJ0WM] ,[/CPMB/WPDPHX5                 PARENTH1].[LOCAL                           /CPMB/WPDPHX5] ,[/CPMB/WPD0T6Z                 PARENTH2].[Plan                            /CPMB/WPD0T6Z] ,[/CPMB/WPDOSEH                 PARENTH1].[ALL_PROD                        /CPMB/WPDOSEH] ,[/CPMB/WPDW8B0                 PARENTH1].[ALL_PROJ                        /CPMB/WPDW8B0] ,[/CPMB/WPDBGH3                 PARENTH1].[REV2] ,[/CPMB/WPDKBDQ                 QTD_PARENTH1].[2016.JAN                        0HIER_NODE] )

V1:Value 2016.JAN 0HIER_NODE for characteristic /CPMB

V2:/WPDKBDQ unknown

V3:

V4:

-----------------

Framework message:

The remote server returned an error: (400) Bad Request.

The remote server returned an error: (400) Bad Request.

The TIME dimension in the Backend has 3 level structure.

Any changes or settings that we are missing on? Your help will be greatly appreciated.

Thanks,

Abhishek

[former_member186338 · Accepted solution]

I can see a lot of members in your time dimension without 3 level structure! That's the reason for this issue!

[Former Member · Accepted solution]

Hi Vadim,

If you are referring to 2011.H1, 2011.H1H2, etc., those all belong to PARENTH2 and the screen shot is for PARENTH1.

Please see screenshot for PARENTH2.

Thanks,

Abhishek

[former_member186338 · Accepted solution]

2 hierarchies in time dimension is not a recommended approach and can result in a lot of issues!

[Former Member · Accepted solution]

But we were able to run and build all reports in DEV with these two hierarchies without any issues. If I run the same report in BVD, it runs fine, with the same master data for TIME.

All these below screenshots are from DEV system.

Thanks,

Abhishek

[former_member186338 · Accepted solution]

Sorry, but with 2 hierarchies in TIME dimension you will never know when the issue will happen At what SP level of BPC and BW... With reports or scripts or consolidation...

[former_member186338 · Accepted solution]

To my mind it's a bad idea!

[Former Member · Accepted solution]

We are currently on BPC Standard version, BW 740 SP0013 and CPMBPC 810 0008 and BPC for Excel SP is SP25, the latest one.

I understand, but its tough for me to understand why it would work in one system and not in another.

Also, business has a strong requirement of running some of their reports at Half Yearly level.

I don't want to mess around with PARENTH1, thats why I created PARENTH2. Do you think, if we add H1/H2 in between TOTAL and Qx, it will cause problems?

Thanks,

Abhishek

[Former Member · Accepted solution]

Also, do you think because of these two hierarchies in TIME dimension, we are getting this problem with MEMBEROFFSET at YEAR level? where instead of jumping to next year's JAN, its showing wrong information?

Thanks,

Abhishek

[former_member186338 · Accepted solution]

I have already told you that 2 hierarchies in time dimension is a bad idea! You can use 4 level hierarchy with half year level - this is ok! But not 2 hierarchies!

[Former Member · Accepted solution]

Thanks for the input Vadim! I will definitely look into this option.

If I add 4 level hierarchy, like below, I have two more questions:

1. What would be the LEVEL property of 2015.H1/H2?

2. Will I have to make any changes in the backend BW to handle this, e.g. UJA_FORMULA_APP tables, etc.?

2015.TOTAL

     2015.H1

          2015.Q1

          2015.Q2

     2015.H2

          2015.Q3

          2015.Q4

Thanks,

Abhishek

[Former Member · Accepted solution]

I checked the below thread, which had a similar question, but I don't think we got an answer from that.

https://scn.sap.com/thread/1905458

Hope this helps on the discussion.

Thanks,

Abhishek

[former_member186338 · Accepted solution]

It's an old post! In the current SP for BPC special hierarchies are created (invisible) to support YTD and QTD calculations... Please search notes!

[Former Member · Accepted solution]

Yes, I am reading notes, starting from Central note of my current SP. let me see if I find answers there.

If you have any notes or know any specific note numbers, please guide me towards them if possible.

Thanks,

Abhishek

[former_member186338 · Accepted solution]

http://service.sap.com/sap/support/notes/1932054

http://service.sap.com/sap/support/notes/1457423

May be some other...

There is no clear explanation how to correctly implement half year level...

Vadim

P.S. Some other notes:

http://service.sap.com/sap/support/notes/1741248

http://service.sap.com/sap/support/notes/2262290

http://service.sap.com/sap/support/notes/1691567

[Former Member · Accepted solution]

Sorry I was trying to post it yesterday, but there was some problem with SCN, it wouldn't let me post.

Thanks for the notes Vadim! The note 2262290 really helped and it aligned with exactly what you were saying with the 'x' level structure of the TIME hierarchy.

I fixed the incosistency of the Level in the Time Dimension and now all the TIME IDs are at the same HLEVELs.

But that still didn't fix the EPMMEMBEROFFSET issue. It still gives wrong information, when the Level parameter is specified.

Thanks,

Abhishek

[former_member186338 · Accepted solution]

"I fixed the incosistency of the Level in the Time Dimension and now all the TIME IDs are at the same HLEVELs." - Sorry, but where is you dimension structure???

[Former Member · Accepted solution]

What I meant was, there were some LEVEL=YEAR nodes in the Hierarchy which were at HLEVEL=1 and some of them were at HLEVEL=2. I fixed it and made all the nodes with LEVEL=YEAR property at same HLEVEL=1.

Earlier, all the years prior to 2011, rolled up under HISTORICAL_YEARS. But now, I removed those from HISTORICAL_YEARS and all yearly totals, Quarterly totals and Months are at same level, HLEVEL=1, 2 and 3 respectively.

Thanks,

Abhishek

[former_member186338 · Accepted solution]

Still you have the issue with second hierarchy!

[Former Member · Accepted solution]

We are still testing that in our Sandbox system, where we add HalfYears as a part of PARENTH1. The above changes were done in QA as a part of our Integration Testing.

I made the exact changes in SBX and also removed PARENTH2 from TIME dimension in SBX system. But still the EPMMEMBEROFFSET is giving the same problem.

Thanks,

Abhishek

[former_member186338 · Accepted solution]

I can still see the member OLD_HISTORICAL_YEARS!!!

Can you try to perform tests on the new TIME hierarchy with 4 levels? What level ID you use for half year?

[Former Member · Accepted solution]

Hi Vadim,

The OLD_HISTORICAL_YEARS is only a standalone ID to provide a visual seperation from the years that are getting used. It just has ID and description and no other details (e.g. LEVEL property is blank).

I have TIME hierarchy with 4 Levels, except for this above ID. I will try to remove this try again.

Thanks,

Abhishek

[former_member186338 · Accepted solution]

Sorry, i have told you already that all members have to be with levels and in line with 4 level structure in your case. No standalone id's.

P.S. And please answer my previous question!

[Former Member · Accepted solution]

Hi Vadim,

Sorry the TIME dimension is corrupted in my Sandbox system, I am trying to restore it and redo all the changes.

To answer your question, the LEVEL property was blank for the half year.

Also, before the TIME dimension was unusable, I tried without that the standalone ID and all members were at the same level and PARENTH2 was deleted.

YEAR was at level 1, Half year at level 2, Quarter at level 3 and Month at level 4 and all of them had the same structure, still the member offset was not working. I will make the change again once I recover the dimension and try again.

Thanks,

Abhishek

[former_member186338 · Accepted solution]

What level property do you have for year, half year, quarter and month?

[Former Member · Accepted solution]

2015.TOTAL - LEVEL = YEAR

2015.H1 - LEVEL = <blank>

2015.Q1 - LEVEL = QUARTER

2015.JAN - LEVEL = MONTH

Thanks,

Abhishek

[former_member225135 · Accepted solution]

Hi Abhsihek,

I am using the same TIME dimension structure as you are using.

In my case, I have setup Levels like this:

2015.TOTAL - LEVEL =TOTAL

2015.H1 - LEVEL = YEAR

2015.Q1 - LEVEL = QUARTER

2015.JAN - LEVEL = MONTH

This setup works fine for me.

Regards,

Rahul

[former_member186338 · Accepted solution]

Empty level is a bad idea...

[jim_link-235]

Looks like SAP has issued the following note in support of setting up the Half-Year members of the Time Dimension:

http://service.sap.com/sap/support/notes/1744366

But perhaps you've seen that by now or resolved your issue.

[former_member186338]

Interesting info!

WEEK level for half year...

Thanks, Jim!

[Former Member]

Hi Vadim,

Thanks for all the information. Below is the summary of things I did which might help others incase they are also having similar issue, which resolved both of my issues (QTD/YTD Reporting and EPMMemberOffset formula not offsetting correctly):

1. All members are with levels and in line with 4 level structure. No standalone id's.

2. Removed the PARENTH2 entirely from the system for TIME Dimension

3. Removed Half Yearly concept entirely from our TIME hierarchy (but will try the "WEEK" level for Half Yearly later on)

Now we don't have any problems running the QTD/YTD Reports and the EPMMemberOffset is also working perfectly, where the YEAR level parameter is offsetting correctly.

Thanks a lot for your help and inputs from eveyone!

Thanks

Abhishek Khatti