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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mageeb
New Member

Many to Many relationships in PowerBI

We have a star schema in a SQL azure database. One of our dimensions tables run some 200 rows. Given power bi max filtration options of (~100) and the ease of use; we have decided to create another category table with a many to many relationship to the dimension table; we need an intermediary table between them holding the foreign keys.

Now the problem is power bi online with SQL azure live connect doesn't understand this many to many relationship.
We resorted to using the desktop designer as it allows us to manage our relationships manually.

The designer worked fine and we were able to create our reports. The problem is, it had to download the entire SQL database (facts table) which runs about 1 GB. The pbix file was too large and couldn't publish to the dashboard.

Is there a way to tell the designer not to download the entire database, rather use live queries? Or is there a way to get the web version to understand many to many relationships?
2 REPLIES 2
ankitpatira
Community Champion
Community Champion

@mageeb May be its worth trying below. (I have).

 

I had relational db with many PK FK relations and to not wanting to do this manually in powerbi desktop, I initally connected to my database using DirectQuery. Once connected and saved pbix file, I changed it to import by clicking on the link on bottom right which says 'DirectQuery enabled click to change'. This helped me replicate my db PK FK relations without needing to create manually.

andre
Memorable Member
Memorable Member

If you need to use many to many relationships or have relationships in general, then you have to have a model created using Power BI Desktop.

 

I am a little surprised that 1GB table is too big, as it should get compressed to about 10% of its uncompressed size which should be well under the 250MB limitation of Power BI.

 

There are certain things you can do to make the size of the model smaller so you may want to search the web on how to do that (too many options to list here).

 

The bad news is that until SQL 16 is out, Power Desktop is the only option for you to do Many to Many in memory, otherwise, I would recommend that you build an Analysis Services model and expose your data that way.

 

I would definitely investigate why 1gb makes to big a a model first though, there might be some low hanging fruit there.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors