How to load data from Mondrian to Power BI Desktop
Tags: powerbi, mondrian, opensource
Published: 2021-03-05 10:22:52

However it’s impossible to explore [Mondrian](https://en.wikipedia.org/wiki/Mondrian_OLAP_server) cubes in [Power BI Desktop](https://powerbi.microsoft.com/en-us/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](https://github.com/SergeiSemenkov/eMondrian) version of Mondrian that is modified to support Excel and some other applications as clients.

First we have to design a [MDX](https://en.wikipedia.org/wiki/MultiDimensional_eXpressions) query. Following query gets sales amount data with dates and store information.

```mdx
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.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dphu9ul4umvs6mh29mtr.png)

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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f1xcm769spbee5jol802.png)

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

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sc1qvegtslwjpe9qlexx.png)

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*.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8sat1yl3uzjvkg3sqgza.png)

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

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/02fz79g6ag6inadfdj6x.png)

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

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vowlbfhcle5vg1g9jltu.png)

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

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q7t5ke9if4k73dpafq1f.png)

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.