Dynamics marketing analytics templates reports timeout

[avaldes]

Hi, not sure if it is the right place to post this question. I'm using the report templates provided by Microsoft for Dynamics marketing analytics. The reports connect to a blob storage where Microsoft exports the Dynamics marketing interactions info. The reports are failing by timeout and in the documentation, they say that it is a possible issue and recommend keep the data into de blob under 5gb and 50k blobs in total. My problem is that I have more than 200k files for one month and we are a small company. I think It should be a very common problem and I can't find a way to reduce the number of files other than filter out data. I try to filter the data on the report by the blob modified on, use data flows, or copy at least one month of data to a new blob storage using ADF but in all cases, the report failed by timeout. I'm looking by a solution that allow me to refresh with at least 6 month, or someone that had the same problem that can provide my a workaround. Thanks.

[v-eqin-msft]

Hi @avaldes ,

There is a 1 GB limit per dataset that is imported into Power BI.

As we know, the workspace storage is 10GB, but when we refresh the 1GB dataset , actually we need 2GB storage size, so if you have 10 datasets with 1GB size, the refresh is probable not working.

You could use DAX Studio and see which columns are taking the most space and look to make them smaller or remove them. Otherwise your need to purchase Power BI Premium license.

Or please try to use Incremental Refresh to reduces overall consumption of memory and other resources.

To optimize the model, you can refer to the details below:

Optimization guide for Power BI - Power BI | Microsoft Docs 

• Data reduction techniques for Import modeling
• DirectQuery model guidance in Power BI Desktop
• Composite model guidance in Power BI Desktop

Refer to :

Manage data storage in your workspaces - Power BI | Microsoft Docs

Can add more than 1 GB limit per data set, into Po... - Microsoft Power BI Community

How to purchase Power BI Premium - Power BI | Microsoft Docs

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[avaldes]

Hi @v-eqin-msft Thanks for the answer. In my path to the solution, I had applied your recommendations. I'm pretty sure that the problem is more the number of files than the size. I create a new blob storage and copied using ADF only one month of data (~ 600 MB and + 200k files). The size is under 1GB and the report failed refreshing from this new blob too. I ended up copying to a SQL Database the blobs and form that the report refreshed. I think the problem is searching into a blob with this number of files. I understand that search in a blob is more inefficient than in a DB because the data is unstructured and without order criteria but I wonder how is supposed to work the report templates with this architecture using blobs. Maybe I'm missing something. I was hoping this had happened to a lot of people working with these templates.

[earagon73]

Hello! Please can you share how do you copy the blob storage to sql server database?  We have the same problem with reports with 1 month using Dynamics 365 Marketing.  The Power BI reportas take more than 8 hours and don't finish.  If you can help us please contact to me by email efrainar@partnerhelper.com.  Thank you!