Parent total in periodic

[Former Member]

Hi!

We have a problem in 3 out of our 4 of our accont hierarchies.

We have problem getting our periodic figues to be calulated right on the highest level in the hierarchies.

Calculation of our base account works fine the problem does only occur in parent levels

YTD values works fine in all levels.

We are using BPC 7 MS SP 3 on SQL 2008

Any one with any good ideas?

[Former Member · Accepted solution]

Hi Fredrik

Here is what we updated

Changed dbo.MeasureFormula table for the following sequence numbers only:

SEQ 200

IIF([%ACCOUNTDIM%].PROPERTIES("ACCTYPE")="INC" OR [%ACCOUNTDIM%].PROPERTIES("ACCTYPE")="LEQ", -1 ,1)*([MEASURES].[SIGNEDDATA],CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%]))

SEQ 260

IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC" OR [%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="AST",-1,1)

SEQ 270

IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC" OR [%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="LEQ",-1,1)

SEQ 210

IIF(([%ACCOUNTDIM%].PROPERTIES("ACCTYPE")= "INC" OR [%ACCOUNTDIM%].PROPERTIES("ACCTYPE")= "EXP") AND NOT ([%TIMEDIM%].PROPERTIES("ISBEGINNING")="1"),[MEASURES].[YTD]-([MEASURES].[YTD],[%TIMEDIM%].LAG(1)),[MEASURES].[YTD])

SEQ 220

IIF([%ACCOUNTDIM%].PROPERTIES("ACCTYPE")= "INC" OR [%ACCOUNTDIM%].PROPERTIES("ACCTYPE")= "EXP",IIF([%TIMEDIM%].CURRENTMEMBER.LEVEL IS [%TIMEDIM%].WEEK,[MEASURES].[PERIODIC],SUM(PERIODSTODATE([%TIMEDIM%].WEEK,[%TIMEDIM%].CURRENTMEMBER),[MEASURES].[PERIODIC])),[MEASURES].[YTD])

SEQ 230

IIF([%ACCOUNTDIM%].PROPERTIES("ACCTYPE")= "INC" OR [%ACCOUNTDIM%].PROPERTIES("ACCTYPE")= "EXP",IIF([%TIMEDIM%].CURRENTMEMBER.LEVEL IS [%TIMEDIM%].MONTH,[MEASURES].[PERIODIC],SUM(PERIODSTODATE([%TIMEDIM%].MONTH,[%TIMEDIM%].CURRENTMEMBER),[MEASURES].[PERIODIC])),[MEASURES].[YTD])

SEQ 240

IIF([%ACCOUNTDIM%].PROPERTIES("ACCTYPE")="INC" OR [%ACCOUNTDIM%].PROPERTIES("ACCTYPE")="EXP",IIF([%TIMEDIM%].CURRENTMEMBER.LEVEL IS [%TIMEDIM%].QUARTER,[MEASURES].[PERIODIC],SUM(PERIODSTODATE([%TIMEDIM%].QUARTER,[%TIMEDIM%].CURRENTMEMBER),[MEASURES].[PERIODIC])),[MEASURES].[YTD])

SEQ 250

IIF([%ACCOUNTDIM%].PROPERTIES("ACCTYPE") = "INC" OR [%ACCOUNTDIM%].PROPERTIES("ACCTYPE") = "EXP",IIF([%TIMEDIM%].CURRENTMEMBER.LEVEL IS [%TIMEDIM%].HALFYEAR,[MEASURES].[PERIODIC],SUM(PERIODSTODATE([%TIMEDIM%].HALFYEAR,[%TIMEDIM%].CURRENTMEMBER),[MEASURES].[PERIODIC])),[MEASURES].[YTD])

This has resolved the issue for us. I don't have what the values were before the changes were applied, but I think some of them changed quite a bit. Good luck.

[PatrickFavre · Accepted solution]

Hi,

You should also have a look to SAP Notes number 1430517. Everything is described there.

And, furthermore, changing the "MeasureFormula" table by removing every INSTR functions (see previous thread), will for sure solve the problem.

Kind Regards,

Patrick

[Former Member]

Hello,

Just to inform everyone this issue has been fixed in v7.5 for the Microsoft platform per note 1409229.

Regards,

Jason

[Former Member]

Here's the latest. The issues seems to be a bug within version 7 and Sql 2008. I'm not a technical expert, so I'll try my best to explain what I've been told.

