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

Best practice data import and model

Hi there Power BI Community

 

I have a strong technical background but I am new to Power BI Desktop. I have the following constellation:

- Monthly exactly 1 file must be imported to Power BI. I can deliver it in Excel or CSV. Typically the file has about 180'000 rows and 10 columns.

- The report and the corresponding visuals in Power BI have to be updated with the data from the previous point.

- There is no limit for an end of the report.

- The report is only used by me.

- The monthly file has the same structure like the previous file

 

Now I don't want an unwanted surprise in X months/years where the report would be unuseful due to the data size. Therefore I wanted to ask what you experts think would be potential risks for this report in the future, what can I do to prevent such a surprise?

I tested a table where I simulated a "mastertable" where I appended almost 40 single month files to the mastertable which resulted in about 7 million rows - had no error message. But the report has no visuals so far so I can't say if this would be a problem.

Is is correct to append the monthly table to the mastertable? I read that the folder option would be better in this case - why?

 

Thank you for your responses, any help is appreciated.

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@datafield , The first two things are Star Schema and Date table.

Prefer Single directional 1 to Many Joins. Avoid Many to Many and bi-directional join

 

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

https://www.sqlbi.com/blog/marco/2017/10/02/why-data-modeling-is-important-in-powerbi/

 

Date Table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

Performace :

https://www.thebiccountant.com/speedperformance-aspects/

 

 

https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-The-Do-s-and-Don-ts-of-Power-BI-Rel...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

4 REPLIES 4
ToddChitt
Post Prodigy
Post Prodigy

  • The Folder source is extrememly useful, but can be slow. If you are refreshing once a month, I suggest you live with it. 
  • I have a model with 45 million rows. It is about 600 MB in size. It takes an hour to refresh, coming from Azure SQL.
  • Data is compressed about 10 to 1. If you have 100 MB of data in CSV files, you will end up with a Power BI model significantly smaller, maybe 10 MB.

Thank you!

amitchandak
Super User
Super User

@datafield , The first two things are Star Schema and Date table.

Prefer Single directional 1 to Many Joins. Avoid Many to Many and bi-directional join

 

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

https://www.sqlbi.com/blog/marco/2017/10/02/why-data-modeling-is-important-in-powerbi/

 

Date Table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

Performace :

https://www.thebiccountant.com/speedperformance-aspects/

 

 

https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-The-Do-s-and-Don-ts-of-Power-BI-Rel...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Thank you for your explanation.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.