I think a reducing of the number of tables must have a positive effect on the gui response-times. It looked that you could it reduce quite easily to 3 tables: Policy_Version + Policy_Wing + Material_FloorCoverings. To merge those 3 tables could be more complicated because their relationship might be not 1:1 which meant with simple joining would effect the number of records. In this case could be a mapping-approach useful or if the key is really only a key to put them into an autonumber-function.
Another important point is the look on the cardinality of the fields - this meant could fields be splitted, for example a timestamp into a date and in time, see: The Importance Of Being Distinct. Further I didn't see a calendar-table which let me assume that you used the inbuilt calendar-functionalities - I have not much experience with them and I don't know how this feature is implemented but by such a large application I would test this against a real calendar.
Further essential are the kind of dimensions and expressions which you used. You should avoid any complexity here, also no aggr-functions, no calculated dimensions, no dimension/expression groups, no nested if-loops and quite probably some further performance-killer.
- Marcus
