 |
 |
Archives of the TeradataForum
Message Posted: Tue, 12 Nov 2002 @ 15:56:39 GMT
| Subj: | | Re: Soft RI and LTST Optimisation |
| |
| From: | | Matthew Winter |
Hi,
| | I believe that Soft RI was incorporated in the Optimizer to eliminate joins (for instance, if your SQL generator always specified
joins to all 4 Dim tables and you only needed Dimensional detail from 2 of the Dim tables.) I'm pretty certain that it was not intended to
'add' joins by pulling non-selected objects into cross-product joins in hopes of accessing your fact table's PI. | |
I realise that at present V2R5 is using Soft RI to perform elimination of joins, but in theory it could also be used to add joins to
perform optimised join paths, such as the LTST method.
| | 1) What tool are you using to access this Star Schema? | |
In this particular example it is a 3NF design, with the Cognos toolset being used to access the tables.
| | 2) Which of the dimension tables are used most often? | |
This is difficult to answer, as we do not currently have a full set of users accessing this system. Based on the current usage, we have
two problem small tables, that when filtered cause high skewing on the join to the large table. Putting these two tables together as a
cross-join helps in eliminating the skew. On average these two tables are used in 1 in 4 queries. Basically origin and destination depot
tables. The other two small tables are calendar tables, providing our company week, month, quarter, year breakdown. On average one of these
tables are used in 1 in 2 queries, but mostly filtered to a week values, therefore bringing back 7 values.
| | 3) What percentage of the atomic table is being retrieved on average per incoming query? | |
Alot of the queries tend to be based on a day or weeks worth of data for a particular country. On average this is upto 1/200th of the
data, i.e. 1 million records.
| | 4) If using an OLAP Tool, when the user chooses information from the lowest level of a dimension, does the SQL generator pull
this data from the Atomic or Dimension table? | |
The main source of detail is from the large table, with the occasional description field being picked from the small tables.
Regards
Matthew Winter
Technical Architect
TPG IS Design Authority
Teradata Certified Professional
| |
