Hierrachy Display Problem ?

[phamtinkt88]

Hi all,

My customer requirement is : display of products under consolition depend on each period of year.
For example : In Q1, product 1 is displayed under consolidation 1.
But when user change view to Q2, product 1 must be hidden, although product 1 is still under consolidation 1.

This is original consolidation

1.JPG (45.35 KiB) Viewed 8454 times

I only want to display some of them, so i remove what i don't want to see. New consolidation looks like this

2.JPG (28.79 KiB) Viewed 8454 times

But when i collapse then expand, the consolidation return to original display.
Could someone tell me way to keep the new conlidation regardless collapse or expand ?

Thanks all,
Tin

[David Usherwood]

Have you 'explored' with your customer why they want this? Is it just to suppress zeroes? If not, what is it for?
You can build multiple hierarchies but TM1 elements must be unique so it becomes messy and unmanageable very quickly.

[phamtinkt88]

Have you 'explored' with your customer why they want this? Is it just to suppress zeroes? If not, what is it for?
You can build multiple hierarchies but TM1 elements must be unique so it becomes messy and unmanageable very quickly.

Hi David,

Thanks for your reply.
Due to product is under "Price segment", so 1 product may fall to different segments at different specific periods.
My solution is put all product under every price segment, but depend on each product's price ,it will display or hide. But i'm still stuck with tech issue like i said above.
Would you please advise me more on solution or tech issue !

Regards,
Tin Pham.

[rmackenzie]

But when i collapse then expand, the consolidation return to original display. Could someone tell me way to keep the new conlidation regardless collapse or expand ?

One solution is to have specific hierarchies in the dimension for each time period for which a product's relationship to a segment can change. If this is once a year, then you might have FY10 Products By Segment, FY11 Products By Segment, FY12 Products By Segment. If it is once a week then it is going to get very messy!

Due to product is under "Price segment", so 1 product may fall to different segments at different specific periods.

From a modelling point of view, you are better advised to have Segment and Product as two separate dimensions. That way, as David mentioned, you can use zero-suppressed cube views, active forms, etc to show the data appropriately without having to struggle with many confusing hierarchies in the Product dimension.

[phamtinkt88]

Hi rmackenzie,

I did separate into 2 dims : Products hierrachy & SKU.
Before expand SKU :

Capture.JPG (24.59 KiB) Viewed 8388 times

After expand SKU :

Capture1.JPG (218.13 KiB) Viewed 8388 times

1. SKUs for levels higher than Price segment is meaningless (in red area) => how could i hide them ?
2. Each price segment include SKUs depend on SKU's price, but in this case, SKUs in each price segment are identical => how to fix it ?

Thanks,
Tin Pham

[rmackenzie]

1. SKUs for levels higher than Price segment is meaningless (in red area) => how could i hide them ?

The obvious option is to remove those consolidated elements from the subset in the Product dimension. Or, you could try and define an asymmetric view, but unfortunately the Cube Viewer tool in Perspectives doesn't do these. You can do this with Executive Viewer/ Cognos Express Advisor and customized MDX reports in Excel. An asymmetric view is where you can pick and choose which elements in dimension 2 show up against dimension 1 when they are nested on rows, or columns for that matter.

2. Each price segment include SKUs depend on SKU's price, but in this case, SKUs in each price segment are identical => how to fix it ?

You need to zero suppress the view. If you do that you will only see rows where a SKU has a price in a particular segment and all the rows containing zeroes will be removed from the view, i.e. the SKU doesn't have a price in that segment. Have you tried that?

[phamtinkt88]

The obvious option is to remove those consolidated elements from the subset in the Product dimension. Or, you could try and define an asymmetric view, but unfortunately the Cube Viewer tool in Perspectives doesn't do these. You can do this with Executive Viewer/ Cognos Express Advisor and customized MDX reports in Excel. An asymmetric view is where you can pick and choose which elements in dimension 2 show up against dimension 1 when they are nested on rows, or columns for that matter.

Due to my customer's planning method is top-down.First, they input amount for consolidated node, then expand it, manually break the amount down to child nodes base on history data => remove consolidated nodes is not suitable.
My customer is using only TM1 and Cognos BI 10 => I can not define asymmetric view as you advise.

You need to zero suppress the view. If you do that you will only see rows where a SKU has a price in a particular segment and all the rows containing zeroes will be removed from the view, i.e. the SKU doesn't have a price in that segment. Have you tried that?

I did suppress zero and this is the result

Capture.JPG (119.47 KiB) Viewed 8350 times

