Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
In a Direct Query report on an Azure SQL database I configured relationships from USER to ORGANISATION (n:1, filter in both directions and "Security filter in both directions") and from ORGANISATION to ERRORS (1:n). I also configured RLS for the user id to filter the USER table.
When I put a filter on user in the the report it work fine, but when I use the "Show as" feature to filter on [USER_ID] = USERNAME() no data is shown in visualizations containing the datetime column.
Looking at some of the generated SQL I see with filtering on a user in the where clause:
WHERE ( [t1].[USER_ID] = N'TWS-OrgUser1@cgi.com' )
With "Show as" for a specific user, 2 queries are executed. 1 with a similar where clause selecting all but the Organisation_id column and a second one with a very large WHERE clause containing every column and value from the first. This one returns 0 rows, because of the clauses on the Date/Time/Timezone column like:
[t2].[Log timestamp] = CAST( '20180706 11:10:23' AS datetime)
The issue disappears if I change the type of the column to text, but I want it to be a date time column for filtering.
My assumption is that the security filter in both directions causes the very large where clause including the filtering on date/time/timezone. Is this a bug or how can I prevent this?
Solved! Go to Solution.
Finally determined that the cause for the second query was a column containing the organisation name from the ORGANISATION table. When removing this column from the report or including the organisation_id the data is correctly filtered. That is a sufficient workaround for us.
Finally determined that the cause for the second query was a column containing the organisation name from the ORGANISATION table. When removing this column from the report or including the organisation_id the data is correctly filtered. That is a sufficient workaround for us.
Hi
Problem seems to be solved, thank you for sharing.
Best regards
maggie
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |