Using SSIS to extract query data and present it through a cube 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.
tl;dr;

SSIS moves data. SSAS creates cubes. SSRS displays data.

Given the following problem statements


The client cannot directly query the database.
The client can only query the cube.


Therefore, you're going to need to modify the existing SSAS cube solution to expose the missing data for them to consume. Creating a new table in the database isn't going to directly help as you've specified they cannot query the database.

It might help in that you can materialize the existing query into a table. Whether that's correct approach is beyond the scope of a question here. You would then use SSIS to pop the data into the table(s) needed for reporting.

Since we're talking cubes, you might not have much to do or you have plenty. The general idea being you're going to define a central table that contains the measurable things at the lowest possible grain required. If they need to report a store's daily sales, then you're probably looking at a table that contains a sales amount and then two foreign keys: store and day. This table will be a fact or a measure table.

You have an existing query that helps identify what they want to filter or slice the data by - those are your dimensions. In the previous example, we'd have a date dimension and a store dimension. Dimensional modeling can get complex pretty quick but your saving grace is there is already an existing cube out there. Your predecessor has likely created "conformed dimensions" which means you don't need to create your own store or date dimension, you simply need to ensure you are correctly tying this new fact table into the existing dimensions.

You might even be able to fake out the dimension table by creating a view. In fact, it's usually a good idea to drop a view over your fact tables before presenting it to SSAS as it abstracts away the physical implementation from the logical.