Best practices for Power BI and a large database?

[Michele_Zanoni]

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?

[GilbertQ · Accepted solution]

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.

[Michele_Zanoni]

Hello Gilbert,

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

[Greg_Deckler]

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?

[Michele_Zanoni]

Hello.

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