Forum Discussion

mageeb's avatar
mageeb
New Member
11 years ago

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?
Need Help

2 Replies

  • andre's avatar
    andre
    Memorable Member
    11 years ago

    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.

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    10 years ago

    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.