I'm fishing for an improv solution idea.
What I think I'd like to do is create connections that allow me to use SQL to extract data from multiple workbooks using a single statement/joins/etc. The intent is this is an interim solution - more of a proof of concept/demo - before I have the opportunity to migrate my sources into SQL Server. Ultimately the users will have a few different options to see data including excel, power bi and ssrs.
I was able to pull data in from multiple workbooks using power pivot and using multiple steps, and eventually transform it to what I needed. However, I didn't like the drill-down behavior. If I had three source connections and I double-clicked for a drill down, it would only give me a new tab showing the relevant source from the column I picked. If there's an opportunity to change that drill down behavior, this might fit my needs.
I tried using a SQL approach (against excel directly) and that works great as long as I'm only targeting worksheets within a single workbook. I demo'd the approach of using a single workbook as a staging point, but from what I've read online, it's supposed to be possible to use SQL (excel to excel) to join source data from multiple workbooks, the hint I saw made me think it's all in how you setup the connections.
I'd appreciate any suggestions. I wasn't really exploring a vba based option but if that's the right way, I'd just appreciate that hint so that I know to stop poking around with other options.
Mike
What I think I'd like to do is create connections that allow me to use SQL to extract data from multiple workbooks using a single statement/joins/etc. The intent is this is an interim solution - more of a proof of concept/demo - before I have the opportunity to migrate my sources into SQL Server. Ultimately the users will have a few different options to see data including excel, power bi and ssrs.
I was able to pull data in from multiple workbooks using power pivot and using multiple steps, and eventually transform it to what I needed. However, I didn't like the drill-down behavior. If I had three source connections and I double-clicked for a drill down, it would only give me a new tab showing the relevant source from the column I picked. If there's an opportunity to change that drill down behavior, this might fit my needs.
I tried using a SQL approach (against excel directly) and that works great as long as I'm only targeting worksheets within a single workbook. I demo'd the approach of using a single workbook as a staging point, but from what I've read online, it's supposed to be possible to use SQL (excel to excel) to join source data from multiple workbooks, the hint I saw made me think it's all in how you setup the connections.
I'd appreciate any suggestions. I wasn't really exploring a vba based option but if that's the right way, I'd just appreciate that hint so that I know to stop poking around with other options.
Mike
