4

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.

3 Answers 3

3

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

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.

2

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.
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.