Friday, January 18, 2013

Mondrian 4: Get ready!


Mondrian 4: Get ready!

Mondrian is a very popular open source analytical engine which is used in various offerings (like Pentaho BA Server, JasperSoft BI Server). Mondrian 4 brings a whole bunch of new features, some of which we will discuss in this blog post.

Prerequisite


  • You are familiar with XML
  • You are familiar with OLAP
  • You are familiar with the command line on a Linux OS or Mac OS.
  • Download Eclipse IDE for Java EE Developers from here. Users unfamiliar with Java, don’t worry, we won’t write a single line of Java! We will use Eclipse only to create an OLAP schema.
  • Download the Mondrian 4 version of Saiku from here. We will use it to test the schema. Please note that this branch is in active development and not suitable for production use! If you need a version for production use (currently without Mondrian 4 support), download it from the main website.
  • This post is not really a step by step tutorial, more a kind of general overview, so it is recommended that you have a data set (ideally a data mart) so that you can follow along and create your own OLAP schema.
  • All my files related to this post can be download here.

What’s new

  • Dimensions are now loosely set up using Attributes. The can be part of a hierarchy but they don’t have to. If defined in a hierarchy, they can still be used on their own.
  • MeasureGroups can be used to define Measures from more than one fact table (that have the same dimensionality and granularity).
    • There are no aggregated patterns any more, use MeasureGroups for defining aggregates.
    • Virtual Cubes are depreciated
  • Schema Workbench is discontinued. Write the OLAP Schema in a text/XML editor of your choice
  • No XMLA server, spin off project OLAP4J xmla server
  • Built-in time dimension generator

A quick overview of the new syntax

We will quickly have a high level overview of the new syntax. I highly recommend obtaining the forthcoming book Mondrian In Action for more detailed instructions. Also, if you don’t know the purpose of an XML element or attribute, you can find a description in the Mondrian API documentation.

High level structure

Please find below a simplified structural overview of the OLAP schema definition:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE Schema SYSTEM "mondrian.dtd" >
<Schema metamodelVersion="4.0">
<!-- Define how the DB tables are set up -->
<PhysicalSchema>
<!-- Specify source tables -->
<Table/>
<!-- Define relationships between snowflake or alias tables, not direct dim tables! -->
<Link/>
</PhysicalSchema>
<!-- Create your cube definition and reference back to the physical schema -->
<Cube>
<!-- Define dimensions and attributes -->
<Dimensions>
<Dimension>
<Attribute/>
</Dimension>
<Hierarchies>
<Hierarchy>
<Level/>
</Hierarchy>
</Hierarchies>
</Dimensions>
<!-- Define Measures -->
<MeasureGroups>
<MeasureGroup>
<Measures>
<Measure/>
</Measures>
<DimensionLinks>
<ForeignKeyLink/>
<FactLink/>
</DimensionLinks>
</MeasureGroup>
</MeasureGroups>
<!-- Define Calculated Members and Names Sets -->
<CalculatedMembers/>
<NamedSets/>
</Cube>
<!-- If you have conformed dimensions, specify them globally.-->
<Dimension>
<!-- Define roles below to restrict access to the cube(s). -->
<Role>
</Schema>

Note that Mondrian 4 is not sensitive to the order you mention these building blocks, so you can mention the cube before the physical schema in example.

How to define the physical schema

The physical schema defines how your database tables are set up:
<PhysicalSchema>
      <Table name='employee'>
          <Key>
              <Column name='employee_id'/>
          </Key>
      </Table>
      <Table name='store'>
          <Key>
              <Column name='store_id'/>
          </Key>
      </Table>
      <Link source='store' target='employee'>
          <ForeignKey>
              <Column name='store_id'/>
          </ForeignKey>
      </Link>
</PhysicalSchema>
Note: The <Link> element is only used to describe the relationship to alias tables or snowflaked tables (so tables that are more than one link away from the fact table).

How to define dimensions