RED BOX : Suppress zero does not remove SKUs for consolidated node (of course, because it is consolidated data from child node)
BLUE BOX : When i expand All in "SKU" dim, it is expand for each coressponding node in "PRODUCT" dim. The view become very long, but my customer only want to show product for each price segment, after complete one price segment, they collapse it and expand the next one => easy for them to observe.

Thanks for your reply,
Tin Pham

[tomok]

BLUE BOX : When i expand All in "SKU" dim, it is expand for each coressponding node in "PRODUCT" dim. The view become very long, but my customer only want to show product for each price segment, after complete one price segment, they collapse it and expand the next one => easy for them to observe.

This is just the way dilling into a cube view works in TM1, there is nothing you can do about it in a view. However, if you create an active form with Perspectives and Excel, you can add some addtional functionality where the user can lock the drill on the PRODUCT dimension (by setting the starting point for the drill in another cell) so that you don't see the combination of all products and SKUs when you drill down. If you use the SUBNM formula in this "Starting Point" cell then the user can navigate the dimension at will to select the starting point. They can select the top of the hierarchy, a middle point, or any child. Here's how it works:

Put this formula in any cell outside the active form range (for example purposes we'll give it a range name of ProductDrillLock):
=SUBNM("ServerName:DimName","SubsetName",Index).

In the PRODUCT column of your active form report make the TM1RPTROW formula look like this:
=TM1RPTROW(CellRefToTM1RPTVIEW,"Servername:DimName","",ProductDrillLock)

Now, users can drill through the entire PRODUCT dimension by going to the SUBNM cell, double-clicking, then navigating in the Subset Editor and selecting "Total", or they can lock onto just "Electronic", or all the way down to "Sony" and the maximum range of the form, when drilled along the PRODUCT and TEST_SKU dimension, will be controlled to just what the user wants to see.

[David Usherwood]

I don't claim to be a Cognos BI expert, but I know it's very flexible, so I'd be amazed if you couldn't build an asymmetric view in it.
Don't forget that your users can't input into a consolidated element anyway.

[tomok]

I don't claim to be a Cognos BI expert, but I know it's very flexible, so I'd be amazed if you couldn't build an asymmetric view in it.
Don't forget that your users can't input into a consolidated element anyway.

I don't think the OP ever mentioned Cognos BI and AFAIK it is read only for TM1 cubes so you can't use BI for an input form . Your only choices for an input form in TM1 are Contributor, a cube view, or an Excel/TM1Web sheet.

[David Usherwood]

The OP did mention BI, but yes of course (doh!) it won't be much use for input
Think some creativity would be needed and to be frank I would need some more commercial incentive to chase this further.

[rmackenzie]

Your only choices for an input form in TM1 are Contributor, a cube view, or an Excel/TM1Web sheet.

EV had a method in its API that allowed it to display an asymmetric cube view and allow writeback. I assume this is still available in CX but haven't used it for a while.

[phamtinkt88]

This is just the way dilling into a cube view works in TM1, there is nothing you can do about it in a view. However, if you create an active form with Perspectives and Excel, you can add some addtional functionality where the user can lock the drill on the PRODUCT dimension (by setting the starting point for the drill in another cell) so that you don't see the combination of all products and SKUs when you drill down. If you use the SUBNM formula in this "Starting Point" cell then the user can navigate the dimension at will to select the starting point. They can select the top of the hierarchy, a middle point, or any child. Here's how it works:

Put this formula in any cell outside the active form range (for example purposes we'll give it a range name of ProductDrillLock):
=SUBNM("ServerName:DimName","SubsetName",Index).

In the PRODUCT column of your active form report make the TM1RPTROW formula look like this:
=TM1RPTROW(CellRefToTM1RPTVIEW,"Servername:DimName","",ProductDrillLock)

Now, users can drill through the entire PRODUCT dimension by going to the SUBNM cell, double-clicking, then navigating in the Subset Editor and selecting "Total", or they can lock onto just "Electronic", or all the way down to "Sony" and the maximum range of the form, when drilled along the PRODUCT and TEST_SKU dimension, will be controlled to just what the user wants to see.

I do appreciate your help ! I'm testing and will let you know the result.

I don't claim to be a Cognos BI expert, but I know it's very flexible, so I'd be amazed if you couldn't build an asymmetric view in it.
Don't forget that your users can't input into a consolidated element anyway.

Thanks for your remind! But due to my customer planning method, I created each leaf element correspond to each consolidated, user will input to leaf node, then data will be copied to consolidated one using rule. I wonder if it's a good solution, could you give me advise on that !