Dimension Member Count Matrix

When managing a system it is useful to have a table of dimensions and the cubes that they are valid in like the example below. The numbers are a count of the members in the dimension that are valid for that cube.

To get the grid, I have a groovy script to produce the CSV file below, that I then pivot in Excel.

Here is the groovy script to create the output. Just paste into the script editor and run. It will create a file “Dimension_Member_Count.csv” in the inbox/outbox folder.

/****************************************************************************
Name: iEPM_System_Dimension_Member_Count
Desc: List dimension member count for each cube in the ouput


Author: Nic Vos 
Date: 
Modifications: Date:Who:What

****************************************************************************/

// Get a list of all cubes in the application
List<Cube> cubes = operation.application.getCubes()

List<Dimension> allDims = operation.application.getDimensions()

Writer exportData = createFileWriter("Dimension_Member_Count.csv")
exportData.write ("CUBE,DIMENSION,MEMBER COUNT" + "\n")

println "CUBE,DIMENSION,MEMBER COUNT"

cubes.each { Cube onecube ->
  allDims.each { Dimension dimname ->

    def dimProps = dimname.getEvaluatedMembers("""IDescendants("${dimname.toString()}")""", onecube) as List<Member>


    if(operation.application.hasDimension("$dimname", onecube)){
// Output to file and Job Console
        println "$onecube,$dimname,${dimProps.size()}"
        exportData.write ("$onecube,$dimname,${dimProps.size()}\n")

    }
  }
}
exportData.close()