Skip to main content

Data & Intelligence

Cognos TM1 – View and Subset Maintenance Considerations

TM1 TurboIntegrator is the programming or scripting tool that allows you to automate data importation, metadata management, and many other tasks. Scripts built with TurboIntegrator or “TI”, can be saved, edited and, through the use of chores, be set up to run at regular intervals.  TI scripts can be used to process existing TM1 data or, through the use of TI’s Data Source feature, import (and then process) data or information external to TM1.

Using TurboIntegrator, you can import data from the following data sources:

  • Industry standard comma-delimited (CSV) text files including ASCII files.
  • Information stored in relational databases (accessible through an ODBC data source).
  • Other OLAP cubes, including Cognos TM1 cubes and views.
  • Microsoft® Analysis Services.
  • SAP via RFC.

Here is a best practice concept for using and maintaining TM1 cube views as the datasource (or input) to a TI script.

View Setup

It’s pretty typical to use a cube view as the data source for processing data in a TurboIntegrator script. To develop your script, the cube view needs to first be created (and verified).  A “trick” you can use when first creating your process is that you can manually create the view (and the subsets that make up the view), open it up in the cube viewer to verify it and then select it as the datasource and let TM1 fill in the variables. After you have saved the script (the TI process) you can add the logic required to process the slice of data qualified by the view.

blod

View Maintenance

A process can also use a “programmatic approach” (by using TM1 functions ViewCreate, SubsetCreate, SubSetDestroy and ViewDestroy) to create subsets and views at run time (and destroy them after using them). This approach ensures that the view will be available to the process and that it is “up to date”; however these functions are memory intensive and impact performance. The recommended approach is to establish that the View and subsets exist (by using ViewExists and SubsetExists) and then programmatically modify the view and subsets to ensure its correctness.

Additionally, since we are recommending that these system or “not for users” views are never destroyed, we also recommend the (best) practice of ensuring that these views and subsets do not potentially impact the client experience or application performance should someone inadvertently access these objects directly. To accomplish this, all subsets in the view should be updated in the Epilog section of the TI script to insert a single leaf element to reduce its overall size.

The Practical Example

In an example, a TI script named “dim_update_planned_employees.pro” uses a predefined cube view named “process_planned_employees” as the datasource. This view contains multiple subsets to filter the cube data. There is business logic programmed in both the Meta and Data sections of the script.

Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

Typically, a scripts Prolog section would contain subset and view maintenance logic. Upon closer inspection, this script does not contain logic for subset maintenance. In fact, the subsets in the view are all (most likely manually) predefined and fixed or static (the number or collection of elements in the subset does not change).

Problem

The dimension named “PlannedEmployees.dim” currently contains 175 elements (or planned employees) and the subset named “N-Level Planned Employees” is static and only contains the first 25 elements. Because of this, the script only processes the 1st 25 elements (the first 25 planned employees). If we manually update the subset to resolve this problem (and force the script to process all of the planned employees), we need to consider that as the dimension is maintained, the subset will also have to be maintained. The risk of the subset being out of sync with the dimension is also very high.

blod2

DimSiz is the Solution!

DimSiz is a useful function that returns a count of the total elements that are currently in a given dimension. Using this function we can add some subset maintenance logic to the script’s Prolog section to ensure that the subset (and view) is always up to date.

The following is my code snippet:

blodcode

Conclusion

In conclusion, since I didn’t add the (recommended) Epilog logic yet, I can re-run the script and then manually open the view (using the cube viewer) to visually verify that the subset (and the view) now includes all of the planned employees. Once I’m happy with the view, I can then add the logic to the Epilog, rerun the process and verify that is in fact working!

Additional Recommendations

As I mentioned, I did not add code for “resetting” the subset after the process uses it (based on best practice outlined above), but that would be one of the follow-throughs on this exercise. I would also recommend:

  • Make sure you add subset maintenance code (to the Prolog & Epilog sections) for all of the subsets within the View.
  • Always perform a general clean-up of your scripts – adopting a more “best practice” programing style
  • Research all TI scripts to identify any similar view and subset maintenance risks

jm

 

 

Jim Miller

Mr. Miller is an IBM certified and accomplished Senior Project Leader and Application/System Architect-Developer with over 30 years of extensive applications and system design and development experience. His current role is National FPM Practice Leader. His experience includes BI, Web architecture & design, systems analysis, GUI design and testing, Database modeling and systems analysis, design, and development of Client/Server, Web and Mainframe applications and systems utilizing: Applix TM1 (including TM1 rules, TI, TM1Web and Planning Manager), dynaSight - ArcPlan, ASP, DHTML, XML, IIS, MS Visual Basic and VBA, Visual Studio, PERL, Websuite, MS SQL Server, ORACLE, SYBASE SQL Server, etc. His Responsibilities have included all aspects of Windows and SQL solution development and design including: analysis; GUI (and Web site) design; data modeling; table, screen/form and script development; SQL (and remote stored procedures and triggers) development and testing; test preparation and management and training of programming staff. Other experience includes development of ETL infrastructure such as data transfer automation between mainframe (DB2, Lawson, Great Plains, etc.) systems and client/server SQL server and Web based applications and integration of enterprise applications and data sources. In addition, Mr. Miller has acted as Internet Applications Development Manager responsible for the design, development, QA and delivery of multiple Web Sites including online trading applications, warehouse process control and scheduling systems and administrative and control applications. Mr. Miller also was responsible for the design, development and administration of a Web based financial reporting system for a 450 million dollar organization, reporting directly to the CFO and his executive team. Mr. Miller has also been responsible for managing and directing multiple resources in various management roles including project and team leader, lead developer and applications development director. Specialties Include: Cognos/TM1 Design and Development, Cognos Planning, IBM SPSS and Modeler, OLAP, Visual Basic, SQL Server, Forecasting and Planning; International Application Development, Business Intelligence, Project Development. IBM Certified Developer - Cognos TM1 (perfect score 100% on exam) IBM Certified Business Analyst - Cognos TM1

More from this Author

Follow Us