Page 1 of 1

Using Alternative Hierarchies to calc stock balances

Posted: Thu Jan 18, 2018 11:15 pm
by Harvey
Hi all,

I'm new to alt hierarchies, so looking for some advice from the trailblazers!

I'm trying an approach utilising alternative hierarchies in PA 2,0 to calculate stock balances. The goal is to store the movements and calculate the balance in any particular week.

The approach is as follows:
  • We have a stock cube that contains raw stock movements in the measure "CSOH Movement".
  • Create an alt hierarchy in the Weeks dimension called "FY_Balances" which contains BAL consolidations and an opening balance element for each year (see screenshot).
  • Create a measure called "CSOH Quantity" in the stock cube that will contain the balances.
  • Write a rule using the new CellValueN function to read the BAL consolidation for the current week from the equivalent BAL element in the "FY_Balances" hierarchy (see below)
The idea seems sound to me, but it's not working.

Here's the Primary hiearchy:
Primary-Hierarchy.png
Primary-Hierarchy.png (11.63 KiB) Viewed 5756 times
Here's the "FY_Balances" alt hierarchy:
BAL-Hierarchy.png
BAL-Hierarchy.png (38.72 KiB) Viewed 5756 times
(Note: the consolidated elements have all but the last child with zero weight to make sure the balance shows at every intersection)

Here's the rule I've written:

Code: Select all

['CSOH Quantity'] = N:
	CellValueN('Stock Cube',
		'Week':'FY_Balances':'BAL ' | !Week,
		'Stock Cube Measure':'CSOH Movement');
And here's what I'm seeing in the cube viewer:
Cube-View.png
Cube-View.png (15.1 KiB) Viewed 5756 times
As you can see, it's simply replicating the movement value, not fetching the balance from the alt hierarchy. When I trace the rule, I get an unfamiliar notation for the alt hierarchy. In the element list the following string appears:

Code: Select all

^FW201828^FY_Balances:BAL FW201828
Does what I'm attempting even make sense, and if so, should the rule be functioning as I thought it would?

Re: Using Alternative Hierarchies to calc stock balances

Posted: Fri Jan 19, 2018 1:44 am
by macsir
What if you use DB function rather than the new one and trace it?

Re: Using Alternative Hierarchies to calc stock balances

Posted: Fri Jan 19, 2018 2:14 am
by Harvey
As far as I know, the DB function does not support alternative hierarchies.

To check, I tried as you suggested and got a syntax error "missing right parenthesis":

Code: Select all

['CSOH Quantity'] = N:
	DB('MFP Scenario Planning',
		!GBL Company,
		!MFP Scenario,
		!MFP Selling Season,
		!MFP Product Lifecycle Status,
		!GBL Brand,
		!GBL Product Category,
		'GBL Week Financial':'FY_Balances': ('BAL ' | !GBL Week Financial),
		!GBL Currency,
		!GBL Adjustment,
		'CSOH Movement');
I'm thinking this confirms that the DB function only supports the "old way" of qualifying element names, with 'dimension':'element' and not option for hierarchy.

Do you know the syntax for doing this with the DB function?

Update: I was thinking this line from the docs might explain it: "The intra-cube reference is restricted to literal parameters, while CellValueN is not." I thought maybe that meant I CAN use DB, but only if it's a simple string literal. In my case it is not, as I need to derive the BAL element from my Week element.

I tested this by trying:

Code: Select all

	DB('MFP Scenario Planning',
		!GBL Company,
		!MFP Scenario,
		!MFP Selling Season,
		!MFP Product Lifecycle Status,
		!GBL Brand,
		!GBL Product Category,
		'GBL Week Financial':'FY_Balances':'BAL FY2018',
		!GBL Currency,
		!GBL Adjustment,
		'Total Movement Qty');
Still no dice, it doesn't like that second colon character. The docs must be referring to the ['element1', ..., 'elementN'] syntax by "intra-cube reference".

Update 2: OK, so I just needed to remove the dimension name from the element specifier. I'll see if I can get it working this way and post the results.

Re: Using Alternative Hierarchies to calc stock balances

Posted: Fri Jan 19, 2018 2:27 am
by kangkc
The ^ character if I am not wrong is supposed to be used internally partly due to MDX.
There was a bug with Set Editor that resulted in this character being exposed when using Subnm under PAX but this has been resolved with later PAX releases. SUBNM returned with the ^ character separating the level for the mentioned bug. I can't seem to find my PMR on this though.
May not be helpful for your problem though. Apology.

Re: Using Alternative Hierarchies to calc stock balances

Posted: Fri Jan 19, 2018 3:06 am
by Harvey
I tried it with the good ole' DB formula and it worked. Thanks to those who gave advice, it got me thinking in the right direction.

I'd say the lesson learnt is to avoid the CellValueN function for now, as it seems to behave differently from the way it is documented.

For those interested in this, the final working rule looks like this:

Code: Select all

['CSOH Quantity'] = N:
	DB('MFP Scenario Planning',
		!GBL Company,
		!MFP Scenario,
		!MFP Selling Season,
		!MFP Product Lifecycle Status,
		!GBL Brand,
		!GBL Product Category,
		'FY_Balances':'BAL ' | !GBL Week Financial,
		!GBL Currency,
		!GBL Adjustment,
		'CSOH Movement');
So far I'm liking this alternative hierarchy feature. I'll be using a similar technique to calculate our stock cover metrics.

Re: Using Alternative Hierarchies to calc stock balances

Posted: Fri Jan 19, 2018 5:21 am
by macsir
Glad to see it is working in old way. To be honest, I have never used CELLVALUEN in any of my projects so far. Need to learn it and figure out the correct way it works.

Re: Using Alternative Hierarchies to calc stock balances

Posted: Fri Jan 19, 2018 3:23 pm
by Steve Rowe
Hi Harvey,

Can I ask what release you are on? Local or on premise? I've tried several times to get something broadly similar to what you list working but with no success.

Cheers

Re: Using Alternative Hierarchies to calc stock balances

Posted: Wed Jan 24, 2018 11:54 pm
by Harvey
Hey Steve, sorry for the late reply.

The customer is on 11.1.0.30 (according the the properties on the tm1s.exe file) or TM1SERVER-AW64-ML-RTM-11.1.00000.30-0 according to the cmplst.txt. It's PA "Express" on-prem.

If there's anything I can do to help you out, shoot me a PM. I'd be happy to review your technique / model and see how it differs from mine.