Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using powerpivot to join a SQL Database query to a table defined in a PowerPivot Excel workspace? Expand / Collapse
Author
Message
Posted Sunday, May 6, 2012 4:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:23 AM
Points: 272, Visits: 734
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
Posted Monday, May 7, 2012 1:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
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
Posted Monday, May 7, 2012 11:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:23 AM
Points: 272, Visits: 734
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 »

Add to briefcase

Permissions Expand / Collapse