CODEWITHSUNDEEP
filteringwhere-clauselooker-studio
Matthias
Matthias

Reputation: 5764

how to use filter control with IDs (key-value) in Google Data Studio

I'm working on a custom connector for Google Datastudio, which connects to a specific API service following the Google Data Studio Community Connectors documentation and the very nice article on Building a custom Google Data Studio.

The data schema is:

function getFields(request) {
  var fields = cc.getFields();
  var types = cc.FieldType;
  var aggregations = cc.AggregationType;

  fields.newDimension().setId('id').setName('ID').setType(types.NUMBER);
  fields.newDimension().setId('date').setName('Date').setType(types.YEAR_MONTH_DAY);
  fields.newDimension().setId('user_id').setName('User ID').setType(types.NUMBER);
  fields.newDimension().setId('user').setName('User').setType(types.TEXT);

  //fields.newMetric().setId('time').setName('Time').setType(types.DURATION).setAggregation(aggregations.SUM);
  fields.newMetric().setId('time').setName('Time').setType(types.NUMBER).setAggregation(aggregations.SUM);
  fields.newMetric().setId('revenue').setName('Revenue').setType(types.CURRENCY_EUR).setAggregation(aggregations.SUM);

  fields.setDefaultDimension('user');
  fields.setDefaultMetric('time');

  return fields;
}

Since the data that is retrieved from the API might be quite large, I want to apply the filter given by Google Data Studio directly at API level. Problem is, that the filter controls in the studio will usually display a user friendly humand readable name like the user name and surely not the user id. But, the API level needs the user id for filtering. The request I get in:

function getData(request) {
  // retrieve the data
}

gets this filter without having the ID. And I'm not really keen on showing the user IDs in a user-facing filter control.

{
  scriptParams={lastRefresh=1588507514238}, 
  fields=[{name=time}, {forFilterOnly=true, name=user}], 
  dateRange={endDate=2020-05-02, startDate=2020-04-05}, 
  dimensionsFilters=[[{fieldName=user, type=INCLUDE, values=[John Doe], operator=IN_LIST}]]
}

I see that the filter control in the studio does not support key-values. Any idea how to achieve that in any other way? My only thought would be storing the known user-user_id combinations in a cache and convert user name to user id before sending the API request. But hopefully, there is any easier way?

The correct filter should be like this:

{
  scriptParams={lastRefresh=1588507514238}, 
  fields=[{name=time}, {forFilterOnly=true, name=user_id}], 
  dateRange={endDate=2020-05-02, startDate=2020-04-05}, 
  dimensionsFilters=[[{fieldName=user_id, type=INCLUDE, values=[1234], operator=IN_LIST}]]
}

Upvotes: 0

Views: 1174

Answers (1)

Matthias
Matthias

Reputation: 5764

Actually there is no such support by Google Data Studio as explained here. This totally makes sense. Why? If the designer of the report has added a filter control (e.g. user names) then that filter control needs to know all possible users. Otherwise, it can not display all options / selections to the user. Therefore, it must ask the connector for all non-filtered data at least once. The Data Studio will automatically take care of caching.

Nevertheless, it is recommended to cache the HTTP result in the connector just in case the UI decides to ask for data again. This is when you need the CacheService for which there is a good demo implementation here. In such case the very first HTTP call will take a while but all subsequent will be much faster. Imaginge your initial result had 1.000 rows, you still may need to filter it inside the connnector for a final result let's say of 6 rows, which may be slow. Filtering which is done by the UI is even slower.

If you still need to convert between user names and user id or similar relations, then you may need to cache those relations whenever you retrieve data and convert the UI filter (which is for user name) to the id (e.g. user id) by resolving this relation with the cached data. In this case you will be ignoring the initially cached result of 1.000 rows (just an example), adjust your HTTP GET query with the id (e.g. user id) thus retrieving only 6 rows. This is one API call, but no filtering in the connector nor in the UI. Filtering for connector implementations is explained here as well as in the filter API reference.

You need to decide yourself if you need to filter on API level, inside the connector or automatically by the UI. In my case the API level is the faster one but it invokes an API call whereas some APIs are very limited in number of subsequent (frequency) or daily (quotas) calls.

Upvotes: 1

Related Questions