Using MySQL with Microsoft Analysis Services for OLAP

I’ve always wanted to use MySQL as my fact, dimensions, etc tables to use for Analysis Services for OLAP cubes. When using Visual Studio to create the Analysis Services database, I’ve always stumbled upon some kind of issue where processing the cube would produce a query that only MSSQL server would understand.
Well, I’ve finally created the cube that has all the data stored in MySQL database(es).

Configuration of MySQL Connector/NET Data Source

First of all, what you need is MySQL Connector/NET. We use this provider for data source. It is important to configure “Sql Server Mode” in Connection Manager for this connection.
Here’s the screen shot of the settings:

With Sql Server Mode active, the MySQL Connector/NET will replace all [] with “.

That’s all you need to change to build the Data Source Views, Dimensions and Cubes.

Creating Aggregation Design(s)

When you try to build an aggregation design Microsoft Visual Studio will use function COUNT_BIG that MySQL does not understand – and also, Visual Studio formats the query in a way that MySQL does not understand. To tackle this problem, I’ve used MySQL Proxy.

Installing MySQL Proxy on Debian

It’s fairly easy to install MySQL Proxy on debian. Simply run:

apt-get install mysql-proxy

Writing MySQL Proxy LUA script

MySQL Proxy uses LUA script. I’ve written a simple LUA script that normalizes the queries received from Analysis Services and changes the COUNT_BIG function into COUNT that MySQL understands. Create the LUA script file in /usr/share/mysql-proxy/ssas.lua with the following contents:

function read_query(packet)
    if packet:byte() == proxy.COM_QUERY then

        -- try to match the string up to the first non-alphanum
        local f_s, f_e, command = string.find(packet, "^%s*(%w+)", 2)
        local option

        if f_e then
            -- if that match, take the next sub-string as option
            f_s, f_e, option = string.find(packet, "^%s+(%w+)", f_e + 1)
        end

        if string.lower(command) == "select" then
            local query = packet:sub(2)
            if (string.match(query, "COUNT_BIG")) then
                query = string.gsub(query, "%c", " ");
                query = string.gsub(query,"COUNT_BIG%s*%(%s*([^%)]+)", 'COUNT(%1')
                proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
                return proxy.PROXY_SEND_QUERY
            end
        end
    end
end

Configuring MySQL Proxy on Debian

To configure MySQL Proxy on Debian you need to edit the default configuration file found at /etc/default/mysql-proxy. Here we enable the MySQL Proxy and set the LUA script that will be used to process the packets. It should look something like this:

ENABLED="true"
OPTIONS="--proxy-lua-script=/usr/local/share/mysql-proxy/ssas.lua"

Starting MySQL Proxy on Debian

Once you’ve created the LUA script and modified configuration, you can start the MySQL Proxy with the standard Debian procedure:

/etc/init.d/./mysql-proxy start

The proxy is started and it is listening on default port which is 4040.

Configure Data Source to use MySQL Proxy

Now we need to tell Analysis Services to use the MySQL Proxy. If you’re running MySQL Proxy on the same machine as your MySQL Server, simply change the Port in your Data Source. You do that by going into Data Source Designer, click Edit and on Connection Manager window click icon “All”, scroll down to Connection and change the Port from 3306 to 4040.
Note that MySQL Server will now see this connection as coming from Localhost not the Analysis Services server. So you might need to add an account to MySQL with username/password you use to connect to MySQL from Analysis Services with hostname: localhost (127.0.0.1).

So here it is. Now you can use MySQL Database(es) with all of the features of Analysis Services. Please let me know if you find any other issues with this setup.

If you need help setting up the Analysis Services database, check my Create OLAP cube for SQL Server 2005 Analysis Services article.

Tags: Analysis, BI, LUA, Microsoft, MySQL, OLAP, proxy, server, Visual Studio

This entry was posted on January 10th, 2013 and is filed under Microsoft. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or Trackback from your own site.