Question
We have multiple clients and to maintain this we modeled the dim_client table and we implemented the data security rules on this table.
We have 2 fact tables (fact_sales and fact_budget) to have the sales information and sales target information.
Finally we have one dimension table (dim_channel) to maintain the channels information and we are using this channel in filter area.
We have one more common date dimension table, that is dim_date
Note: According to our business, Channels are dependent on the client, meaning every client is having their own channels.
We create the star schema data model with 2 fact tables and 3 dim tables and in addition to this we draw one more additional relationship between dim_client and dim_channel to implement the data security, without this relation data security rules are not working properly because on the filter area we should display the channels related to the logged in user (client).
According to the best practices of Sisense we should not create any relationship between to dim tables.
Answer
You can create a relationship between dim tables, you just have to be careful how and when you do it. Remember, they are all inner joins.
As long as there is a 1:1 relationship between client and channel, you can create something like the diagram below. dim_channel will filter dim_client, which in turn will filter your fact tables.
Your dim_channel should exist as an "outrigger" dimension of "dim_client.
