TM1 User Maintained Public Subsets

3/6/2017

As a TM1 Admin have you ever wished for user maintained public subsets? User maintained public subsets puts the onus for maintaining said subsets back in the hands of the users, a welcome respite for an TM1 Admins attempting to accommodate a multitude of user requests.

Note: In order to avoid a proliferation of user maintained public subsets, the following assumes the subset to be maintained is already in existence.

Create a cube using the three dimensions shown below:

User Maintained Public Subset

The qSubset Dimension contains element names which correspond to user maintained public subset names in other dimensions.

User Maintained Public Subsets

Example:  The Element names above (qSubset Dimension) correspond to subset names on the Dimensions (BUDUSDU and Iterations) below.

User Maintained Public Subsets

Create a Picklist (text) Attribute that corresponds to the pool of elements from which a user can select for inclusion in their user maintained public subsets.

Create a second Text Attribute to link the qSubset Element Name to the Dimension in which it would be found.

User Maintained Public Subsets

The Slots Dimension is a listing of place holder elements. Sequential numbers tend to work best. Ensure the minimum number of elements is greater than or equal to the maximum number of elements in the largest/longest user maintained public subset.

User Maintained Public Subsets

Generic Input is a Dimension with a string value element or placeholder.

Updating User Maintained Public Subsets:

Create two Turbo Integrator Processes, one to load the user maintained public subsets and one to update the user maintained public subsets.

Turbo Integrator Process qSubset_Load:
  • Data Source is none.
  • There are no variables.
  • Create a pSubset string Parameter.

User Maintained Public Subsets

Prolog Tab:
Creating the View:

You are creating a view which will contain the elements of the user maintained public subsets.

  • Cube Name is the name of the cube you just created.
  • View Name is the name of the view you will be creating.
  • Dimension Name is the name of the Dimension whose elements are the user maintained subset names i.e. qSubset.
  • Subset Name is the name of the subset you will be creating. In the example below, zTI_ is concatenated with the pSubset parameter to denote a. the Subset is used in a Turbo Integrator process and b. which Dimension Subset the Turbo Integrator process will use.
  • Subset Element Insert inserts the value from [parameter] pSubset when the Turbo Integrator Process is executed.

After the View is created with the subset name is cleared.

User Maintained Public Subsets

Populating the View:
  • pCount is the number of elements in the user created public subset that will be maintained. The Dimension attribute created above tells the Turbo Integrator process to which Dimension (e.g. BUDUSDU or Iterations) the pSubset parameter entry belongs.
  • Set pIndex to one.
  • pElement is the name of the element in the pSubset Subset being processed.
  • Cycle through the elements in the pSubset Subset

Determine which row/cell in zSubset_Creation is empty

  • Count is the number elements in the place holder dimension i.e. the Slots Dimension.
  • Set Index to one.
  • Element is the Index number converted to a string (as noted above sequential number works best in the Slots Dimension).
  • Check if the cell in zSubset_Creation corresponding to pSubset (subset name), element (row number) and string is blank.
    • If the Cell is blank, populate it with the pElement.
    • Set Index equal to Count.
  • Increase the Index by one. If Index is equal to Count, increasing it by one will stop the While Loop; if less than Count, the search for an open cell to populate continues.
  • Increase pIndex by 1 to process the next element in pSubset.

User Maintained Public Subset

Epilog Tab:

Destroy the View

Destroy the Subset

User Maintained Public Subsets

Turbo Integrator Process qSubset_Update:
  • Data Source is Cube View

User Maintained Public Subseets

  • Variables are as follows:

User Maintained Public Subsets

qDimension:

qDimension is the name of the dimension to which the subset to be updated belongs.

User Maintained Public Subsets

  • Create a pSubset string Parameter

User Maintained Public Subsets

Prolog Tab:

Create a view that houses the elements of the user maintained public subset loaded in the qSubset_Load Turbo Integrator Process.

Cube Name is the name of the cube.

View Name is the name of the view indicated in the Data Source Name.

Dimension Name is the name of the Dimension (e.g. BUDUSDU or Iterations) whose elements are the user maintained subset names i.e. qSubset.

Subset Name is the name of the subset you will be creating. In the example below, zTI_ is concatenated with the pSubset parameter to denote a. the Subset is used in a Turbo Integrator process and b. which Dimension Subset the Turbo Integrator process will use.

