"Can't determine relationships between fields" message: Does Power BI have a limit to joins?

[Anonymous]

I have a report I'm developing in Power BI.  The data is coming from a major ERP system with a standard SQL Server database.  All the tables join successfully in smaller groupings.  But when I attempt to join a 6th table to a Visualization that already has data from 5 tables, I get the "Can't determine relationships between fields" message.  When I click "Fix this" dialog and get to the Manage Relationships pane, all the joins are showing as active.

Is there a limit to the number of joined tables that Power BI can assemble into a Visualization?

[jdbuchanan71]

It's not that PowerBI is limited in what it can handle.  You can do multiple levels of tables chained together but a properly designed star schema will give you better performance and because the memory footprint is based on cardinality there is no penalty for denormalization.

[Anonymous]

Actually, if you look at my screenshots, you can see that PBI is most definitely limited.  I had a series of tables with legit joins that PBI recognized.  In any SQL-based reporting program, this would have worked with no sweat.  But PBI cannot handle these seven tables, which are representative of what true ERP reporting has to do every day.  Not only that, but with SQL-based reporting, you don't have to choose the perspective of the report beforehand as tightly--when you bring in 5--10--15--20 tables, you have a LOT more flexibility as to which table is the "focus" or "fact" of any given report based on the query.  PBI definitely has its uses, such as bringing together data from very different sources; handling data in companies that don't have a comprehensive ERP but rather have their data spread out in spreadsheets, FileMaker, whatever; and situations where the built-in slicer functionality is demanded.   But it definitely has its "don't use" arena as well, from what I see of it's functionality.  In many situations, the robust flexibility of SQL is going to facilitate getting way more data to users with far less work.  And actually, far less complexity.  PBI is pitched as "low-code/no-code for users: but what I see is three languages instead of one, you have to understand the data well enough to be able to "smush" (ok "transform") it into a dataset that will work for a single intended use: in fact, you have to know more about data technicality with PBI that working with SQL in order to really harness it beyond basics.  IMO. 

[Anonymous]

And actually, my little trial of true SQL ERP data shows another major--and I mean MAJOR--limitation of PBI.  Namely, you can only join table-to-table using a single key.  In my ERP data example, some of the tables are joined using 5, 6, or 7 keys.  This is standard in manufacturing, in order to link material to specific operations on specific work orders.  Before even bringing the data into PBI, I had to "smush" (OK, "merge" to make is sound cool) the keys into a single key, in both tables.  It was a LOT more technical work than what is required to bring the same data into any SQL-based reporting program.  It may not have required SQL up front, but it required as much or more understanding of the database structure.  So much for the "easy reporting for non-technical users".  I'm not saying PBI doesn't have uses or that it doesn't have some awesome features.  I'm just saying we need to be honest with ourselves about it's limitations, understand when it's best to use it and when not.

[jdbuchanan71]

@Anonymous 

Denormalization is having tables where some columns have duplicated data.  It is done so you can have a star schema.  Think of an order header and order detail table.  The order header would have things like PO #, Customer #, etc.  Things that relate to the whole order.  Order detail would have order line, product code, quantity, as well as order number.  They would link together using the order number.  When you denormalize you would combine them into an order table that would have every order line but also the records you need from the order header table.  This means that the PO# would be repeated for every live of the order.  This is all discussed in the modeling course I linked.

[Anonymous]

@jdbuchanan71 Thanks again.  I was wondering if that was what I would have to do.  Can you help me with all this "IT-speak"?  Does "denormalize" mean "I'm going to combine my three tables into one table that is essentially a raw ungrouped query like I would get from a SQL query in SSMS"?  In other words "denormalized" means, "I'm not going to use the data in the normal tables: I'm going to smush it up into one "denormal" table  so I can get PowerBI to work with this data"...do I have that right?

[jdbuchanan71]

It has to do with the way filters flow by default.  They only from from the one side to the many side so it is not flowing up from Operation to Work Order, that is why you are not seing the filtering you would expect.  You can make the relationships bi-directional if you need to.   I would also try to denormalize the model so the hightled ones might be combined into just 2 tables.

[Anonymous]

Thanks @jdbuchanan71 !  Here's what I'm trying to do.  This works in the ERP's own query writer, SSMS, and in other SQL-front-end report writers.  

But in PowerBI, joins work if there are 2, 3, 4, or 5 tables.  Note which tables are checked off as being in use by Power BI (far right of screenshots)

If I try to add Operation, it fails:

Have to eliminate CUST_ORDER_LINE and DEMAND_SUPPLY_LINK in order to get OPERATION and REQUIREMENT showing...

So I'm guessing I have to Append REQUIREMENT and OPERATION to WORK_ORDER in order to be able to get the whole shebang....??

[jdbuchanan71]

My work model has > 25 tables that are all driven from views created in SQL.  The views are used to denormalize the data so it fits into a star schema better.  Just for reference on the size of my model, my member table has 4.2 M records and the claim table is 37 M records.
Take a look at this course to get a better understanding of data modeling for PowerBI.

https://www.sqlbi.com/p/introduction-to-data-modeling-for-power-bi-video-course/

It's free and gives a good introduction.

[Anonymous]

OK this makes sense now.  Basically, Power BI is limited in what it can handle.  So you have to take 2 or more tables and combine them into a single table that is essentially like a raw SQL query that repeats information from, say, a Sales Order Header table--repeats for every line item in each Sales Order.   This is "denormalization".  Denormalization is done until the data is in a "star schema" which means there is a central "fact" table which houses "what the report is about", plus 1 or more "dimension tables" that feed into the "fact" table.  Examples of "dimensions" might be the Product Family of the part on the line item; or the Territory Description, Currency Name, etc.

[Anonymous]

Just wondering if anyone out there has attempted a Power BI report stemming from a database where five or six or seven tables have to be related, and have three or four layers of relationship with multiple joins. What are the techniques required to get this into PBI?

[Anonymous]

Thanks!  A colleague got me up to speed on the "star model" of data warehouses; and it seems that PBI is designed to be a front end for data warehouses, NOT SQL databases!  Not to mention, unless I missed something, it cannot handle table relationships where sometimes a field from the joined table is used, sometimes not.  For example, when a Customer Order can have an associated Work Order if the part is fabricated and the Work Order is done specifically for the Sales Order--but other times, the Sales Order might have a purchased part that is sold from inventory.  So sometimes there is a value for the Work Order associated with the Sales Order Line, and sometimes not.  I do not see how PBI can do this.  It only seems capable of direct association, like, "What is the Customer Name on the Sales order--presupposing that there will ALWAYS be a Customer Name.

If Microsoft would beef up PBI so it can handle database relationships fully, it would be an awesome tool!  Without it, I'm having a hard time seeing it as a serious reporting tool.  Lots of cool automation, but at a relatively shallow level.  I guess that's the price one pays for "low-code/no-code"...

[jdbuchanan71]

That comes from having fields from tables where the relationship direction does not let the filters flow together.  In the image below I have a field from the 'Product List' table and the 'Customer' table.  The relationship between 'Product List' and 'Product' flows from 'Product' to 'Product List' so the model cannot figure out the relationship between 'Product List' and 'Customer'