Dimensions are defined with the <Dimension> element and their members with the <Attribute> element. Mondrian will by default create a hierarchy for every attribute you create unless you specify <Attribute hasHierarchy=”false”>. There is an optional <Hiearchy> element for specifying multi-level hierarchies.

          <Dimension name='Promotion' table='promotion' key='Promotion Id'>
              <Attributes>
                  <Attribute name='Promotion Id' keyColumn='promotion_id' hasHierarchy='false'/>
                  <Attribute name='Promotion Name' keyColumn='promotion_name' hasHierarchy='false'/>
                  <Attribute name='Media Type' keyColumn='media_type' hierarchyAllMemberName='All Media' hasHierarchy='false'/>
              </Attributes>
              <Hierarchies>
                  <Hierarchy name='Media Type' allMemberName='All Media'>
                      <Level attribute='Media Type'/>
                  </Hierarchy>
                  <Hierarchy name='Promotions' allMemberName='All Promotions'>
                      <Level attribute='Promotion Name'/>
                  </Hierarchy>
              </Hierarchies>
          </Dimension>


One of the big advantages of Mondrian 4 is that you can now use attribute on their own as well even if they are part of a mulitlevel hierarchy!

How to define measures

Measures are defined with a <MeasureGroup> element. Within the <DimensionLinks> element you can define the foreign keys for all the dimensions that are related to this MeasureGroup:
<MeasureGroups>
          <MeasureGroup name='Sales' table='sales_fact_1997'>
              <Measures>
                  <Measure name='Unit Sales' column='unit_sales' aggregator='sum' formatString='Standard'/>
                  <Measure name='Store Cost' column='store_cost' aggregator='sum' formatString='#,###.00'/>
                  <Measure name='Store Sales' column='store_sales' aggregator='sum' formatString='#,###.00'/>
                  <Measure name='Sales Count' column='product_id' aggregator='count' formatString='#,###'/>
                  <Measure name='Customer Count' column='customer_id' aggregator='distinct-count' formatString='#,###'/>
                  <Measure name='Promotion Sales' column='promotion_sales' aggregator='sum' formatString='#,###.00' datatype='Numeric'/>
              </Measures>
              <DimensionLinks>
                  <ForeignKeyLink dimension='Store' foreignKeyColumn='store_id'/>
                  <ForeignKeyLink dimension='Time' foreignKeyColumn='time_id'/>
                  <ForeignKeyLink dimension='Product' foreignKeyColumn='product_id'/>
                  <ForeignKeyLink dimension='Promotion' foreignKeyColumn='promotion_id'/>
                  <ForeignKeyLink dimension='Customer' foreignKeyColumn='customer_id'/>
              </DimensionLinks>
          </MeasureGroup>
</MeasureGroups>

How to define aggregated tables

In previous versions configuring aggregated tables was an area of confusion for many users. Thankfully this has been massively simplified with the arrival of Mondrian 4. Now aggregated tables can be directly referenced in the OLAP schema inside the <PhysicalSchema> and properly defined inside the <MeasureGroup> element.

<PhysicalSchema>

<Table name='agg_c_special_sales_fact_1997'/>
<Table name='agg_pl_01_sales_fact_1997'/>
<Table name='agg_l_05_sales_fact_1997'/>
<Table name='agg_g_ms_pcat_sales_fact_1997'/>
<Table name='agg_c_14_sales_fact_1997'/>
</PhysicalSchema>

<MeasureGroups>
 …
          <MeasureGroup table='agg_c_special_sales_fact_1997' type='aggregate'>
              <Measures>
                  <MeasureRef name='Fact Count' aggColumn='fact_count'/>
                  <MeasureRef name='Unit Sales' aggColumn='unit_sales_sum'/>
                  <MeasureRef name='Store Cost' aggColumn='store_cost_sum'/>
                  <MeasureRef name='Store Sales' aggColumn='store_sales_sum'/>
              </Measures>
              <DimensionLinks>
                  <ForeignKeyLink dimension='Store' foreignKeyColumn='store_id'/>
                  <ForeignKeyLink dimension='Product' foreignKeyColumn='product_id'/>
                  <ForeignKeyLink dimension='Promotion' foreignKeyColumn='promotion_id'/>
                  <ForeignKeyLink dimension='Customer' foreignKeyColumn='customer_id'/>
                  <CopyLink dimension='Time' attribute='Month'>
                      <Column aggColumn='time_year' table='time_by_day' name='the_year'/>
                      <Column aggColumn='time_quarter' table='time_by_day' name='quarter'/>
                      <Column aggColumn='time_month' table='time_by_day' name='month_of_year'/>
                  </CopyLink>
              </DimensionLinks>
          </MeasureGroup>
</MeasureGroups>

Creating an OLAP Schema in Eclipse

You can create the Mondrian OLAP Schema in any text editor, XML editor or IDE. Eclipse is quite a popular IDE and will serve as an example here.

I created an XSD based on the DTD file found in the Mondrian lib folder (Link to original Mondrian 4 DTD) to be able to validate the XML file (the actual OLAP Schema). Currently this DTD is not suitable for validating the OLAP schema XML document (Julian pointed at that he added multiple inheritance to the schema. He created this JIRA case to address this). So, what I basically did, and this was a very quick fix, is to run the DTD through a converted that outputs an XSD and then I quickly fixed the few problems I encountered. I tested this XSD against one of my XML files and against the Foodmart one which worked fine. I am sure that this schema (XSD) needs some more work, so please report back to me any problems that you encounter using the comment function on this post.

Download my Mondrian XSD version mondria4.xsd here.

Please don’t get confused: The XSD (short for XML Schema Definition) is a file which defines the structure of an XML document. XSD is a successor of DTD (Document Type Definition) and is way more powerful. The XML document (which we will create based on this XSD) in our case happens to be called the Mondrian OLAP Schema (I will try to mostly call it XML file).
Open Eclipse and create a new Project (not a Java Project) called Mondrian4Schema. Place the XSD file directly in the project folder.
The easiest way to create the XML file is to right click on the XSD file and choose Generate > XML File:


Next specify the Root element, which in our case is Schema:


A basic XML structure will be created for you. People familiar with XML will probably want to jump directly to the source view and start working there.

For people not so familiar with XML, you can use the Outline panel to add elements and attributes by simply right clicking on a node:


And before you upload the xml file to the server, you should validate it. Right click on the xml file and choose Validate:



Note: This validation only checks if your XML file matches the structure define in the XSD file. So it doesn’t check if you referenced the correct tables, if the relationships are correct etc. We can call this our first check. The second check will be to understand if the xml file is logically correct - this will be done by running it on Mondrian (i.e. via Saiku) and analyzing the error messages (if there are any). The third check is then to see if the data is correct / dimensions and measures behave as expected - test this via a graphical interface like Saiku.

Using an XML Template in Eclipse

To make your life even easier, start your OLAP schema based on a template. I created a template which you can download here and use for the following steps. You can also create your own one.

Note that the sample template expects the XSD file to be in the same folder as the xml file you create. You can easily avoid this dependency by pointing to a public XSD.

Copy the content of the template file.

Go to Preferences  and select XML > XML Files > Editor > Templates and then click New:


Provide a Name and Description. Set the Context to New XML and paste the content of our template into Pattern:


Click Finish.

Now, when you create a new XML File, you can choose Create XML file from an XML template:


And then you can choose the template we set up earlier:


Using a template should save you quite some time and users unfamiliar with Mondrian schemas will have an easier entry point.

How to upload the OLAP Schema to the BI Server

Schema Workbench had the quite useful option to upload the XML file to the BI Server. There are plenty of alternatives now … you might as well use Filezilla or a similar FTP client to upload the file to a remote server. Later on I will show you how you can export your XML file directly from Eclipse to your local webserver.

Testing Mondrian 4 Schema with Saiku locally


Setting up Saiku

Current download location of the Mondrian 4 version (2013-01-14):
http://ci.analytical-labs.com/job/saiku-mondrian4/

If you have the Pentaho BI Server installed, then you can just download the plugin. Otherwise if you want to have the standalone version, download Saiku Server.

Instruction below are for the standalone Saiku Server:
If you are not familiar with the Saiku Server, then read this short introduction.

I extracted the files in my home folder. Let’s get ready and start the server:

cd ~/saiku-server
chmod -R 700 *.sh
sh ./start-saiku.sh

Wait a minute or so until the server is ready, then check if you can access it in your browser:

http://localhost:8080
username: admin
password: admin

If you can access the web interface, carry on with the following steps:

Export the Mondrian XML file to your local test server

We could have created a Dynamic Web Project in Eclipse as well and deployed it on the server, but this is probably a bit too ambitious. It is very simple to export to xml file to the server from our Eclipse project.

