Unraveling TM1 : Lesser Known Facets – Part B

Planning Analytics Workspace (PAW) offers a robust suite of visualizations, enabling users to create rich and compelling reports and dashboards with remarkable flexibility. However, even with these capabilities, you may occasionally encounter requirements that push the limits of what PAW provides out of the box. 

One such scenario I encountered was the need to create a column chart comparing Actual vs Budget variance. The twist? Any negative variance should be highlighted with a red bar, while positive variance should be displayed in green, as shown below: 

PAW’s default settings don't currently offer this kind of custom conditional formatting for visualizations. However, with a little MDX magic and a few formatting tweaks, you can achieve this effect in just five simple steps. 

Step-by-Step Guide to Creating Custom Visualizations in PAW 

Step 1: Position the Version Dimension in the Column 

Start by positioning the Version dimension in the column of the Exploration view. This is where we will apply the MDX logic to derive the desired results. 

Step 2: Use MDX to Create Calculated Members 

Next, you'll need to update the MDX query by creating three calculated members to represent Actual vs Budget (AvB), Positive Variance, and Negative Variance. 

Here’s the MDX code: 

MDX code: 

WITH  

MEMBER [Version].[Version].[AvB] AS [Version].[Version].[Actual] - [Version].[Version].[Budget]  

MEMBER [Version].[Version].[Positive] AS IIF([Version].[Version].[AvB] > 0, [Version].[Version].[AvB], "") 

MEMBER [Version].[Version].[Negative] AS IIF([Version].[Version].[AvB] < 0, [Version].[Version].[AvB], "") 

Note: The AvB calculation could also be done using a consolidated member in the Version dimension, where the Budget has a negative weight. 

Step 3: Replace the MDX in the Row Axes 

Now, replace the MDX in the Row Axes relating to the Version dimension to show only the Positive and Negative calculated members, while excluding the AvB calculation (and any other member): 

MDX code: 

{ 

    EXCEPT( 

        { 

            [Version].[Version].[AvB], 

            [Version].[Version].[Positive], 

            [Version].[Version].[Negative] 

        },  

        { 

            [Version].[Version].[AvB] 

        },  

        ALL 

    ) 

} 

This MDX will generate a view that displays only Positive and Negative members in the Version dimension, leaving the non-relevant member (whether positive or negative) as blank, depending on the AvB value. 

Step 4: Convert the Exploration View into a Column Chart

Once the MDX has been applied, convert the Exploration view into a Column Chart. By default, PAW will show the columns for positive and negative values with its standard color scheme.  

Step 5: Apply a Custom Color Palette 

To finalize the visualization, we’ll apply a custom color palette. Navigate to the visualization properties and create a color palette that includes only two colors: green for positive values and red for negative values. 

Conclusion 

With just a few lines of MDX and a bit of customization, you can significantly enhance PAW visualizations. This technique allows you to move beyond the standard out-of-the-box options, giving you the flexibility to create more intuitive and visually effective reports. Whether you're comparing Actual vs Budget or any other metrics, these methods help you build visuals that not only convey the necessary information but do so in a way that is easy to interpret at a glance. 

By leveraging MDX and PAW’s formatting tools, you can push the boundaries of your reporting and create dynamic, insightful dashboards tailored to your business needs. 

print    

PAW Local Distributed is an upgrade to Planning Analytics Local Workspace that can be deployed in a container orchestration engine using either Docker Swarm by Docker or Kubernetes - an open source by Google, for high availability, fail-over, scalability, and fault tolerance in multiple application servers or virtual or even cloud machines.

Architecture:

The Planning Analytics Workspace Distributed is run on a Swarm mode by deploying the application on multiple Docker nodes (with unique node ids) also known as Swarm.

Docker Engine CLI could be used to create a swarm and deploy and manage the application services in swarm.

Swarm mode ensures secured connection across multiple servers. Additionally, some of the key features that Swarm mode offers includes:

Cluster management integrated with Docker Engine,

Declarative service model,

Desired state reconciliation,

Horizontal Scaling and Load Balancing,

Multi-host networking,

Automatic service discovery,

Service Discovery,

Rolling updates with roll-back

