cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Flexible analysis with big datasets with more then 4000 columns

Hi experts,

 

I work in a market research company. We try to figure out if Power BI fits our needs.

We have a very big project right now. There will be a new wave every month. We want to compare these waves with each other and also have other deeper insights into the data. Our creative researchers are constantly coming up with new ideas. we are lagging behind in the evaluation. now the idea is that our data scientists will prepare and model the data in power bi and our researchers will be able to access the data independently to answer specific questions for themselves that might not have been defined before. Is this generally possible with power bi? or does everything have to be ready in advance?

 

Right now I want to test out power bi desktop and load only a SINGLE wave of many waves (20MB excel data set with more than 4000 columns is one wave). After loading it, power bi is so laggy that I cant hardly do anything anymore. (i5 processor and 16GB RAM). And this should be just one twelfth after one year.

 

Now the assumption creeps up on me that I can only load specific data to process clearly defined questions. But since we need more flexibility, we do not know in advance which data will be needed later. And the plan also was to edit and clean the data directly in Power BI without the need of spss or so. Power BI doesn't seem to be the right tool? or is that an user error?

 

Thank you for your help.

 

elaj

1 REPLY 1
Highlighted
Post Patron
Post Patron

Re: Flexible analysis with big datasets with more then 4000 columns

Although you haven't given enough information to be sure, it is likely your are experiencing two issues:

1.  You are using DirectQuery instead of Import which significantly impacts performance both of Power BI and your SQL server

2.  You are loading WIDE and short tables instead of long and narrow.  Data analysis software like PBI is suited to work with limited columns and (nearly) unlimited rows, which is different from how people usually consider the data.  4000 columns is outside the scope of PBI, but 4 million rows would not be.

 

Power BI is very powerful and can handle enormous data loads very efiiciently, but you need to understand how it works and why it works that way.  I would recommend doing more research and watching some tutorials before making any assumptions. 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors