Forum Discussion
Helper IIAvoiding Bi-directional relationships
I have a pretty stock-standard model in my dataset:
- Fact table (Customer Activity) -- ~65 million rows
- Customer dimension table (DimDemographic) -- ~ 9 million rows, unique IDs
- Product holding dimension table & a calendar (small, inconsequential)
The challenge I have is that each of my customers can belong to one or many 'audiences' and I need to filter the fact table by those audiences.
To achieve that I have a bridging table containing each customer-audience combination and linking it with the Customer dimension table in a many-to-one relationship. I am hoping to propagate filters from the bridging table to the fact table through the customer dimension.
Because the bringing table is much bigger than the customer dimension table, the default direction is going to be from the customer dimension to the bridging table. To reverse that, the only option I have (I think) is to enable a bi-directional relationship.
This is going to kill performance because the bridging table is going to be massive (well north of 100M rows I think) and I will also have to attach a reference table to the other side of it with audience names etc.
Is there any way around this?
MightyMicrobe , One way I suggest is to keep Audience Independent. and then use it as a filter in measures as per need
Measure =
var _tab = allselected(audience[CustomerID]) //Based on audience name
return
calculate(Sum(Fact[Value]), Filter(Customer, Customer[CustomerID] in _tab))
2 Replies
- amitchandak
Super User
MightyMicrobe , One way I suggest is to keep Audience Independent. and then use it as a filter in measures as per need
Measure =
var _tab = allselected(audience[CustomerID]) //Based on audience name
return
calculate(Sum(Fact[Value]), Filter(Customer, Customer[CustomerID] in _tab))
- MightyMicrobe
Thanks for your reply!
