Perspectives represent a view of a cube where some MDX entities (dimensions, members, levels...) are hidden and renamed.


Perspectives allow for hiding and renaming a set of MDX entities. They represent a 'limited' view of an underlying cube by removing and renaming entities such as dimensions, hierarchies, levels, members and measures among others. There are mainly used to offer end users a simplified version of a cube / schema making visible only part of the MDX entities.

How are they defined?

Perspectives are defined in the 'Advanced' tab of the Designer tab. For end users, they are no different from cubes. In icCube two main groups of perspectives are possible :

  • The default perspective, optional and unique, defines a perspective that will be applied to all available cubes.
  • No default perspectives, they defines a new 'view' that can be accessed as a cube from any front end

Perspectives are easy to define, you'll have to enter a list of MDX entities defining that you want to hide(-) or show (+). Follow an example of perspective :

The definition of a perspective can contain MDX functions (e.g. Filter() ) as well as standard MDX entities. Note however, that it is not possible at this point in time to use functions that access fact values (e.g. TopCount() function ). Follow an example of perspective definition:

     /* hide Product dimension */
      /* hide Time dimension */
     /* hide Countries and Politics hierarchy from Geography dimension */
     /* hide measure hidden */
     /* hide all members of [Geo].[city] level that contain an 'a' in his name */
     -Filter( [Geography].[Geo].[City].members, CONTAINS([Geography].[Geo].currentmember.name, 'a'))
     /* add Madrid as a visible city */
     + [Geography].[Geo].[City].[Madrid]
    /* rename a dimension, a hierarchy, a level, a member and a measure */
    rename [Geography] as [Location Dim]
    rename [Geography].[Geo] as [Location]
    rename [Geography].[Geo].[All-L] as [Planet level]
    rename [Geography].[Geo].[All-M] as [Earth]
    rename [Measures].[amount] as [Quantity]
    /* it's possible to redefine a hierarchy default member (since 4.8.2) */
    default [Geography].[Geo].[City].[Madrid]

How perspectives impact a model?

It is important to stress that hidden members will have no impact on aggregates values as opposed to security permissions. Therefore hiding a member will not change the value of his parent. Some general rules on how perspectives impact MDX :

  • Hidden entities are not directly available using XMLA or GVI interface (e.g. not visible in Excel Pivot)
  • Hidden entities are not returned by MDX set functions (e.g. members, descendants, children...)
  • Hidden entities are returned if directly entered as text string (e.g. [Geography].[Country].[Spain] is availabe even though hidden)
  • Hidden entities have no impact on facts or measures (e.g. the value of [Europe] will not change if we hide [Spain])
  • Renamed entities are not any longer accessible through their 'original' name
  • Removing members from facts is possible through security (access rights) permissions. They are available in Schema Permission use case (Administration/Roles)

Loading the perspective model and executing a request in the IDE will show the hierarchy without the hidden members. For example, executing the above request will show Paris, even though it is hidden, as is entered as a string, and the list of visible members of the [Geo] hierarchy:

  {[Location Dim].[Location].[Paris]} + [Location Dim].[Location].members  on 0
from [Cube OnlyGeo]

Advanced perspectives (since v6.5)

Sometimes you want to hide to the end user a hierarchy and its levels but would still like to be able to have access to all members of a level (e.g. [Geography].[Geo].members). The scenario is useful if you want to add some calculated measures that perform some operations on the members of the level. For this you can define a perspective as:

     /* hide Countries hierarchy */
     /* allow [Geography].[Geo].[City].members to return all members, level is still invisible */

Self contained working schema is available for download here.