ExploringTM1 is best known for its resources on all things TM1, Planning Analytics Workspace (PAW) and IBM business analytics. Lesser-known is its perfect symmetry: in the seventeen years (at time of writing) ExploringTM1 has existed, there has been no mention of asymmetry, let alone asymmetrical views.1
Although one could take advantage of this and pivot the blog into the #oddlysatisfying, perfect symmetry content space, instead, I’m going to have to break the blog’s symmetrical run and talk about asymmetrical views. Specifically:
- What asymmetrical views are, and how they can add power and nuance to your reporting
- Point-and-click and MDX methods for building asymmetrical views
- Use cases for asymmetrical views in reporting, planning and data visualisation
About Asymmetrical Views
Your usual views, built by dragging and dropping, are symmetric. When you have multiple dimensions on rows (or columns), every single member of your outer set has every single member of your inner set underneath it.
To borrow an example from set theory, it’s like a standard deck of 52 cards: there are four suits, and thirteen values in each. Every single value appears under every single suit.
Now consider an incomplete deck of cards – you’re missing a few, or perhaps you’re playing a game where you don’t use all the cards. This is an asymmetric view: some suits have different values underneath them.
Likewise, for an asymmetric view, the members of your outer set have different members of the inner set appearing underneath them.
Zero-suppressed views are often asymmetrical, but only incidentally: you can’t choose which exact combination of members you see, and the values of your hidden combinations must be zero. When you deliberately build asymmetry into your view, you have the power to show your viewers exactly what data they need to see for planning, reporting and decision-making.
How to Build Asymmetrical Views
PAW has many methods for you to build the asymmetrical views you need for your planning and reporting.
Target Selection Method
This is the easiest method for building asymmetrical viewers. It can be fiddly and slow if you need to a lot of it, but it’s good if you don’t know what MDX is (although you’re about to find out).
This is the method IBM recommends for asymmetrical views. They explain it well themselves, but in short:
- Build a symmetrical view with all the members you need. Ensure your dimensions are in the right place. It will have some extra members.
- Select members. If both rows and columns are asymmetrical, you’ll have to do one axis at a time: select members for either just rows or just columns, then repeat for the other.
- Click “Target Selection” on the PAW toolbar.
- Ctrl-click all the members you want to keep (or delete).
- Right click any selected member and click “Keep” (or “Hide”).
- For easy access and deployment, save the view by clicking “Save” on the toolbar and following the prompts.
MDX
MDX is the blueprint for your view. All views have a corresponding MDX query. Even if you drag and drop your view, PAW is building the MDX in the background. Click the “MDX” button on the PAW toolbar to see it, but be warned that computer-generated MDX is unformatted and often convoluted.
Despite how it looks, surmounting the MDX learning curve is well worth it. The flexibility, efficiency and power you get from MDX is a huge advantage.
Your MDX has three main parts (they may look familiar for SQL users):
SELECT: this determines which members appear on the rows and columns of your view. There’s one query each for rows and columns: the query for your columns is followed byON 0, and the query your rows are followed byON 1.FROM: the cube your data is coming from.WHERE: the values your context dimensions take.
Asymmetrical views are made in the rows and columns, so we’ll only look at the SELECT part.
Members are the smallest possible unit you can have in MDX. They’re written [Dimension].[Hierarchy].[Member].
Create a list of these, all with the same dimension and hierarchy. Separate the members with commas, and put them in curly brackets {}. This is a set.
{
[Value].[Value Hierarchy].[Four],
[Value].[Value Hierarchy].[Five],
[Value].[Value Hierarchy].[Six]
}2
When you multiply two sets, it puts the first set on the outside, and the second set on the inside, with each member of the first set having every member of the second set appear on the inside.
{
[Suit].[Suit Hierarchy].[Diamonds],
[Suit].[Suit Hierarchy].[Hearts]
} * {
[Value].[Value Hierarchy].[Four],
[Value].[Value Hierarchy].[Five],
[Value].[Value Hierarchy].[Six]
}
+
It’s not just one of Ed Sheeran’s more tolerable albums3, but one way to build asymmetrical PAW views with MDX. If you build an asymmetrical view with target selection, you’ll see “+”s throughout the code.
To create your asymmetrical view, you’ll need two symmetrical sets. They should have the same dimension in the same order. Then, simply place “+” in between them. This will put them one after the other in your view.
{
[Suit].[Suit Hierarchy].[Spades],
[Suit].[Suit Hierarchy].[Clubs]
} * {
[Value].[Value Hierarchy].[Five],
[Value].[Value Hierarchy].[Six]
}
+
{
[Suit].[Suit Hierarchy].[Diamonds],
[Suit].[Suit Hierarchy].[Hearts]
} * {
[Value].[Value Hierarchy].[Four],
[Value].[Value Hierarchy].[Five],
[Value].[Value Hierarchy].[Six]
}The order of operations in MDX is the same as for numbers: it “multiplies”4 the sets together first, and it adds these products second.
Instead of building the sets manually, you can also make an asymmetric view with any MDX function that outputs a set, no matter how how obfuscated:
[Suit].[Suit Hierarchy].[Black Suits].CHILDREN * {
[Value].[Value Hierarchy].[Five],
[Value].[Value Hierarchy].[Six]
}
+
{
[Suit].[Suit Hierarchy].[Diamonds],
[Suit].[Suit Hierarchy].[Hearts]
} * UNION(
TM1FILTERBYPATTERN(
[Value].[Value Hierarchy].MEMBERS,
'F*'
),
{
[Value].[Value Hierarchy].[Six]
}
)Except
The “+” method for asymmetrical views is additive and bottom-up: you start with a bunch of smaller symmetrical views and add them together. The EXCEPT method is subtractive and top-down: you start with one symmetrical view, then you remove the member combinations you don’t want. If your asymmetrical view is a symmetrical view with a few elements removed, then the EXCEPT method is much quicker.
Start with your symmetrical view. Put it in an extra set of {curly brackets} to make it a set:
{
{
[Suit].[Suit Hierarchy].[Spades],
[Suit].[Suit Hierarchy].[Clubs],
[Suit].[Suit Hierarchy].[Diamonds],
[Suit].[Suit Hierarchy].[Hearts]
} * {
[Value].[Value Hierarchy].[Four],
[Value].[Value Hierarchy].[Five],
[Value].[Value Hierarchy].[Six]
}
}
Then, get the combination of members you want to exclude. Put it in {curly brackets} also:
{
{
[Suit].[Suit Hierarchy].[Spades],
[Suit].[Suit Hierarchy].[Clubs]
} * {
[Value].[Value Hierarchy].[Four]
}
}
Now put these two sets in an EXCEPT clause: this gives you the first set, but with anything in the second set excluded:
EXCEPT(
{
{
[Suit].[Suit Hierarchy].[Spades],
[Suit].[Suit Hierarchy].[Clubs],
[Suit].[Suit Hierarchy].[Diamonds],
[Suit].[Suit Hierarchy].[Hearts]
} * {
[Value].[Value Hierarchy].[Four],
[Value].[Value Hierarchy].[Five],
[Value].[Value Hierarchy].[Six]
}
},
{
{
[Suit].[Suit Hierarchy].[Spades],
[Suit].[Suit Hierarchy].[Clubs]
} * {
[Value].[Value Hierarchy].[Fours]
}
}
)Save for easy access and deploy as you please.
Deployment Tips for Asymmetrical Views
I understand just how feverishly you’re longing to build asymmetric views in PAW, but your experience will be much smoother if you read the following tips first:
Synchronise with care: if you synchronise your asymmetrical dimensions, then you’ll lose your asymmetry. Synchronisation on different dimensions or different sheets is safe, however. Dynamic asymmetrical views are possible, but they’re involved enough for their own article…
Protect your books: the most important thing is good security, so your end users can’t edit the book where your asymmetrical view is. This way, if they drag and drop your asymmetry out of existence, they can reset the book to get it back. You can lock down your view further by selecting the view and going to Properties>Custom in the top-right corner. There, you can turn off “Allow access to Set editor”, as well as “Edit MDX” and “Show/hide overview” under “Toolbar”.
Let PAW do the hard work: you could write your entire MDX query from scratch, but it’s far quicker to start with an existing view. Create a symmetrical view with the dimensions in the right place, then edit your asymmetric dimensions into shape.
Avoid repeat fields on charts: when turning an asymmetrical view into a chart, using a “repeat” field will destroy the asymmetry. If a repeat field is inevitable, consider making duplicates of the chart instead, and fix the axis endpoints where necessary.
Choose the right method: your asymmetrical view will be much easier to build if your method aligns with your development practices and your desired end result:
| Target Selection | MDX + | MDX EXCEPT | |
| Coding required? | No | Yes | Yes |
| Can automate with TurboIntegrator? | No | Yes | Yes |
| Ideal view type | Small, minimal asymmetry | Multiple symmetrical views added together | Symmetrical view with a few member combinations excluded |
Use Cases for Asymmetrical Views
So you know what asymmetrical views are, and how to use them, but what for? Aside from cards, I’ve outlined a handful of their many use cases, both as views and as PAW visualisations.
Phased Reporting
Asymmetrical views allow you to seamlessly display current actual figures alongside future budget or forecast figures. Zero suppression could give you gaps in your chart, or it could hide your actual data, if it has zeroes in it. When you choose exactly which time periods to show, and which versions to show for each, your reporting is more precise, more predictable and more faithful to your data.
Column charts are the easiest to implement this way. Line and point charts are also effective, but ensure you have some overlap for lines: at least one time period (more for smooth line styles) should have data for both your versions. If your values at this junction aren’t close, you can use a WITH statement in your MDX to adjust the value of your data within the scope of your chart only.
Budget and Forecast Comparison
You don’t have to stop at one: add multiple different budget or forecast versions to your phased reporting chart, and your user can compare these versions with your actuals and with each other. All phased reporting formatting tips apply.
Summary Statistics
Time cannot hold your top-level reporting back: your audience needs these numbers fast, and you cannot force them into a simple, symmetrical timescale. When you use an asymmetrical view to display only the values and time periods you need, you save space and your audience’s time. If you’re converting your view into a chart, label your values for further clarity: click on the chart, then go to Properties > Visualization > Chart > Show value labels .
To Conclude
Asymmetrical views in PAW let you choose the exact combination of members in your views, via both point-and-click and MDX code. Asymmetrical views and charts derived from them elevate your reporting, giving your users exactly the data they need to empower their planning and decision-making. I’ve disturbed the symmetry on ExploringTM1, but I think it was worth it.
Notes
All card images from Pixabay, cover of Ed Sheeran’s “+” from Asylum Records and Atlantic Records under fair use. Charts and views created in IBM Planning Analytics.
- At time of writing, “asymmetry”, “asymmetrical” and “asym” all had no search results on exploringtm1.com. ↩︎
- In MDX, you can add spaces, tabs and new lines in between members as desired, so you could write
{[Value].[Value Hierarchy].[Four],[Value].[Value Hierarchy].[Five],[Value].[Value Hierarchy].[Six]}and get the exact same result. Once you click “OK”, PAW’s built-in MDX editor removes these superfluous spaces. However, they make your code much easier to read, as you will discover when you see enough auto-generated MDX. By formatting your code and perhaps storing a formatted version of it in a separate document, it’s easier to develop MDX collaboratively and quickly build more complex queries. ↩︎ - Lest I talked the talk without having walked the walk, I subjected myself to all seven of Mr Sheeran’s studio albums out at the time of writing. I found “x” and “Autumn Variations” slightly more bearable than “+”, but they sadly do not have predefined MDX functionality. I personally look forward to Ed’s upcoming album, “TM1DRILLDOWNMEMBER”. ↩︎
- The multiplication in MDX is actually the Cartesian product: the product of your two sets is every possible ordered pair where the first element comes from the first set and the second element comes from the second set. ↩︎
SELECT {your set here} ON 0,↩︎
{
[Month].[Month].[Jan],
} * {
} + {
} * {
}WITH MEMBER [Version].[Version].[Budget_}QSM] AS. The
IIF(
[Month].[Month].CURRENTMEMBER.NAME = "Jun",
[Version].[Version].[Actual],
[Version].[Version].[Budget]
)
SELECT {your set here} ON 0,
{
[Month].[Month].[Jan],
[Month].[Month].[Feb],
[Month].[Month].[Mar],
[Month].[Month].[Apr],
[Month].[Month].[May],
[Month].[Month].[Jun]
} * {
[Version].[Version].[Actual]
} + {
[Month].[Month].[Jun],
[Month].[Month].[Jul],
[Month].[Month].[Aug],
[Month].[Month].[Sep],
[Month].[Month].[Oct],
[Month].[Month].[Nov],
[Month].[Month].[Dec]
} * {
[Version].[Version].[Budget_}QSM]
}
ON 1_}QSMdisappears in your report or view, but it distinguishes the member you just created from globally-defined members of the same name. ↩︎WITH MEMBER [Version].[Version].[Forecast_}QSM] AS↩︎
IIF(
[Month].[Month].CURRENTMEMBER.NAME = "Jun" ,
[Version].[Version].[Actual] ,
[Version].[Version].[Forecast]
)
MEMBER [Version].[Version].[Aggressive Forecast_}QSM] AS
IIF(
[Month].[Month].CURRENTMEMBER.NAME = "Jun" ,
[Version].[Version].[Actual] ,
[Version].[Version].[Forecast] * 1.3
)
MEMBER [Version].[Version].[Conservative Forecast_}QSM] AS
IIF(
)
SELECT {your set here} ON 0,
{
} * {
} + {
} * {
} ON 1SELECT {your set here} ON 0,↩︎
{
[Version].[Version].[Actual],
} * {
} + {
} * {
} + {
} * {
}
