The following example will contain a Variable Creation analysis, which will then be input into the Refresh analysis.
Parameterize a Variable Creation analysis as follows.
- Select TWM_CUSTOMER as the Available Table.
Create one variable by dragging and dropping the following column. Note the variable name will default to the column name.
- Select TWM_CREDIT_TRAN as the Available Table.
Create a variable by clicking on New and build up an expression as follows:
- Rename the variable to avg_tran_amt.
Drag an AVERAGE (Arithmetic) SQL Element over the Variable, and then drag the following column over the empty arguments:
- Go to the INPUT- variables-Dimensions tab.
Do the following to create a dimension:
- Click on New.
Drag an AND (Logical) on the Dimension Value. Rename the Dimension to LastMonth.
Refresh Dimension (Step 1)
Drag a LESS THAN OR EQUALS (Comparison) onto the first empty argument, and a GREATER THAN (Comparison) onto the second empty argument.
Refresh Dimension (Step 2)
Drag a DATE DIFFERENCE (Date and Time) onto the first empty argument of each comparison operator.
Refresh Dimension (Step 3)
- Drag a TARGET DATE (Literal Parameters) onto the first empty argument of each Date Difference.
Drag TWM_CREDIT_TRAN.tran_date onto the second empty argument of each Date Difference.
Refresh Dimension (Step 4)
- Drag a NUMBER (30) (Literal) onto the empty argument of the LESS THAN OR EQUALS.
Drag a NUMBER (0) (Literal) onto the empty argument of the GREATER THAN.
Refresh Dimension (Step 5)
- Go to the INPUT-dimensions tab.
Apply the dimension to the variable by dragging the dimension and dropping it on the variable to dimension, or by clicking the the over button after highlighting the desired dimension and variable, as shown below.
Refresh > Input > Expert: Apply The Dimension
- Go to the INPUT-expert tab.
Enter the following Where Clause.
Refresh > Input > Expert: Enter Where Clause
- Specify the Join Paths from TWM_CUSTOMER to each of the following by clicking on the Wizard button. The Wizard will initially display the join path selected in the Join Paths window.
Specify the following Join Paths:
TWM_CUSTOMER.cust_id - TWM_CREDIT_TRAN.cust_id
TWM_CUSTOMER.cust_id – TWM_CREDIT_ACCT.cust_id
- Go to the INPUT-target date tab.
- Change the Target Date to 7/31/1995.
- Go to the OUTPUT-storage tab.
- Select Store the tabular output of this analysis in the database.
- Specify that a Table should be created named twm_tutorials_refresh.
- Run the analysis.
Parameterize a Refresh Analysis as follows:
- Available Analyses — Variable Creation to Refresh
- Modify Anchor Table — checked - twm_source.twm_customer_analysis
- Modify Target Date — checked - 6/30/1995
- Modify Output — checked
- Database Name — twm_results
- Table/View Name — twm_tutorials_refresh2
- Modify Literal Parameters — checked - p1 (4), p2 (6/1/1995), p3 (‘A’, ‘B’)
- Run the analysis.
- View the generated SQL (which has been generated by the Variable Creation analysis, but modified by the Refresh analysis) to see how the anchor table, target date, output table and literal parameters have been changed.