Happy New Year.
I have a report which had run well until I attempted to add a new data source in Power BI Desktop.
I'm using the latest version of Power BI Desktop [Version: 2.88.802.0 64-bit (December 2020)]. And my laptop has 8G ram.
It went well when I added this new data source in Power Query. After the "Apply & Close" button was clicked, the "Load" pop-up window occurred while the memory usage was increasing very quickly. A mass of memory was taken over by the "Power BI Desktop" process and its sub-process "SQL Server Analysis Services". After a couple of minutes, the error "Failed to save modifications to the server … not enough memory to complete this operation" occurred. I've unticked the "Auto Date/Time" option in the current file. But it doesn't work.
The size of the new data source is very small, with only 400 rows and 10 columns. There is a column of Date type. After an investigation, it was the Date type column that caused this error! If I removed the Date column from the data source, it can be added to the data model successfully.
I guess that when "Apply & Close", Power BI automatically (and stupidly) associated this new data source to my Date Table (created by DAX). In the Date column of the new data source, there's few "very old" date, like "15/5/2001" (the date columns in my fact table start from 2014). I am not sure if it caused my Date Table (created by DAX) was expanded to 2001. Probably, the fact table (or the visual cube?) expanded as well?
So is there any method to turn off Power BI automatically (and stupidly) associating the new data source to my Date Table? I don't need this function at all.
Thank you very much.
I think the date table should not the root casing of your scenario. Have you turned off the 'relationship detect' options to disable auto detect the table relationships?
How many records are your data table hosted? Any complex expressions calculation(multiple iterators, nested cross table looping calculation ....) and advanced operation(merge, combine, reference, invoke custom functions, recursion...) existed in Dax or query editor side?
AFAIK, they may affect the performance and spend a huge amount of system resources.You can also take a look at the following document about performance tips:
Optimization guide for Power BI
The 'auto detect relationship' has been disabled since I created the report. There're over 500k rows in my fact table and I do have a few complex DAX. But there's only 15 rows and 4 columns (2 TEXT columns and 2 DATE columns) in the new data source which caused this problem.
I did further troubleshooting. In Power Query, I changed the type of the 2 DATE columns to TEXT (for instance, it's still '31/12/2099', but it's a string, not date), the problem would not happen after I clicked the "Apply & Close" button.
I guess Power BI automatically does some extra pre-calculation to optimise DATE type. But this might bring side-effect in some situation. For examples, in my case, I will not want the DATE columns in the small table to join my date dimension table.
Yes, power bi will do some processing with date fields. (it will generate the hidden calendar to mapping date fields for further chart record summarize and time intelligence functions usage)
Currently, you can't do customized with this hidden calendar.
@Bestball , In the Options and setting disable Auto Date time option , Increase the Cache size to max .
Refer to these setting: https://www.thebiccountant.com/speedperformance-aspects/
Thanks for your advice.
The "Auto Date/Time" option has been off.
I increased the Cache size from 4G to 8G, but the error still happened.
Check out the News & Announcements to learn more.
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.