MDX views for Dimension and Cube information

With the deprecation of Architect and Perspectives, the properties window that shows the user information about the Dimensions and Cubes will no longer be available and does not have a replacement in PAW currently.

For Dimensions, this is what the properties window would currently show to the user:

Maybe not all the columns are that useful but typically the number of Elements and Subsets as well as the Memory Used is what people typically want to review and sort by.

Similarly for Cubes, we can see the information below:

Memory used is probably what most people look at but may be useful to know some other information like number of Dimensions etc.

With these two views falling away, how can we build something similar in PAW to give this information and possibly additional information?

We can leverage MDX views with calculated members to try and simulate some of the above columns.

Dimension Information

We can leverage the }DimensionProperties cube as the basis for rendering the Dimension view. If you create a new workbench and navigate to the Control Objects then Cubes and add a view for }DimensionProperties, we can see the default layout.

Open the MDX and paste in the following code:

WITH
MEMBER [}DimensionProperties].[}DimensionProperties].[Elements] AS 
	Count(
			StrToSet( "TM1SubsetAll([" + [}Dimensions].[}Dimensions].CurrentMember.Name + "])"
				)
		),
	SOLVE_ORDER = 1, FORMAT_STRING = '#,##0.00;(#,##0.00)' 

MEMBER [}DimensionProperties].[}DimensionProperties].[Members] AS 
	Count(
		StrToSet( "[" + [}Dimensions].[}Dimensions].CurrentMember.Name + "].[" +
			[}Dimensions].[}Dimensions].CurrentMember.Name +"].Members")
		),
	SOLVE_ORDER = 2, FORMAT_STRING = '#,##0.00;(#,##0.00)' 
	
MEMBER [}DimensionProperties].[}DimensionProperties].[N_Elements] AS 
	Count(
			StrToSet( "{Filter(TM1SubsetAll([" + [}Dimensions].[}Dimensions].CurrentMember.Name + "]), " +
				"[" + [}Dimensions].[}Dimensions].CurrentMember.Name + "].CurrentMember.Properties('Element_Type')='1')}"					
				)
		),
	SOLVE_ORDER = 3, FORMAT_STRING = '#,##0.00;(#,##0.00)' 	

MEMBER [}DimensionProperties].[}DimensionProperties].[C_Elements] AS 
	Count(
			StrToSet( "{Filter(TM1SubsetAll([" + [}Dimensions].[}Dimensions].CurrentMember.Name + "]), " +
				"[" + [}Dimensions].[}Dimensions].CurrentMember.Name + "].CurrentMember.Properties('Element_Type')='3')}"					
				)
		),
	SOLVE_ORDER = 4, FORMAT_STRING = '#,##0.00;(#,##0.00)' 
	
MEMBER [}DimensionProperties].[}DimensionProperties].[S_Elements] AS 
	Count(
			StrToSet( "{Filter(TM1SubsetAll([" + [}Dimensions].[}Dimensions].CurrentMember.Name + "]), " +
				"[" + [}Dimensions].[}Dimensions].CurrentMember.Name + "].CurrentMember.Properties('Element_Type')='2')}"					
				)
		),
	SOLVE_ORDER = 5, FORMAT_STRING = '#,##0.00;(#,##0.00)' 	
	
MEMBER [}DimensionProperties].[}DimensionProperties].[Sets] AS 
		Count(
			StrToSet( "TM1SubsetAll([}Subsets_" + [}Dimensions].[}Dimensions].CurrentMember.Name + "])")
		),
	SOLVE_ORDER = 6, FORMAT_STRING = '#,##0.00;(#,##0.00)' 	
SELECT
{
	[}DimensionProperties].[}DimensionProperties].[Elements],
	[}DimensionProperties].[}DimensionProperties].[Members],
	[}DimensionProperties].[}DimensionProperties].[N_Elements],
	[}DimensionProperties].[}DimensionProperties].[C_Elements],
	[}DimensionProperties].[}DimensionProperties].[S_Elements],
	[}DimensionProperties].[}DimensionProperties].[Sets]
} ON 0,
{[}Dimensions].[}Dimensions].MEMBERS} ON 1 
FROM [}DimensionProperties]

When you submit the MDX, a view similar to mine below should be returned:

You can dissect the code above but you will notice that we have separate columns from Elements and Members and then a section with three columns showing a count of the different types of elements.

The Sets column will show a count of the sets associated with the dimension. Where there are no sets, an error is returned. Currently I am unable to trap for this event but should anyone know how, would love to hear.

Cube Information

We can use the }StatsByCube cube as the basis for cube information as this cube contains a dimension for all the cubes in the instance. 

To get relevant statistics, ensure that you have enabled the performance monitor in PAW Administration under the Configuration, Administration, Default options:

In the below view there are essentially three sections of information:

• Cube Statistics showing some columns from the original cube with an additional calculated member to give an indication of memory used per populated cell
• Cube Property related information like Logging, Rules Stats etc.
• Cube Attributes for additional information not in the system cubes

The Cube Statistics show a calculated member called Memory per Populated Cell. This is essentially created using the following logic:

(Memory Used for Input Data]) /

     ( Number of Populated Numeric Cells] +

       Number of Populated String Cells] +

       Number of Fed Cells]

The lower the number, the better optimised is the dimension order of the cube with respect to storing data. This may not be the best for performance though. Have a look at OptimusPy should you need to look at reordering dimensions using a tool to test both memory and performance optimisation. This is a whole topic on its own and for those wanting to understand more, check out Hubert Heijkers' How TM1 Really Works video.

The Cube Properties section shows useful information like Logging. This could be further enhanced using conditional formatting with a red icon to highlight any cubes where logging is not "YES".

Similarly, it is useful to see if the cube has been locked by an Administrator or other user, what VMT and VMM settings are configured, if any.

In my model, I have created two Cube Attributes called Include in Snapshots and Purpose. These are included in the below MDX to show that you can add additional columns into the view as required. Where your model does not have these, you would need to remove from the MDX script.

The MDX is thus a combination of reading from the }StatsByCube, }CubeProperties and }CubeAttributes cubes to build a view for us:

WITH
	MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Memory Per Populated Cell] AS
		([}StatsStatsByCube].[}StatsStatsByCube].[Memory Used for Input Data]) /
		( [}StatsStatsByCube].[}StatsStatsByCube].[Number of Populated Numeric Cells] +
		  [}StatsStatsByCube].[}StatsStatsByCube].[Number of Populated String Cells] +
		  [}StatsStatsByCube].[}StatsStatsByCube].[Number of Fed Cells]
		), FORMAT_STRING="#,##0.00"
			
	MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Logging] AS
		[}CubeProperties].(
			StrToMember("[}Cubes].[}Cubes].[" + [}PerfCubes].[}PerfCubes].CurrentMember.Name + "]"),
			[}CubeProperties].[}CubeProperties].[Logging])
			
	MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Rule Stats] AS
		[}CubeProperties].(
			StrToMember("[}Cubes].[}Cubes].[" + [}PerfCubes].[}PerfCubes].CurrentMember.Name + "]"),
			[}CubeProperties].[}CubeProperties].[RULE_STATS])

	MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Lock] AS
		[}CubeProperties].(
			StrToMember("[}Cubes].[}Cubes].[" + [}PerfCubes].[}PerfCubes].CurrentMember.Name + "]"),
			[}CubeProperties].[}CubeProperties].[LOCK])

	MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[VMT] AS
		[}CubeProperties].(
			StrToMember("[}Cubes].[}Cubes].[" + [}PerfCubes].[}PerfCubes].CurrentMember.Name + "]"),
			[}CubeProperties].[}CubeProperties].[VMT])

	MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[VMM] AS
		[}CubeProperties].(
			StrToMember("[}Cubes].[}Cubes].[" + [}PerfCubes].[}PerfCubes].CurrentMember.Name + "]"),
			[}CubeProperties].[}CubeProperties].[VMM])
			
	MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Measures Dimension] AS
		[}CubeProperties].(
			StrToMember("[}Cubes].[}Cubes].[" + [}PerfCubes].[}PerfCubes].CurrentMember.Name + "]"),
			[}CubeProperties].[}CubeProperties].[MEASURES_DIMENSION])
	
	MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Time Dimension] AS 
			[}CubeProperties].(
			StrToMember("[}Cubes].[}Cubes].[" + [}PerfCubes].[}PerfCubes].CurrentMember.Name + "]"),
			[}CubeProperties].[}CubeProperties].[TIME_DIMENSION])
	
	MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Include in Snapshots] AS
			[}CubeAttributes].(
			StrToMember("[}Cubes].[}Cubes].[" + [}PerfCubes].[}PerfCubes].CurrentMember.Name + "]"),
			[}CubeAttributes].[}CubeAttributes].[Include in Snapshots]),
			FORMAT_STRING="#"
	
	MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Purpose] AS
			[}CubeAttributes].(
			StrToMember("[}Cubes].[}Cubes].[" + [}PerfCubes].[}PerfCubes].CurrentMember.Name + "]"),
			[}CubeAttributes].[}CubeAttributes].[Purpose])

SELECT {
	[}StatsStatsByCube].[}StatsStatsByCube].[Total Memory Used],
	[}StatsStatsByCube].[}StatsStatsByCube].[Memory Per Populated Cell],
	[}StatsStatsByCube].[}StatsStatsByCube].[Memory Used for Input Data],
	[}StatsStatsByCube].[}StatsStatsByCube].[Number of Populated Numeric Cells],
	[}StatsStatsByCube].[}StatsStatsByCube].[Number of Populated String Cells],
	[}StatsStatsByCube].[}StatsStatsByCube].[Number of Fed Cells], 
	[}StatsStatsByCube].[}StatsStatsByCube].[Logging],
	[}StatsStatsByCube].[}StatsStatsByCube].[Rule Stats],
	[}StatsStatsByCube].[}StatsStatsByCube].[Lock],
	[}StatsStatsByCube].[}StatsStatsByCube].[VMT],
	[}StatsStatsByCube].[}StatsStatsByCube].[VMM],
	[}StatsStatsByCube].[}StatsStatsByCube].[Measures Dimension],
	[}StatsStatsByCube].[}StatsStatsByCube].[Time Dimension],
	[}StatsStatsByCube].[}StatsStatsByCube].[Include in Snapshots],
	[}StatsStatsByCube].[}StatsStatsByCube].[Purpose]
	} ON 0, 
ORDER(
	{TM1FILTERBYLEVEL(TM1SubsetAll([}PerfCubes].[}PerfCubes]), 0)},
	[}StatsByCube].([}TimeIntervals].[}TimeIntervals].[Latest],
	[}StatsStatsByCube].[}StatsStatsByCube].[Total Memory Used]), BDESC) ON 1 
FROM [}StatsByCube] 
WHERE ([}TimeIntervals].[}TimeIntervals].[LATEST])

With those two views that can be saved into a PAW book for referral, the loss of the properties window in Architect should not be too much of a setback.

Besides being able to view the Security Owner for any Reservations and Memory Used for dimensions and cubes in Architect, we probably can get more information and more targeted insights leveraging an MDX view to return Dimension and Cube information.

What are your thoughts?

For more on MDX views, please see my blog series on Learning MDX view in Planning Analytics

#PlanningAnalyticsWorkspace

#IBMChampion