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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
BM4291
Resolver I
Resolver I

Get source queries from PowerPivot/1103 Compatibility Mode

Hi,

 

A little bit of cross-posting but not getting much traction on StackOverflow, and hoping someone here might provide some clarity: excel - VBA - ModelTable get all SQL queries - Stack Overflow

As in the above link, I am looking to document our legacy Excel workbooks; we have about 50 over the estate which are connected to our SQL database via PowerPivot.

 

I'm able to extract the measures quite easily through the Model object and DMVs but still unable to get the source queries of the tables; each file contains one or two connections, with multiple tables attached. I'm open to any method that will help us extract this information.

 

Any help greatly appreciated!

4 REPLIES 4
lbendlin
Super User
Super User

select * from $SYSTEM.TMSCHEMA_PARTITIONS

select * from $SYSTEM.TMSCHEMA_EXPRESSIONS

@lbendlin unfortunately the TMSCHEMA_* DMVs aren't accessible in a 1103 compatibility mode model so those queries aren't an option in this instance. For whatever reason, that looks to be the latest compatibility mode available via PowerPivot in Excel.

 

Any suggestions for 1103? I've tried to run a DISCOVER_XML_METADATA query but unable to get the XMLA connection to open correctly when pointing at the local model.

 

PowerPivot Compatibility across versions - SQLBI

No idea - is the compatibility level higher in newer Excel versions?

@lbendlin doesn't look that way, the last thing I can see documenting which compatibility mode goes with which version is the SQLBI article above; anything > Excel 2013 is 1103.

 

We're using the M365 version so fairly recent.

 

BM4291_0-1671002095940.png
I saw something about the level being higher in PowerQuery but we've not got the time or desire to make that switch just to extract the queries before ultimately dumping these workbooks.

 

We've recently had an issue with a high priority measure being shared incorrectly so this is really an exercise in damage control and to strengthen the business case.

 

We have accesss to Gartner through work so likely going to take it to their technical analysts, seems to be the only option as just losing time on this one daily.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors