It is possible to set up dimension properties in an OLAP cube dimension that allows end users to see certain properties that are associated with a dimension value without having these properties available in the dimension hierarchy. This allows users to access dimension properties in a report without cluttering the user interface with dozens of levels that would never be used for grouping. It is also possible to disable the use of hierarchies for these properties which will help to improve performance as well because the OLAP database no longer needs to calculate and process values based solely on these properties.
How to Define Dimension Properties
The dimension property to be used must first be added to the dimension table in the data warehouse.
The example used in this article will involve adding the ability for end users to see the Phone No. associated with customers.
As displayed in the screenshot below, the Phone No. field has been added to the data warehouse from the Customer table in the data source:
A new dimension level is then added to the Customer dimension that represents the Phone No field that was previously added in the data warehouse. This is accomplished by right-clicking the Customer dimension and selecting Add Dimension Level.
A name is given to the dimension level and the Key Table and Key Column are then specified.
When left as a standard dimension level, Phone No would be able as a level in the Customer dimension that could be used to build a hierarchy and aggregations would be calculated for the Phone No dimension level. Since these are unnecessary, the dimension level can be hidden and the use of this dimension level as a hierarchy can be disabled.
To accomplish this, both the Visible and Hierarchy Enabled check boxes can be unchecked:
Press the OK button to close and save the new dimension level for Phone No.
It is important that the dimension level that is being created as a dimension property be directly associated with the dimension key under the Dimension Level Relations screen. It will be automatically associated with the key level by default.
The relevant dimensions and cubes can now be deployed and executed to physically include the property member in the dimension.
How to Use Dimension Properties in Excel
Dimension properties are meant to be used by the end users to display information related to a dimension level without including the dimension property in a hierarchy or having data aggregated based on the dimension property.
Dimension properties can generally only be displayed when the key level of the dimension is being used
To add dimension properties to a report, right-click in the pivot table and navigate to Show Properties in Report. This will present a list of properties that can be used for this dimension.
The end result is that the Phone No for each customer can be retrieved from the cube without having this visible in the normal dimension selection pane and without aggregations being performed for the dimension property in the cube.