Hierarchy Maintenance in PAW or TI?

Post Reply
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Hierarchy Maintenance in PAW or TI?

Post by michaelc99 »

Hello Everyone,

My normal approach for adding elements and attributes has been to edit the dimension and manually add them. This works for one-off, minor modifications; however, in lieu of a future-state MDM approach. I am looking to either use PAW or a TI script to add future years to multiple PERIOD dimensions. So, I am looking for your perspective and advice on best practices for hierarchy maintenance (PAW 2.0.76).

When I saw that PAW has an Import Members option, I took this as an opportunity to test the functionality. So, I exported the PERIODS dimension to CSV (using a TI script), then used Excel to modify the contents (effectively removing prior periods while adding future periods.) This includes adding the standard YEAR>QUARTER>MONTH elements plus comparison hierarchies. Once done, I took the new condensed CSV and uploaded using PAW. Unfortunately, the results were not clean and I had to restore the DIM from PROD to roll-back the changes.

Would a better approach be to Export the DIM to CSV, add the new members/hierarchies, rebuild the DIM through TI (with all exported members plus the new members/hierarchies), and finally manually add the attributes? Any concern over data loss assuming no elements are removed in the process and leaf elements will not be modified to consolidated elements? Is there a better approach that I'm over-looking?

Thank you in advance,
Michael
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Hierarchy Maintenance in PAW or TI?

Post by gtonkin »

HI Michael,

One of those - it depends answers.

If your TI skills are reasonable, you could write a process to loop from 1 through 12 to add the necessary periods as well as update the attributes.
Where you need words and abbreviations like Jan, January, it may get a little more complicated but throw these into strings and then read process the strings each month i.e. in Period one look for a separator in your string like, take everything before than and you have what you need.
You can delete up to where you found your separator JAN;FEB;MAR... Becomes FEB;MAR;... MAR;...

Alternatively pop everything you need into a spreadsheet, upload to PAW or copy to model_upload then do what you need in metadata/data.

I only make minor changes in PAW to dimensions as often the dimension is too big (more than a handful of elements) and the performance is poor, keep refreshing and hanging. The last thing I am likely to do is use the built in function that creates backwards dates.

HTH
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: Hierarchy Maintenance in PAW or TI?

Post by michaelc99 »

Hello gtonkin,

Thank you for the quick response. I exported the entire dimension to CSV, then used Notepad++ to add the new periods/hierarchies. I'm not sure it mattered or not, but I positioned the new members at the top of the file. I used PAW to rebuild the entire dimension, but after re-building, I received a number of warning messages indicating that certain members are not numeric.

Code: Select all

Line 1: "Q2 23 vs Q2 22","","0"
Line 2: "Q2 23","Q2 23 vs Q2 22","1"

Code: Select all

line (2): Dimension Element "Q2 23 vs Q2 22" not numeric.
I set the elements to String during the rebuild dimension process, so I am scratching my head on this one.

Thank you,
Michael
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Hierarchy Maintenance in PAW or TI?

Post by gtonkin »

Not easy to diagnose without seeing screenshots.
Ensure that all your datasource variables are string, unless you have real numerics.

When adding to the dimension, you add them as either type N or C, not S.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Hierarchy Maintenance in PAW or TI?

Post by Wim Gielis »

EDIT: I posted in the wrong topic, admin may delete this one
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Hierarchy Maintenance in PAW or TI?

Post by declanr »

michaelc99 wrote: Tue May 24, 2022 7:11 pm

Code: Select all

Line 1: "Q2 23 vs Q2 22","","0"
Line 2: "Q2 23","Q2 23 vs Q2 22","1"

Code: Select all

line (2): Dimension Element "Q2 23 vs Q2 22" not numeric.
I expect what you have here is for Line (2) - you are using a ComponentAdd to make "Q2 23" a child of "Q2 23 vs Q2 22".
But you have created "Q2 23" as a String element.

George has already alluded to this but will try and "double down" on it.

This is a relatively common misunderstanding I have seen when people get into development with Planning Analytics.
The key thing to understand is the types Numeric or String don't relate to the element itself, but the type of data you will hold against it.
So even though the name of the element includes the letter Q, that does not make the element a String.

Any hierarchy/dimension that will include roll-ups (parent/child relations) will be C and N elements (consolidated and Numeric/Nodal.)
Think of this as being that a Roll-Up is a simple calculation - the calculation is performed on the data in the cells of the cube. It would be impossible to "consolidate" text.

The only time you ever need to use S (string) is in a measures dimension (the last dimension in a cube) - as that is the one that dictates whether the cell intersections are text or numbers.
Declan Rodger
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Hierarchy Maintenance in PAW or TI?

Post by lotsaram »

michaelc99 wrote: Tue May 24, 2022 7:11 pm I set the elements to String during the rebuild dimension process, so I am scratching my head on this one.
As Declan has pointed out this is your problem. The data type of the field that holds the element name may be a string, but the element itself when added to the dimension should be either N for leaf (numeric) or C for a consolidation.

If you add elements of type S they can't be added to consolidations.

S elements can still store numeric data as the element typing between N and S only affects the type of data the cell can store when the dimension is the last dimension in the cube.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: Hierarchy Maintenance in PAW or TI?

Post by michaelc99 »

Good Morning Everyone,

When I looked at the Import Options (see attached) I initially thought it referred to the data type itself as String versus the element when being added to the dimension as either N for leaf (numeric) or C for consolidation. I rolled back my changes to mirror PROD, then went back to the Import Options, set it to Numeric and it imported successfully!

I'll go ahead and perform testing to ensure that it matches expectations, but I wanted to thank you all for your responses and helpful advice! I greatly appreciate it!

Thank you!
Michael

EDIT: Is there a way to export a dimension to a CSV file in Hierarchy Sort order?
Attachments
import option.png
import option.png (37.83 KiB) Viewed 1480 times
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Hierarchy Maintenance in PAW or TI?

Post by gtonkin »

Not a native function AFAIK. I typically go to the control cubes, open up the element attributes cube and download the view.

You could obviously write TI code too to run through the elements based on a subset in the dimension then use ASCIIOUTPUT to write the necessary to a file.

Attributes cube may suffice for now on a small dimension, as well as being quick and codeless.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Hierarchy Maintenance in PAW or TI?

Post by lotsaram »

michaelc99 wrote: Wed May 25, 2022 12:52 pm Is there a way to export a dimension to a CSV file in Hierarchy Sort order?
Yes there is. But only in the old C API based thick clients (Architect and Perspectives). Just right-click the dimension and select "export dimension". This will produce a CMA file (which is just a CSV) which is a representation of the dimension structure with all elements in index order. If using perspectives you can open this file in Excel and SaveAs with the file extension "XDI" and you can even edit the dimension using this format.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Post Reply