First let’s create a folder on the server. There is currently a folder set up for all the Saiku demo files - we will be using this one for simplicity sake:
cd ~/saiku-server/tomcat/webapps/saiku
mkdir supplierChain

In Eclipse, right click on the Mondrian xml file, choose Export:

Next choose General > File System and click Next:

Define which file from which project you want to export and define the destination folder:

And click Finish. Now you have a copy of your file on your local server.

Configuring Saiku

Configure the data source as described here.
If necessary, add a JDBC driver as described here.
In my case, this burns down to the following (as an example my DB tables reside on my local MySQL DB and I created a Mondrian schema called supplierChain.mondrian.xml):

cd ~/saiku-server/tomcat/webapps/saiku/WEB-INF/classes/saiku-datasources
cp foodmart supplierChain
vi supplierChain

Change the content of the file to:

type=OLAP
name=supplierChain
driver=mondrian.olap4j.MondrianOlap4jDriver
location=jdbc:mondrian:Jdbc=jdbc:mysql://localhost/datamart_demo;Catalog=../webapps/saiku/supplierChain/supplierChain.mondrian.xml;JdbcDrivers=com.mysql.jdbc.Driver;
username=root
password=

Save and close.

Note: In my download the Saiku Server had already the MySQL JDBC jar included, so the next section is just an overview in case you need to add any other driver

Next download the JDBC MySQL driver from here.

cd ~/saiku-server/tomcat/webapps/saiku/WEB-INF/lib/
cp ~/Downloads/mysql-connector-java-5.1.22/mysql-connector-java-5.1.18-bin.jar .

Nice and easy.

Restart Saiku and check if you can see the schema now in the web interface:

If you encounter problems, have a look at the server log:
vi ~/saiku-server/tomcat/logs/catalina.out
Go straight to the end of the file by pressing SHIFT+G and then start scrolling up and keep looking out for human readable error messages. Note at new users: There will be a lot of cryptic lines … just ignore them … at some point you should find something meaningful (if there is an error).
Or if you want to keep watching the log:
tail -f ~/saiku-server/tomcat/logs/catalina.out
In case there are problems with your OLAP schema, you should find some hints there.

Example of which errors you can find via reading the weblog: My new OLAP schema was not showing up in the web interface, so I analyzed the log and found the following line:

As you can see, it says here “Table 'dim_customers' does not exist in database. (in Table) (at line 27, column 4)”, which means the table name I specified in the XML file must be wrong. Doing a double check I realised, the table is actually named dim_customer. So I go back to Eclipse, correct this mistake, reupload the file and refresh the cache again … now my Schema is showing up.


If you make changes to the Schema and export it to the server again, you can simply refresh the Saiku server cash by clicking the Refresh button in the web interface:

Now we can do the last part of our checks: Analyzing if the dimensions and measures behave as intended and as we are already on the way, we also check if the data is correct. We do all this by just playing around with the dimensions and measures in the GUI and also run some SQL queries to cross check results:

How to enable Mondrian MDX and SQL logging on Saiku

In some testing scenarios you might be interested in the SQL that Mondrian generates. To enable this kind of logging, edit the log4j.xml file in
~saiku-server/tomcat/webapps/saiku/WEB-INF/classes

Uncomment the very last section just below the title Special Log File specifically for Mondrian SQL Statements. If you want to log the MDX statements as well that gets submitted, uncomment the section above (Special Log File specifically for Mondrian MDX Statements). Save the file.

Restart the server then.

You will then find two new log files in ~/saiku-server/tomcat/logs:
mondrian_sql.log
mondrian_mdx.log

In example, if you use the fancy MDX mode functionality on Saiku which allows you to write and execute your own MDX queries, you can just follow the SQL which gets generated by Mondrian by running this command in your terminal:
tail -f ~saiku-server/tomcat/logs/mondrian_sql.log

