Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
This is the first time I've come across this issue. However I am using some 'bridge' tables for the first time, which could be why this is the first time I am seeing it.
I have a 1gb document which works as quick as I would expect it to work.
It has one fact table, a couple of dimensions hanging off it and then a 'date bridge' table (using a numeric key field) which links to a calendar.
The fact is circa 9m rows, 5 dates so the bridge is 45m rows.
What I have noticed is that as I filter down to lower levels, the document really slows up... One simple chart goes from a 0 calc time and a 16kb mem usage to 5000ms and 30mb
This is when I've drilled from say 9m rows down to 50 rows
formula is stored in a variable and is essentially sum({<datebridge={'datetype'}>}somevalue) so not exactly intensive.
I can't spot / understand what is causing such a decrease in performance
I managed to trace this to a calculated dimension in the end.
I did mess around with the bridge table and obviously reducing the rows helped, but removing the calculated dimension resolved the issue instantly
The reason is really your bridge-table which led to huge virtual tables on which your expressions calculate their results. The best explanations will you find here:
Logical Inference and Aggregations
- Marcus
Excellent I'll have a read thank you
Yep makes sense, I had read the post on the Canonical date and assumed it was a new accepted 'standard' but in this case multiple cals are the way to go, a little more faf in the formula but at least it will run properly.
I'm not totally clear on why the run time increases as the data is reduced, I assume as its because reducing the data is increasing the selected items and therefore the calculation complexity
I managed to trace this to a calculated dimension in the end.
I did mess around with the bridge table and obviously reducing the rows helped, but removing the calculated dimension resolved the issue instantly