Databases Reference
In-Depth Information
An alternative approach
The previous example depends on the fact table being loaded before the dimension
tables. It often makes more sense to load dimension tables first and fact tables later.
In that scenario, the solution shown before will not work because the actual fact table
has not yet been loaded at the time we load the dimension table. There is no way for
us to "load only dimension values for which facts have been loaded." Therefore, the
Exists()
function cannot be used.
The alternative approach consists of first loading the entire dimension table and then
reducing the record set based on the corresponding values in the fact table after the
facts have been loaded. Let's see how this works by following these steps:
1.
Open the
Script Editor
window again and go to the
Aircraft
tab.
2.
Comment out the lines we added previously by selecting the code
WHERE Exists([%Aircraft Type ID], AC_TYPEID);
right-clicking
on it, and selecting
Comment
.
3.
Then, add a semicolon on the next line to ensure that the
LOAD
statement is
properly ended.
4.
Next, we need to make sure that the
Aircrafts
tab is run
before
the
Main Data
tab. With the
Aircrafts
tab still active, press
Ctrl
+
Q,T,P
simultaneously twice
to promote it, or select
Tab | Promote
from the menu bar until the
Aircrafts
tab is placed to the left of the
Main Data
tab.
5.
Next, activate the
Main Data
tab and, after the end of the corresponding
LOAD
statement, enter the following code:
Temp_Aircraft_Type_Dim:
RIGHT KEEP ([Aircraft Types])
LOAD DISTINCT
[%Aircraft Type ID]
RESIDENT [Main Data];
DROP TABLE Temp_Aircraft_Type_Dim;
6.
Save
and
reload
the script, and use the
Table Viewer
window to check
the result.
The code we inserted creates a temporary table,
Temp_Aircraft_Type_Dim
, which
contains all of the distinct
%Aircraft Type ID
values from the
Main Data
fact table.
By using a
RIGHT KEEP
statement, the data in the original
Aircraft Types
table is
reduced to only those rows that are associated with the
Main Data
table. After the
Aircraft Types
table has been truncated, we remove the temporary table.
