DEV Community: Sergei Semenkov

Planning in Excel with Writeback

Sergei Semenkov — Mon, 05 Sep 2022 18:26:30 +0000

Check our newest video on how eMondrian can support you to decentralize your planning and forecasting activities for your organization.

Our example from today is a beverages company which operates in many different regions and has plenty of stores. We are going to quickly show you how you could outsource the initial planning for the sales per product to the store managers.

This video was created by Leandro Aeschlimann. His company Consenso specialises in implementing data analytics tools and data infrastructure.

eMondrian is a free and open source alternative to Analysis Services from Microsoft which you can install on Linux and Windows.
It allows you to build cubes and consolidate the tables of you underlying data sources to a data model and connect to it from various client tools to visualize & analyse your data in a more user friendly way.

Welcome to the free open-source OLAP server project

Sergei Semenkov — Mon, 15 Nov 2021 13:59:06 +0000

Welcome to the site of the eMondrian project.

eMondrian is a free open-source OLAP server. It is based on the Mondrian project. OLAP server allows you to represent your database as a multidimensional space with dimensions and measures. It hides the complexity of underlying tables and their relations and allows you to interactively analyze data from multiple perspectives. eMondrian server can run on Windows and Linux operating systems.

eMondrian supports XML for Analysis (XMLA) standard and OLE DB for OLAP at the level that makes it possible to connect to it from client tools such as Microsoft Excel, Power BI, Tableau and many others. The logic of the original Mondrian was changed in order to improve performance of queries specific to these clients and to support clients’ features, for example Excel’s sessions, calculated members and sets.

Any database that has a JDBC driver can be the source for eMondrian. eMondrian server is a Relational OLAP (ROLAP) server that means it always shows real time data from a source. This server runs queries written in the MDX language, reads data from a source database and presents results in a multidimensional format.

The most efficient way is to use column store databases as data sources for eMondrian. For example, ClickHouse could run as a powerful and fast query engine while eMondrian works as a proxy representing data as cubes and executing MDX queries.

A simple way to import data to Power BI from Mondrian

Sergei Semenkov — Sun, 01 Aug 2021 16:22:05 +0000

In the previous article I described how to import data to Power BI using MDX query. A new version of eMondrain allows you to load data from Mondrian without any query, using only Power BI designer. eMondrain is a version of Mondrian that is modified to support Excel and some other applications as clients.

As an example, we will load data from the Mondrian sample database Foodmart.

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. Pick the Import radio button 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.

Navigator window will appear. There you have to pick up columns that you want to see in your report. While you are choosing columns you will see a data preview on the right side of the window. As you finish push the Load button.

Data will be loaded to our report and available columns will appear on the right side of the application.

This way of loading data from Mondrian OLAP server to Power BI is very simple and doesn’t need any knowledge of a query language.

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

How to load data from Mondrian to Power BI Desktop

Sergei Semenkov — Fri, 05 Mar 2021 10:22:52 +0000

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.

How to explore ClickHouse data via Excel PivotTable using Mondrian

Sergei Semenkov — Wed, 20 Jan 2021 12:39:26 +0000

ClickHouse is a very powerful OLAP engine. Microsoft Excel is one of the world's most popular and powerful business programs. There are several ways to bring ClickHouse data to Excel spreadsheets.

One way to do this is using ClickHouse ODBC driver. In order to do this you have to install the ODBC driver and create ClickHouse data source in Excel. Then you can explore data as tables or you can run a query on ClickHouse and browse results.

The other way to explore ClickHouse data in Excel is using Mondrian. This approach allows you to describe ClickHouse data as a multidimensional model and give access to this model through XMLA standart. It also allows client applications to run MDX queries on ClickHouse data. You represent ClickHouse data as cubes and dimensions and then browse those cubes in Excel PivotTable.

This article describes how to install Mondrian, create a Mondrian datasource for ClickHouse database and connect Excel PivotTable to this datasource.

We will use the eMondrian fork of the Mondrian ROLAP engine. The main feature of this version is support of ADOMD.NET and Excel clients. eMondrian already includes ClickHouse driver.

Mondrian can be hosted on different java servers (Apache Tomcat, Jetty, Tiny Java Web Server). This example describes the case with Ubuntu operating system and Apache Tomcat java server.

In this example Tomcat is installed in /opt/tomcat/latest/ directory.

You have to change the following command to your Tomcat path.



export TOMCATDIR="/opt/tomcat/latest"

Next commands download the Mondrian archive, copy it to Tomcat directory and restart Tomcat java server.



sudo wget https://github.com/SergeiSemenkov/eMondrian/releases/latest/download/emondrian.war
sudo cp emondrian.war $TOMCATDIR/webapps
sudo systemctl restart tomcat

Now, let's connect Excel to the Mondrian demo Foodmart database.

In order to get address to your Mondrian XMLA service you have to insert your Ubuntu server ip in following template
http://<your_server_address>:8080/emondrian/xmla
To get your server ip, run command



hostname -I

In this example Mondrian server address is
http://192.168.100.4:8080/emondrian/xmla

Open Excel, select the Data tab, then Get Data -> From Database -> From Analysis Services.

Paste address of Mondrian XMLA service as Server name and click Next.

Then choose a cube and click Finish. The PivotTable with cube data will appear.

Next, we will create a Mondrian schema for ClickHouse example dataset OnTime. The schema defines two cubes. Cube OnTime is based on data from ontime table. Cube ViewOnTime is an example of how to create a cube based on a query instead of a table.

Following command will create new schema file OnTime.xml.



sudo nano $TOMCATDIR/webapps/emondrian/WEB-INF/schema/OnTime.xml

Copy xml to this file and press Ctrl-S (Save) and Ctrl-X(Exit).



<?xml version="1.0"?>
<Schema name="OnTime">
<Cube name="OnTime">
  <Table name="ontime"/>
  <Dimension name="Date">
    <Hierarchy hasAll="true" allMemberName="All Dates">
      <Level name="Year" column="Year" uniqueMembers="true" type="Numeric"/>
      <Level name="Quarter" column="Quarter" uniqueMembers="false" type="Numeric">
        <CaptionExpression>
          <SQL dialect="generic">concat(toString(Quarter), ' ', toString(Year))</SQL>
        </CaptionExpression>
      </Level>
      <Level name="Month" column="Month" uniqueMembers="false" type="Numeric">
        <CaptionExpression>
          <SQL dialect="generic">
            concat(          
            CASE toString(Month) WHEN '1' THEN 'January'
            WHEN '2' THEN 'February'
            WHEN '3' THEN 'March'
            WHEN '4' THEN 'April'
            WHEN '5' THEN 'May'
            WHEN '6' THEN 'June'
            WHEN '7' THEN 'July'
            WHEN '8' THEN 'August'
            WHEN '9' THEN 'September'
            WHEN '10' THEN 'October'
            WHEN '11' THEN 'November'
            WHEN '12' THEN 'December'
            ELSE 'Unknown' END,
            ' ',
            toString(Year))
          </SQL>
        </CaptionExpression> 
      </Level>
      <Level name="Day" column="FlightDate" uniqueMembers="true" type="Date">
        <CaptionExpression>
          <SQL dialect="generic">concat(toString(Year), '-', toString(Month), '-', toString(DayofMonth))</SQL>
        </CaptionExpression>
      </Level>
    </Hierarchy>
  </Dimension>
  <Dimension name="Unique Carrier">
    <Hierarchy hasAll="true" allMemberName="All Unique Carriers">
      <Level name="Unique Carrier" uniqueMembers="true">
        <KeyExpression>
          <SQL dialect="generic">toStringCutToZero(UniqueCarrier)</SQL>
        </KeyExpression>
      </Level>
    </Hierarchy>
  </Dimension>
  <Dimension name="Destination">
    <Hierarchy hasAll="true" allMemberName="All Destinations">
      <Level name="Destination State Name" column="DestStateName" uniqueMembers="true"/>
      <Level name="Destination City" column="DestCityName" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Dimension name="Origin">
    <Hierarchy hasAll="true" allMemberName="All Origins">
      <Level name="Origin State Name" column="OriginStateName" uniqueMembers="true"/>
      <Level name="Origin City" column="OriginCityName" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Measure name="Flights" column="Flights" aggregator="sum" formatString="#,###"/>
  <Measure name="Air Time" column="AirTime" aggregator="sum" formatString="#,###"/>
  <Measure name="Distance" column="Distance" aggregator="sum" formatString="#,###"/>
  <Measure name="Dep Delay" column="DepDelay" aggregator="sum" formatString="#,###"/>
  <Measure name="Carrier Delay" column="CarrierDelay" aggregator="sum" formatString="#,###"/>
  <Measure name="Weather Delay" column="WeatherDelay" aggregator="sum" formatString="#,###"/>
</Cube>
<Cube name="ViewOnTime">
<View alias="ViewOnTime">
<SQL dialect="generic">
<![CDATA[select * from ontime]]>
</SQL>
</View>
  <Dimension name="Destination City Name" foreignKey="DestCityName">
    <Hierarchy hasAll="true" allMemberName="All Destinations">
      <Level name="Destination City Name" column="DestCityName" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Dimension name="Origin City Name" foreignKey="OriginCityName">
    <Hierarchy hasAll="true" allMemberName="All Origin Cities">
      <Level name="Origin City Name" column="OriginCityName" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Measure name="Dep Delay" column="DepDelay" aggregator="sum" formatString="#,###"/>
</Cube>
</Schema>

You have to give rights to this file to the user under which Tomcat server is running. In the following command change the user tomcat:tomcat name to the name under which your Tomcat server works and run it.



sudo chown -RH tomcat:tomcat $TOMCATDIR/webapps/emondrian/WEB-INF/schema/OnTime.xml

Now, we will edit the Mondrian datasource configuration file. Run following command.



sudo nano $TOMCATDIR/webapps/emondrian/WEB-INF/datasources.xml

Replace the DataSources tag with following xml.



<DataSources>
  <DataSource>
    <DataSourceName>OnTime</DataSourceName>
    <DataSourceDescription>ClickHouse Sample Data</DataSourceDescription>
    <URL>http://localhost:8080/emondrian/xmla</URL>
    <DataSourceInfo>Provider=mondrian;Jdbc=jdbc:clickhouse://192.168.100.8:8123/datasets;JdbcDrivers=ru.yandex.clickhouse.ClickHouseDriver</DataSourceInfo>
    <ProviderName>Mondrian</ProviderName>
    <ProviderType>MDP</ProviderType>
    <AuthenticationMode>Unauthenticated</AuthenticationMode>
    <Catalogs>
        <Catalog name="OnTime">
            <Definition>/WEB-INF/schema/OnTime.xml</Definition>
        </Catalog>
    </Catalogs>
  </DataSource>
</DataSources>

Edit connection string to your ClickHouse dataset in DataSourceInfo tag using following syntax:
jdbc:clickhouse://<host>:<port>[/<database>][?user=<my_user>&password=<my_password>]

Save (Ctrl-S) and close (Ctrl-X) datasources file.
Restart tomcat server.



sudo systemctl restart tomcat

Create a new connection to Mondrian server in Excel to see cubes from your new schema. Select one to browse its data in PivotTable.

Try and create your one Mondrian schema for ClickHouse datasets (documentation).

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