Databricks - Direct Query (transaction) - Import Master data - Matrix view

[Anonymous]

Hi, I have huge data 90mill rows in Transactiond data (direct query) and Master data import mode.

1. In Matrix view to show Amount per GL account, how can I show Account number and Name at same level. (if I add them in Rows, it is coming one under another). Do I need to concatenate and show in single cell? 

2. Now all these in Manage Relationship are having Many to Many (*to*) relation 

e.g. GL_Account to Gl_Account description etc. Whats the best way without creating intermediate table to handle them to show correct data

3. What is best performance wise - to connect via SQL endpoint or Cluster (I think it is via SQL Endpoint)

Kind regards

Krishna.

[v-yalanwu-msft]

Hi， @Anonymous ;

Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi， @Anonymous ；

1. In Matrix view to show Amount per GL account, how can I show Account number and Name at same level. (if I add them in Rows, it is coming one under another). Do I need to concatenate and show in single cell? 

2. Now all these in Manage Relationship are having Many to Many (*to*) relation 

e.g. GL_Account to Gl_Account description etc. Whats the best way without creating intermediate table to handle them to show correct data

3. What is best performance wise - to connect via SQL endpoint or Cluster (I think it is via SQL Endpoint)

---

As 1 question,If you use matrix and then add them to Rows, there will be several levels, like this:

2.This can be used if your relationship is many-to-many. But the official recommendation is to add a bridge table,

When you have a many-to-many relationship between dimension-type tables, we provide the following guidance:

• Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column
• Add a bridging table to store associated entities
• Create one-to-many relationships between the three tables
• Configure one bi-directional relationship to allow filter propagation to continue to the fact-type tables
• When it isn't appropriate to have missing ID values, set the Is Nullable property of ID columns to FALSE—data refresh will then fail if missing values are sourced
• Hide the bridging table (unless it contains additional columns or measures required for reporting)
• Hide any ID columns that aren't suitable for reporting (for example, when IDs are surrogate keys)
• If it makes sense to leave an ID column visible, ensure that it's on the "one" slide of the relationship—always hide the "many" side column. It results in the best filter performance.
• To avoid confusion or misinterpretation, communicate explanations to your report users—you can add descriptions with text boxes or visual header tooltips

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many

3.I also think via SQL endpoint is better.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, Thanks for your response. yes, as to Point 1, yes Matrix is coming like in a hierarchy - But if its a plain table, it comes like a Name value pair. I am trying to that way and hope it will be ok.