When business users inquire about Planning Analytics for Excel (PAfE) or TM1 Perspectives, I often resort to the widely recognized phrase 'Excel on steroids' to effectively convey its true power and potential.
PAfE acts as a dynamic complement to their existing Excel skills, bridging the functional gaps between the two tools to boost productivity significantly. Rather than replacing Excel, PAfE expands its capabilities, empowering users to delve deeper into analysis and unlock untapped opportunities. This professional alliance between PAfE and Excel helps businesses leverage their existing skills while harnessing the advanced features and functionalities of PAfE to achieve exceptional insights and maximize efficiency.
This blog aims to showcase how Excel, in turn, can enhance the productivity of PAfE users by enabling more in-depth analysis and unlocking untapped potential. Through practical use cases, we will explore how leveraging Excel's capabilities alongside PAfE can amplify the analytical capabilities and deliver tangible benefits.
PAfE functions offer a wide range of capabilities for data analysis in Planning Analytics cubes. Functions like DBRW and DBRA are commonly used to extract specific data from cube cells and retrieve attribute information associated with cube members, respectively. These functions are essential for performing detailed analysis on cube data.
However, it's worth noting that there are currently no built-in functions available in PAfE specifically designed to analyze the members within a dimension directly. To perform more advanced operations on dimension members, one may need to rely on their MDX expertise. This requires a deeper understanding of MDX (Multidimensional Expressions) language, which might be outside the typical scope of an end user's domain and therefore the need for functions that enables them to perform such advanced actions without delving into the ‘realms’ of MDX.
Custom functions in Excel, also referred to as lambda functions, empower end users to create personalized calculations using the new LAMBDA function introduced in Excel 365. These functions offer unparalleled flexibility and simplicity as they enable users to define calculations directly within a formula, eliminating the necessity for VBA programming. Consequently, the barriers to creating custom functions based on individualized requirements have been eliminated.
By leveraging the power of lambda functions, users can tailor their calculations to their specific needs, resulting in enhanced productivity and streamlined workflows.
With the introduction of lambda functions, Excel users now have the freedom to craft their own calculations and algorithms, unleashing their creativity and enabling them to solve unique problems efficiently.
To address the limitations to PAfE in analysing the dimension members, I have developed a set of custom functions that combine
LAMBDA,
PAfE,
Dynamic Array,
MDX and
traditional Excel functions
These functions expand’s PAfE’s capabilities enabling users to gain insights from dimension members that was previously difficult or impossible to achieve.
Below is a list of functions I have created demonstrating how we can extend the PAfE’s capabilities beyond its current offerings:
Function: TM1FindDuplicates
Syntax: TM1FindDuplicates(Dim, SubsetOverride, [Consol], [Instance])
Dim: The name of the dimension.
SubsetOverride: The name of the subset. When both SubsetOverride and Consol arguments are defined, SubsetOverride takes precedence.
[Consol]: (Optional) The name of the consolidation. If provided, SubsetOverride should be left blank to use this argument.
[Instance]: (Optional) The name of the instance. If left blank, uses the primary server name that the user is authenticated through. This option is mandatory when using TM1 Perspectives.
Function: TM1FindExclusive
Syntax: TM1FindExclusive(Dim, SubsetOverride, Consolidation, [Instance])
The TM1FindExclusive function allows you to determine members that exist exclusively within a specific consolidation or subset.
By leveraging this function, you can easily identify and analyze the distinct members present in a consolidation or subset, aiding in data analysis, reconciliation, and error detection.
It takes the following arguments:
Dim: The name of the dimension.
SubsetOverride: The name of the subset. When both SubsetOverride and Consol arguments are defined, SubsetOverride takes precedence.
Consolidation: The name of the consolidation.
[Instance]: (Optional) The name of the instance. If left blank, uses the primary server name
that the user is authenticated through. This option is mandatory when using TM1 Perspectives.
Function: TM1FindMutuallyExclusive
Syntax: TM1FindMutuallyExclusive(Dim, Consol1, Consol2, [Instance])
This function allows you to identify members that exist exclusively in either of the two specified consolidations. This function compares the members of the two specified consolidations and returns a list of members that exist in one consolidation but not in the other.
It helps identify mutually exclusive members, enabling users to analyze differences and potential inconsistencies between consolidations.
It takes the following arguments:
Dim: The name of the dimension.
Consol1: The name of the first consolidation.
Consol2: The name of the second consolidation.
[Instance]: (Optional) The name of the instance. If left blank, uses the primary server name that the user is authenticated through. This option is mandatory when using TM1 Perspectives.
Function: TM1FindMissingEle
Syntax: TM1FindMissingEle(Dim, SourceConsol, TargetConsol, [Instance])
The TM1FindMissingEle function is used to identify missing members in the target consolidation compared to the source consolidation.
With the TM1FindMissingEle function, you can easily identify members that exist in the source consolidation but are missing in the target consolidation. This function helps you analyze and reconcile differences between consolidations, ensuring data consistency and accuracy.
It takes the following arguments:
Dim: The name of the dimension.
SourceConsol: The name of source consolidation.
TargetConsol: The name of the target consolidation.
[Instance]: (Optional) The name of the instance. If left blank, uses the primary server name that the user is authenticated through. This option is mandatory when using TM1 Perspectives.
Function: TM1SearchEle
Syntax: TM1SearchEle(Dim, Filter, [Instance])
The TM1SearchEle function enables you to search for members within a dimension based on wildcard filters such as "?" and "*".
With the TM1SearchEle function, you can easily locate members within a dimension that match specific patterns or criteria. This function utilizes wildcard filters to provide flexible and powerful searching capabilities, allowing you to find members based on varying combinations of characters.
It takes the following arguments:
Dim: The name of the dimension.
Filter: The wildcard filter to apply for the search. Use "?" to represent a single character and "*" to represent any number of characters.
[Instance]: (Optional) The name of the instance. If left blank, uses the primary server name that the user is authenticated through. This option is mandatory when using TM1 Perspectives.
In conclusion, the examples provided showcase the power of custom functions in PAfE to extend and enhance its analytical capabilities. These functions are just a glimpse of what can be achieved by leveraging the flexibility of PAfE and combining it with other tools such as LAMBDA, Dynamic Array, MDX, and traditional Excel functions.
However, it's important to note that there are certain limitations in PAfE, such as the current lack of support for hierarchies within MDX in the TM1Set function. This limitation restricts the full potential of custom formulas that could compare hierarchies and ensure the alignment of leaf members. Nonetheless, once this issue is addressed, it opens up exciting possibilities for even more advanced analyses and comparisons.
I have provided a template file for you to explore and experiment with these custom functions in your own TM1 model. I encourage you to test and provide feedback on the template, sharing your thoughts and perspectives on how these custom functions can be further improved and tailored to meet your specific needs.
As an end user, your insights and feedback are invaluable in driving enhancements and pushing the boundaries of PAfE's analytical capabilities. By collaborating and exchanging ideas, we can collectively contribute to the growth and evolution of PAfE, ensuring it becomes a powerful tool for data analysis and decision-making.
I look forward to hearing your comments, suggestions, and experiences as you explore the template and work towards overcoming the limitations of PAfE. Together, we can unlock new possibilities and empower users to extract meaningful insights from their TM1 models.
Download PAfE custom formula template