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
Michele_Zanoni
Frequent Visitor

Best practices for Power BI and a large database?

Hello all,

 

I'd like to ask for advice on deciding how to proceed with Power BI. This is our current predicament: we have a database where up to half a million records are added every day. We sit at about 40 millions rows and 20 Gb today. The visuals we need to present to the clients involve operations of sum, distributions and so on. I'd like to understand the correct way of using Power BI:

 

  1. The dashboard (and reports) will have a slicer. The interest of the client is to usually see the data relative to the last day or few days. The idea is that I would like to not have the model download 20 Gb of data when I know I'll need only 1. I'd like the data to be downloaded/imported only if the slicer is moved to include a longer period of time. Is it possible?
  2. All the clients will have access to an account on Power BI Pro and we'll share with them dashboards and records. Can Power BI Pro handle data sources of that dimension? What are the best practices in doing so?
  3. Direct Query of Import? From what I understand Import would be limited at 1 Gb (compressed). One million rows max? But it would be faster. With this amount of data, is Direct Query going to pose problems of performance?
1 ACCEPTED SOLUTION
GilbertQ
Super User
Super User

Hi there I would look to converting that if possible to Azure Analysis Services, which can compress the data.

Along with that you will be to enable Row Level Security.

As well as using DirectQuery to the Azure Analysis Services database.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

4 REPLIES 4
GilbertQ
Super User
Super User

Hi there I would look to converting that if possible to Azure Analysis Services, which can compress the data.

Along with that you will be to enable Row Level Security.

As well as using DirectQuery to the Azure Analysis Services database.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hello Gilbert,

 

thank you for the answer. I will look into Azure Analysis Server and come back with questions when they arise.

Greg_Deckler
Super User
Super User

Sounds like Direct Query is going to be your best bet. If you have Power BI Pro or Premium you can have larger imported datasets. Where does your data sit, on-premises, cloud? What is the data source, SQL?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hello.

 

Thank you for your answer. The data will be on a SQL database located on an Azure service.

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