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