Forum Discussion
Relationships
Hi,
We have created a model and defined the relationships between tables. When creating a visualisation in the report pane and capturing the SQL statement generated by Power BI within SQL Profiler when we apply a filter, we have found that the order of the tables is in an order that does meet our requirments and is limiting our dataset (outside of the filter). The first table in the FROM clause has less rows than our 'main' table and therefore this is limiting our resultset.
We have TABLE_A and TABLE B with a 1:Many relation as TABLE_A:TABLE_B (single cross-filtering direction). When capturing the generated SQL statement TABLE_B is the first table in the FROM clause with a LEFT JOIN to TABLE_A. However, TABLE_A is our primary table and we would want this table to be our first tablein the FROM clause with LEFT JOINs coming from this table.
Is there a way to define the primacy / order of tables? Does the behaviour we are observing indicate that we have made a mistake with our table relationships?
Many thnaks in advane for nay assistance provided.
Carl
Hi,
With some help, we've bee able to determine why we were not seeing the behaviour we wanted.
As a Power Model is created as a Tabular Analysis Service in the back-end this model attempts to enforce INNER JOIN relationships between tables. In our example we wanted to exhibit LEFT JOIN behaviour from our CRM table to our Task table. A CRM record could have either no associated tasks, one associated task or multiple asociated tasks and we wanted to return all CRM records and the information of related tasks, if present. However, when viewing the query passed back to the server, via SQL profiler, we could see that the first table referenced was the Task table with a LEFT OUTER join to the CRM table, which in essence, enforced an INNER JOIN relationship.
In order to exhibit the LEFT JOIN behaviour we wanted there is a field option within the Report Pane of Power BI Desktop once you have dragged a field into the 'Values' section of the visualisation. If you select the little dropdown arrow to the right of the field which has come from the table you want to left join TO (in our case the Task table), there is an otion of 'Show Items with No Data'.
With this option selected we then profiled the query passed back to the server and the query showed the first table to be the CRM table with a LEFT JOIN to the Task table, as required.
So, the solution to our problem was unbelievably simple. However, if you are unaware of this option or that this needs to be ticked in order to enforce LEFT JOIN behaviour then this could cause large issues in results.
Hopefully, this will help anyne who encounters a similar problem as we weren't able to find much help on the internet. Maybe because it's so simple...
Thanks,
7 Replies
Could you please post your relationships diagram picture and your M code from Power Query .
N -
Hi N,
Thanks for your reply. Please see below some pseudo code based on the code provided by SQL Profiler. I have just removed the actual names and replaced with TABLE_A, TABLE_B and TABLE_DATE.
Please see below an image of our relationships. There are also some additional tables with Many:1 relationships with TABLE_A.
SELECT TOP (1000001) [t1].[Call_ID],[t6].[Task_ID], COUNT_BIG(*) AS [a0] FROM ( ((select [$Table].[Task_ID] as [Task_ID], [$Table].[CRM_Call_ID] as [CRM_Call_ID] from TABLE_B as [$Table]) AS [t6] left outer join (select [$Table].[Call_ID] as [Call_ID], from TABLE_A as [$Table]) AS [t1] on ( [t6].[CRM_Call_ID] = [t1].[Call_ID] ) ) left outer join (select [_].[DATE_ID], [_].[DATE], [_].[NEXT_DAY_DATE], [_].[YEAR], [_].[YEAR_QUARTER], [_].[YEAR_MONTH], [_].[YEAR_DAY_OF_YEAR], [_].[QUARTER], [_].[MONTH], [_].[DAY_OF_YEAR], [_].[DAY_OF_MONTH], [_].[DAY_OF_WEEK], [_].[YEAR_NAME], [_].[YEAR_QUARTER_NAME], [_].[YEAR_MONTH_NAME], [_].[MONTH_YEAR_NAME], [_].[YEAR_MONTH_NAME_LONG], [_].[QUARTER_NAME], [_].[MONTH_NAME], [_].[MONTH_NAME_LONG], [_].[WEEKDAY_NAME], [_].[WEEKDAY_NAME_LONG], [_].[START_OF_YEAR_DATE], [_].[END_OF_YEAR_DATE], [_].[START_OF_QUARTER_DATE], [_].[END_OF_QUARTER_DATE], [_].[START_OF_MONTH_DATE], [_].[END_OF_MONTH_DATE], [_].[DATE_FORMAT_YYYY_MM_DD], [_].[DATE_FORMAT_YYYY_M_D], [_].[FISCAL_YEAR_NAME], [_].[FISCAL_YEAR_START], [_].[FISCAL_YEAR_END], [_].[FISCAL_QUARTER_NAME], [_].[FISCAL_MONTH], [_].[LAST_FRIDAY_OF_MONTH], [_].[NEAREST_FRIDAY_TO_END_OF_MONTH], [_].[WEEKDAY_FLAG], [_].[WEEKEND_FLAG], [_].[BANK_HOLIDAY_FLAG] from TABLE_DATE as [_] where convert(date, [_].[DATE]) >= convert(datetime2, '2017-01-01 00:00:00') and convert(date, [_].[DATE]) < convert(datetime2, '2018-01-01 00:00:00')) AS [t4] on ( [t1].[ContactDate] = [t4].[DATE] ) ) WHERE ( ( [t1].[ContactDate] = CAST( N'2017-08-06 00:00:00' AS datetime) ) AND ( [t4].[MONTH_YEAR_NAME] = 'Aug 2017' ) ) GROUP BY [t1].[Call_ID],[t6].[Task_ID]Thanks in advance for any help.
Carl
It appears it's more of a dimensional modeling issue in general than just a relationship issue. We will need to see the diagram of the whole model. Please identify your dimensions and facts. If possible use named tables.
Also, which fields are you using to build the visualisation you mentioned in the first post? Please post the M code of the tables in questions in case if you used Power Query for merging, appending anything to do with querying two or more tables.
CRMCalls is a fact table and the 1:* relationship it has with TaskingTask it suspicious. Do you have dup rows in TaskingTask?
Tks, N -
