Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KevinSGoff
Helper I
Helper I

Dynamic Dimension/Grouping Solutions with unpivoting - concerns about file sizes

I come from the SSRS world, where developers had the abilty to define both dynamic measures in a chart "and" dynamic groupings, based on a simple runtime expression.

 

In PBI, we have the abilty to define a dynamic measure with a SWITCH/CASE statement based on a user selection, so that works great as well.

 

Unfortunately, when someone wants to implement dynamic groupings in PBI, that's a different story.  Unless there is some brand new feature I haven't seen, a developer cannot have a SWITCH/CASE statement to evaluate a user selection, and then show a breakout that could be "by department", or "by GL code", or "by product". etc.  

 

There's only been one solution I've seen, and that involves unpivoting the fact table for the desired dimensions and then setting up a simple join/"bridge".  Many authors and bloggers have shown this solution.  I've done it, and it can be made to work, but suffers two potentially serious drawbacks:

 

- First and foremost, it does increase the size of the PBIX if you're using the default in-memory model.  Even with compression, the increase in size is not trivial.  I can provide some details in another post.     Now, depending on the deploy model, this might not be a big deal.  If I deploy to an on-prem report server with a large amount of memory, the increase in size might be harmless.  However (in our case), if you're using Power BI Embedded where the licensing is based on factors including memory capacity, then it's potentially a big issue.

 

- Second, and maybe more philosophically, this is a "copy and paste" model approach.  Not very elegant, and gets even trickier if you built a solution that allows multiple levels of dynamic drilldown (as we were hoping).  Maybe there is a workaround, but I was only able to get it to work by exploding the original fact table twice.  I'll stand corrected if there's a way to implement multiple levels of dynamic dimension drilldown without doing the "unpivot" twice on the fact table - but so far that's been the only way I can solve it.   

 

But even if I'm wrong on #2, #1 is (for us) the bigger issue.   Is there any hope that Microsoft will someday implementing dynamic dimension handling in PBI, in the same/similar manner they did it in SSRS?

 

Thanks,

Kevin S. Goff

 

 

 

 

 

 

 

 

 

4 REPLIES 4
v-rzhou-msft
Community Support
Community Support

Hi @KevinSGoff 

You problem may be that when you want to build a slicer or a measure to dynamic show results in your visual, you may need to unpivot different kinds of columns to get a category column and a value column.

Then the size of your data source may be larger.

Power BI will calculate by column, so for Dynamic Dimension, unpivot is useful.

You may refer to blogs as below for more details about Switching Dimensions Dynamically in Power BI.

If this reply still couldn't help you solve your problem, could you provide me a sample without any sensitive data like what you are dealing with by your Onedrive for Business?

And please show me the result you want.

This may make it easier for me to understand your requirement.

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Thanks for replying.    I had seen that blog before (from Danica Hui) but took a closer read.

 

It does seem that some form of the separate approach might carry somewhat less overhead.

I have a fact table with a large # of rows by ship transaction.   My "dynamic dimensions" don't involve sales transaction IDs....they involve rollups by customer, product, ship location, GL acct, region, carrier, etc. etc.

So it occured to me I could take a varation of the famous Kimball "Junk dimension" approach....create a unique dimension table for a cross join of customers/product/ship locations/etc.  I can create it dynamically and assign a unique identifier to the fact table as a cross-joined dimenison ID.  Will still be rather large, but probably a little less overhead that the unpivot approach.

 

So #2 is probaby a better approach - as we need dynamic dimensions.  They are very important for us.

 

Now...at some point, I hope the PBI team makes it as easy to handle dynamic dimensions as they do dynamic measures.  But #2 is probably something we can sustain.    Thanks!

vanessafvg
Super User
Super User

could you provide some more context for example demonstrate what it is that you want to do exactly, its hard to conceptualise without any examples.  Are you able to provide some sample data with a few scenarios or provide a pbix of the solution you currently have?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




For data privacy reasons I can't use the one I have right now, but I'll come up with one and post it over the weekend.      

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors