DAX Fridays! #140:Flat tables or models in Power BI? Which one should I choose and why?

Should you work with flat wide tables or create models in Power BI? In this video, I walk you through the advantages of creating models instead of flattening your tables.
#curbal #daxfridays #powerbi

Link to resources mentioned in the video:

SUBSCRIBE to learn more about Power and Excel BI!

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

▲▲▲▲▲▲▲▲▲▲

Our PLAYLISTS:

************

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#daxfridays #curbal #dax #powerbi #mvp

[jon]

The maximum number of columns allowed in a dataset, across all tables in the dataset, is 16, 000 columns. This limit applies to the Power BI service and to datasets used in Power BI Desktop.
Power BI uses an internal row number column per table in the dataset, which means the maximum number of columns is 16, 000 minus one for each table in the dataset.

[Xperiun_]

Perfect Ruth. This is something I always mention to my students, and many of them still do not follow these advices and start building big and flat tables. So I have always to remember them about these modeling best practices!

[ayobamiologun]

No doubt, using models is far way better. It helps reduces redundancy, it saves space and enhances performance. Thank you Ruth for the exposé once again.

[dbszepesi]

Power BI Marketing needs to rip that off - 'Your data will get wings and fly!' Awesome video!

[midsummer]

Thanks Ruth, I found this very helpful

[Bharath_PBI]

One of the main difference between flat table and model is the auto exist concept of DAX, which I believe the reason to be listed next to performance..

[Phoenixspin]

I'm in the Introverted bucket, by the way. In Excel, I will often use Index/Match lookups to build one big table to use as a Pivot table source. I wonder does this make me a bad person. Another implementation is using Access to join various tables in one Account Schema file. An account number may be categorized by various attributes spread across various tables. I join them all together in Access. So I guess I am making a big flat dimension table before it's imported into a Power BI model, which then links that Account Schema file to a GL Transactions file. Not sure I'm using the correct terminology like Ruth would. I wonder though if I am a bad person for doing this and not importing and modeling all of the different account dimension tables directly in the Power BI model. I want to stay on Ruth's good side but sometimes breaking the rules feels convenient and practical. Am I a bad data person? I want to know the truth.

[oxxygen]

Hi Ruth, It might be interesting to amend this topic (model design) by showing performance tuning for table layout, column choices, and cardinality optimizations by doing a tutorial on the vertipaq analyzer. I use this tool all the time...its crazy helpful!

[rkbrook]

The important sound-bite is "Small, but Tall". Thank you Ruth

[arlansouza]

Thanks, Ruth! At 8:32, any comment about one vs multiple fact tables?

[robionaire]

Funny i actually found out just recently that you can have one flat table in PBI and still create reports...I always thought one must create a data model. Great that you shared the benefits of using a model and again the way you clarify things is always gold. I still get confused though when I have two fact tables like orders and order details. Would be great if you could do a tutorial on this. If you have done one already kindly share the link. Thanks!

[jntconsulting]

The first thing caught my eyes is your hair..it looks amazing :)

[cruc]

Hola Ruth!
First of all I want to thank the universe I found your channel XD. I've been working with Power BI for a year without any knowledge at all and I am now learning all the things I did wrong.
One of the issues I need to address guess what.. I used to work with flat tables.
I am currently refactoring some of my dashboards from flat tables into models but I am struggling sometimes and I finally need to merge tables to finally get the information I want. For instance, when I have 2 fact tables - that I can't directly trace because it might be a many to many relationship - no problem, I create all the look up tables, trace them all and everything seems to work until I try to put information from the 2 fact tables into the same chart. Then Power BI shows an error in power view.
Well, I'll keep investigating but if you could refer to some video about complex data models I'd very grateful :)

Gracias!! Un abrazo

[manuelcerqueira]

Hello Ruth, my spanish colleague!! I'm very glad with your work It's very useful for my activities. I have a doubt with the flat tables paradigm. By using Influencers visual is necesary to have the data in a flat format, how can I rise this problem based in a model of several tables? Do I have to create an extra table composed of the data from other tables? Plese give me your advise! ;-) Manolo ;-)

[jerrykappa]

Interesting.... I never use only one table. I guess as well in terms of flexibility. For ex price change, product class, description or attributes, BOM... you name it. Therefore I never use a single big table.