Hi all,

I would like to have a data connection file used by excel to extract data form a SQL Server DB (so that change in location of the DB needs only a change to the  single data connection file). The excel file will retrieve data from only a single database, but there are multiple queries (30+) stored on SQL server that are used - each SQL server views' data is returned to a pivot table in an excel worksheet, which has an associated chart with various slicers on the main dashboard worksheet.

Do I need a seperate data conenction file for each SQL server query being retrieved, or can a single connection file be created as all data comes from a single database?

(all my attempts and research on the net have led me to believe that a seperate file is needed for each view, but this seems unnecessary)

Have tried to use stored procedure to return multiple data set ,but looks like excel will just take the first result only, however, if you are familiar with SSIS, I find an article that describes How to Use a Multi-Result Set Stored Procedure in SSIS:
http://www.codeproject.com/Articles/32151/How-to-Use-a-Multi-Result-Set-Stored-Procedure-in

hope that helps.

• Edited by zkandyModerator Tuesday, February 25, 2014 3:43 AM

The question is, how big the effort in the front-end tool will be for you and if you would accept that?

If you have currently 30 seperate sheets, whereof each one gets the data from a different query, it will be hard to maintain the current dashboard/report-structure, as you have to specify the selection criteria again (reference changing).

Furthermore, the question is, if it really comes from a single data table (SSAS cube or flat SQL table)?

If it's really only a single cube or a single sheet, you should be able to combine all the columns you are selecting into one big SQL-statement (SQL server) or mdx query (SSAS cube).

Then you can put a pivot on that basic table.

If the single query produces too much data (more than ~1 mn lines), you should go for PowerPivot or stay with the current solution.

But honestly, best solution would be that you build a cube, enter a database connection and then a) go for MDX to select the data or b) PowerPivot/PowerView/Native Excel with SSAS connection.

Hope that helps a bit. Please explain your setup in more detail.

Hi, im learning my way with this, so apologies if I am providing too much or too little info.

There are 8 source files which are very loosely related in that they capture infromation regarding what has happened within a metro railway over a day. However there are only a few relationships between the contents of tehse files.

These tables are imported into SQL Server using SSIS where I have developed a number of views that query these 8 source tables to generate a number of metrics to provide insight into the service provided to the passengers. Some examples of metrics are: Number of passengers in transit at any given time, time taken to travel between adjacent stations, how much power was used during the day, what distance did the trains travel during the day, etc. Some views provide only a handful of rows, some provide 1M plus. There are now approx 40 seperate views created.

I have then used a spreadsheet with a worksheet associated with each SQL server view. Each worksheet is set up as a pivot table, which allows the related chart on the main dashboard worksheet to use standard excel capability to slice and present the data in different ways.

At the moment if the server on which teh database is stored moves, I have had to recreate the spreadsheet from scratch as I dont know how to change the connection information in any other way. Ideally I would like to have connection info in a single place to reduce ongoing maintenance, particularly as I would like to place the spreadsheet on a SharePoint server to distribute it to other users.

Thanks

Hi Chris,

have you solved the issue?

You can change the servername in the embedded connections in the workbook. That's pretty simple.

The hard part is to merge all your sources into a single file, which is not possible in my eyes. It seems like a real "iT task". ;-)