Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
mm5308
Helper I
Helper I

Challenge: Can Essbase Directly Access SharePoint as a Source?

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:

  • The solution is built this way intentionally.
    • A client wants some transformations automated but doesn't have coding experience.
    • They also want the ability to control the outputs.
    • As a result, I'm building them a "black box" - they control the inputs and the solution produces their desired outputs.
    • Hence the Excel file as inputs/filters and the need for the native Essbase query to reference those inputs directly.
  • The architecture looks like this.
    • Excel files are hosted on SharePoint and contain tables like Date, Product, Geography. The tables can be thought of as "Dimensions" and their records as "Members".
    • Dimension tables from Excel are brought into Power BI.
    • Transformations are performed, turning the "Members" into MDX strings.
    • MDX strings are referenced in a native Essbase query.
    • Outputs from the native Essbase query are cleaned up (mainly to remove extra columns that come in because of the way Essbase and Power BI interact).
  • PBIX is published to my Personal Workspace for testing.
  • Gateway is On-Premises (Personal). Again, both the SharePoint and Essbase connections are set to Organizational privacy level.

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.)

2 REPLIES 2
v-heq-msft
Community Support
Community Support

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors