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.
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
- You have to point the SSAS to the MySQL proxy.
- Everything else works like a charm for me.