We have created a report on Data Studio which shows certain metrics retrieved from a BigQuery table through a query connector. There are several countries, so we do not want people in one country to have access to data from a country other than their own. For this, we have thought about creating a different report for each country and modify the WHERE clause of the query like this:
SELECT
*
FROM `mytable`
WHERE
country = 'ESP'
This will work, however we will have too many reports to maintain, so the same change will need to be performed on multiple reports. Is there a way to detect the user (or even better, the group the user is assigned to) that is accessing the dashboard, and depending on that set the country to retrieve on the query? Is there any other better way of achieving it?
To keep this entirely dynamic you could try using BigQuery's row-level security feature [1]. From DataStudio's point of view, you would send the same query for everyone, but BigQuery would only return the rows the authenticated user is authorized to read. For this to work, the DataStudio data source will need to be configured with viewer's credentials access.
It's worth pointing out that managing row-level access policies comes with its own challenges. This article covers some of them [1] and compares this approach with other methods (authorized views, separate tables, etc.).
[1] https://cloud.google.com/bigquery/docs/row-level-security-intro