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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ovonel
Post Prodigy
Post Prodigy

delete unused measures in cube without breaking pbix?

I have 3 .pbix  files that consume live from a SSAS cube.

 

The .bim cube has thousands of measures (most of them not used at all).

 

My goal is to delete the unused measures in the .bim (without breaking any visual in the .pbix).

 

Is there a way of opening the 3 .pbix and extracting all the measures/columns used? (or a way of seeing in the .bim which measures are unused?

 

Ps: each .pbix has 15 pages, so its hard to do it manually.

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

To know if a SSAS measure is used you would have to enumerate all visuals across all pages in each of the PBIX files and fetch the query definition for each of the queries. Then you would have to check if any of the measures is mentioned.

 

The closest you can get to that is Get pages and visuals in a Power BI embedded analytics application | Microsoft Docs

 

As you probably know, a PBIX file is an archive. If you look inside it the Report\Layout file has JSON-y data about the visuals. It does not have the queries though, only the queryName.

 

lbendlin_0-1659831182279.png

If you pretty up that JSON you can sort of glimpse the fields that are shown in the visual. 

lbendlin_1-1659831697405.png

But the query itself is not there. Not sure where to look for that. I guess the only remedy would be to run DMV queries against the XMLA endpoint (either the local SSAS server or the service dataset)

 

select * from $SYSTEM.TMSCHEMA_MEASURES

 

And then you can judge if your SSAS measures are used in any of the DAX measures.

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

To know if a SSAS measure is used you would have to enumerate all visuals across all pages in each of the PBIX files and fetch the query definition for each of the queries. Then you would have to check if any of the measures is mentioned.

 

The closest you can get to that is Get pages and visuals in a Power BI embedded analytics application | Microsoft Docs

 

As you probably know, a PBIX file is an archive. If you look inside it the Report\Layout file has JSON-y data about the visuals. It does not have the queries though, only the queryName.

 

lbendlin_0-1659831182279.png

If you pretty up that JSON you can sort of glimpse the fields that are shown in the visual. 

lbendlin_1-1659831697405.png

But the query itself is not there. Not sure where to look for that. I guess the only remedy would be to run DMV queries against the XMLA endpoint (either the local SSAS server or the service dataset)

 

select * from $SYSTEM.TMSCHEMA_MEASURES

 

And then you can judge if your SSAS measures are used in any of the DAX measures.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors