Revelwood

Your SUPER-powered WP Engine Site

IBM

IBM Planning Analytics Tips & Tricks: Rename Views in PAfE

by

Have you ever been in a situation where you saved a view and then realized that you want to change the name in IBM Planning Analytics for Excel (PAfE, formerly PAx)?  Maybe you had a typo or maybe you referenced a date that has changed.

In the old Perspectives world, there was no way to change the name of an existing view, so you were forced to resave the view with a new name and then delete the original view.  Planning Analytics for Excel offers the ability to rename a view without having to perform multiple steps.  

Renaming a view in PAfE is as simple as right-clicking on the view!

Once the option is selected, a popup box will appear that allows you to rename the view.  

This approach gives you the ability to maintain your environment with a single click, thereby simplifying your overall processes.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel’s AGGREGATE Function

IBM Planning Analytics Tips & Tricks: Update on Planning Analytics’ Deprecation

IBM Planning Analytics Tips & Tricks: Unpivot

Home » IBM

IBM Planning Analytics Tips & Tricks: Excel’s AGGREGATE Function

by

Excel’s AGGREGATE function lets you manage complex reports and ensure they foot correctly.

For example, suppose you are building a sales report by product and region.

A screenshot of a table Description automatically generated

Building the Grand Total calculation for a multi-level report like this is error-prone. You would need to use the SUM function and select every individual region while ensuring that no totals were included.

You can use instead use the AGGREGATE function to define all the totals … both the subtotal and the grand total. AGGREGATE can sum (among other things) and you can set the option to ignore other AGGREGATE formulas.

The AGREGATE function requires three parameters:

  • A function number to define Average, Count, Max, Sum, etc.  Function number 9 is sum.
  • An option to define how to interact with other subtotals, hidden rows, etc.  Option 0 ignores other subtotals and aggregate functions.
  • The range to perform the function.

The image below shows the same report along with the formulas used.

A screenshot of a spreadsheet Description automatically generated

Notice how the formula in C17 can include all the cells above it. You can now insert rows in any region and not have to update the grand total.

Additionally, you can have AGGREGATE ignore hidden rows. Hide a particular SKU, see the results and unhide the row. Very easy for on the fly what-if scenarios. The ignore hidden rows feature works very well when trying to derive a sum of an Autofiltered list.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Unpivot

IBM Planning Analytics Tips & Tricks: PAW Export Data

A Resolution You Should Actually Keep: Upgrade Your IBM Planning Analytics Environment

Home » IBM

IBM Planning Analytics Tips & Tricks: Update on Planning Analytics’ Deprecation

by

IBM recently made the End of Support dates for Planning Analytics 2.0.9.x official.

As we mentioned in our previous blog post on the deprecation of Planning Analytics 2.0.9.x, IBM is extending the general support period for Planning Analytics 2.0.9.x until October 31, 2025. This includes IBM Planning Analytics Local, IBM Planning Analytics Advanced and IBM Planning Analytics Express.

Now is the time for you to start thinking about your migration plan. The next ten months will pass by quickly.

Our team has created a few resources for you regarding the upcoming changes. They include:

We’re happy to work with you on your migration. Email us at info@revelwood.com.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Home » IBM

IBM Planning Analytics Tips & Tricks: Unpivot

by

Did you know you can unpivot data in IBM Planning Analytics?

Sometimes you’ll find yourself in a situation where you need to change the layout of data from a grid format to a vertical table format, this is called unpivoting the data.

I’ll show you how to do this via some new Excel formulas.

Consider this sample data set:

A screenshot of a spreadsheet Description automatically generated

The goal is to have a table with three columns: Invoice, Month, Data. Here’s how to do that step-by-step.

Step 1

Use the EXPAND formula to create an array of Invoices.  In this case, we are creating a 5 x 12 array (5 rows x 12 columns).

=EXPAND($A$2:$A$6,5,12)

This will generate this:

A white grid with black and green text Description automatically generated

Step 2

EXPAND doesn’t know what to do with the other columns, so we can use IFERROR to fill in the errors with a specified value. In this case, if there’s an error then we want to replace the error with the first column value.

=IFERROR(EXPAND($A$2:$A$6,5,12),$A$2:$A$6)

Now we have this:

A screenshot of a computer Description automatically generated

Step 3

Now we use the TOCOL function to make the grid of invoice numbers into a column

=TOCOL(IFERROR(EXPAND($A$2:$A$6,5,12),$A$2:$A$6))

That generates:

A number of binary code Description automatically generated with medium confidence

Step 4

We repeat the previous steps for the months. We want an array of 12 x 5 for the months. The end formula is:

=TOCOL(IFERROR(EXPAND($B$1:$M$1,5),$B$1:$M$1))

Step 5

Use TOCOL on the data points to pivot them into a columnar format.

=TOCOL($B$2:$M$6)

Now we have a live, formula-based unpivot model that will refresh when the data is changed.

A screenshot of a spreadsheet Description automatically generated

Bonus Tip!

You can make the formulas more dynamic by using COUNTA instead of hard coding the number of rows/columns in the EXPAND function.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: PAW Export Data

A Resolution You Should Actually Keep: Upgrade Your IBM Planning Analytics Environment

Popular IBM Planning Analytics Videos of 2024

Home » IBM

IBM Planning Analytics Tips & Tricks: PAW Export Data

by

While IBM Planning Analytics is an excellent database that allows you to easily slice and dice your data, there may be situations where you want to export data from the model.  Maybe you want to share information with someone who uses different software or maybe you want to use your data in another tool? PAW version 99 introduced the ability to export a data set from the PAW interface.

To export data, right-click on a cube within a modeling workbench, select the option to Import/Export and then select the option to Export Data.  

Once selected, you will be prompted to define the name of the .csv file that will be created.  You then can define the data to be exported by using an existing view and/or by defining the dimension details. For each dimension, you can define a hierarchy to use, a subset to use, or an element to use.

A screenshot of a computer Description automatically generated

You also have the ability to define filters for your numeric and string data (example: only show values greater than or equal to 100) and the ability to skip consolidated values, rule calculated values, and zero/blank values.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

A Resolution You Should Actually Keep: Upgrade Your IBM Planning Analytics Environment

Popular IBM Planning Analytics Videos of 2024

IBM Planning Analytics Tips & Tricks: PAW Export Dim Structure

Home » IBM

Popular IBM Planning Analytics Videos of 2024

by

We all love analytics, right? We took a look at the analytics on our videos to see which ones you and your peers watched the most. Here are our most popular IBM Planning Analytics videos – both on-demand webinars and Tips & Tricks:

1. Understanding the New Planning Analytics Engine in IBM Planning Analytics

Planning Analytics Engine is the next version of IBM’s cloud-based Planning Analytics environment. The Engine uses Planning Analytics’ existing modeling and reporting approaches while also simplifying administrative functionality such as backups and database resource maintenance. Watch this on-demand webinar to learn about the new Planning Analytics Engine.

2. IBM Planning Analytics Tips & Tricks: Create Database

Learn how to create a database efficiently. This video provides a step-by-step guide to setting up your database, ensuring a solid foundation for your data analysis and planning activities. Whether you are a beginner or are looking to refine your skills, these tips will help you create a robust and organized database.

3. IBM Planning Analytics Tips & Tricks: File Manager

In this video, we guide you through efficiently managing your files, streamlining your workflow and enhancing your data organization.

4. IBM Planning Analytics Tips & Tricks: Backups

Watch this video to learn effective backup strategies to ensure your valuable information is always safe and recoverable.

5. IBM Planning Analytics Tips & Tricks: High Availability

Ensure uninterrupted access and seamless performance with our high availability tips to IBM Planning Analytics. Watch this video to learn essential strategies to maintain system resilience and reliability, which helps you to avoid downtime and keep your data secure.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: PAW Export Dim Structure

IBM Planning Analytics Tips & Tricks: PAW Selector Menu

IBM Planning Analytics Tips & Tricks: PAW Thumb Cell Formatting

Home » IBM

IBM Planning Analytics Tips & Tricks: PAW Thumb Cell Formatting

by

Have you ever created an exploration in IBM Planning Analytics Workspace (PAW) and noticed the arrows at the beginning of your rows and columns? These are called thumb cells and are used as the equivalent of a “select all” button. For example, if I click on the down arrow at the top of my rows, then all elements in the rows will be highlighted.  

Below is an example of a view with two thumb cells: a row thumb that I clicked on (note the arrow pointing down) and a column thumb that I did not click on (note the arrow pointing right).

A screenshot of a computer Description automatically generated

Sometimes a thumb cell is great since it allows you to reference all of the elements in your exploration, but sometimes you may not want to see these additional gray boxes on the screen.  PAW version 99 introduced formatting options to modify the appearance of the thumb cells.

Within the format manager, you can modify the thumb options in both the column header and row header settings.

A screenshot of a computer Description automatically generated