In version 7, SAP changed the way the measures dimension is calculated. In version 5.1, measures were defined using both the dbo.tblformula and dbo.ytdformula tables. In version 7 there is only dbo.measureformula table that defines the measure. This table apparently has mdx formulas that are used to calculate the various measure members. The bug arises with the introduction of SQL 2008. According to SAP u2013 the bug/design issue stems from how SQL2008 handles the INSTR function in the new dbo.measureformula table differently than prior versions of SQL. The bug fix IM#3552421 is scheduled to be fixed in version 7.0MS SP7. SP7

Since we are in a Production environment already, we needed something other than a future SP. To help resolve our issue, we were asked to modify the MDX formulas for several of the records in the dbo.measureformula table. We're still working our way through testing, but this does seem to have resolved the issue. We are now getting what appears to be correct Periodic data in the reports that previously were incorrect. The most recent test environment we've been using does not have Cumulate Update 4 for SQL 2008 installed. Only SQL 2008 SP1.

BPC 7.0 SP7 looked to have been released late last week, but the SAP Note I found did not contain a listing of resolved issues so I cannot say whether the bug fix was included there or not. If you'd like to know which records were updated, let know and I can forward you a copy of the records changed.

[Former Member]

Hi!

That was very interesting.

I would be very nice if you could send me a copy of the records changed.

I just got an answer from the SAP Support to apply Cumulative update package 6 for SQL Server 2008 Service Pack 1

- build 10.00.2757 http://support.microsoft.com/kb/977443/en, and this is should solve the problem.

I will try this and post the result here.

/Fredrik

[Former Member]

Hi all,

I am actually working on a very similar issue in collaboration with Microsoft SQL Server Development team.

I will update this thread with our conclusions as soon as possible.

Best regards,

Julien

[Former Member]

Hi Fredrik

We installed Cumulative Update 4 yesterday and it did not solve the issue. I'll update this thread if I learn anything more.

[Former Member]

Thanks for the update Fredrik.

Unfortunately for us we did not discover this issue until we'd made the move to Production. The issue is not happening in our development environment, so we're not able to do testing there. We have yet to find a difference between the environments.

SAP has suggested that we install Cumulative Update 4 to our SQL 2008 SP1 install. Has this been something you've performed as well?

[Former Member]

Hi!

No we havent installed Cumulative Update 4

SAP suggested that we installed Cumulative Update 3 when this update was new but that didn´t help.

If you install Cumulative Update 4, I would be very thankful if you wrote the result of this on this thread.

Regards

Fredrik

[Former Member]

Hi Fredrik

I'm just curious to see if this issue was ever resolved. We are experiencing the same thing after having just completing an upgrade to BPC 7 SP5 and SQL 2008.

[Former Member]

Hi !

No the problem is still not solved!

SAP Support has been looking in to the problem for a very long time now but they have still not come up with any solution hence we haven´t been able to upgrade our production environment to BPC 7 and SQL 2008 yet.

[sorin_radulescu]

If you don't have any dimension formula then I will say the problem is happen because of lite optimize was failing or because one of dimension is still with issues.

Otherwise I don't think this can happen but I suppose the data is wrong into SSAS cube and that's happen just because how you setup your dimensions.

Kind Regards

Sorin Radulescu

[Former Member]

Hi!

I processed all dimensions in the application and I found error in four of the dimensiones so maybe this can be the problem.

I get the same error message in all four dimensions:

- Error Code = 0x8007000E, External Code = 0x00000000:.

Error Code = 0x8007000E, External Code = 0x00000000:.

Error Code = 0x8007000E, External Code = 0x00000000:.

Any idea what can cause this errror message?

Regards

Fredrik

[Former Member]

Hi,

Can you please tell what are the types of these dimensions? Can you try to delete these dimensions and recreate them and then assign them to the application? Are these dimensions used in any other applications?

[Former Member]

Hi!

We don´t have problem with the dimension processing any more it was solved making a copy of the application but the original problem with problem to calculate parents total in our account dimension is still not solved!

Regards

Fredrik

[sorin_radulescu]

If you don't have any dimension formula then I will say the problem is happen because of lite optimize was failing or because one of dimension is still with issues.

Otherwise I don't think this can happen but I suppose the data is wrong into SSAS cube and that's happen just because how you setup your dimensions.

Kind Regards

Sorin Radulescu

[sorin_radulescu]

Usually this happen when you have multiple hierachies and you have dimension formula into H1 using members from H2.

Regards

Sorin Radulescu

[Former Member]

Hi!

In this case we don´t have any dimension formulas.

Regards

Fredrik