Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Does anyone know a way to allow an Essbase connector to directly reference data from Excel on SharePoint?
The kicker is that the native Essbase query is dependent on that data from Excel so they have to talk. The queries cannot run separately in "staging" tables then be combined later because the Excel data is used to actually build MDX statements that become elements of the native Essbase query.
Of course, the "[Query in Question] references other queries or steps, so it may not directly access a data source" error that a million other people have written about keeps popping up. But the architecture of this project seems unique so I haven't found a solution that works yet.
Not for lack of trying either! I've spent way too many hours researching and testing solutions but haven't solved the issue yet. I've checked privacy settings multiple times (both connections are "Organizational", on Desktop and Service). I've deleted my gateway and set it up again. I've created a fresh PBIX file and published a new dataset, adding query steps one at a time. I always hit the same error.
The one factor that gives me hope that what I'm trying to build may be possible is that the solution works in Desktop. Only when I published to Service did I discover this issue...
A little more background in case it helps:
I'm starting to wonder if this architecture will even work in Power BI Service or if I've built something outside its capabilities. Hopefully that's not the case and I'm simply missing something. Please help!
(PS I will be offline for the weekend but look forward to re-engaging on Monday.)
Hi @mm5308 ,
Based on your description, you are trying to build a Power BI solution that requires the Essbase connector to directly reference data from an Excel file on SharePoint that is used to build MDX statements as part of an Essbase query. I noticed that you have tried a few steps while in power bi desktop that have worked.
You might consider using an intermediate database to store the Excel data and then have Essbase fetch the data directly from the intermediate database. This way, the Essbase query is not directly dependent on the Excel data source in Power BI.
Alternatively, import the Excel data into Power BI and publish it as a dataset, and then Essbase can fetch the data through the Power BI dataset.
You can try these two approaches to see if they meet your requirements
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @v-heq-msft,
Thanks for your suggestions. Unfortunately, I have read-only access to Essbase and no server space to perform data pulls and serve as intermediary. Neither of those approaches will work in this case.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.