Xref / Xwrite

Xref / Xwrite

Krishna
Krishna

January 5th, 2017, 3:48 pm #1

On 11.1.2.1, have 2 planning EPMA applications.

Common Dimensions
Year
Scenario
Version
Entity
Dept
Accounts

Dimension only in Expense cube (target)
Projects

Dimension only in PayExp cube (source)
Employee

PayExp cube has an account (Acct_project, type smartlist) to indicate which project an employee is working on, in any month. The smartlist in this cube is same as level0 members of Projects dimension in the target cube.
Most of the employees of only one Entity (Ent1) have assigned projects. For the rest of the employees it is none.

Based on the project an employee is working his expenses/metrics should go to the corresponding project in the target cube. If Acct_project is missing, it needs to go to Project_None.

In target cube, Level0 members of Projects dimension are : Project_10, Project_20, Project_30 etc.
In Source cube, Smartlist Ids of Acct_project are 10,20,30 etc.

The following script is giving inconsistent results. any thoughts? In logs on the target app, is there a way to see which intersection xref / xwrite are using.

Set Updatecalc OFF; Set Cache HIGH; Set Aggmissg ON;
SET MSG DETAIL;

Fix(FY17,working,dept1,dept2, Ent1)
fix( Sam, Joe, violet, HDManager)
fix(jan:Mar)

var varTmpData ;

forecast (
If ("FTEManager" <> #missing) /* to execute only for valid emp, dept, ent */
if ( Acct_project <> #missing )
varTmpData = @XREF( "ExpCube", @CONCATENATE("Project_",@HspNumToString (Acct_project) ));
@XWRITE(varTmpData + forecast, "ExpCube", @CONCATENATE("Project_",@HspNumToString (Acct_project)));

else
varTmpData = @XREF( "ExpCube", Project_none );
@XWRITE( varTmpData + forecast , "ExpCube", Project_none);

Acct_project = 1000; /* just for testing */
endif
endif
)

Endfix
Endfix
EndFix

Source cube data

FY17 Forecast Working Ent1
Jan Feb Mar
Dept1 Sam FTEManager 1 1 1
HDManager 1 1 1
Acct_project - 10 10
Joe FTEManager 1 1 1
HDManager 1 1 1
Acct_project - 10 30
Dept2 Violet FTEManager 1 1 1
HDManager 1 1 1
Acct_project 20 10 10


Expected data intarget cube.
FY17 Forecast Working Ent1
Jan Feb Mar
Dept1 Project_None HDManager 2 - -
Project_10 HDManager - 2 1
Project_30 HDManager - - 1
Dept2 Project_10 HDManager - 1 1
Project_20 HDManager 1 - -

Thanks,
Krishna
Quote
Share

Krishna
Krishna

January 5th, 2017, 5:06 pm #2

Periods dimension is in both applications.
Periods and accounts are dense dimensions in both applications. All others are sparse dimensions.

Thanks,
Krishna

Quote
Share

Cameron Lackpour
Cameron Lackpour

January 5th, 2017, 5:19 pm #3

Since it works sometimes, it almost certainly has to either be:
1) Block related -- XREF or even the overall formula is puking because blocks don't exist or the formula itself fails.
2) That really cool XREF to a variable isn't working because it's...too cool for Essbase. Or something like that.

If you could FIX on an example intersection where there is a block and then another test where there isn't and use that IF test the it works/it goes tits up results would tell you that.

You might also try getting rid of the XREF, push the value to the target, and then do the math there.

Regards,

Cameron Lackpour

Quote
Share

Joined: April 13th, 2011, 5:00 pm

January 5th, 2017, 8:35 pm #4

I'm wondering if the problem has to do with multiple employees assigned to the same project.

If Joe (50) and Sam (25) are assigned to Project X, you'll end up with 50 or 25 in Project X (depending on the order in which they appear in the outline).

If this is the case, you may need to run some code that works in an XWRITE ( source_value + XREF [target_value], target_cube, target_member ) -kinda way
Quote
Like
Share

Pete
Pete

January 5th, 2017, 8:52 pm #5

Javier, as always, is probably correct.

Xrefs will not consolidate, and because you don't have employees in the second cube as soon as 2 employees have the same project the second data line will just replace the first. Does that look like what the data is doing?

I am, unlike Cameron 'Luddite' Lackpour, very impressed with the xref to a variable bit to get the consolidation of the original forecast and new data. Perhaps you could do something similar? Could get a bit messy - maybe just load into a seperate member (another scenario or version and then consolidate on the target).

Alternatively, does the Map reporting function exist in 11.1.2.1? I can't remember when it was added. That WILL do consolidation of smartlist members (I use it to push and consolidate level 0 employee data to a GL cube because xwrites from an upper level aren't available in Hybrid).

Quote
Share

Joined: April 13th, 2011, 5:00 pm

January 5th, 2017, 9:15 pm #6

Ugh. I just took a second look at the code and it already does what I thought it should do.
Quote
Like
Share

Joined: April 13th, 2011, 5:00 pm

January 5th, 2017, 9:20 pm #7

The next thing I would look at is the values assigned to Acct_Project. My understanding is that @HspNumToString will convert the Stored Numeric value and not the Smart List value.

So if you have an employee assigned to project "123" but the SL entry is mapped to number 124, that data is going to go to Project_124.
Quote
Like
Share

Cameron Lackpour
Cameron Lackpour

January 5th, 2017, 9:30 pm #8

Javier, as always, is probably correct.

Xrefs will not consolidate, and because you don't have employees in the second cube as soon as 2 employees have the same project the second data line will just replace the first. Does that look like what the data is doing?

I am, unlike Cameron 'Luddite' Lackpour, very impressed with the xref to a variable bit to get the consolidation of the original forecast and new data. Perhaps you could do something similar? Could get a bit messy - maybe just load into a seperate member (another scenario or version and then consolidate on the target).

Alternatively, does the Map reporting function exist in 11.1.2.1? I can't remember when it was added. That WILL do consolidation of smartlist members (I use it to push and consolidate level 0 employee data to a GL cube because xwrites from an upper level aren't available in Hybrid).
>>I am, unlike Cameron 'Luddite' Lackpour, very impressed with the xref to a variable bit to get the consolidation of the original forecast and new data.
^^^Actually, I did think it was pretty cool. Beyond cool. So cool that I am totally going to steal it. Although I do sort of wonder about performance. Wouldn't it be faster to do a single push and the do the rest of the math in the target? This is a cross application pull/push.

>>Perhaps you could do something similar? Could get a bit messy - maybe just load into a seperate member (another scenario or version and then consolidate on the target).
^^^If I didn't make this clear (and apparently I didn't, this was what I was suggesting.

Regards,

Cmaeron Lackpour

Quote
Share

Pete
Pete

January 5th, 2017, 11:59 pm #9

The next thing I would look at is the values assigned to Acct_Project. My understanding is that @HspNumToString will convert the Stored Numeric value and not the Smart List value.

So if you have an employee assigned to project "123" but the SL entry is mapped to number 124, that data is going to go to Project_124.
You'll be glad to know I thought the same thing.

I just assumed the xref query was to deal with data that was already submitted there, forgetting that it would actually natively do the consolidation. I even noted that it might be possible to change the code to do that...

Though - to the OP - aren't you going to have a problem if you run the script twice? Or are you clearing all of the target members first?

Javier - you're correct that HSPNumtostring is going to look at the ID, but I think the OP is dealing with that.

And Cameron - I'm actually wondering about the performance - but I reckon it would actually be pretty reasonable.

It's only looking at blocks that exist at the source (which is weird given that it's the target of the Xref!) and it's loading into a variable. Normally xrefs are 'slow' because you've got to deal with the block creation component which takes the time - but think about how quick dynamic xrefs are.

Quote
Share

Pete
Pete

January 6th, 2017, 12:01 am #10

Is it possible that one of the Xrefs isn't returning a project correctly and it's therefore grabbing the 'top' level data?

Alternatively, is it possibly 'grabbing' the number too early in the return xref? It's a bit fiddly to think how that will iterate.
Quote
Share


Confirmation of reply: