Sql-server – How to we restrict content by user role in Tableau dashboard without using Cross Join

reportingsql server

I have a BI admin that created a tool-generated query from Tableau, connecting to a custom database application that tracks issue reports for follow-up action.
I can make almost any changes to the database, though I'm not the owner.

The requirement we're having trouble with is that certain "admin users" need to be able to see ALL database items, while "normal users" should only see items that pertain to them (that they reported or they're responsible for resolving). The BI admin handles this with a Cross Join to the "Groups" table, filtering for the Admins key – and this takes the result set from 94K rows to 2.4M rows, and the query response time from 12s to 5m12s.

Obviously Cross Join is to be avoided wherever possible, but I'm not familiar enough with Tableau or other reporting suites to suggest an alternative way to handle admin access vs normal user access. What's the proper way to do something like this?

Edit: After reading initial comments and looking into this closer with the BI guy, the query Tableau generated is very poor and could use significant cleanup. The database has a total of ~3900 rows in all tables, so even the 94k means a significant amount of crossing and duplication is already occurring from one-to-many relationships and Left Join.

I simplified the query a bit, removing about half the columns in the SELECT portion.

  SELECT [AreaAccess].[ID] AS [ID (AreaAccess)],
  [AreaAccess].[FunctionalArea] AS [FunctionalArea],
  [AreaAccess].[EmplId] AS [EmplId],
  [CEO_OR].[ID] AS [ID (CEO_OR)],
  [CEO_OR].[Project_ID] AS [Project_ID],
  [CEO_OR].[Title] AS [Title],
  [CEO_OR].[Materials] AS [Materials],
  [CEO_OR].[Four_Blocker] AS [CEO_OR_FourBlocker],
  [MemberAccess].[ID] AS [ID (MemberAccess)],
  [MemberAccess].[Project_ID] AS [Project_ID (MemberAccess)],
  [MemberAccess].[Milestone_ID] AS [Milestone_ID],
  [MemberAccess].[EmplID] AS [EmplID (MemberAccess)],
  [Metrics].[ID] AS [ID (Metrics)],
  [Metrics].[Project_ID] AS [Project_ID (Metrics)],
  [Metrics].[Metric_Name] AS [Metric_Name],
  [Metrics].[Metric_Value] AS [Metric_Value],
  [Milestones].[ID] AS [ID (Milestones)],
  [Milestones].[Project_ID] AS [Project_ID (Milestones)],
  [Milestones].[Name] AS [Name],
  [Milestones].[Start_Date] AS [Start_Date (Milestones)],
  [Milestones].[End_Date] AS [End_Date],
  [Milestones].[Status] AS [Status (Milestones)],
  [NextSteps].[ID] AS [ID (NextSteps)],
  [NextSteps].[Project_ID] AS [Project_ID (NextSteps)],
  [NextSteps].[NextSteps] AS [NextSteps],
  [NextSteps].[Comments] AS [Comments (NextSteps)],
  [NextSteps].[Four_Blocker] AS [NextStepsFour_Blocker],
  [Projects].[ID] AS [ID],
  [Projects].[Project_Name] AS [Project_Name],
  [Projects].[Priority] AS [Priority],
  [Projects].[Start_Date] AS [Start_Date],
  [Projects].[Completion_Date] AS [Completion_Date],
  [Projects].[LastUpdated] AS [LastUpdated],
  [Status].[ID] AS [ID (Status)],
  [Status].[Project_ID] AS [Project_ID (Status)],
  [Status].[Accomplishment] AS [Accomplishment],
  [Status].[Comments] AS [Comments (Status)],
  [Status].[Four_Blocker] AS [StatusFour_Blocker],
  [Groups].[ID] AS [GroupID],
  [Groups].[EmplId] AS [GroupAdminEmplId],
  [Groups].[Type] AS [GroupAdminType],
  [Actions].[Project_ID] AS [A_Project_ID],
  [Actions].[Name] AS [A_Name],
  [Actions].[Owner] AS [A_Owner],
  IIF ([Projects].[Operating_Rhythm] = 0,NULL,'OpRhythm') AS OpRythmTrue,
  IIF ([Projects].[Frequency] IS NOT NULL,'Frequency',NULL) AS FrequencyTrue,
  IIF ([Projects].[Priority] IS NOT NULL,'Priorities',NULL) AS PrioritiesTrue,
  IIF ([Milestones].[Status] = 0,NULL,'MilestoneStatus') AS MilestoneStatusTrue,
  IIF ([Actions].[Status] = 0,NULL,'ActionStatus') AS ActionStatusTrue,
  [Projects].[Scope] AS [Scope]

FROM ([dbo].[Projects] [Projects]
  LEFT JOIN [dbo].[AreaAccess] [AreaAccess] ON ([Projects].[Function_Area] = [AreaAccess].[FunctionalArea])
  LEFT JOIN [dbo].[CEO_OR] [CEO_OR] ON ([Projects].[ID] = [CEO_OR].[Project_ID])
  LEFT JOIN [dbo].[Metrics] [Metrics] ON ([Projects].[ID] = [Metrics].[Project_ID])
  LEFT JOIN [dbo].[Milestones] [Milestones] ON ([Projects].[ID] = [Milestones].[Project_ID])
  LEFT JOIN [dbo].[MemberAccess] [MemberAccess] ON (([Projects].[ID] = [MemberAccess].[Project_ID]) AND ([Milestones].[ID] = [MemberAccess].[Milestone_ID]))
  LEFT JOIN [dbo].[NextSteps] [NextSteps] ON ([Projects].[ID] = [NextSteps].[Project_ID])
  LEFT JOIN [dbo].[Status] [Status] ON ([Projects].[ID] = [Status].[Project_ID])
  LEFT JOIN [dbo].[Actions] [Actions] ON ([Projects].[ID] = [Actions].[Project_ID]))
  CROSS JOIN [dbo].[Groups] [Groups] WHERE [Groups].[TYPE] IN (1,2)

The Groups table is very simple, just containing ID, EmplId, Name, Type (types 1 and 2 are admins)

Best Answer

One option may be to run one query to determine if the user should get the full results (admin), or results filtered by their user account data. Once you know that, you might actually have two distinct queries; one guesses that a query that will always bring back the full results might perform better than one that sometimes brings back the full results, and sometimes brings back a limited subset. This would eliminate the possibility of a "parameter-sniffing" issue, where the query plan generated for the query for an non-admin user doesn't work very well for admin users.

(Bandwidth - I had originally suggested that it might be useful to compare the amount of time it takes to bring back to the 2.4 million rows from this query vs 2.4 million rows from a simple query that shouldn't require any significant work to build (for instance, SELECT * FROM sys.objects o1 CROSS JOIN sys.objects o2). This was to see if the time the query took to respond was due simply to the amount of data being returned, or due to the complexity of the query. However, some basic math answers that. 100K rows took 12 seconds to return. 24*100K rows took 312 seconds to return. 12 * 24 = 288 seconds, close enough to the actual time. The increase in time seems explained by the additional rows being returned)

Related Question