one of my apps is getting rather large now, and becoming rather slow. I started to think about optimisation, and looking at what i could do to improve the app.
The app uses a couple of link tables, where datasets need to be joined that are aggregated at different levels, and as this is the only solution that i know, i was wondering if there is a better, faster way?
As i say, as an example, dataset A is at its lowest level whereas dataset B is aggregated up - think of it in terms of sales and targets where targets aren't set at individual item (lowest level), but at product type level (aggregated up).
I tried concatenating the two tables, and although they share some similar fields, each dataset just ended up padded with nulls at the join.
Multiple linktables within the datamodel are the worst approach from a performance point of view - the more you are able to merge the tables the better will be the UI performance. To get NULL's within rows/columns by joining/mapping or concatenating is often no problem within the calculations or the performance. Also the match of mixed granularities is in general possible, for example: Fact Table with Mixed Granularity.
Multiple linktables within the datamodel are the worst approach from a performance point of view - the more you are able to merge the tables the better will be the UI performance. To get NULL's within rows/columns by joining/mapping or concatenating is often no problem within the calculations or the performance. Also the match of mixed granularities is in general possible, for example: Fact Table with Mixed Granularity.
