Create a Data Studio Community Connector
Budget: $60 – $80 USD
create a Data Studio Community connector for BigQuery:
https://developers.google.com/datastudio/connector
IMPORTANT TO UNDERSTAND: the goal of the project is to create ONE report which will give access to different BIGQUERY DATASETS depending on the E-MAIL ADDRESS of the person VIEWING the report. So we can use the same report and simply control which e-mail address should see which data.
it should use the Advanced Services : https://developers.google.com/datastudio/connector/advanced-services
It should work very similarly to the standard BigQuery connector, but the user should not select a BigQuery Dataset, instead it should select a DataSet type
then the name of the table can be simply selected from a list provided per DataSet type.
The idea, is that the system must identify the right dataset based on the identification of the Report Viewer E-mail : https://stackoverflow.com/questions/52874003/get-current-user-inside-getdata-of-datastudio-community-connector
Based on the e-mail address, the system will retrieve the DataSets, by accessing a BigQuery view.
So for example, if the user has the e-mail address "abc at abc.com", then there must be a BigQuery view which can be called and which will return, for this e-mail address what are the datasets this user has access to (e.g. "accountA", "accountB", "accountC".
Then, the system can take the name of the table configured in the data source.
The resulting bigquery select will then be like this (considering the dataset type is "reports", the project is "projectY", and the table is "tableX" :
SELECT *, 'accountA' as eshop FROM projectY.accountA_reports.tableX
UNION ALL
SELECT *, 'accountB' as eshop FROM projectY.accountB_reports.tableX
UNION ALL
SELECT *, 'accountC' as eshop FROM projectY.accountC_reports.tableX
As you can see, the result will be that (in case the user has access to several dataset) each dataset will be accessed with a UNION ALL between the queries and the name of the dataset will be added as a field (eshop).
Otherwise, the Connector should work exactly the same way as the standard BigQuery Connector.
The UI should be done in a way which is simple to configure where the user can simply select one of the possible dataset types ("reports", "views", "lab", "core") and select a table name (another BigQuery view should be created to provide a list of possible tables per dataset type).
IMPORTANT: IF THERE ARE SEVERAL SOURCES IN THE SAME REPORT, WE NEED TO MAKE SURE THE AGREEEMENT OF THE VIEWER TO ACCESS THE EMAIL ADDRESS IS ONLY ASKED ONCE FOR THE ENTIRE REPORT!
https://developers.google.com/datastudio/connector
IMPORTANT TO UNDERSTAND: the goal of the project is to create ONE report which will give access to different BIGQUERY DATASETS depending on the E-MAIL ADDRESS of the person VIEWING the report. So we can use the same report and simply control which e-mail address should see which data.
it should use the Advanced Services : https://developers.google.com/datastudio/connector/advanced-services
It should work very similarly to the standard BigQuery connector, but the user should not select a BigQuery Dataset, instead it should select a DataSet type
then the name of the table can be simply selected from a list provided per DataSet type.
The idea, is that the system must identify the right dataset based on the identification of the Report Viewer E-mail : https://stackoverflow.com/questions/52874003/get-current-user-inside-getdata-of-datastudio-community-connector
Based on the e-mail address, the system will retrieve the DataSets, by accessing a BigQuery view.
So for example, if the user has the e-mail address "abc at abc.com", then there must be a BigQuery view which can be called and which will return, for this e-mail address what are the datasets this user has access to (e.g. "accountA", "accountB", "accountC".
Then, the system can take the name of the table configured in the data source.
The resulting bigquery select will then be like this (considering the dataset type is "reports", the project is "projectY", and the table is "tableX" :
SELECT *, 'accountA' as eshop FROM projectY.accountA_reports.tableX
UNION ALL
SELECT *, 'accountB' as eshop FROM projectY.accountB_reports.tableX
UNION ALL
SELECT *, 'accountC' as eshop FROM projectY.accountC_reports.tableX
As you can see, the result will be that (in case the user has access to several dataset) each dataset will be accessed with a UNION ALL between the queries and the name of the dataset will be added as a field (eshop).
Otherwise, the Connector should work exactly the same way as the standard BigQuery Connector.
The UI should be done in a way which is simple to configure where the user can simply select one of the possible dataset types ("reports", "views", "lab", "core") and select a table name (another BigQuery view should be created to provide a list of possible tables per dataset type).
IMPORTANT: IF THERE ARE SEVERAL SOURCES IN THE SAME REPORT, WE NEED TO MAKE SURE THE AGREEEMENT OF THE VIEWER TO ACCESS THE EMAIL ADDRESS IS ONLY ASKED ONCE FOR THE ENTIRE REPORT!