Data Gateway personal mode consume a lot of memory

[gcresta]

Hi guys,

I have installed on a Windows Server 2008 R2 Standard the Data Gateway (personal mode) and i noticed that it consumes a lot of memory, 1gb divided into: 360mb for the process Microsoft.PowerB.DataMovement.PersonalGateway.exe and the others for several Microsoft.Mashup.Container.NetFX45.exe.

Anyone knows how can I improve performances? What is the purpose of the process Microsoft.Mashup.Container.NetFX45.exe ? Can be disabled?

The Dataset published in Power BI Service is not so large to jusity such memory consumption...

Thanks in advance.

G.

[zoloturu]

Hi @gcresta ,

Give some information about your report and system:

1) How much memory (RAM) are consumed by Power BI Desktop during refresh (max) and after the refresh?

2) What is the PBIX size?

3) What about CPU characteristics (clock, model)?

4) Number of cores at your machine?

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question?Mark my post as a solution!

[gcresta]

Hi @zoloturu ,

thanks for the answer.

1) I cannot measure it. Power Bi Desktop is not installed on the server, I develop Datasets and Reports in local and publish them.

On the Server is only installed the Gateway and Oracle db.

2) 90mb

3) 3.30 Ghz, CPU E5-2643

4) 4 cores

Regards,

G.

[zoloturu]

@gcresta ,

Thanks for details.

You can measure RAM usage at your local at least. Provide that information, please.

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question?Mark my post as a solution!

[gcresta]

@zoloturu ,

the RAM usage during refreshing is 1.6Gb (max), after is 1.2.

Regards,

G.

[zoloturu]

@gcresta ,

It is not the proper way to reduce by disabling some processes of apps. You can achieve some unexpected results and if something will happen Microsoft will no longer support you in this particular case.

I recommend you to go through standard optimization techniques. Apply as much as possible, but carefully. Be sure you made a backup of report before appling optimizations.

1. Reduce data imported into the PBIX by:

a) Removing unused columns in tables. Each separate column takes additional storage.

You can check column's size first. To do that use Vertipaq Analyzer tool. It is free.

Excel version of it you can download from here - https://www.sqlbi.com/tools/vertipaq-analyzer/

Short tutorial - https://www.sqlbi.com/articles/data-model-size-with-vertipaq-analyzer/

Power BI version - here

b) Decreasing cardinality of columns. Here you need to work with data types most of all. Work with Vertipaq Analyzer to check column's cardinality.

Remove a time part from date column if you don't need it.

The same works for text fields. Remove part of text if it repeats in each row.

And try to avoid fractions in numeric fields. Convert them into integer or currency (it is stored as integer, actually).

2. Optimize DAX calculations

a) Converting calculated DAX columns into generic ones. As you know Power BI Desktop has a Power Query (M) editor, where you can create different and even complex calculations. So try to calculate columns at this step and not with DAX. It can help Vertipaq engine of Power BI to handle the best sort order for storing data. (It is not related to DAX measures, only for columns).

b) Use variables when DAX code repeats.

Information - https://www.sqlbi.com/articles/variables-in-dax/

c) Try to optimize DAX formulas of measures, columns and tables.

There is a nice tool called DAX Studio which allows you to easily test and modify DAX code. Download and tutorial link - https://daxstudio.org/ There is another tool which can run DAX code against PBIX or Tabular model - SQL Server Management Studio (SSMS).

So when I see that measure runs slowly then I copy that code to DAX Studio/SSMS and test the performance by experimenting with different functions.

3. Remove unused fields from Power BI visuals especially tables and matrixes

You can check that speed of render by using Performance Analyzer feature. Tutorials - here and here.

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question?Mark my post as a solution!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups