How to establish an evaluation order when applying query changes (out of memory error)?

[Joerobert]

BI community,

I have created a new data model and have attempted to replicate a star scheme with my data tables. The center "star" table consist of two tables that have been merged together and I am using two differnet parameters to filter the data down. Please reference below screenshot #1 as an example:

1. Group #1 is a query that only has one applied step and is only identifying the source of my data. All of the other queries listed below this group are referenced to the "DBconnection" query to help speed up the evaluation process.

2. Group #2 contain a series of queries and two parameters that I am using to filter the data down. Parameter #1 is using the query list "Division" which is being applied to query "wvWELLHEADER" and parameter #2 is using the query list "DTTMSTART" which is being applied to query "wvJOB".

3. Group #3 contains all of the queries that I am using in my reports. The first query in this group is "wvWELLHEADER_wvJOB" and this is a merged table from the two queries listed up in group #2. The idea here is that I combine both filtered tabled within query editor, merge the tables together and then use this table as the fact or "star" table in my data model. I am working with a massive amount of data and I feel like this method should be able to reduce the file size of the .pbix file. All of the remaining queries have then been individually merged (inner joined) with "wvWELLHEADER_wvJOB" using a unique identifier so that I can utilize the filtered fact table when fetching the data. After constructing this data modeling, i was able to get the file size down from 400-500mb down to 120mb and my computer never crashed when applying any of the query changes. Now that it is completed and able to create my report, i have exported the template and I am now trying to utilize the parameters for different DIVISIONs and DTTMSTART time periods.

Problem: When I have loaded the queryes with the new parameters, I keep recieveing the error message that I have run out of memory (I am running 64bit OS, i5 3320M cpu, 8GB RAM). I have restarted my computer and have deleted multiple columns out of my queries in attempt, but keep recieving this error message. When looking at the evaluation prompt after applying the changes (see screenshot #2), i'm noticing that the query tables are not in the same order as my editor and I am suspecting that the data is not fetching in the proper order and therefore I am pulling in more data that I need. Or maybe all of the queries are fetching data at the same time and this is filling up my memory cache. This might explain why this was not a problem when i constructed my data model & report because I was applying the chagnes to each individual query instead as a whole model. Does anybody know of a solution to this or maybe konw how I can apply an evaluation order to my queries when applying changes. I am thinking of a way to a)apply query chagnes to group #1 b) then apply changs to group #2, c) then group #3.

Thanks in advance!

Screenshot #1Screenshot #2

[ImkeF]

I've collected some possible performance killers here: http://www.thebiccountant.com/speedperformance-aspects/

Are you querying SQL-data? Watch out for the folding-bug then!

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

[Joerobert]

Thank you for the support, I will check out those links to see if I can utilize those steps. Also I am importing from an oracle database.