Planning Analytics

 View Only

MDX views for Dimension and Cube information

By George Tonkin posted Thu June 27, 2024 03:11 PM

  

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}QSM] AS 
		Count(
			StrToSet( "TM1SubsetAll([}Subsets_" + [}Dimensions].[}Dimensions].CurrentMember.Name + "])")
		),
	SOLVE_ORDER = 6

MEMBER [}DimensionProperties].[}DimensionProperties].[Sets] AS  	
	[}DimensionProperties].[}DimensionProperties].[Sets}QSM] + null,
	SOLVE_ORDER = 7, 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. I have updated the code per comment by Paul C.

Using a "query scoped member" as a temporary or intermediate variable I can then add a null to the Set count and where it would show an error, now shows a blank. Thanks Paul.

Just a note on "query scoped members" - by adding }QSM as a suffix to a member, the member is hidden from the set editor as it is defined purely as a temporary member that may form part of another calculation and would not want to be added to the view. In our case, we would not want to show it as it would show the error message.

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

4 comments
54 views

Permalink

Comments

Fri August 30, 2024 08:42 AM

Thanks for the feedback Paul!

And thanks for the idea of using an intermediate variable then adding zero to it. This works and at least masks the error giving a cleaner view.

Just adds more hoops to jump through and hope too that in the future we get enhanced functionality. I know V12 should have enhancements coming...

Edit: Instead of adding 0 (zero) adding null works too...

Fri August 30, 2024 08:28 AM

Hi George,

Thanks for the amazing work documenting MDX expressions. I've stumbled upon the same issue with error handling. It would be great if IBM introduces an ISERROR() function like other software using MDX queries. I've managed to work around the issue by creating a calculation on top of the existing one that simply adds a 0 to the previous calculation. This results in the error being converted to an empty string. You can even go further and test that empty string result through a IIF() function, this will allow you to display a custom error message.

Tue July 09, 2024 06:15 AM

Thanks for the feedback Mark - sounds like a good solution but will need to play with it.

Tue July 09, 2024 04:40 AM

Hi George,

The only way I could address the error in the sets column was to add another member expression which looked at sets and said if is greater than zero then return the sets column, otherwise return zero. As below (see solve order 6 and 7) and then remove the original sets member from the Select statement:

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].[SetsA] AS COUNT(STRTOSET("TM1SubsetAll([}Subsets_" + [}Dimensions].[}Dimensions].CURRENTMEMBER.NAME + "])")), SOLVE_ORDER = 6, FORMAT_STRING = '#,##0.00;(#,##0.00)' MEMBER [}DimensionProperties].[}DimensionProperties].[Sets] AS IIF([}DimensionProperties].[}DimensionProperties].[SetsA] > 0 , [}DimensionProperties].[}DimensionProperties].[SetsA] , 0), SOLVE_ORDER = 7, 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]