Install the Saiku Analytics plugin in Pentaho BIServer CE

meterorite

I’ve been working with Mondrian and Pentaho’s Schema Workbench lately and attempted to add Meteorite Consulting’s Saiku Analytic plugin to my installation of Pentaho BI Server community edition, to process some MDX queries. MDX is a query language similar to SQL that is used for processing database cubes. Mondrian is a OLAP engine that implements the MDX language and is incorporated into the Saiku Analytic software. It differs from other OLAP engines in that the cubes are built on the fly as the query processes, rather than having the cube data stored on a server. For simpler cubes, the trade off between a slightly slower build time and disk space is negligible.

Here is the process I followed to get Saiku enabled in my BI Server:

Continue reading

Access a MySQL Server Remotely

logo-mysqlA quick one today: While working on a project, I couldn’t access the MySQL server (version 5.7.12) that was on another system. I was in a development environment on a local network with just me on in, so the MySQL server did not have a firewall running. Here is what I did to get my connection to work.

  1. Add an Administrator user account with permissions to connect from any host:
    CREATE USER 'edpflager'@'%' IDENTIFIED BY 'my_password';
    GRANT ALL PRIVILEGES ON *.* TO 'edpflager'@'%' WITH GRANT OPTION;
  2. Next open a terminal prompt on the MySQL server, and navigate to /etc/mysql/mysql.conf.d
  3. Open a text editor as superuser  and edit mysqld.cnf
    sudo nano ./mysqld.cnf
  4. Find the following line and add a # to the beginning to comment it out:
    bind-address = 127.0.0.1
  5. Save, exit, and restart MySQL to make it take effect.

You should now be able to access MySQL as the admin account you created previously.

 

Pentaho Data Integration’s Fuzzy Match

fuzzyWhen cleansing data, one of the biggest challenges is determining if one record is the same as another in the absence of a unique identifier. For example, if your database has a record for Terri Lee Duffy, and you get a new record for Terry Lee Duffy, is it the same person? If you have a government ID number then its possible to tell definitively, that its the same person. But what if you don’t have that to distinguish the record? You could check other related data if you have it, like street address, but what if one record has 100 South Ave and the other is 100 South Road? A human looking could say yes or no that this is the same person.

We don’t want to have to check every discrepancy, especially if we are moving millions of rows at a time. In order to automate this process, we can use a component in Pentaho called Fuzzy Match. (For a longer discussion of Fuzzy Matching, Melissa Data Corporation has a good overview.) While the results of a Fuzzy Match process are not 100% perfect, you can set an allowance threshold so that similarities have to be within a certain range or you can show only the closest match as a result of your Fuzzy Match. Finally, the Fuzzy Match component can use one of several algorithms to determine if one field is a match for another.  The Pentaho Wiki discusses the nuances of these algorithms and has some discussion on the best times to use them.

Continue reading

Using Docker on demand with Linux Mint

container_shippingIf you are like me and work on multiple things on your development system, you don’t always want everything running when you start your PC. I’ve previously covered starting other services on demand, and this time around I’ll cover running Docker as needed.

Docker has essentially two separate components. There is the Docker daemon (or service) that is configured to start when the system is booted up and there is the Docker CLI that you interact with and your commands are passed to the daemon. The CLI only runs when you specifically call it from the terminal prompt with the DOCKER command. For my purposes, I didn’t need or want the daemon running all the time because its a laptop. (If I was using a production system or even a full blown development box, I would prefer to have the daemon always running.) So after installing Docker, I needed to configure it to not start up every time the system starts, and then come up with an easy way to start it as needed. Continue reading