Goal:
I have a CustomerGroup table where a customer can appear multiple times (one row per Concept per time period), so customer_code is not unique in this table. I want users to filter by Concept on a dashboard and have that filter apply to our sales-orders table (SL_CustomerOrders) - showing only sales for customers who belong to the selected Concept - without duplicating or losing any sales rows.
Schema setup:
- Customer (master table, unique key: Code + Company)
- CustomerGroup - Child of Customer, joined on CustomerGroup.Code = Customer.Code AND CustomerGroup.Company = Customer.Company. Multiple rows per customer (one per Concept/period).
- SL_CustomerOrders - Child of Customer, joined on SL_CustomerOrders.Customer = Customer.Code AND SL_CustomerOrders.Company_Customer_Key = Customer.Company.
So both CustomerGroup and SL_CustomerOrders are Children of the same Parent, Customer.
What I tried:
I want to filter Concept (from CustomerGroup) and have it cascade to SL_CustomerOrders via Customer as the bridge, without joining CustomerGroup directly to SL_CustomerOrders (that caused sales duplication when tried via MV/SQL join).
I set CustomerGroup as the Base Table on the insight, but the Query Plan Viewer shows SL_CustomerOrders completely disconnected from Customer/CustomerGroup in the plan - [attach query plan screenshot].
I then tried switching the Base Table to SL_CustomerOrders instead, expecting Incorta to traverse SL_CustomerOrders → Customer → CustomerGroup for the filter, but i end up with same result: concept from CustomerGroup as filter has no effect.
Question:
- Is Base Table the right mechanism for this "shared parent, filter cascades to sibling child table" pattern, or do I need a different approach (e.g., a bridge/junction table, or restructuring the joins)?
- Is there a known limitation with propagating filters between two Child tables that share a common Parent, when neither is unique on the join key?
- Any working examples of a similar "dimension used purely as a filter source for a sibling fact table" pattern would be really helpful.
Happy to share more schema/query plan screenshots if useful.
