How to load data from Mondrian to Power BI Desktop

However it’s impossible to explore Mondrian cubes in Power BI Desktop through live connection, there is a way how you can load data from Mondrian by queries.

Let’s load data from the Mondrian sample database Foodmart and create a simple interactive report. This example is using the eMondrain version of Mondrian that is modified to support Excel and some other applications as clients.

First we have to design a MDX query. Following query gets sales amount data with dates and store information.

SELECT
[Measures].[Store Sales] on COLUMNS,
CrossJoin( [Time].[Month].Members, [Store].[Store Name].Members ) on ROWS
FROM [Sales]

Mondrian server can return data in two formats. First format is CellSet data format which is convenient in case of using client controls like Pivot Table.

The second format is tabular format. It’s a simple table and is convenient when you are designing a report with tables and charts. This format is used by Power Bi when it loads data from Mondrian.

In the Power BI Desktop application we create a new report and select the menu Get data - Analysis Services.

In SQL Server Analysis Services database dialog we fill in the Server field as a connection string to the Mondrian server. Fill in database name in field Database. Database is a required field and is case sensitive (in our case it's FoodMart). Pick the Import radio button. In the MDX or DAX query (optional) field copy our MDX query and push the Ok button.

To try this example you can use an online eMondrian server with a sample database. The server address is https://ssemenkoff.dev/emondrian/xmla.

Preview window will appear where you can see part of the query result data in tabular format. Click the Load button.

Go to the Data tab, rename columns and change Sales column data type from Text to Decimal number.

Let’s return to the Report tab and add some visualizations. In this example we use Slicer, Pie chart and Table controls.

To refresh your report and load newly added data you can always use the Refresh button.

As you see, you can add to your Power BI Desktop reports new ones which use data from the Mondrian OLAP server.

If you have any questions or suggestions, feel free to contact me at siarhei.semiankou@gmail.com.