The Docker engine maintains high availability by effectively scheduling the failed node’s task to other nodes.

It was released in 2.0.41 version of Planning Analytics Workspace and is available to be downloaded from IBM Fix Centre from below link.

https://www-945.ibm.com/support/fixcentral/swg/selectFixes?product=ibm%2FInformation+Management%2FIBM+Planning+Analytics+Local&fixids=BA-PAWL-2.0.41&source=dbluesearch&function=fixId&parent=Analytics%20Solutions

Note: The Docker Swarm is currently supported on Red Hat Enterprise Linux (RHEL) only.

The Docker Enterprise Edition for RHEL could be downloaded from the following link:

https://docs.docker.com/install/linux/docker-ee/rhel/

print    

In the latest version of PAX (2.0.44) as well as PAW (2.0.44), IBM has added a new capability to allow the views to be shared between PAW and PAX using Planning Analytics Workspace Content Store.

What does this mean for us?

We can now save the views in content store and share it across to be accessed from either PAW or PAX which was not possible earlier. This further strengthens the integration and make the PAX and PAW interoperation even more seamless which a good step as far as usability and software portability is concerned.

Steps to access the views from PAW content store from PAX:

We already know how to save the views in content store within PAW – this is done by clicking the save button in the view and selecting the destination folder in content store.

To access the views saved in content store, click at View icon available on PAX toolbar that opens the folder structure of the content store.

Navigate to the folder where the view is saved then select the desired Report Type from the bottom and click Select.

Steps to save the views in PAW content store from PAX:

Note: This applies only to Exploration Views in PAX. For the other report types, they can be published to TM1 Applications folder from PAX and access in PAW. The saved exploration view when opened in PAW, opens as a normal view.

Open a view in Exploration mode and click Save View icon in PAX toolbar under Exploration tab.

You will be provided with two options to choose from:

1. Save to Content Store: This saves the view in PAW Content Store
2. Save to Server: This saves the view to TM1 data server.

When saving the view to Server, there is an option to save it as MDX view, however, please note that if            this is checked, the view will not be accessible from Architect as Architect does not support MDX based            views.

       2.1 When saving the view

Once the view is saved in PAW Content Store, it can then be accessed from PAW and any changes made to the view and saved in either of the platforms will be reflected across.

*Tip: You may still be able to create a view in PAW and save it to TM1 Server so it can be accessed from both PAX and Architect, however be mindful that if you make any change to the view in PAW and try to override the view, that will not be possible. It will only give you an option to save the view in PAW Content Store. It is possible to update the view and save the changes in PAX that is reflected across all platforms but currently not in PAW.

To create a view in PAW, right click the Views and select ‘Create view’. This view when saved will be saved in TM1 database directory and is visible in both PAX and Architect both.

print    

What's interesting to note is that Planning Analytics Workspace (PAW) version 2.0.40 and 2.0.41 are combined so that the release of Planning Analytics Workspace 2.0.41 aligns with the IBM Planning Analytics for Microsoft Excel (PAX) release.

With several fixes applied to both PAW and PAX in this version; this post will focus more on the enhancements and features. However, should you want to review the fixes, you can find those here.

Features:

With this release, you will now be able to create and edit drill through rules and processes in Workspace.

Also finding users who don't have any permissions is quick and straightforward by using the new menu.

A new quality of life enhancement is that grid refreshes can be configured to automatically refresh on new views when it's n level data changes.

Database configuration parameters are now set from the database activity report.

With this other metrics such as threads blocked will also now appear on the database activity report.

The set editor allows you to now define which levels of a hierarchy to include in a report with dynamic ranges.These can be defined as Level >= level002.

There are individual icons for dynamic and static sets helping determine whats dynamic in the hierarchy and what is not.

Planning Analytics Workspace can now be Distributed on Docker Swarm but currently supported on Red Hat Enterprise Linux only.

You can use Constrained Calculations in PAX to narrow the scope of the recalculates to just your active worksheet; Increasing the performance and speed of the worksheet.

Whilst this covers the main features; there is plenty more to read, follow this link to IBM for more.

print    

