cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akapoor03
Helper III
Helper III

Space consumption issue and error-Urgent

I have recently started using Power BI and till now have basically created 4 reports. My data source is a custom connecter with .mez extension provided by the warehouse management system. I directly use this connector using Get Data option and it gives me below view-

akapoor03_1-1627430656755.png

I use the tables that i need and apply transformation steps in Power query and click apply.What I find is that it takes about an hour to upload data when I click Close and apply. 

Also, after creating 3 reports, when I clicked Close and apply for this 4th one, it showed me this below error after refresh for about 1 and half hour.

 

akapoor03_0-1627430330657.png

I do not know what is consuming space and how can I free up the space or why is this happeneing only after crating 3 basic reports. My Data load  looks like below-

akapoor03_2-1627430907654.png

My Laptop's specification are-

akapoor03_3-1627431015505.png

 

1 ACCEPTED SOLUTION

@akapoor03 Measures will not affect data load, only calculated columns. The code looks relatively innocent enough. You can use performance analyzer in Power Query Editor to perhaps see what row is taking the most time. Alternatively, duplicate your query and disable load on the duplicate. Then on the original, remove half the steps from the bottom and see if the issue persists. If so, remove another half of the steps. Otherwise, if the issue is gone, then add half of the steps you removed. In this manner, zero in on what is causing the problem.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Greg_Deckler
Super User IV
Super User IV

@akapoor03 Well, could be an issue with the custom connector and for that you would need to contact your data warehouse vendor. How complex are your Power BI queries in Power Query Editor? How big is your PBIX? How many rows of data? Are you doing DAX calculated columns? I would turn off Auto date/time definitely if your data model is large, this will potentially save a ton of space. 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




@Greg_Deckler 

How complex are your Power BI queries in Power Query Editor?

There are some transformation steps such as removing some coloumn, taking data starting from 1st July 2021, selecting only selcted peopl's name etc.

How big is your PBIX?

The one with this error s "memory is full" is 800 KB and another one is 4.7 MB.

 

How many rows of data?

One table has 40,000 rows, another one 1500 and the rest 3 have only 10 rows.

 

Are you doing DAX calculated columns?

Yes, I am using DAX calculated Column and they are criticIe for my measures that have created for visualisation.

 

Would turn off Auto date/time definitely if your data model is large, this will potentially save a ton of space.

 

Should I do that on Glabal level or current level and will doing so will affect my alrady created reports? 

@akapoor03 My personal opinion based upon what you have shared is that this is an issue with that custom connector because the stats you are citing are insanely small for Power BI. That's just my opinion with no proof. Might be helpful if you posted your code from Advanced Editor for your queries so that we could really get a sense of the complexity but it would have to be stupidly complex for 40K rows to cause a problem for Power BI.

 

One thing you could do is to enable tracing in your diagnostics. Run the refresh, get the error and save the logs. Open a support ticket with Microsoft and see what they have to say. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




@Greg_Deckler 

Below is the code from the table that took the most time, around 1 and half hours and gave the error-

let
Source = dotWMS_PowerBI_Connector.Contents(),
H2 = Source{[Key="H2"]}[Data],
F1 = H2{[Key="F1"]}[Data],
T18 = F1{[Key="T18"]}[Data],
#"Invoked FunctionT1" = T18(null, null, "1/07/2021", null, null, null, null, null),
#"Filtered Rows" = Table.SelectRows(#"Invoked FunctionT1", each [Timestamp] > #datetime(2021, 7, 1, 0, 0, 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Message"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each [TransactionType] = "Pick"),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"AdditionalInfo"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns1", each [TenantCode] = "SIMBA_RETAIL"),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows2",{"ReviewerEmail", "ReviewedTimestamp"})
in
#"Removed Columns2"

 

Also example of a measure is-

MTD =
VAR Transactions =
CALCULATE(
SUM('ULDs - History'[lines]),
DATESMTD('Calender'[Date])
)
VAR WorkingHours =
SUMX(
    SUMMARIZE(
        'ULDs - History',
        'ULDs - History'[Name],
        'ULDs - History'[Time Stamp(Date format)]
    ),
    CALCULATE(
        DATEDIFF(MIN('ULDs - History'[Timestamp]), MAX( 'ULDs - History'[Timestamp]), HOUR),
DATESMTD('Calender'[Date])
    )
)

RETURN
DIVIDE(
Transactions,
WorkingHours,
0
)

 

I will also do the Diagnostic thing as you said.

@akapoor03 Measures will not affect data load, only calculated columns. The code looks relatively innocent enough. You can use performance analyzer in Power Query Editor to perhaps see what row is taking the most time. Alternatively, duplicate your query and disable load on the duplicate. Then on the original, remove half the steps from the bottom and see if the issue persists. If so, remove another half of the steps. Otherwise, if the issue is gone, then add half of the steps you removed. In this manner, zero in on what is causing the problem.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors