Connecting to Database Cube that uses MySQL database from PHP (using JDBC)

Question

My database team has set up a database Cube using MySQL database. I need to connect to this Cube from PHP and get the data using MDX queries.

I can't find how to do that. Could someone please help me with this ASAP?

Reference document :- Creating Interactive OLAPApplications with MySQLEnterprise and Mondrian

Ause

what's your bi server version? – Advisedly

Have you looked at PHPOLAP on github? – Mauchi

Checking out phpolap. thanks for the suggestion. Will be back once I check with it :) – Ause

I just downloaded the PHPOLAP zip from github and placed it in my project directory. and tried it by using the code given the Database exploration part on the github page. But it does not work. Any help? FYI, I am on PHP 5.4.4. Also I am using MAMP and not any PHP framework – Ause

Answer 1

Your best option is to use Schema workbench to create the XML for your Cube

Reference

Schema Workbench download

MySQL Jdbc Driver

Most of the time people get a little bit stuck at first using the workbench, i highly recommend that before you use it, put the mysql jdbc driver inside the driver folder. That way the application will be able to connect to your database as soon as you open it for the first time.

After you puted your jdbc-driver-file.jar inside the driver folder:

1- Execute Schema Workbench (.sh on unix/linux based systems)

2- Go to Tools -> Preferences and setup your connection string:

• driver class name: com.mysql.jdbc.Driver

• connection string: jdbc:mysql://serverlocation:port/database

• username: mysql user

• password: password

3- Go to File -> Open and open your cube

4- Go to File -> New -> MDX Query

If you want a prettier view of your MDX query using jPivot you can use the bi server for testing.

Advisedly

Can you please guide me where to put the mysql jdbc connector folder? – Ause

Also, I have the XML file. I now have to connect to it and get the data using some queries. – Ause

Ok, you can use the schema workbench to validate the cube and also for testing MDX. It´s very simple only put your jdbc file in the drivers folder – Advisedly

Excuse me but I am unable to get you. I am on Mac OS X and using MAMP stack. Can you please help me in a more detailed way as to where to put the JDBC connector I just downloaded for MySQL and use it to get the data using the Cube's XML file? Sorry to trouble you but this is urgent :( – Ause

Have you downloaded and unziped the schema workbench? it has a driver folder. – Advisedly

I am trying to install Mondrian and am stuck with an error. An answer to the question here (#12386693) might be of some help! – Ause