qDimension is the name of the Dimension (e.g. BUDUSDU or Iterations) which contains the actual user maintained public subset.

Delete all elements in the user maintained public subset [so that it can be repopulated].

User Maintained Public Subsets

Insert SValue [from the Data Source Name Cube View) into the qDimension (e.g. BUDUSDU or Iterations) pSubset.

Convert the Slot number to numeric to keep the subset order intact.

User Maintained Public Subsets

Epilog Tab:

Clear the View.

Destroy the View.

Destroy the Subset.

User Maintained Public Subsets

End User Perspective:

Tie it all together for the users by creating a websheet.

User Maintained Public Subsets

Cube and Generic Input rows do not need to be visible to the users.

Hopefully this technique will save you time and endear you to your end users. At Lodestar Solutions our consultants and coaches teach you time saving tips. Hence, we empower you to deliver

Faster, Cheaper Analytics

Finally, if you like our tips, consider joining our Lodestar Club to get access to our member only library of educational videos.  If you would like additional help on your TM1 project please contact at services@lodestarsolutions.com.

Recreating TM1 Alternate Hierarchies

2/8/2017

Cognos TM1 allows alternate hierarchies which, from a user perspective, is greatly appreciated but from a TM1 Admin perspective, probably not so much.  The official hierarchy may be maintained from a data warehouse or general ledger while TM1 Alternate Hierarchies may be maintained from user defined data or attributes.  Also, TM1 Alternate hierarchies may be subjected to frequent changes e.g. territories assigned to managers.  And when multiple hierarchies are in use, maintaining or rebuilding one hierarchy without jeopardizing the remaining hierarchies can be tricky.  So, you need to create a Turbo Integrator Process to remove all children (including other parents) from a designated parent.

The following example is generic and can be used with any dimension and any parent within the dimension. Also, the rebuild of the hierarchy is not part of this TI Process.

The Data Source of this TI Process is None. (When rebuilding the hierarchy, select the appropriate Data Source.)

TM1 Alternate Hierarchies

There are no Variables or Maps in this generic TI Process. (If rebuilding, designate your Variables and Maps accordingly.)

Parameters:
TM1 Alternate Hierarchies

pDimension allows the Turbo Integrator Process to be generic and used with any dimension. (If rebuilding the hierarchy, pDimension can be omitted and the name of the dimension can be hard coded into the TI Process.)

pParent  is the Top Level Parent from which you want to remove all children. Even if this Turbo Integrator Process will be Dimension specific, skipping this parameter is not recommended since even a Top Level Parent can change. (You can populate the Default Value with the Top Level Parent.)

pYN is an optional parameter to determine if any parent elements below pParent should be deleted.

Parameter Check:

This is optional code to determine if the Parameters entered are valid. Dimension and Top Parent values must exist; pYN must be populated.

TM1 Alternate Hierarchies

Parent/child relationships may be used in more than one hierarchy.  In the example shown below, 1st Half and 2nd Half exist in both the TOTAL YEAR and YEAR hierarchies.

TM1 Alternate Hierarchies

If a parent/child relationship exists in the hierarchy with which you are working as well as another hierarchy, the relationship must be removed intact from the hierarchy with which you are working.

  • Determine the Level of pParent.
  • Set pLevelIndex to one less than the pParent pLevel.  You are looking for the children of pParent so the level of any children would be less than pParent.  So, start at the top and work your way down.  If you were to start at the bottom, you could potentially break other hierarchies as it would be difficult to know at what level the parent/child relationship starts.
  • Cycle through the elements in pDimension.
    • Does the level of the element equal the current index level?
    • Is pParent an ancestor of the element?
    • Does the element have more than one parent?
TM1 Alternate Hierarchies
Multiple Parents:

If the [pDimEle] element has more than one parent, the assumption is it belongs to more than one hierarchy.

  • Set the pLevelIndex2 to one higher than the current pLevelIndex.  You need to determine to which parent of the hierarchy the element with multiple parents belongs.
  • Cycle through the elements in pDimension.
    • Does the level of the element equal the current pLevelIndex2?
    • Is pParent an ancestor of the element?
  • Delete the element with multiple parents from the current parent.  (DeleteDirect is used so that, if needed, the dimension can be rebuilt as part of the TI Process.)
