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

  • polkadot

    SSChampion

    Points: 13618

    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?

    --Quote me

  • Raunak Jhawar

    SSCoach

    Points: 15701

    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

  • polkadot

    SSChampion

    Points: 13618

    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?

    --Quote me

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply