https://www.growthbook.io/ logo
Join Slack
Powered by
Hi there, I have several projects and one fact ta...
# experimentation
l
Hi there, I have several projects and one fact table. The table is in ClickHouse, and the main sorting key there is the project. If you add a filter by project, the number of rows scanned and the query execution time are reduced by an order of magnitude. I have a feature request: is it possible to make it so that in the future, filters can be set for specific projects for the fact table? Or at least in the experiment settings, as is done with slices? I also see the extra query condition in the experiment, but it filters only experiment assignment table.
h
Hi there Vladislav, If you have metrics that only use filtered data, we should add those filters to the query to improve query execution. If you add a single metric that has no filters, we have to scan the full table any ways. But I also am not sure I understand your goal. Why can't you just use metrics that have a certain filter in a given experiment? What is the mapping between experiments and the 
project
column you are filtering on?
l
> If you have metrics that only use filtered data, we should add those filters to the query to improve query execution. If you add a single metric that has no filters, we have to scan the full table any ways. Yep, as I remember, I asked to implement that 🙂
h
Yeah!
So now you want to just apply a set of filters to all metrics automatically in an experiment?
l
And it works fine, but some of my friends, who are using Enterprise version of GB, having an issue, that queries with the fact table optimization fail, but work fine when you turn it off
> But I also am not sure I understand your goal. Why can't you just use metrics that have a certain filter in a given experiment? What is the mapping between experiments and the 
project
column you are filtering on? This could be a solution. I thought about it, but with such approach we would have x5 of current metrics count and must maintain them
h
So I'm not sure of your current request. You want to input 
project
at the experiment level and apply that as a filter to all Fact Tables in the analysis?
I think this is do-able today.
1. Add a 
project
custom field to experiments so you can enter a "project" at the experiment level. 2. Modify your fact tables with a custom WHERE statement like the following: https://docs.growthbook.io/app/sql-templates#custom-field-filters, where you put something like 
{{_#if customFields.project}} AND project = '{{customFields.project}}' {{/if}}_
I don't think we have an easy way to pass in a list of projects right now and use 
IN
, but as a workaround for now you could add 
project1
and 
project2
and so on, if there's only a couple projects per experiment.
l
So I'm not sure of your current request. You want to input 
project
at the experiment level and apply that as a filter to all Fact Tables in the analysis?
I also don't know how to implement that correctly, especially for the several fact tables. We should have mapping 
gb project <-> table project
somehow. My suggestion is to add the ability to select a column that identifies the project and enable mapping within it. Then use this mapping for the __factTable query.
h
I think my above suggestion solves this problem, but you have to input the 
project
each time for each experiment. I think being able to generically inject a custom value set at the project level would be easier.
l
Yep, that's the best option we have for now. Could you please add an option to use 
project_id
in sql templates?
Thanks Luke!
h
Could you please add an option to use 
project_id
in sql templates?
Yeah, this is a reasonable ask.
Can you open a github issue for that?
👌 1
l
Sure, will create tomorrow
10 Views
Open in Slack
PreviousNext
Powered by