No matter how much we think we know about TM1, there are always these set of conditions that make you think “what-if there was a better way”??!! For example, not having to write a bunch of codes just to delete the subsets after using it in a Source View Or not writing the same area definition twice. What-if this could be done with less or no coding at all!

This blog and its subsequent part will focus on showcasing few of such many lesser known features in TM1. These little tricks and tips are a step towards better code management and of course peace of mind.

TIP 1. One area definition for two different rules at N and C level

Let’s take an example of Headcount in an Employee (HR Data) cube.

• Headcount is calculated monthly based on on-roll employee count in a particular month.
• However, if we pan-out at an All Months level (as illustrated in the figure below), total headcount adds up from Jan to Dec, which is not a true representation of employee count.

• Correcting this needs a rule to be written at a consolidated level which will pick the headcount from Dec.
• Instead of writing a separate line of rule with same Area definition, different rules can be clubbed and written for both N and C level separated by a semicolon (as illustrated in the figure below).

TIP 2. Error file directory

On any given day, you would hardcode the directory path or fetch it from a cube where the path is stored, this to ensure dynamism and to address issues with regards to data movement from one server to another.

But if you are not using this cube often or for any other purpose, it soon becomes redundant. There is a work around in which use of GetProcessErrorDirectory function offers the path and allows you to log into directory of respective server instances.

A folder inside logging can be created (as illustrated in the example below) and the file directed to that path, so even if/when the code is moved to different server/instance, it still works seamlessly.

Code snippet shows the use of function:

Exporting the exception to the file:

Output file:

TIP 3. Temporary Subset and Views

 In Turbo Integrator (TI), to process a cube view, it is a good practise to delete view and subsets created in Prolog to reduce redundancy. This is commonly done in Epilog tab of process using various functions.

But there is a much simpler way to delete views and subsets without the need of writing code in Epilog.

The screenshot below illustrates a code snippet, highlighted portion is the secret recipe; Adding “1” as the third parameter to functions ViewCreate, SubsetCreate or SubsetCreatebyMDX considers the views and subsets as temporary and deletes them once the process is completed.

This improves overall performance as deleted temporary objects can’t create lock, as a result of which TI doesn’t need to wait for locks to be released before executing a temporary object.

Note: These functions are available from 10.2.2 FP4 onwards.

Hope you would have enjoyed reading this blog as much as I had testing these cool features; stay tuned for Part B of this blog series on Lesser Known Facets of TM1. To Subscribe, visit http://blog.octanesolutions.com.au

You may also like reading: 

“ What is IBM Planning Analytics Local ”

“IBM TM1 10.2 vs IBM Planning Analytics”

“All you need to know about Planning Analytics 2.0.5”

“Little known TM1 Feature - Ad hoc Consolidations”

“IBM PA Workspace Installation & Benefits for Windows 2016”

“101 Guide to Blockchain”

“TI Optimisation – An Epilogue”

For more Information: To check on your existing Planning Analytics (TM1) entitlements and understand “how to”, reach out to us at info@octanesolutions.com.au

Octane Software Solutions is an IBM Registered Business Partner specializing in Corporate Performance Management and Business Intelligence. We provide our clients advice on best practices and help scale up applications to optimise their return on investment. Our key services include Consulting, Delivery, Support and Training.

Octane has its head office in Sydney, Australia as well as offices in Canberra, Bangalore, Gurgaon, Mumbai, and Hyderabad.

To know more about us visit, OctaneSoftwareSolutions.

print    

Welcome back, for those who had read Part A of this blog, hope you would have tested Parallel Interaction and benefitted from it. For those who haven’t, you can read it at http://blog.octanesolutions.com.au/caveat-around-concurrent-data-loads-part-a

This blog builds upon the previous one and focusses on improving performance by Parallel Interaction, it highlights TM1 facets worth consideration while an object is locking.

Synopsys

Tips towards improvements around support for concurrent read/write and/or parallel execution of turbo integrator process enabling higher efficiencies and productivity.

Analysis

• Declare unique view and subset names

This allows to run a Turbo Integrator process without locking when executed by concurrent users simultaneously.

• Establish Cube Dependency

This nullifies the chance of a query or process triggering a cube dependency; this usually happens during periods of user activity that may block objects and cause contention issues for concurrent reads and writes.

To establish a cube dependency, include AddCubeDependency function and valid only in the Turbo Integrator process. This function creates a manual dependency between two cubes in the model.

Syntax: AddCubeDependency('CubeA','CubeB');

Cube B relies on a rule that is dependent on Cube A.

• Use a ViewConstruct function in Turbo Integrator processes

This will increase the speed of Turbo Integrator process as it stores a stargate view in memory on the server. The purpose of ViewConstruct function is to cache the view by pre-calculating and storing large views for quick retrieval post a data load or update.

Syntax: ViewConstruct(CubeName, ViewName);

• Dimension Maintenance (or Dimension read/write)

This places locks on read and write operations in any cube that includes dimension updates. Thrashing may result in locks during dimension maintenance (read/write to dimensions) as part of executing the Turbo Integrator process.

Note: Do not include dimension maintenance as part of data upload or updates.

• Maintaining an Attribute (Alias)

This again places locks on read and write in any cube that includes dimension updates.

Thrashing may result in locks during dimension maintenance (read/write to dimensions) as part of executing the Turbo Integrator process.

Note: Do not include attribute alias maintenance as part of data upload or updates.

For more Information: To check on your existing Planning Analytics (TM1) entitlements and understand “how to”, reach out to us at info@octanesolutions.com.au.

Octane Software Solutions Pty Ltd is an IBM Registered Business Partner specializing in Corporate Performance Management and Business Intelligence. We provide our clients advice on best practices and help scale up applications to optimise their return on investment. Our key services include Consulting, Delivery, Support and Training.

Octane has its head office in Sydney, Australia as well as offices in Canberra, Bangalore, Gurgaon, Mumbai, and Hyderabad.

Get a free one-hour consultation on us

To know more about us visit, OctaneSoftwareSolutions.

print    

In this blog, would like to introduce you to a new feature called "Compare Sandboxes"; this feature, an extension to Planning Analytics Workspace’s existing capability, shall be available both on Cloud and in Local versions.

What is a Sandbox? (Jump to “Feature Explained” section if you are an existing TM1 user & know this well)

A Sandbox lets you create your own personal workspace, your own version where you can enter & store data-value-changes without impacting the base (actual) data. 

• A sandbox is not a copy of the base data, but a separate overlay or a layer of your own data values that you have entered on top of the base data.
• Sandboxed data is your own personal work area, its private to each user and cannot be seen by others.
• Once a sandbox is created, user can run multiple iterations on the data set without effecting the base data.
• When user commits the sandboxed data back into the base data, the changed values are then visible to others.
• Sandbox help user explore different business scenarios, for example, user may create best case, average case, worst case.

So, What’s new??

While “sandboxing” has been around for some while now, users have been asking (almost demanding) for a “Compare Sandbox” feature.  This in essence is the possibility of comparing different scenarios (like best vs average vs worst) to help users analyse and drive better business decisions. This would also help streamline scenarios well before they are committed in the system.

Feature Explained:

As we now know, earlier versions of Planning Analytics (TM1) did not have an option where in users could compare different sandboxes, Now, users with entitlements to Planning Analytics Workspace (PAW) licenses can not only create personal scenarios in sandboxes but also view them side-by-side to compare and analyse the cause & its effect.

This was made possible as PAW considers Sandbox names as elements of dimension called Sandboxes (refer fig 1).

Figure 1

For illustration, you can now display your Best & Worst case sandbox scenarios next to each other in nested columns, and then, calculate the variance, as shown below. 

In this example, we have two sandboxes Sri_BCase and Sri_WCase.

In Figure 2 Sandboxes are same as base data. BCase-WCase is on-fly calculation which gives variance between Sri_BCase and Sri_WCase.       

Figure 2 

In Figure 3 Sandbox Sri_BCase, budgeted Units for S Series 2WD for world has been increased by 3%, Sri_WCase still holds base data. We can now see variance between Sandboxes in below figure.

Figure 3

In Figure 4 Sandbox Sri_WCase, budgeted Units for S Series 2WD for world has been increased by 0.5%, Sri_BCase holds 5% increased data earlier. We can now see variance between Sandboxes.

Figure 4

