Get dimensions of MDX query

[Pablo S]

Hi Xavier, I'm trying to analyze the members and dimensions of the following MDX query on FoodMart:

select {[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} ON COLUMNS, {[Product].[All Products].Children} ON ROWS from [Sales]

The code below gets the members correctly, however I couldn't figure out how to get the dimensions:

            val model = getModel(configuration)
            model.setMdx(req.mdx)
            model.initialize()
            
            val cellSet = model.getCellSet()
            val axes = cellSet.getAxes()
            val columns = axes.get(0)
            val rows = axes.get(1)
            val rowPositions = rows.getPositions()
            val rowLength = rowPositions.length
            val colPositions = columns.getPositions()
            val colLength = colPositions.length
            
            println("cols members:")
            colPositions.foreach { c => c.getMembers.foreach{  m => println( m.getName ) } }
            println("row members:")
            rowPositions.foreach { r => r.getMembers.foreach{  m => println( m.getName ) } } 

prints

cols members:

Unit Sales

Store Cost

Store Sales

row members:

Drink

Food

Non-Consumable

Should I get the dimensions using Olap4J API instead of Pivot4J? 

Thanks

Pablo

[Pablo S]

This is how I got the dimensions using Olap4J API:

            val colDims = columns.getAxisMetaData.getHierarchies.map {h => h.getDimension.getName}

            val rowDims = rows.getAxisMetaData.getHierarchies.map {h => h.getDimension.getName}

First get axis hierarchies and then take the name of the related dimension.

[Xavier Cho]

Sorry I couldn't write a reply earlier.

Yes, it's the right way to extract dimension names from an axis. And you can make it a bit shorter as :

val colDims = columns.getAxisMetaData.getHierarchies.map(_.getDimension.getName)
val rowDims = rows.getAxisMetaData.getHierarchies.map(_.getDimension.getName)

Or better,

val dimensionsIn = (axis: CellSetAxis) => 

  axis.getAxisMetaData.getHierarchies.map(_.getDimension.getName)

val colDims = dimensionsIn(columns)

val rowDims = dimensionsIn(rows)

Or fancier :)

implicit class ExtendedCellSet(axis: CellSetAxis) {

  def dimensions = (axis: CellSetAxis) = 

  axis.getAxisMetaData.getHierarchies.map(_.getDimension.getName)

}

val colDims = columns.dimensions

val rowDims = rows.dimensions

2016년 8월 2일 화요일 오전 11시 42분 55초 UTC+9, Pablo S 님의 말:

[Pablo S]

Thanks! great answer.