Project Controls blog

saw this on twitter (Thanks Pablo), and I had an idea of using Regex_Match as currently comparing dimension does not work !!!

How RLS Works in Data Studio

Data Studio support RLS using google account ( can work with any email address not only gmail), so when you login using your account, Data Studio get your email adress which is unique to you, then you can use that email to filter rows .

in Some cases, maybe you want to implement security using username/Password, it turn out using parameter, it is trivial to implement, notice we are using Google sheet as a data Source, if you need more control, using SQL Parameter is even more Powerful

Warning : Storing password as plain text is dangerous

now for a very simple scenario, you have data and you want people to have access only to some rows

1- Create two parameters

usernameParameter and PasswordParameter

let’s start with usernameParameter, it has to be any value, and default value null or none etc

do the same for PasswordParameter

2- Create calculated field RLSfilter

basically, if both the values typed in the parameter control match then return 1 else 0

3- Create a Filter using the calculated field RLSfilter

4- Apply the filter to Visual

go to file, report setting, and add the filter, so it will automatically apply to all the visual in your report, or you can apply to every visual where it make sense only ( you may have multiple dataset where it does not apply)

you can add an image on the filter control to hide the password when the user type it.

You can see the report here

Limitation

This RLS apply at the report level, it means report editors can see everything.

if you want the report editors not to have total access, then you need to push down the filter upstream, either using email, or leveraging SQL Parameter in BigQuery

let’s say you have a chart with a lot of categories, for example Google Analytics data per country

now let’s say you want to highlight only one country to see how it compare to the rest, it is possible using parameter

Define your Parameter

as of Sept 2020, you have to manually type the values, hopefully Google Data Studio add the option to get the values from an existing dataset.

Create a new dynamic calculated field

this is the interesting part of the blog, you can compare a dimension to a parameter using REGEX_MATCH 🙂

if you select India for Example, the row for india will return India and anything else will be “Other”

Use custom Viz vega-lite

In google data studio by default the colors match the categories, in our case, we want to keep the level of details “Country”, but use the new field “Color_Selected” for ( wait for it …) colors 🙂

luckily we have Vega/Vega-Lite custom viz.

it is a very powerful viz, literally you can define literaly any complex chart just by writing script the data is automatically mapped when you assign dimension and metrics, here is the script I am using

{
"$schema":"https://vega.github.io/schema/vega-lite/v4.0.2.json",
"mark":"line",
"encoding":{
"detail":{
"type":"nominal",
"field":"$dimension1"
},
"color":{
"type":"nominal",
"field":"$dimension2"
},
"y":{
"type":"quantitative",
"field":"$metric0",
"axis":{
"labels":false
}
},
"x":{
"type":"nominal",
"field":"$dimension0",
"axis":{
"labels":true
}
},
"tooltip":[
{
"type":"nominal",
"field":"$dimension0"
},
{
"type":"nominal",
"field":"$dimension1"
},
{
"type":"quantitative",
"field":"$metric0"
}
]
},
"height":600,
"width":1300
}

and here is the fields used from google analytics

and here is the final results, Link to the report

a very common pattern in Data Analytics is to calculate top performing items by category, for example Top 5 car sales by Brand and Country , now that GDS support SQL Parameter I thought it is a nice opportunity to try it.

I am using a data set that show sales of items by country and geographic region for a hypothetical Company

to get the rank, I am using Row_Number () analytics functions and to make the example a bit more complicated, I presume, the user want to filter by Sales_Channel , Online/Offline or Both

here is the query

WITH
xx AS (
SELECT
Region,
Country,
SUM(Total_Revenue) AS Total_Revenue
FROM
Sales_Summary_rand
WHERE
Sales_Channel IN UNNEST(@Sales_Channel)
GROUP BY
1,
2)
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY Region ORDER BY Total_Revenue DESC) AS rnk
FROM
xx

now you define the parameter ‘@Sales_Channel’, notice you have to type the values manually, for this example there are only two Values, Online/Offline

and here is the report, notice, I am using vega-lite custom viz as the native visual shows only max of 20 categories, the user will have the option to filter the Top 5,10 etc, it is a very powerful visual analytic tool

Unfortunately as of August 2020, BigQuery BI Engines does not support Analytic functions nor UNEST ( which is required for multiple selection of the parameter)

ok, someone may say ( I am looking at you piedatastudio ) that it was possible before and we don’t need parameter, which is true, technically we can show the rank for the three possibilities (Online Sales/Offline Sales, and both) and just use a UNION them add a filter to select the Sale Channel.

now to make it more interesting, let’s say they want to see the top performing based on a date selection, this year, this month etc, very easy using Dates Parameter

WITH
xx AS (
SELECT
Region,
Country,
SUM(Total_Revenue) AS Total_Revenue
FROM
GIS.salesdates
WHERE
Sales_Channel IN UNNEST(@Sales_Channel)
AND Ship_Date >= PARSE_DATE('%Y%m%d',
@DS_START_DATE)
AND Ship_Date <= PARSE_DATE('%Y%m%d',
@DS_END_DATE)
GROUP BY
1,
2)
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY Region ORDER BY Total_Revenue DESC) AS rnk
FROM
xx

in this cases you can not pre calculate the results, as they are thousands of different combination for Date selection.

Please notice, as the Query is not accelerated by BI Engine in Data Studio, the report will incurs BigQuery cost, very negligible for small tables but still 🙂

I added a new report based on Covid19 dataset which is provided for free by Google.