In Figure 5 this data can also be visualized using different graphs with in PAW. In this case Stack Bar has been used to Visualise data.

Figure 5

In Figure 6, we can also visualise the impact of these changes on other values like Sales, Price and other related measures.

Figure 6

What would I like to see more

• Capability to spread data across multiple sandboxes.
• Capability to add members (new versions) to the Sandboxes dimension unlike the traditional way.

Frequent Feature refresh from IBM:

As a part of its continuous improvement program, IBM basis its Client's business requirement and requests has been adding features to PA Workspace. IBM published "Compare Sandboxes" feature in PA Workspace in its version 2.0.31 release. Some of the features from earlier versions are Create Virtual Dimensions on fly, Calculation on the fly, Sorting, Ranking etc.

Hope you would have enjoyed reading this blog as much as I had testing this cool feature; stay tuned for upcoming blogs.

You may also like reading “ What is IBM Planning Analytics Local ” , “IBM TM1 10.2 vs IBM Planning Analytics”, “Little known TM1 Feature - Ad hoc Consolidations”, “IBM PA Workspace Installation & Benefits for Windows 2016”.

For more Information: To check on your existing Planning Analytics (TM1) entitlements and understand how to upgrade to Planning Analytics Workspace (PAW) reach out to us at info@octanesolutions.com.au for further assistance.

Octane Software Solutions Pty Ltd is an IBM Registered Business Partner specialising in Corporate Performance Management and Business Intelligence. We provide our clients advice on best practices and help scale up applications to optimise their return on investment. Our key services include Consulting, Delivery, Support and Training.

Octane has its head office in Sydney, Australia as well as offices in Canberra, Bangalore, Gurgaon, Mumbai, and Hyderabad.

To know more about us visit, OctaneSoftwareSolutions.

print    

In this weeks blog, our team make it our duty to shine some light on right tool your company can choose to meet your business needs. We will cover the much discussed benefits of installing and using IBM's Planning Analytics workspace on Windows 2016.

1. What is PA and PAW
2. PAW and Windows 2016
3. Benefits on PAW on Windows 2016

What is PA and PAW?

Planning Analytics ("PA") is the next version, or can be considered as the next generation of TM1. IBM enhanced TM1 functionality and features and embraced new tools to its' suite. This new enhanced TM1 has been rebranded as PA. IBMs PA has the best of BI, Watson, planning and analytic engines under one solution. 

IBM Cognos TM1 which required trendy data presentation and visualization capability – a demand from Clients - has now been fulfilled by IBM. IBM has embedded Workspace to Planning Analytics, which is now called Planning Analytics Workspace ("PAW"). The below diagram gives a high level picture of new Architecture.

The following diagram shows where PAW fits into your IBM PA Local architecture:

IBM PA Workspace

As a new face of the Cognos TM1/IBM Planning Analytics solution, IBM's PAW delivers a rich, interactive user interface where you can easily build analytical and planning applications or dashboards by combining cube views, web sheets, scorecards, and data visualizations. It is flexible enough to export to excel and publish the same data to workspace. And, you can now create dynamic reports via active forms for planning and budgeting, and gain insight and discovery via data visualisations from a very clean UX workspace and dashboard – even easily share content, reports and dashboards between users. 

Highly Clean and Visual View-Based Interface: The interface is a highly visual, freeform design with over 25 charts, scorecards, images, shapes and many other options. You can easily synchronize data between different objects (cube views, web sheets etc.)

On Premise and On Cloud: An amazing innovation is Planning Analytics Workspace (PAW) and its two variants. One, being on premise called Local. And the other, is cloud based referred as on Cloud.

It is a data rich tool, that can show data from all different cubes, to meet business needs: PAW is also view-based, which means you can import multi-dimensional data into a workspace and then it effectively converts and displays it as a chart to map your visualisations (whether it is a bar graph, scatter graph, line graph or so forth).

Easily share between hundreds of users: PAW is a highly interactive viewer that makes it easy to swap data exploration to charts. The main advantage of a drag and drop workspace, unlike some other platforms that require you to write the rule for reports yourself, is that business users can easily build and share their own reports and dashboards.

