OBIEE 11G Using Initialization Blocks and Variables

You can use variables in a repository to streamline administrative tasks and modify metadata content dynamically to adjust to a changing data environment. A variable has a single value at any point in time. Variables can be used instead of literals or constants in the Expression Builder in the Administration Tool or in end-user analyses. At run time, Oracle BI Server substitutes the value of the variable.
In this set of steps you create a new initialization block, Current Periods, and three new dynamic repository variables—CurrentYear, CurrentMonth, and CurrentDay. You then use the variables as column filters in an Oracle BI analysis. You use the Variable Manager in the Administration Tool to define variables and initialization blocks.
To set up and use initialization blocks and variables, perform the following steps:
  • Create an Initialization Block
  • Create Variables
  • Test Your Work

Create an Initialization Block

1 . Open the BISAMPLE repository in offline mode.
2 . Select Manage > Variables to open the Variable Manager.
Screenshot for Step
3 . Select Action > New > Repository > Initialization Block.
Screenshot for Step
4 . Name the initialization block Current Periods.
Screenshot for Step
5 . Click the Edit Data Source button to open the Repository Variable Initialization Block Data Source dialog box.
Screenshot for Step
6 . Click the Browse button to open the Select Connection Pool dialog box.
Screenshot for Step
7 . Double-click the Connection Pool object to select it.
Screenshot for Step
The connection pool is added.
Screenshot for Step
8 . Enter the following SQL to determine the value of the current day, month, and year by finding the maximum value of the period key (BILL_DAY_DT) in the fact table:

SELECT CALENDAR_DATE, PER_NAME_MONTH, PER_NAME_YEAR FROM BISAMPLE.SAMP_TIME_DAY_D WHERE CALENDAR_DATE = (SELECT MAX(BILL_DAY_DT) FROM BISAMPLE.SAMP_REVENUE_F)
Screenshot for Step
9 . Click Test and confirm the expected results are returned. In this example, the results are determined by the data in the sample database used for this tutorial, which holds data through December 2010.
Screenshot for Step
10 . Close the Results window.
11 . Click OK to close the Repository Variable Initialization Block Data Source dialog box. Check your work:
Screenshot for Step

Create Variables

1 . Click Edit Data Target to open the Repository Variable Initialization Block Variable Target dialog box.
Screenshot for Step
2 . Use the New button to create three new variables: CurrentDay, CurrentMonth, CurrentYear. The order is important. The value returned from the first column in the initialization block SQL, CALENDAR_DATE, is assigned to the CurrentDay variable. The value of the second column, PER_NAME_MONTH, is assigned to CurrentMonth (the second variable), and the value of the third column, PER_NAME_YEAR, is assigned to CurrentYear (the third variable). If necessary, use the Up and Down buttons to arrange the variables.
Screenshot for Step
3 . Click OK to close the Repository Variable Initialization Block Variable Target dialog box.
4 . Leave the default refresh interval set to every hour. This means that the variables will be reinitialized every hour.
Screenshot for Step
5 . Click the Test button and check the results:
Screenshot for Step
In this example, the results are determined by the data in the sample database used for this tutorial, which holds data through December 2010.
6 . Close the Results window.
7 . Click OK to close the Repository Variable Initialization Block dialog box.
8 . Check your work in the Variable Manager:
Screenshot for Step
9 . Close the Variable Manager.
10 . Save the repository and check consistency. Fix any errors or warnings before proceeding.
11 . Close the repository. Leave the Administration Tool open.

Test Your Work

1 . Return to Fusion Middleware Control and load the BISAMPLE repository. If you need help, click here to review steps from earlier in this tutorial.
2 . Return to Oracle BI and sign in.
3 . Create the following analysis to test the variables.
Time.Per Name Year
Time.Per Name Month
Time.Calendar Date

Base Facts.Revenue
Screenshot for Step
4 . Click Filter for the Per Name Year column. The New Filter dialog box opens.
Screenshot for Step
5 . Select Add More Options > Repository Variable.
Screenshot for Step
6 . In the Repository Variable field, enter CurrentYear to create a filter for the Per Name Year column using the CurrentYear repository variable.
Screenshot for Step
7 . Click OK to close the New Filter dialog box. The filter is added to the Filters pane.
Screenshot for Step
8 . Repeat the steps to add the CurrentMonth and CurrentDay repository variables as filters for Per Name Month and Calendar Date columns, respectively.
Screenshot for Step
Screenshot for Step
Screenshot for Step
9 . Click Results and confirm that data only for the current year, month, and day is returned (based on the sample data set).
Screenshot for Step
10 . Sign out of Oracle BI.

No comments:

Post a Comment

Popular Posts