cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dpetrancuri Frequent Visitor
Frequent Visitor

Bi-Di Relationship Impact on Model Memory Usage

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.


2 REPLIES 2

Re: Bi-Di Relationship Impact on Model Memory Usage

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

Alberto Ferrari - SQLBI
dpetrancuri Frequent Visitor
Frequent Visitor

Re: Bi-Di Relationship Impact on Model Memory Usage

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 342 members 3,272 guests
Please welcome our newest community members: