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