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
S19K15
New Member

Two column relationship based on data consolidation

Hello, I am bringing again an old hot topic into discussion. I have created a custom datafactory platform which is consolidating data from ERP system databases with same schema into other group databases.

 

Group databases have same primary keys columns (usually one, the OID) as the source databases plus one, the TenantID. At the end all tables into group database have primary key of two columns and therefore in order to relate every master data dimension we need to join always into these two columns.

 

The data are flowing in real-time from production systems into group databases and most of the data tables are not being transformed with the old classic method of ETL.

 

For front end I have Power BI which will deliver the reports to customer for large scale projects though PowerBI service. My biggest issue is the limitation of power bi to use two or more column as relationship.

 

The option to have one column which is the merge of the two is not a solution as this will decrease the performance especially on millions of data and will not be enhanced from present indexes. In addition, having an inactive relationship of the second column is not also a solution as the idea is to provide the ability to the customer to easily select attribute from model from different tables without involving dax. Like the old classic SSAS multidimensional.

 

Why this is not implemented yet for relational databases?

2 REPLIES 2
Greg_Deckler
Super User
Super User

@S19K15 Because the product team has been too busy adding accent colors? Only other thing I can think of is that you could do a Merge step and merging allows merging on multiple columns.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

It is just crazy that you didn't even read my post. The product team to add why exactly? And merge of columns are not good idea for production solution in big scale. But thanks 

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.