Tm1 Alternate Hierarchies

Because you only need the level above the current pLevelIndex, there is no need to increment pLevelIndex2.

  • pLevelIndex is decreased by one each time.  You need to work your way DOWN the levels of pParent.
Tm1 Alternate Hierarchies
Removing  Elements from TM1 Alternate Hierarchies:

Once all parent/child relationships that exist in other hierarchies are removed, the remaining relationships/elements can be removed from your hierarchy.

  • Reset pLevel to the Level of pParent.
  • Set the LevelIndex to 1.  You are working from the bottom UP.
  • Cycle through the elements in pDimension.
    • Does the Level of the element equal the current LevelIndex?
    • Is pParent an ancestor of the element or does the element equal pParent?
TM1 Alternate Hierarchy
  • Determine the number of Children of the parent element.
  • Determine the Child based on the pChildCount.
  • Delete the Child from the parent element.  (DeleteDirect is used so that the child is removed immediately.)
  • pChildCount is decreased by one each time as the number of children decreases each time one is removed.
TM1 Alternate Hierarchies
  • If the element type is "C" and the number of children is zero and pYN parameter is set to "Y", delete the parent element.
TM1 Alternate Hierarchies
Tm1 Alternate Hierarchies

Hopefully this technique will save you time and endear you to your end users. At Lodestar Solutions our consultants and coaches teach you time saving tips.   Hence, we empower you to deliver

Faster, Cheaper Analytics.

Finally, if you like our tips, consider joining our Lodestar Club to get access to our members only library of education videos. If you would like additional help on your TM1 project please contact us at Services@lodestarsolutions.com.

TM1 Data Export in Multiple Columns

2/2/2016

In TM1 (aka Performance Management), you can use a Turbo Integrator (TI) process to not only load data, but to export data as well, using the ASCIIOutput or TextOutput functions. So for example, you finalized your budget and now you need to load it into your Accounting system.  Your TM1 data export needs to be in a particular format.  Keep in mind that when you export data, TM1 naturally wants to export the data in the style of a flat file.  This means that all your dimension elements and data are in singular columns, something along the lines of this:

TM1 data export 1
TM1 data export 1

However, you need it so that each month’s data is in its own column:

TM1 data export 2
TM1 data export 2

Not to worry, because there is a way to make this happen. The method I use is to have a “counter” that flags the data records and then use CellGetN (works with CellGetS too if your data is text) to get your values to export.  I use a counter to flag the data that is not zero and simply populate each cell at my counter element with the number 1 if my data point does not equal zero.  You can’t simply take “Total Year” as a counter, because you could very well have a situation where you have $10,000 in June and then a minus $10,000 in July, netting “Total Year” to zero.  So to flag our data, we need to add the element “Counter” (or whatever you want to name it) to your measure dimension in your cube.

I always make sure I have a measure dimension that is specific to each cube.  In my Budget_PL cube, my measure dimension originally contained only one element called “Data”, so now I can just add “Counter” to it.  For a P&L cube, you may think of your Accounts dimension as the measure dimension and not have added my extra dimension that had just the element of “Data” that I used as a holding place in case I need to add functionality to the cube.  In that case, you could create a duplicate cube that has the missing dimension and populate it via TI process or keep things a bit tidier and write the TI process to build a temporary cube that houses your flag and data, exports the data from your temporary cube and then destroys the temporary cube at the end of your process.  Also, if your data is numeric, you will need to use the NumberToString function, because TM1 needs to export the data as string.

TM1 data export in a nutshell:

Write a TI process that populates the number one on your “Counter” element coordinate if data point is not equal to zero. Make sure that you have a zero-out process scripted before you populate your target cells.

Write a second TI process that executes your first process on the Prolog tab so that your flags are current. Define a cube view on the Prolog tab in your process that is based off of your counter element and Total Year.  You don’t need the individual months in your view, because you will get that from your CellGet(s) on defined on your Data tab.  Use CellGetN (and/or CellGetS) on your Data tab and have the export code on your Data tab.

For more detailed information and scripting of these TI processes, please contact us at Sales@lodestarsolutions.com and one of our TM1 coaches will be happy to assist you.

Cognos TM1 TI Processes Using Cubes for Dynamic Variables

4/28/15

TM1 cubes can be used for more than calculations and analysis.  They can be utilized as parameter variables for TI Processes as well.  This can also be used as a work around if you are on a version of Perspectives that merges multiple parameters when you use an action button to execute TI Processes.

Here is an example of a cube used to make it easier to select the variables needed to allow TI Processes to be dynamic.  I created a basically generic cube and then saved different views of this cube for the user to choose the parameters for specific TI Processes.  Keep in mind that your measures dimension needs to be the last dimension, as it will contain string data.

TM1 Dynamic Variables

This cube has only two dimensions.  Since some processes may have one or more variables that need to be dynamic, the Parameters is a simple list of available slots for the parameters needed.  I also added a slot that gets populated with a time/date stamp of when the process last ran.

TM1 Dynamic Variables

Leveraging a picklist control cube and aliases, the views make it easy to choose valid variables for running your TI processes.

Picklist control cube:

TM1 Dynamic Variables

Aliases:

TM1 Dynamic Variables

View:

By creating the picklist control cube, the user can simply choose a valid parameter from a drop-down menu.

 TM1 Dynamic Variables

In the Prolog tab of the TI Process, I create a variable using the CellGetS function to retrieve the variables chosen in my parameter cube:

TM1 Dynamic Variables

In the Epilog tab of the TI Process, I script the coding to populate my parameter cube with the timestamp of when the process ran.

TM1 Dynamic Variables

Another benefit of using a cube instead of the Parameters tab in a TI Process is that, like in this case, I needed to use these same exact parameters to run several processes to populate data in different cubes.  Doing it this way, the user makes the selections once and then runs a TI Process that has those multiple processes batched to run.

Last, organize it in your applications folders.

TM1 Dynamic Variables

Cascade Cognos TM1 Security from Parent to all Children

Turbo Inegrator:  Cascade Cognos TM1 Security from Parent to all Children

Note:  This TI Process is dimension specific

  • This cascades cognos tm1 security from parent to all children

After the initial cogno tm1 security (access) is loaded, the level of the Dept variable (1st column in the text file) is checked.  If the level is greater than zero, the elements in the Department dimension are cycled.  If the cycled element is a child of the Dept variable, the parent security is grabbed [ElementSecurityGet] and loaded [ElementSecurityPut]to the child.  If the cycled element is not a child, the security is set to none.

Cognos TM1 Security
Turbo Integrator Security Cascade

If you have any other questions about Turbo Integrator (TI), email us at Sales@LodestarSolutions.com.

Copying a TM1 Service to Another Server

​Copying a TM1 service from one environment to another

I recall growing up on the north shore of Eastern Long Island. Our cozy little house was surrounded by acres of farmland. Being completely cold weather phobic (I’m not sure of the scientific term for that if there is one), I haven’t been back there in over a decade. Sadly, my parents tell me that all of the open farmland has been plowed over and replaced with tightly packed housing developments. In my mind’s eye, however, I still see our happy home amid the fields of crops. Sometimes retaining an image can be a nice trip down memory lane, but in the case of copying a TM1 service from one environment to another, it can create an error.
We recently built a simple TM1 model and deployed the TM1 Web application for use as a demonstration model. We wanted to keep a backup copy of it on another server. We copied over all of the data and log files to the second machine, including the tm1.cfg file. We then added the new service instance to IBM Cognos Configuration for TM1.
The copied model opened and was fully functional within Performance Modeler. However, when we tried to deploy the application, we received the error message, “Approval Hierarchy is being used by another application”. TM1 was retaining its reference to the application deployed on the other machine. Since TM1 does not allow sharing of the dimension subset that serves as the application hierarchy, we needed to erase any references to the application on the first machine. Accomplishing that task is easy within Performance Modeler where all we have to do is run a prebuilt TurboIntegrator process. The system administrator can access the many control objects contained within TM1 to simplify its administration.

By default, the control objects are hidden. In order to view them, click the Actions menu icon on the TM1 Performance Modeler toolbar. Then select Show Control Objects from the submenu. The Control Objects folder will become visible.

Steps to Copying a TM1 service

copying a tm1 service

There are subfolders containing each of the different types of control objects. Since we are looking to execute a TI process, we expand the Processes subfolder. We want to run }tp_admin_delete_all, which is the TI process to remove all Contributor applications references from TM1 model. Right click on the }tp_admin_delete_all process and select Execute Process. Upon completion of the process, the application deployed with no error message. We succeeded in creating a fully functional copy of the TM1 model and TM1 Web application on a second machine.

copying a tm1 service

copying a tm1 service

If you liked this blog on copying a Cognos TM1 service from one system to another check out this blog on Perspective Websheets & Active Forms For Cognos TM1 by clicking here.

Create TM1 Dimensions in Excel Worksheet

​Create TM1 Dimensions in Excel Worksheet

Though the preferred method for creating dimensions in TM1 is via a Turbo Integrator Process, sometimes you may want to create TM1 Dimensions with a dimension worksheet. With a dimension worksheet, you can incorporate Excel functionality to do concatenations that will populate your attributes. This works nicely for Aliases where you would like to use the element name with a description. The example below shows you how to do this for an Accounts dimension.

Elements:
3000
4000
Gross Profit

The first Alias will be called “Name” and will have the alpha name for the accounts:
Revenue
COS
Gross Profit

The second Alias will be called “Long Name” and will have the alpha-numeric name for the accounts:
3000-Revenue
4000-COS
Gross Profit

TM1 saves the dimension worksheet files to the first directory listed in the Local Server Data Directory field in the Options dialog box. Therefore, TM1 might save your dimension worksheet (.xdi) to a different directory than your dimension file (.dim). To choose where you would like to save the file, click on “Options” and then browse to the directory where you would like to save the file:

TM1 Dimensions

Through the TM1 Toolbar, you will use the Developer ribbon and choose “Dimension New”:

TM1 Dimensions
After you do that, you would set up your worksheet as such:

  • Column A has an “N” for leaf-level numeric elements; “S” for string elements and “C” for consolidated elements.
  • Column B will be the element.
  • Column C will be the weighting that is associated to an element in a consolidation. To the right of Column C is where you will be able to use columns for your attributes.
  • In our example, Columns D and E are used for input of our alias information.

Columns F and G will contain the TM1 formulas to send that information to update the dimension.

TM1 Dimensions

In Cell E5, we are using the Excel concatenation function to create the Long Name using the cell data in Columns B and D. In Columns F and G, we use the DBSA function with the following syntax:
DBSA (att_value, “server_name:dimension_name”, element, att_name)
Cell F5 formula: =DBSA(D5,"Demo:Accounts",B5,$F$4)
Cell G5 formula: =DBSA(E5,"Demo:Accounts",B5,$F$4)

Through the TM1 Toolbar, you will use the Developer ribbon and choose “Dimension Save”:

TM1 Dimensions

After you save the worksheet, you must create the “Name” and “Long Name” aliases through Server Explorer, since dimension worksheets will not create the actual alias, just read and update the info to send to the specific attribute. Once those attributes are created, you can simply copy your formulas down for new element additions and hit recalculate.

Again, dimension worksheets are not recommended for use with dimensions that require continual maintenance, as there can be issues with the saved file becoming out-of-synch if one makes changes through the Dimension Editor and this may cause data loss.

If you liked this blog on creating TM1 Dimensions in Excel Worksheet, then check out this blog on Using Stored Procedures in TM1 Turbo Integrator by clicking here.

 

Stored Procedures in TM1 Turbo Integrator

​Learn How to use Stored Procedures in TM1 Turbo integrator

As many of you know, when you are using stored procedures in TM1 Turbo Integrator to import data and metadata into TM1, you can bring it in from many sources. You can use comma-delimited ASCII files, other TM1 cubes and views, MSAS (Microsoft Analysis Service) and relational database tables that are accessible through an ODBC connection. For this blog, we are going to concentrate on relation database tables through SQL and what you may not know is that using multiple tables and writing multiple joins query in SQL using the Turbo Integrator query box can make the pull of the data very slow!

An easy way to correct this is to write your join statements in SQL using SQL Server Management Studio. Write your joins to create a “summary table” of all the fields you want pulled into TM1. Now create a stored procedure that uses the joins, creates your new table and subsequently updates the summary table based on the schedule you have set.

So, instead of using the long join statement in the Turbo Integrator query box, simply write a statement to execute a stored procedure in the query box. BAM! Your query is much quicker and returns your data as you would expect!

Stored Procedures in TM1 Turbo integrator

Did you like this blog on Stored Procedures in TM1 Turbo integrator, if so, then check out this blog on how to assign TM1 Element Security Within a Dimension by clicking here.

If you need help setting this up our excellent services is here to help!  Reach out to us at services@lodestarsolutions.com today!  Also, if you have not joined our community, go to lodestarsolutions.com and join now!  Don't miss out on the latest programs, events and educational blog like this one!  It is easy to join and takes less than 1 minute!

Execute Multiple TM1 TI Processes with Parallel Loading

​Execute Multiple TM1 TI Processes with Parallel Loading

As a TM1 power user, you are aware that a TM1 TI process sets a lock to the cube, which is loaded in a dimension maintenance function. This lock can cause end users to wait until the TI process is complete and the lock has been released. If you have a large amount of data to load, this can be very annoying to the end users, seeing that they have to wait a long time. So, it is recommended that you separate the Metadata maintenance from the data maintenance in separate TI processes and run them as a chore.   Are you aware that you can Execute Multiple TM1 TI Processes?

If long loading times are still an issue, try Parallel Loading, which is running multiple TI processes at the same time. This will allow the system to take advantage of multi-reading. You can even run the same TI process multiple times concurrently. When you define the parallel loading process make sure that all concurrent processes are loading a different segment of the data. Examples of segments could be time, products, regions, or business units.

Running Concurrent TM1 TurboIntegrator Processes –

  1. One way to run concurrent TI processes is to schedule a chore for each segment process at the same time. In the chore you will specify the segment parameters.
  2. You can also use a command-line tool to run a TI process, which will allow you to start the process from outside the Cognos TM1 server. This would allow you to also leverage non-Cognos schedulers, like Windows Scheduler, to run TM1 TI processes. The tool is called tm1runti.exe. This magically appeared in hot fix 1 for 9.5.2 and is incorporated in TM1 10. The tm1unti.exe tool will allow you to create Action buttons for executing TM1 process which can be embedded in other code, so you may be able to remove manual steps.
    For a guide on this, try this link. (You might have to cut and paste it into your browser).
    http://www-01.ibm.com/support/docview.wss?uid=swg21566543&aid=1

Execute Multiple TM1 TI Processes

If you would like help on your TM1 models, Execute Multiple TM1 TI Processes, TM1 customized training or other IBM Cognos assistance, please contact Bethanie at bnonami@lodestarsolutions. Lodestar is here to help.

Integrating TM1 9.5 and Cognos BI – Part 2

​Integrating TM1 9.5 and Cognos BI – Part 2

We continue from the previous Lodestar blog on Integrating TM1 9.5 and Cognos BI  withTM1 tips.

Integrating TM1 9.5 and Cognos BI

The }HierarchyProperties control cube in TM1 stores custom named levels for the hierarchy levels of TM1 dimensions. You can enter your own names for these levels in the }HierarchyProperties control cube and then use these names outside of TM1 to access TM1 data with IBM Cognos Report Studio, MDX statements or other MDX OLAP tools. You can also use this control cube to assign a default member for the dimension.

  1. In TM1 Architect, click the View menu and select Display Control Objects in the navigation pane, click to expand the Cubes
  2. Open the }HierarchyProperties control cube
  3. Click the title dimension list to select the dimension for which you want to assign named levels
  4. In the default Member cell, enter an existing element name to set as the default member for this dimension: Allows for filtering the dimension when TM1 data is retrieved from IBM Cognos Report Studio
  5. Enter your own custom name for each hierarchy level that exists in the dimension
  6. After configuring named levels, you must do one of the following to apply the changes: Restart the IBM Cognos TM1 server, or Run the RefreshMdxHierarchy function in a TurboIntegrator process.
Heads Up!
  1. Changes to element names or dimension structure are not automatically detected by the named levels feature.
  2. If your dimension changes, you must first manually update the named level assignments in the }HierarchyProperties control cube, then either restart the TM1 server or run the RefreshMdxHierarchy TurboIntegrator function to update the MDX hierarchies in the TM1 server.
  3. Watch out for names: if a TM1 cube has a level named "Products" and a member in the hierarchy also has the name "Products" when the cube is published, it will error.

In case you missed part 1 of our Lodestar TM1 tips, click HERE.

X