Support Analysis, Reporting and Write-back features: So for example if you require write-back or what-if analysis of data, or need the aggregate data from high-volatility APPs in real-time, users can benefit from PAs consistent performance and tightly controlled latency of cached and non-cached data.

Mobile Compatibility: Workspace is mobile and can be accessed from tablets and iPads! Aside from supporting all your web interfaces such as Chrome, Safari, Explorer and Mozilla, you can even access work on the go from meeting to meeting, with mobile compatibility access. This full service analytics solution supports faster loading and scrolling for both web and cloud interfaces that enables high performance across WAN wireless access networks, and has iPad mobile compatibility.

Fast Querying and Loading Time: A T1 debugger in PA now uses a TM1 server as part of the back end. This means whether you are on a web browser or cloud interface there are less latencies and errors in querying, building or visualising reports, as opposed to traditional BI engines. Loading time is two (2) to four (4) times faster.

PAW comes with the above features and more such as combing additional highly versatile visualization features.

What makes your additional visualisation plugins unique with IBM PAW is that it does not require additional add-on licenses!

PAW Installation

On the other hand, the installation of PAW is not as smooth sailing as a TM1 or Cognos BI installation. PAW needs an additional software called Docker to be setup before installing PAW. Once docker is installed and ready to use, the next step is PAW installation.

The following diagram shows where Planning Analytics Workspace fits into your IBM Planning Analytics Local architecture:

Installation Workflow

Though the installation process looks simple, this needs technical manual assistance to fix issues and make installation and configuration a success.

If not all TM1 Experts, at least 98% of technical consultants who would have tried to upgrade from TM1 to PA, whilst installing PAW, would have encountered below error. 

Fixing this issue may cause delay in upgrading your system. It may also incur costs to fix this issues in non-prod environment. Once the non-prod is fixed, an outage or planned reboot, as well as BIOS changes are musts to fix the prod environment.

Benefits of PAW on Windows 2016

PAW for Windows 2016 has been available since June 2, 2017 ( 2.0.21).

Let us consider your clients business objectives for a second. Your companies strategy and technolocy benefits from embacing business intelligence trends that has been made available via systems such as the new PAW package support for Windows 2016 OS.

Again, docker is a must for getting PAW installed and configured on Windows 2016. The docker used here is not a native docker but a different docker called docker EE. 

What’s the Gain with PAW for Windows 2016 OS?

Microsoft has come long way with confounding features. The most versatile features, we would say, are:

• Nano Server: A Nano Server boasts a 92 percent smaller installation footprint than the Windows Server graphical user interface (GUI) installation option.

• Containers: Docker-based containers to Windows Server.

• Linux Secure Boot: Deploy Linux VMs under Windows Server 2016 Hyper-V with no trouble without having to disable the otherwise stellar Secure Boot feature.

• Storage Replica and ReFS: ReFS is intended as a high-performance, high-resiliency file system intended for use with Storage Spaces Direct and Hyper-V workloads.

• Storage Spaces Direct: More affordable for administrators to create redundant and flexible disk storage.

• Nested Virtualization: Nested virtualization refers to the capability of a virtual machine to itself host virtual machine. Nested virtualization makes sense when a business wants to deploy additional Hyper-V hosts and needs to minimize hardware costs.

• Hyper-V Hot-Add Virtual Hardware: We can now "hot add" virtual hardware while VMs are online and running.

Gain for PA and PAW Administrators

• The first and foremost advantage is that there is no need to have a Virtualize active in VMWARE.
• As there is no need to activate Virtualization, physical server is not required instead a VM can be used for the PAW. Thus reducing not just the cost involved with money but also the time and effort must be counted.
• Embracing new Windows OS with new features means that you can be rest assured in regards to the performance and availability of PAW – which was not the case with TM1 10x.
• Storage Replica is an amazing feature in Windows 2016, this helps during failovers and quit turnaround at critical times.
• All leading to reduced expenses.

Contact one of Octane Software Solutions specialists for an upgrade made easy today!

Planning Analytics is one of the reasons that makes IBM a market leader performance management quadrant.

• We are experts in this Cognos TM1 technology, having extensive experience in upgrading TM1 from older versions to newer Versions.

print