Flexible analysis with big datasets with more then 4000 columns

[elaj]

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

[jstorm]

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.