Add filter from data model to dataset

Ask the Community
1

My data model includes a boolean field for records that are to be kept for reporting within Holistics. When I pull the model in is there a way to add the filter to the dataset? Or is a data query the only way I can add a filter to my dataset?

TIA

2

Hi @Cherice

Can you expand a little bit more on your use case? From my understanding, you have something like a field duplicated_records which can either be true or false, and you want to filter out all false rows before adding that model to the dataset. Am I understanding correctly, and why would it be necessary for your use case ?

3

Hi @minhthanh3145 , that’s correct yep! It’s necessary as not all the orders that we have in our main data model are to be used in reporting, for various reasons.
So although we want to keep those records in our data warehouse for analysis, we don’t’ want them available in the holistics dataset for stakeholders to report on.

4

Hi @Cherice

Sorry for the late response. I missed the notification for this thread :sweat_smile:

I’ve discussed with the team and there are two ways to get this done:

  1. Use query model to query from your original model and filter out all the rows that match a certain condition
  2. Use row-level permission to restrict your users so that they can only view rows with certain values on a particular dataset.

The second way would work only if your stakeholders do not have access to the data source, so any analysts or admins will still see all the data when exploring the dataset. It’s also straightforward just to follow the docs, so I won’t show it here.

Query model

Please read this docs to understand the syntax of a query model.

Query model is a way to define a data model to sit on top of a SQL query. For a regular data model, all dimensions are retrieved from the table name. For a query model, all dimensions are retrieved from the SQL. But we can derive a query model from a regular model as well…

Let’s say that I have a regular model

Model users {
  type: 'table'
  label: 'Users'
table_name: "users"
  data_source_name: 'demodb'
 dimension id { 
// ....
  }
  dimension gender {
    label: "Gender"
    type: "text"
  }
  dimension full_name {
//...
  }
}

I want to now create a new model that derives from this model, but only with rows with gender equals female. I can create a new query model with syntax like this:

Model female_only_users {

  type: 'query' // <- This is `query` instead of `table`
  label: 'Users'
// Do not include table_name, since we're using SQL
// table_name: "users"
  data_source_name: 'demodb'

models: [users] // <- this syntax lets Holistics knows that we're gonna reference `users` model in this model.

// This query will retrieve all the users with gender `female` and use that as the basis for this data model
query: @sql
select * from {{ #users}}. as users where users.gender == 'female';;
 dimension id { 
// ....
  }
  dimension gender {
    label: "Gender"
    type: "text"
   definition: @sql {{ #SOURCE.gender }};;
// ...
  }
  dimension full_name {
//...
  }
}

Now in my dataset, I can reference female_only_users instead of users model. If you have a lot of relationships to the model users already in your dataset, then one way would be to rename it to old_users, and then the derived model can be named users. This way you don’t have to modify the relationships in your dataset, provided that all the relevant dimensions are present in both users and old_users

Let me know if this addresses your problem!

1 Like
5

Thanks very much! I’ve only done query models form the database itself but didn’t realise I could build them based on existing models in holistics. That’s sorted the problem now :slight_smile:

2 Likes