Using SSIS to extract query data and present it through a cube

Question

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.

Answer 1

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 task in SSIS as described in this answer

Answer 2

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.