Below is the same view with the row thumbs not showing and the column thumbs not showing the icon. You can see that that there is no longer a gray box above the rows and there is no longer an arrow icon to the left of the columns.

A screenshot of a data Description automatically generated

These formatting settings will allow you to customize the appearance of an exploration and, if preferred, eliminate boxes that may confuse your end users.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: PAW Exploration Icons

IBM Planning Analytics Tips & Tricks: High Availability in Version 12

IBM Planning Analytics Tips & Tricks: ASCIIOUTPUT

Home » IBM

IBM Planning Analytics Tips & Tricks: PAW Exploration Icons

by

Have you ever been in a situation where you wanted your users to reference an exploration or chart on your IBM Planning Analytics Workspace (PAW) page, but you didn’t want to give them full access to make changes? You can do this by editing the toolbar to include or exclude icons.

This setting is found within the Properties area within the Custom, Toolbar section. Here is a sample of the options for an exploration:

A screenshot of a computer Description automatically generated

Not all icons are configurable (for example, the zero suppression icon will always be available to users), but each item in the list uses a simple on/off trigger to determine if the icons should appear for end users.

Here is an example with the “Edit MDX” icon enabled:

And here is an example with the “Edit MDX” icon disabled:

As you can see, disabled icons will completely disappear from the list.

This approach provides an easy way to simplify the ribbon for your end users.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks

IBM Planning Analytics Tips & Tricks: High Availability in Version 12

IBM Planning Analytics Tips & Tricks: ASCIIOUTPUT

IBM Planning Analytics Tips & Tricks: Upcoming Changes to Planning Analytics

Home » IBM

IBM Planning Analytics Tips & Tricks: High Availability in Version 12

by

IBM’s Planning Analytics version 12 introduces a new feature known as high availability. It enables you to manage multiple database replicas in parallel, thereby enhancing both performance and reliability. 

Revelwood’s IBM Planning Analytics Practice Director Lee Lazarow explains high availability in one of our IBM Planning Analytics Tips & Tricks videos.

How High Availability Works

Load balancing and fault tolerance is at the core of high availability. When a user submits a request—be it updating a data point, executing a TurboIntegrator script, or conducting some other processing—the request is directed to one of the database replicas, known as the leader replica.

  1. 1. Role of the Leader Replica:
    • The leader executes all required changes, performs calculations, and manages processing tasks.
    • Once the leader completes these tasks successfully, it shares/sends the changes to all other replicas, ensuring that they are kept in sync.
  2. 2. Load Distribution:
    • By distributing user requests across multiple replicas, the system mitigates the risk of a single heavy processing task impacting overall performance. This load spreading enhances the user experience, particularly in environments with fluctuating demands.
  3. 3. Active Management of Replicas:
    • The engine takes an active role in monitoring and managing the health of each replica. If the leader becomes unavailable for any reason, another instance is automatically promoted to take over its responsibilities, ensuring continuity of service.
  4. 4. Dynamic Scaling:
    • One of the standout features of high availability is the ability to dynamically adjust the number of replicas based on current demands—all without requiring downtime. This flexibility is particularly useful for organizations with varying usage patterns:
      • During peak periods (e.g., month-end closings, budgeting cycles), you can scale up the number of replicas to accommodate increased user activity.
      • Conversely, during quieter periods, you can scale down, optimizing resource usage and costs.

Managing High Availability

Managing high availability in Planning Analytics is straightforward and user-friendly. Here’s a brief overview of how to configure and monitor this feature:

  1. 1. Accessing the Management Interface:
    • Within the administration interface, you can easily enable or disable high availability by selecting the relevant checkbox.
  2. 2. Configuring Replicas:
    • You can define the number of replicas you wish to create. As you increase the number of replicas, the system automatically adjusts memory usage based on your configuration. For instance:
      • If the original database requires 4 GB of memory, adding a replica would require an additional 4 GB.
  3. 3. Real-time Monitoring:
    • The system provides real-time feedback on memory usage and warns you if you attempt to allocate more replicas than your available resources can support. This proactive management ensures that you maintain optimal performance without overloading the system.

IBM Planning Analytics version 12’s high availability feature is an advancement in how organizations can manage their Planning Analytics environments. Users achieve greater efficiency, reliability and scalability by leveraging multiple replicas.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: ASCIIOUTPUT

IBM Planning Analytics Tips & Tricks: Upcoming Changes to Planning Analytics

IBM Planning Analytics Tips & Tricks: PAW Explorations and MDX

Home » IBM