MDX query is very slow in at the time of loading through SSIS data flow task

0
votes

Hi we have a ssis package which is loading data from a SSAS cube.So we are using mdx query inside the data flow task for fetching data from Cube.But at the time of fetching data from cube, the package is taking huge time. it is first valiadting then executing so total 1 hour for some 1000 rows.. So is there any option us there to optimize??.. in mdx we have ony select query for one meausre and some 4 dimension.. and same mdx giving result in query window from cube in seconds.. so where is the problem??

1
sql-serverssisssasmdxolap-cube
Is validation part taking a lot of time? – SouravA
validation and execution both taking a lot time – Aritra

1 Answers

1
votes

Try creating a linked server on the database server to the cube server and run an OPENQUERY command to execute the MDX. This data source can then be mapped to the destination. I have heard that it's usually the better way of obtaining results from a multidimensional source.

References - 1 and 2. I have also heard that this is a great tool. If you are open to using open source code then, try this out as well.