The problem: I have a client who is running their application on SQL Server. They use SQL Server Analysis Services for additional reporting based on the cube that's been provided. It turns out, there is one query they are looking to run that is written in SQL and is fairly complex. The cube doesn't give them the data that they are looking for.
The workaround: As the client, they don't have access to directly query the database. Hence, they send me this query, and every week, I send them back the results on a spreadsheet.
Proposed solution: I believe I would be able to use SQL Server Integration Services to work around this whereby, I can automate the creation of a table with the results of said query which they would be able to query using their existing access to Analysis Services. Am I correct in saying this?
If so, I believe I need to create an SSIS package with certain control and data flows. What is the high level structure of what needs to be built?
P.S. In looking so far, I see a control flow task called "Execute SQL Task". I imagine I would have to build on this.
Best Answer
If you just transfer the data to another server using SSIS it won't magically appear in the SSAS database, so you would need to change the SSAS solution.
If your question is about providing access to the production database you could use SSIS to transfer data onto another server where the user does have access, but it would need to be access to the database engine hosting the database that contains your exported table.
However, if you just want to remove the manual weekly intervention and your users are currently happy with receiving an Excel file you could just use SSIS to export the results of your query into an Excel file and email that.
To do that you would need to add a data flow task with a SQL Server source and an Excel destination.
You could then either store that Excel in a file share they have access to or mail it from a
Send mail taskin SSIS as described in this answer