Bi-Di Relationship Impact on Model Memory Usage

[dpetrancuri]

Good Day Everyone:

I have a model where I have abused the hell out of Bi-Di relationships and now that I can visually filter slicers / etc by a measure (as recently illustrated by "The Italians" @marcorusso @AlbertoFerrari ), I will begin to unwind this mess.

But, in the meantime, I would really like to know how Bi-Di relationships affect model memory size / footprint / utilization and performance in general. My model is a true star-schema and is not large by any reasonable measurement, but it takes a long time to process and I recently bumped into the dreaded insufficient memory in the allocated (and I think it's probably as a result of my bi-di abuses) and I had to bump up my AAS instance to the next S level as a result.

I'm very resourceful and have searched exhaustively for this information or someone to comment definitively on this and have found nothing. Can someone please help?

Thank you for your attention and consideration.

With Gratitude,

SQLArchitect

P S 

Yes. obviously I have failed to head the advice of Marco and Alberto to eschew bi-di relationships to this point, and the reason why I have done so is I'm trying to build a BIaaS model for my employer that would allow clients to leverage the model in such a way as to not need much DAX expertise to develop rich visualizations with little to no DAX. I realize this design goal may be unattainable at this point.

[AlbertoFerrari]

You should not see an impact on processing performance, as long as you don't have very complex calculated columns that may be affected by bidi. On the query memory usage and/or speed, on the other hand, the impact can be quite devastating, but you know, the answer is always the same: it strongly depends on the measures you wrote.

You need to use DAX Studio and VertiPaq analyzer to gather this information, and then process them with your brain. Every model is unique, depending on data distribution, this is why you will find it really hard to gather "golden rules" from the web. Most of these, by the way, would be misleading. This is the reason we never provide rules of thumb, or simple directions. VertiPaq is a complex beast, you need to thoroughly study its internals to understand exactly what is happening.  Ths, by the way, is the reason I find it so fascinating to study 🙂

Alberto

[dpetrancuri]

Thank you for your insights, Marco.

I'm working to remove the bi-di relationships and also getting rid of primary keys from fact tables. These two items alone seem to be having significant performance improvements at relatively low cost in effort. The hardest part is giving up the power of the bi-di relationships from a simplified self-serve UX perspective if users want to build reports off a model that has bi-di it becomes much easier for them to do so than in one without. However, that being said, I've reached the point where I believe the trade-off of sacrificing bi-di to be worth it, especially if I can manage the usability issues from providing guidance on creating appropriate "fact table relationship" driven sync'd slicers as per the recent article you published on SQLBI.com