Within the "browser" the data I'm interested in is a single dimension on the Y-axis and a couple measures. Also, I have a couple filters set up. The columns will always be the same, so the data should always fit into the same table.
I have tried to set up an Integration Services data transformation package for this. I have set up my source to be 'Ole DB', which lets you pick SSAS as a source. The problem is that the source editor seems to only allow adding a dimension, without any of the rich detail allowed by the "browser" interface. What the heck am I missing here?
Thanks, Joe
I'm not sure if this works or not. I did have a play with it at one point, but did not have much luck. It might be worth asking in the SSIS forum too.
What I have done in the past is to set up a linked server to SSAS and then used SELECT ... FROM OPENQUERY(LinkedSSAS, 'MDX Query') However this does mean that you need a bit of working knowledge of MDX. There are a couple of options there. If you set up a Reporting Services report you could use the MDX query builder there to design your query, it works "ok" if you only need measures on the columns, but it will not let you put other dimensions in the column axis. You could also run a profiler trace on SSAS while you refresh your browser, but some of the browsers break their queries into pieces, creating session level sets which make it harder to capture the entire query.
|||Thanks for the reply Darren as it got me on the right track. I ended up writing my query in MDX and then the problem became, getting an MDX query into SSIS, which has been written about in the SSIS forums. Strangely, there isn't an obvious MDX data source in SSIS. What I did was use OLE DB "SQL Command" for the data access mode. Also, I needed to add "Format=Tabular" to the connection string... to get things running "smoothly". I wonder why this isn't more straightforward.
Here is the thread that talks about all this:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=408058&SiteID=1
-Joe
No comments:
Post a Comment