Note that PAW adds the braces ( {…} )when you save the set and are not shown in the above example but should be there to make a valid set in MDX.

What are the differences between Sets and Views

We have seen that Sets contain zero or more members in a specific dimension and a Tuple relates to a specific cell but what then is a view?

A view is simply one or more Tuples or cells of data, a collection of cells if you want, or cell sets. For the most part users work with cell sets in a 2 dimensional view, in an X and Y style matrix. This is analogous to sheets in Excel where members may be listed on the rows and columns and at their intersection, a value is seen.

In TM1/Planning Analytics, this goes further as we are dealing with cubes where we typically work with cubes that contain more than 2 dimensions. To represent the cells within a cube in a 2 dimensional world, we need to query the data using an MDX expression.

The query allows us to define what we would like to see on the rows, columns and in the context area which is used as a filter on specific members. Dimensions are not limited to one on rows and another on columns but can be nested as required.

In Architect and Perspectives, users typically opened a native view and then dragged dimensions between the rows, columns and context areas, nesting and stacking dimensions as needed. Once a suitable view was obtained, that view could be saved and reused at a later date.

If the user reopened their view and any dimensions were linked to sets that were dynamic or based on MDX, the MDX would update and potentially the user would see different results. If for example the view was linked to a set on the Period dimension called Current Period and this contained the current month, opening the view and thus set in the following month, one would expect to see the results differ due to this change.

An MDX View is similar to the native view with respect to being able to save and reopen these at a later date. Both can be linked to sets, dynamic or static, ensuring that when the view is reopened, members shown within each dimension are updated.

MDX and native views are similar in many respects e.g. being able to save them, alter them, use them as sources for imports, exports etc.

MDX views however give some more flexibility that native views do not:

Calculations can be added into MDX views to create new derived members e.g. a variance between Actual and Budget or a Gross Margin %, where these do not exist in the cube as members and are only required for analysis. More on this later but you can also add data from other cubes e.g. attributes or add in synthetic members that are notes or whitespace.

Additional filtering could be added to a base set being used in the view with additional MDX around the underlying set. Using a native view would require you to create a new set and save it.

Asymmetrical views can be thought of as unbalanced. Where you have multiple dimensions nested, typically members from the first dimension are shown then members from the next dimension are repeated for each member in the first dimension.
Asymmetrical views allow you to choose which member combinations you would want to see with repeating or showing irrelevant members and values to users.

MDX views can be programmatically generated using MDX code. Native views require assigning sets to each dimension and then assigning those dimensions and sets to rows, columns and context areas.

View ordering can be configured by specifying the necessary MDX to nest the dimensions on rows, column and context. When extracting data from an MDX view, this order is retained whereas extracting from a native view, the cube’s dimension order is used. More on this in the future.

Constructing a basic MDX view

A basic MDX view typically has the below syntax with one on columns, another on rows, a cube directive and a filter relating to the members in each of the other dimensions to be used in the view: