Memory Issue when refreshing, change source or using template

[theo]

Hi Everyone,

I have data that already increases to 14million rows and 150 columns.

i was able to create the summary (cumulative sum and percentage) i need thru the following solution offered by helpful users

http://community.powerbi.com/t5/Desktop/measure/m-p/136791#U136791

the file then will have the

1. main table with 14mil rows and 150 columns

2. 150 calculated tables linked to the main table

3. created a relationship between teh main table and the 150 calculated table

what i then need to do is sometime to change the source of the main table but alway have the following error.

the solutions i tried are below:

1. refresh source (i rename the new source to the original source name)- FAILED (same error)

2. change source in the edit query - FAILED (same error)

3. create a template and load teh source - FAILED (same error)

4. divide the main table into 3 (50 columns each) and do item 1 to 3 -FAILED (same error)

the only thing that is working is to delete the old source in the query and change the table name to a different one at first and then change it again to the correct one once loaded.

all the mentioned 150 calculated tables were able to connect to the main table.

now the problem is everytime, i need to recreate the relationship since it became missing.

with this, could i get advise from anyone to solve the issue or on the following option that i could take:

1. increase memory of my laptop (currently 16gb, i7, 64bit)

2. how to make detection of relationship automatic? right now it cannot detect the relationship when in fact the columns in the calculated table were the same name as teh main table

3. any other solution

[Anonymous]

Hi @theo,

Since you have a huge data table, I'd like to suggest you use the 64 bit power bi desktop.(32 bit application has the memory limit)

Memory limits of 32-bit application running on 64-bit OS (Windows 2008 server 64-bit)

In addition, calculations(measure, calculated column, calculate table) which you create on power bi will also affect the performance and memory cost.

>>increase memory of my laptop (currently 16gb, i7, 64bit)

Current data level is less than your memory, if there are not any high memory cost applications running on your laptop, you not need to increase memory.

>> how to make detection of relationship automatic?

You can try to use below method to detect the relationships:

1. You can open the manage relationship and use the autodetect button to check the exists relationships.

2. Turn on the "Update relationship when refreshing queries" option and refresh.(Options and settings -> Options -> Data load -> "Update relationship when refreshing queries")

In my opinion, your report has too many tables and these relationship detect funcitons seems not very effective to detect the relationships between table and calculate table.  If they not work, you need to manual create relationships.

Regards,

Xiaoxin Sheng

[theo]

Hi @Anonymous,

thanks for taking the time to reply.

it cannot be detected automatically - any other dax expression that could do it perhaps.

i am thinking same as the format expression that could force it to be detected or make a connection to its source table.

Regards,

Theo