Google data studio to provide reports and dashboard based on organization

Question

We are a SaaS based company. We are exploring to use Google data studio to provide reports and dashboard to our users (belong to an organisations). We have our data in BigQuery. Our challenge is - we need to show report on our web application as per the logged in user's org id. So, need to have a filter. We cannot create the report and embed the link as we cannot apply the pre-filter (logged in user's org) in this case and it has to be dynamically applied. So, the other option seems to be writing a custom data connector of our own. On a high level, I understand that I need to create a data connector, bind it to biqquery, report template Id and params (org Id in our case). But not able to really execute it. Any direction or example will really help.

Thank you.

score 0 · Answer 1 · answered Jun 14 '19 at 13:11

We need to show report on our web application as per the logged in user's org id ...
Any direction or example will really help.

Below simplified example for BigQuery Standard SQL

Assume you have reports table as below - metrics field mimics your actual report fields and org_id is to identify for which user this row to include into report

`project.dataset.table` AS (
  SELECT 1 metrics, 1 org_id UNION ALL
  SELECT 2, 2 UNION ALL
  SELECT 3, 3
)

Having above you want to be able to run below query, but return only rows assigned to logged user (with specific org_id)

SELECT metrics
FROM `project.dataset.table`

To do so, you need to have meta table which will host mapping between org_id and correspondent user account used in GCP as

`project.dataset.users` AS (
  SELECT 1 org_id, 'user1@gmail.com' user UNION ALL
  SELECT 2, 'user2@gmail.com' UNION ALL
  SELECT 3, 'user3@gmail.com' 
)

So, now you can run below query instead of original

#standardSQL
SELECT t.* EXCEPT(org_id)
FROM `project.dataset.table` t
JOIN `project.dataset.users` USING(org_id)
WHERE LOWER(user) = LOWER(SESSION_USER())

As you can expect, result of this query will depend on which user runs it

For example, for user2@gmail.com - output will be

Row metrics  
1   2

while, for user4@gmail.com - output is

Row metrics  
Query returned zero records.

I omit template_id for simplicity sake - but you can just easily add it into picture

Hello Mikhail, Thanks. We are embedding the data studio reports within our application, We are not sure how to pass the users org reference/user id to big query/data studio. Since we are embedding the reports in an iframe within our web application, we don't see a way to pass parameters. — Raju Chittampalle, Jun 17 '19 at 07:02

score 0 · Answer 2 · answered Feb 14 '20 at 06:21

Native email filtering has now been introduced to Google Data Studio. Quoting the section specific to BigQuery:

Filter by email in BigQuery
In a BigQuery data source, you can filter by email address using the @DS_USER_EMAIL parameter in a custom query. For example:

Select * from Sales WHERE sales-rep-email = @DS_USER_EMAIL;

This provides additional flexibility in your filters. Learn more about BigQuery parameters.

Google data studio to provide reports and dashboard based on organization

2 Answers2