Connecting to MySQL database from Analysis Services

Question

I need to know whether it is possible or not to get data from a MySQL database and create a Cube in Analysis Services 2008. If so, tell me how to connect to MySQL Database from Analysis Services.

Answer 1

You can do this using MyODBC. Install the connector, then just set up a DSN and use it as normal.

Answer 2

You can also create a linked server connection to the MySQL DB using ODBC. Openquery can be used to query the MySQL DB from within SSMS. See these links for Openquery, SQL2008-MySQL linked server.

Answer 3

Here's what I did to get it working. I've used MySQL proxy: http://www.blrf.net/blog/252/microsoft/using-mysql-with-microsoft-analysis-services-for-olap/

Small recap of what I did:

• Get MySQL Connector/NET and make sure you configure the Sql Server Mode to true.
• Because SSAS will use COUNT_BIG when processing Aggregation Designs, which MySQL does not understand, I've install MySQL proxy and used this rather simple LUA script to change all COUNT_BIG into COUNT:

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

• You have to point the SSAS to the MySQL proxy.
• Everything else works like a charm for me.