Forum Discussion
Space consumption issue and error-Urgent
learner03 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.
learner03 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.
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?
learner03 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".
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])))RETURNDIVIDE(Transactions,WorkingHours,0)I will also do the Diagnostic thing as you said.
learner03 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.