43 comments:

  1. Thanks for the article! Very good

    ReplyDelete
    Replies
    1. Thanks a lot for your feedback! Much appreciated.

      Delete
  2. Under "High Level Structure", is there an error here? Had to take the <> out to make Blogger happy.

    -- Define dimensions and attributes --
    Dimensions
    Dimension
    /Attribute
    /Dimension

    Should it be?:

    -- Define dimensions and attributes --
    Dimensions
    Attribute
    /Attribute
    /Dimension

    ReplyDelete
    Replies
    1. Thanks for posting your concerns! I think it should be fine, because if you don't use a start and end tag, then you can just put a forward slash at the end of the one tag (xml element). You can find some examples of this in the supplierChain.xml. Let me know if this clarifies your concern.

      Delete
  3. Hello,
    I have a security problem:
    If I want that a role see only the All of a dimension and one data of a dimension. How can I achieve it?
    I'll try to explain better:. If I have my Dimension Country and for every country I've got a role how can I achieve that my role see only the data related to his Country and the measure All of the countries?
    I would like that my role are readed by a db.

    ReplyDelete
    Replies
    1. I would recommend that you take a look at the Mondrian documentation which provides some good examples on how to do this: http://mondrian.pentaho.com/documentation/schema.php#Access_control

      Delete
    2. Thank you but the problem is that I don't want to manually write for every value an entry in xml file. Should I write a procedure that generate an xml file like that from the db to solve this problem? This isn't good because if my web application add a new role I should add a new entry at that xml file. :(

      Delete
    3. As far as I know you can create roles programmatically as well. Have a look at the Mondrian API documentation, in example: http://mondrian.pentaho.com/api/mondrian/olap/Role.html

      Delete
    4. The "Mondrian in Action" book also has a chapter that talks about the subject. Right now there's a preview version available.

      Delete
  4. Awesome, great contribution to the community!

    ReplyDelete
  5. This blog entry is realy good. I am starting in the world of BI and this has been very helpful. I wonder if you could also share the SQL script to create the datamart_demo physical DB.

    ReplyDelete
    Replies
    1. Thanks a lot for your feedback. I tried to find the relevant files. I added them to the shared directory (link is at the beginning of the article). You can find a transformation file and a Excel spreadsheet. Please add some more data to the spreadsheet as I only added a minimum amount of data manually. I also added some instructions to the transformation file.

      Delete
    2. Thanks a lot for the additional files, they also helped. Now it is working fine :)

      Delete
  6. Hello....
    The blog is good... I want some information about how we can provide web interface to the mondrian. By database MySQL. And i am using mondrian 3.5.

    Thank you..

    ReplyDelete
    Replies
    1. Thanks for your feedback! You want to write your own web interface for Mondrian? You should ask this question on the Mondrian mailing list or the Mondrian forum.

      Delete

  7. Hello,

    I am new to Saiku, so I am sorry for basic questions, but I can not solve it after reading all docs.

    I have PostgreSQL database with structure and data in it (runs on localhost:5432). I want to connect to it from Saiku, and analyze data. I was trying to add datasource using this blog, unfortunately unsuccessfully.

    I am using Saiku Server for Win (without Foodmart db). I created file: saiku-server\tomcat\webapps\saiku\WEB-INF\classes\saiku-datasources\my_test

    my_test file contains:

    type=OLAP
    name=test_db (my data base name )
    driver=mondrian.olap4j.MondrianOlap4jDriver
    location=jdbc:mondrian:Jdbc=jdbc:postgresql://localhost:5432/test_db;
    Catalog=D:\Projet_stage\saiku-server\tomcat\webapps\saiku\Schema1\Schema1.xml;
    JdbcDrivers=org.postgresql.Driver;
    username=postgres
    password=my_password_db

    Also, I created empty folder: C:\saiku-server\tomcat\webapps\saiku\D:\Projet_stage\saiku-server\tomcat\webapps\saiku\Schema1\Schema1.xml

    And I copied postgresql-9.2-1002.jdbc4 into tomcat\webapps\saiku\WEB-INF\lib

    I can start Saiku server without problems, I can login as "admin", but I see dropbox "cubes" empty.


    Please I need some help, i'am using postgresql-9.2

    ReplyDelete
    Replies
    1. Did you have a look at catalina.out to see if there are any error messages?

      Delete
    2. I have this message: java.net.ConnectException: Connection refused: connect
      is it a connection error?

      this is my catalina.out:

      juin 20, 2013 9:41:24 AM org.apache.catalina.startup.Catalina stopServer
      SEVERE: Catalina.stop:
      java.net.ConnectException: Connection refused: connect
      at java.net.DualStackPlainSocketImpl.connect0(Native Method)
      at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:69)
      at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
      at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
      at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
      at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:157)
      at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:391)
      at java.net.Socket.connect(Socket.java:579)
      at java.net.Socket.connect(Socket.java:528)
      at java.net.Socket.(Socket.java:425)
      at java.net.Socket.(Socket.java:208)
      at org.apache.catalina.startup.Catalina.stopServer(Catalina.java:424)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:601)
      at org.apache.catalina.startup.Bootstrap.stopServer(Bootstrap.java:338)
      at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:416)

      juin 20, 2013 9:41:29 AM org.apache.catalina.core.AprLifecycleListener init
      INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: C:\Program Files\Java\jdk1.7.0_07\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\DTS\Binn\;c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;c:\Program Files\Microsoft SQL Server\110\Tools\Binn\;c:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\QuickTime\QTSystem\;.
      juin 20, 2013 9:41:29 AM org.apache.coyote.http11.Http11Protocol init
      INFO: Initialisation de Coyote HTTP/1.1 sur http-8080
      juin 20, 2013 9:41:29 AM org.apache.catalina.startup.Catalina load
      INFO: Initialization processed in 1771 ms
      juin 20, 2013 9:41:30 AM org.apache.catalina.core.StandardService start
      INFO: Démarrage du service Catalina
      juin 20, 2013 9:41:30 AM org.apache.catalina.core.StandardEngine start
      INFO: Starting Servlet Engine: Apache Tomcat/6.0.24
      juin 20, 2013 9:41:30 AM org.apache.catalina.startup.HostConfig deployDirectory
      INFO: Déploiement du répertoire ROOT de l'application web
      juin 20, 2013 9:41:31 AM org.apache.catalina.startup.HostConfig deployDirectory
      INFO: Déploiement du répertoire saiku de l'application web
      juin 20, 2013 9:41:47 AM com.sun.jersey.api.core.PackagesResourceConfig init
      INFO: Scanning for root resource and provider classes in the packages:
      org.saiku.web

      INFO: No provider classes found.
      juin 20, 2013 9:41:47 AM org.codehaus.enunciate.modules.jersey.EnunciateJerseyServletContainer configure
      INFO: org.codehaus.enunciate.modules.amf.JAXRSProvider not found.


      thank's for your Reply

      Delete
    3. please can i have again the link to the datamart_demo.sql for creation and integration of data in the database datamart_demo ? so that i can test my postgresql-connexion with your supplierChain.mondrian.xml to see where i made a mistake. Thanks for your attention

      Delete
    4. I uploaded the file datamart_demo_mysqldump.sql. There is really hardly any data in there, but at least it will allow you to validate the setup.

      Delete
  8. with your datamart_demo I saw my mistake, now I can see my "cube" in saiku using Postgresql. Thank you for all

    ReplyDelete
  9. hey great article by the way. im having some problems though.

    so ive installed the mondrian4 plugins to the supposed directory in the bi server folders.
    normally, when i open the bi server (from the browser), i can easily create a cube for saiku analysis just by creating a datasource. (file > new > datasource)

    im using sakila database as a practice. so when i create a datasource then i just click the "new saiku analytics". at the menu i can just select the sakila cube from the cube list.

    the problem is, when ive installed the mondrian4 plugin, i cant do that. when i click the drop-down list, theres no sakila cube.

    can you tell me whats the problem? thanks

    ReplyDelete
  10. great article. im having some problems.

    I added a "Measure " and a "CalculedMember " in my schema_file.xml, but when i run Saiku, i just see the Measure but not the "CalculedMember .






    how can i see my CalculatedMember in saiku. Please i need some help
    Im using saiku 2.4 and mondrian 3.5

    ReplyDelete
    Replies
    1. This article is for Mondrian 4. Creating Cube Schemata for Mondrian 3.5 is different.
      If you created an OLAP Schema on the old specifications (pre v4) and you can see the dims and measures in Saiku, but not the calculated member, then check your log if you can see any errors.

      Delete
  11. Hi, thanks for the Mondrian 4 introduction, very nice! So as i get it Saiku is the only way to test the new schema? Does Mondrian 4 support dimension member properties? THX

    ReplyDelete
  12. Diethard - LOVE the XSD! Great contribution to the community ... Best, Mark

    ReplyDelete
    Replies
    1. Excellent! Thanks for your feedback!

      Delete
    2. No problem, great work. Would you mind sharing (on your blog or offline) how you are publishing the XML schema file to the Web server repository?

      Delete
    3. Hm, I didn't export the XML schema to Saiku. But you can just use the Eclipse Export function described above as well for this one. I don't think Mondrian would use the XML Schema reference to validate the Mondrian Schema, so this reference is just ignored.

      Delete
  13. I checked in your XSD (with minor formatting changes) as https://github.com/pentaho/mondrian/commit/7c8cf9c315404ba38f159616abd20faee5ac87fd.

    ReplyDelete
  14. I know this post is old, however I've been trying to get to grips with Mondrian 4.0. I have the Mondrian in Action book, though it's section on aggregates isn't giving me the information I need.

    I'm trying to use the CopyLink section and getting the aggregate to be hit. Using your example with 'agg_c_special_sales_fact_1997', the fact_count measure and Month, I get no results. I've tried several options and the only way I can get the aggregate to be hit is by using the ForeignKeyLinks. Any help is much appreciated.

    ReplyDelete
  15. Hi! I am using Mondrian 4.0 and trying to run my schema on it. I am trying to use a shared Time dimension multiple times (role playing dimensions) but getting the following error: Could not find a path from time as time_1 to any of time. time is the table on which my Time dimension is based on.

    To be more specific, I have a shared 'Time' dimension and two role playing dimensions in the cube as:



    I also have the following two dimensional links for the above mentioned dimensions




    I am stuck with this problem for quite a long time now and any comment or suggestion in this regard will be a great help.

    ReplyDelete
    Replies
    1. I have the same problem.
      Similar to here: https://forums.pentaho.com/showthread.php?196917-Error-using-Mondrian-4-aggregate-tables
      Do you have any idea?

      Delete
  16. Hi Mr. Steiner,

    can you confirm that the "attribute" tag was added to Mondrian schema at version 4 and the Schema Workbench is stuck at version 3.x?

    http://stackoverflow.com/questions/23568858/how-to-add-an-attribute-using-schema-workbench/23573389#23573389

    Is there a reason for why maintenance of the Schema Workbench has been discontinued?

    What is your suggestion for a tool or workflow that allows to edit a schema and test it using MDX?

    Kind regards

    Raffael

    ReplyDelete
    Replies
    1. Yes, this is correct. You can use an XML Editor or Text Editor instead (as also shown in the blog post above).

      Delete
  17. Thanks for the Awesome Article,

    Can we refer a Fact table defined in the Physical Schema inside

    For Example:




    Table name="FACT_A" />




    select from FACT_A






    Since FACT_A has some calculated columns which I would like to use instead of the raw columns.

    ReplyDelete
  18. Thanks for this amazing article! I'm having a problem in my Saiku 3.8 that uses Mondrian 4 and it's giving me a Mondrian error, could you help me?
    I already know the problem, I use PostgreSQL and I've put the FACT and DIMENSION in different database schemas (fact.xxxxx and dimension.yyyyy). When Mondrian tries the SELECT on table using POSTGRESQL it doesn't use the DATABASE SCHEMA and tries to do the select ONLY with table. Example: select count(*) FROM "chegadas". It should do this: select count(*) FROM "fact"."chegadas".
    When I changed all tables to PUBLIC or created a VIEW it worked normally. Do you know if there is any solution on using DATABASE SCHEMA different then PUBLIC on POSTGRESQL and probably on MSSQL SERVER?
    Just to make sure, this was the error I got:
    ERROR [org.saiku.web.core.SecurityAwareConnectionManager] Error connecting: chegadaturistas
    mondrian.olap.MondrianException: Mondrian Error:Internal error: Reading row count from table [null, null, tempo]; sql=[select count(*) from "chegadas"]
    The correct table is fato.chegadas

    Thanks!

    ReplyDelete
    Replies
    1. Thanks for your feedback. The table element has a schema attribute which you can use for this purpose. Several of my blog posts use Postgres as a database, so you should find some examples, especially on my GitHub blog.

      Delete
  19. This comment has been removed by the author.

    ReplyDelete