eMondrian: Relational OLAP Server for Real-Time with ADOMD.NET and Excel Support

We will be using the eMondrian branch of the Mondrian ROLAP engine. The eMondrian server is a relational OLAP server (ROLAP), meaning it always shows data from the source in real time. This server executes queries written in MDX, reads data from the underlying database, and presents the results in a multidimensional format. It is a branch of the Mondrian ROLAP engine. The main feature of this version is support for ADOMD.NET and Excel clients, and eMondrian already includes a ClickHouse driver. Deployment is done using nxs-universal-chart, based on the image emondrian/emondrian. During deployment, two files are mounted:

• name: datasources

 mountPath: /usr/local/tomcat/webapps/emondrian/WEB-INF/datasources.xml

 subPath: datasources.xml

• name: custom-schema

 mountPath: /usr/local/tomcat/webapps/emondrian/WEB-INF/schema/custom-schema.xml

 subPath: custom-schema.xml

The ClickHouse data source for Mondrian is defined in datasources.xml:

datasources.xml
<?xml version="1.0"?>
<!--
 
  http://mondrian.pentaho.com/documentation/installation.php#5_1_Describe_the_data_sources_in_datasources.xml
 
-->
<DataSources>
  <DataSource>
    <DataSourceName>ClickHouse</DataSourceName>
    <DataSourceDescription>ClickHouse Data</DataSourceDescription>
    <URL>https://<emondrian_url>/emondrian/xmla</URL>
    <DataSourceInfo>Provider=mondrian;Jdbc=jdbc:clickhouse://<clickhouse_url>:8123/<database>?ssl=false&user=<user>&password=<password>;JdbcDrivers=ru.yandex.clickhouse.ClickHouseDriver</DataSourceInfo>
    <ProviderName>Mondrian</ProviderName>
    <ProviderType>MDP</ProviderType>
    <AuthenticationMode>Unauthenticated</AuthenticationMode>
    <Catalogs>
        <Catalog name="CustomSchema">
            <Definition>/WEB-INF/schema/custom-schema.xml</Definition>
        </Catalog>
    </Catalogs>
  </DataSource>
</DataSources>

The Mondrian schema is defined in the custom-schema.xml file:

custom-schema.xml
<?xml version="1.0"?>
<Schema name="CustomSchema">
  <!-- Cube BasedOnQuery based on a query instead of a table.-->
  <Cube name="BasedOnQuery">
    <View alias="BasedOnQuery">
      <SQL dialect="generic">
        <![CDATA[select * from <table_name>]]>
      </SQL>
    </View>
    <Dimension name="Title" foreignKey="title">
      <Hierarchy hasAll="true" allMemberName="All Titles">
        <Level name="Title" column="title" uniqueMembers="true"/>
      </Hierarchy>
    </Dimension>
    <Measure name="Overdue" column="overdue" aggregator="sum" formatString="#,###"/>
  </Cube>
  <!--Cube BasedOnData is based on data from '<table_name>' table.-->
  <Cube name="BasedOnData">
    <Table name="bitrix_tasks_view"/>
    <Dimension name="Title" foreignKey="title">
      <Hierarchy hasAll="true" allMemberName="All Titles">
        <Level name="Title" column="title" uniqueMembers="true"/>
      </Hierarchy>
    </Dimension>
    <Measure name="Overdue" column="overdue" aggregator="sum" formatString="#,###"/>
  </Cube>
</Schema>

Here, a very simple schema and two approaches to writing a cube are described.

The BasedOnQuery cube is an example of how to create a cube based on a query instead of a table.

The BasedOnData cube is based on data from the <table_name> table.

Mondrian and Excel:

Open Excel → Create → Blank Workbook:

Data → Get Data → From Database → From Analysis Services:

Enter the server name → https://<emondrian_url>/emondrian/xmla

Select Cube:

Save the data connection path:

Import data:

Check the fields for the pivot table: