Using Alternative Hierarchies to calc stock balances

Post Reply
User avatar
Harvey
Community Contributor
Posts: 214
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Using Alternative Hierarchies to calc stock balances

Post by Harvey » Thu Jan 18, 2018 11:15 pm

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 752 times
Here's the "FY_Balances" alt hierarchy:
BAL-Hierarchy.png
BAL-Hierarchy.png (38.72 KiB) Viewed 752 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 752 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?
Flow OLAP - thinking outside the cube.

User avatar
macsir
Community Contributor
Posts: 514
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Using Alternative Hierarchies to calc stock balances

Post by macsir » Fri Jan 19, 2018 1:44 am

What if you use DB function rather than the new one and trace it?
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
Harvey
Community Contributor
Posts: 214
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Re: Using Alternative Hierarchies to calc stock balances

Post by Harvey » Fri Jan 19, 2018 2:14 am

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.
Flow OLAP - thinking outside the cube.

kangkc
Community Contributor
Posts: 181
Joined: Fri Oct 17, 2008 2:40 am
OLAP Product: TM1
Version: 9.x
Excel Version: 200x
Location: Singapore

Re: Using Alternative Hierarchies to calc stock balances

Post by kangkc » Fri Jan 19, 2018 2:27 am

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.

User avatar
Harvey
Community Contributor
Posts: 214
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Re: Using Alternative Hierarchies to calc stock balances

Post by Harvey » Fri Jan 19, 2018 3:06 am

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.
Flow OLAP - thinking outside the cube.

User avatar
macsir
Community Contributor
Posts: 514
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Using Alternative Hierarchies to calc stock balances

Post by macsir » Fri Jan 19, 2018 5:21 am

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.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
Steve Rowe
Site Admin
Posts: 1774
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Using Alternative Hierarchies to calc stock balances

Post by Steve Rowe » Fri Jan 19, 2018 3:23 pm

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

User avatar
Harvey
Community Contributor
Posts: 214
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Re: Using Alternative Hierarchies to calc stock balances

Post by Harvey » Wed Jan 24, 2018 11:54 pm

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.
Flow OLAP - thinking outside the cube.

Post Reply