„Query too complex“?

Because instantOLAP is usable as „adhoc“ ROLAP tool, there is a maximum complexity for reports and a maximum number of loadable cube cells. When using adhoc-cubes, the system has to determine where to load which data from and which data is needed to populate the result or to calculate formulas.

This must be done before sending any SQL statement to the databases and in instantOLAP this is realized with the internal „ToDo List“, which lists all needed cells and tries to resolve the source for each cell (when running a reports, you can see the generation of the ToDo-List in the Log). There is a maximum size for ToDo-Lists, and if the lists exceed this limit, the system raises the „Query too complex“ error. The default maximum size for this ToDo-List is 250.000 cells, but it can be changed easily in the configuration. However, increasing the maximum will result in longer running and more memory-using reports.

There are several possible strategies to avoid large ToDo-List, and some of them are only available in our new version 2.5 or later:

1. Try to calculate as much as possible inside the database: E.g. if you only have a „Quantity“ and „Product price“ stored in the database, the „Turnaround“ (calculated by Quantity * Price) could be realized with a formula. But this formula would add 2 additional entries to the ToDo-List (for each „Turnaround“ cell, an additional „Quantity“ and „Price“ would be necessary) and the list would become three times bigger than needed. If you use the database to calculate this fact and use a SQL expression like „SUM( table1.quantity * table.price)“, the SQL cube could deliver the fact on itself and the ToDo-List would remain small.
2. Use LOOKUPs: When creating large and sparse reports, where only a small percentage of the cells will be filled with data, instantOLAP will also generate ToDo-Entries for this empty cells. If you use the LOOKUP function to eliminate empty rows or columns in ahead, you can reduce the size of the ToDo-List dramatically.
3. CLUSTER your selections: One of the main reasons for large ToDo-Lists are multiple- and range-selectors. E.g. if you have a small pivot table but a range-selector for months and a display aggregated fact (like SUM(Amount()) in your cells, the system will need to load the amount for each cell in combination with each selected month. So, the ToDo-list for 15 selected months would be 15 times larger than for a single month. But you can use the CLUSTER function in your query filter and reduce the number of selected keys for the time dimension. The CLUSTER function replaces selection by their parent, if all of its children are selected. E.g. if you select a whole year, the function will replace the months by their parent year key.
4. Use offline cubes: Since version 2.5, there is no need for any ToDo-List if every cube in the model is in the offline mode. Then, instantOLAP has to generate no SQL statements (and no ToDo-Lists) and every data will be loaded directly out of the offline stores.