Filter a fact table based on another (indirectly connected) fact table

[Varishtha]

I have multiple fact tables (red arrows) connected to one dimension table ('user') in my data model. 'user' table has 'ID' as primary key which serve as foreign key in all the connected fact tables (using column name - 'UserID'). 'jsacm_capmpaigns' contain each unique campaigns details and 'js_main_clicklog_userwise' contains dailywise and userwise daily clicks summary and 'user' table has details of each users. A user can have multiple campaigns and a campaign can have multiple jobs(uniquely listed in 'jobsdatafull' table). Below is the picture for reference.

Now since i have formed a two-way relationship between 'jsacm_campaigns' and 'users' table, filter can flow from 'jsacm_campaigns' to 'users'. But I want to filter the other fact table 'js_main_clicklog_userwise' as well using  filters from 'jsacm_campaigns'.

For example, for every campaign in 'jsacm_campaigns' table, i want to filter 'js_main_clicklog_userwise' table, based on below filters:

1. 'jsacm_campaigns'[user_id] = 'js_main_clicklog_userwise'[user_id]
2. 'js_main_clicklog_userwise'[date]<='jsacm_campaigns'[end_date]
3. 'js_main_clicklog_userwise'[date]>='jsacm_campaigns'[start_date]

Some reference to visuals i want to create. Clicking on campaign name should filter the two trend graphs below.

Once i can filter 'js_main_clicklog_userwise' table based on above conditions, i intend to visualise 'js_main_clicklog_userwise' which can be filtered based on above conditions from 'jsacm_campaigns'. Also if possible, i want to drill down campaigns from main page of report filter to another detailed page.

It would be great if someone could help me with this.

[lbendlin]

Instead of these data model modifications the safer way is to use TREATAS to transfer a filter context from one table to another (unrelated) table.

[Varishtha]

hi @lbendlin 

Thanks for the response.

Could you please explain more about this approach, since i need to use multiple filters on the unrealted table.

Your help is greatly appreciated.

[lbendlin]

There are good tutorials on the usage of TREATAS - choose one that fits your learning style. You can also look at the queries in the performance analyzer, they make extensive use if it.