using powerpivot to join a SQL Database query to a table defined in a PowerPivot Excel workspace?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » PowerPivot » using powerpivot to join a SQL Database query...

using powerpivot to join a SQL Database query to a table defined in a PowerPivot Excel workspace?

Rate Topic

Display Mode

Topic Options

Author

Message

polkadot

Posted Sunday, May 06, 2012 4:14 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 5:00 PM
Points: 172, Visits: 446

Scenario:
I have a working sql query based excel report AND I have new data that I need to bring into the query which resides exclusively on an excel spreadsheet. I cannot (due to permission restrictions) create a new table in SQL Server and I can't store the excel datasource on the same server as the SQL Server instance (therefore, linked server option isn't viable) and I do not have bulkload permissions on SQL server so can't bulkload date from .csv file and I do not have permissions to reconfigure the instance for ad hoc queries (therefore Select * from OPENDATASOURCE is also not viable).

Does anyone know if it is possible for a query based excel report to join on a table defined exclusively in PowerPivot Excel workspace?

Post #1295737

Raunak Jhawar

Posted Monday, May 07, 2012 1:11 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:25 PM
Points: 946, Visits: 1,739

Hi,

have you considered a Integration Solution(SSIS) wherein the excel file would act as a source and you can later introduce a MERGE transformation to join data as required.

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum

Post #1295809

polkadot

Posted Monday, May 07, 2012 11:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 5:00 PM
Points: 172, Visits: 446

Yes, I did think of SSIS solution however I ended up converting the Excel spreadsheet into a .csv file and using Microsoft Query to import it as a table. I have now created relationships between this imported table and my existing query (also a table) and generated a report successfully based on defined relationship.

My next problem is related...while this report renders and slices well on my local harddrive, it doesn't once it's uploaded to SharePoint site. If I click on a slicer button I get:

Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: PowerPivot Data

Prior to joining my query table to the table based on imported .csv file, I was able to slice the report just fine.

Doesn't the imported table get uploaded to Sharepoint PowerPivot Gallery along with the query definition?

Post #1296022

« Prev Topic | Next Topic »

Permissions