I recently had a need to extract the meta data for a tabular model into a database. Unfortunetly the AMO object model handles MOLAP and Tabular cubes very differently so I ended up using MDX queries against the $system.MDSCHEMA DMVs. I found the following blog post on using MDX queries in an SSIS package to be quite helpful when it came to integrating my MDX queries into my meta data population package. I will follow up with a post on extracting the Tabular Model meta data into a set of SQL Server tables.
The reason for this blog post, is I recently had a query if it was possible to get data returned from an MDX Query and then insert the data into a SQL Server Table.
And the reason for putting it into SSIS was that we could schedule the job to run on a schedule.
I have inserted pictures from my own documentation to save me some time publishing this post.
The reason that I also like using the ADO NET Source is that you can use Expressions for both your ADO NET Source and Destination.
For my requirement I needed to ensure that my MDX Query only got data for the previous week. So in order to do this I had to create Variables which would be populated in the previous steps in my SSIS Package.
NOTE: The Previous MDX query did not include getting the dates for the previous…
View original post 59 more words