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?