Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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