Create a New Repository
To create a new repository, perform the following steps:
1. Click Start > Programs > Administrative Tools > Services.
2. In the Services dialog box, stop the Oracle BI Java Host, Oracle BI Presentation Server and Oracle
BI Server service(s).
3. Click Start > Programs > Oracle Business Intelligence > Administration to open the Oracle BI
Administration Tool.
4. Click File > New to open the New Repository dialog box.
5. In the New Repository dialog box, the Repository folder is selected by default. Name the file
NCDEX_OBI.RPD
. 
6. Click Save. The new repository opens in the Administration Tool and displays three empty layers: Presentation, Business Model and Mapping, and Physical. If necessary, select Tools > Options >
Show toolbar to display the toolbar.
The Physical layer is where information on physical data sources is stored. The Business Model and Mapping layer is where measurements and terms used in business are mapped to the physical data sources. The Presentation layer is where the business model is customized for presentation to the user. You can work on each layer at any stage in creating a repository, but the typical order is to create the Physical layer first, then the Business Model and Mapping layer, and then the Presentation layer.
Create an ODBC Data Source
An ODBC data source is needed to import schema information about a data source into an Oracle BI Server repository. To create an ODBC data source for importing the SH schema information, perform the following steps:
1. Click Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC) to open the ODBC Data
2. Click the System DSN tab and click Add.
The Create New Data Source dialog box opens.
3. In the Create New Data Source dialog box, select the Oracle driver.
4. Click Finish to open the Oracle ODBC Driver Configuration dialog box.
5. In the Oracle ODBC Driver Configuration dialog box, enter a Data Source Name (this can be any name), select the appropriate TNS Service Name from the drop down list (OCOE in this example), and enter vus as the User ID for the OBI_PROD schema.
7. In the Oracle ODBC Driver Connect dialog box, enter the password <to be provided> for the
Vus user and click OK.
8. You should see a "Testing Connection" message indicating the connection is successful
9. Click OK to close the Testing Connection message.
10. Click OK to close the Oracle ODBC Driver Configuration dialog box.
11. Verify that the OBI_PROD system data source is added in the ODBC Data Source Administrator and click OK to close the ODBC Data Source Administrator.
To import the OBI_PROD schema information into the repository, perform the following steps:
1. In the Oracle BI Administration Tool, click File > Import > from Database . . . 
2. In the Select Data Source dialog box, select the OBI_PROD ODBC DSN you created in a previous step, enter VUS as the user name and password <to be provided>, and click OK to open the Import dialog box.
3. In the Import dialog box, locate the VUS schema folder.
4. Expand the VUS schema folder and use Ctrl + Click to select the following tables: REPORT_DATES,VW_CLIENT,VW_
5. Verify that only the Tables, Synonyms and Keys checkboxes are selected, which is the default and click the Import button. The Connection Pool dialog box opens.
It is best practice to only import objects needed to support your business model. However, don’t worry
If you import extra objects at this point. You can always delete objects later if they do not support your
Business model.
6. In the Connection Pool dialog box, on the General tab, verify that the call interface is set to Default
(OCI 10g) and change the data source name to the appropriate tnsnames.ora entry. Please note that this is the TNS service name, not the ODBC DSN.
7. Leave the rest of the settings as they are and click OK to close the Connection Pool dialog box. The
Import process starts.
8. When the Import process completes, click Close to close the Import dialog box.
9. In the Physical layer of the repository, expand the VUS schema folder and verify that the correct tables are imported.
10. To verify connectivity, click Tools > Update All Row Counts.
To create physical joins in the Physical layer of the repository, perform the following steps:
1. In the Physical layer, right-click the VUS schema folder and select Physical Diagram > Object (s) and All Joins. Alternatively, you can select the VUS database object and then click the Physical Diagram button on the toolbar.
2. In the Physical Diagram, if the tables are too big, right-click in the white space, select Zoom, and adjust the size.
3. . Rearrange the tables so they are all visible in the Physical Diagram.
4. Click the New Foreign Key button on the toolbar.
5. Click the Channels table and then the Sales table. The Physical Foreign Key dialog box opens.
It matters which table you click first. The join is creating a one-to-many (1:N) relationship that joins the key column in the first table to a foreign key column in the second table. The Administration Tool makes a best "guess" and automatically determines which columns should be included in the join.
Make sure the join expression is:
VW_MEMBER.MEMBER_SKEY = VW_FACT_TRADE_SMRY.MBR_KEY
6. Click OK to close the Physical Foreign Key dialog box. The join between VW_MEMBER and VW_FACT_TRADE_SMRY is displayed in the Physical Diagram.
7. Continue to create the following joins:
· VW_USER.USER_SKEY = VW_FACT_TRADE_SMRY.USR_KEY
· VW_MEMBER_BRANCH.BRANCH_SKEY = VW_FACT_TRADE_SMRY.BRNH_KEY
· VW_MEMBER.MEMBER_SKEY = VW_FACT_TRADE_SMRY.MBR_KEY
· VW_LOCATION.LOCATION_SKEY = VW_FACT_TRADE_SMRY.LCTN_KEY
· VW_INSTRUMENT.INSTRUMENT_SKEY = VW_FACT_TRADE_SMRY.INSTRMNT_
· VW_EXCH_CMDTY_CTGRY.CATEGORY_
· VW_EXCH_CMDTY.EXCH_CMDTY_SKEY = VW_FACT_TRADE_SMRY.CMDTY_KEY
· VW_EXCHANGE.EXCHANGE_SKEY = VW_FACT_TRADE_SMRY.EXCH_KEY
· VW_CLIENT.CLIENT_SKEY = VW_FACT_TRADE_SMRY.CLNT_KEY
· REPORT_DATES.DATE_SKEY = VW_FACT_TRADE_SMRY.DATE_KEY
· VW_INSTRUMENT.INSTRUMENT_SKEY = VW_EXCH_BHV_CPY.INSTRUMENT_
· VW_EXCH_CMDTY_CTGRY.CATEGORY_
· VW_EXCH_CMDTY.EXCH_CMDTY_SKEY = VW_EXCH_BHV_CPY.EXCH_CMDTY_
· VW_EXCHANGE.EXCHANGE_SKEY = VW_EXCH_BHV_CPY.EXCHANGE_SKEY
· REPORT_DATES.DATE_SKEY = VW_EXCH_BHV_CPY.DATE_SKEY
· VW_EXCH_SMBL_MAPNG.SMBL_MPNG_
When you are finished, click the X in the upper right corner to close the Physical Diagram.
To create a new business model, perform the following steps:
1. In the Physical Layer, right click on “VUS” and click copy.
2. In the Business Model and Mapping layer, right-click the white space and select Paste.
3. The business model layer will look as follows:
4. In the Business Model layer, right click on VUs and open the properties. Rename the business model as “Trade Data Summary” and leave the Available for queries box unchecked.
5. Click OK to close the Business Model dialog.
To create logical joins in the business model, perform the following steps:
1. Right-click the “Trade Data Summary” business model and select Business Model Diagram > Whole Diagram.
2. As the Business Model is Copied and pasted, Logical joins are automatically done.
3. When one clicks on any of the Complex Joins. The following window will appear.
.
4. Click the X in the upper right corner to close the Logical Table Diagram. Notice that the color of the table icons for the dimension tables has changed to white in the business model. In a business model, a yellow icon indicates a fact table and a white icon indicates a dimension table. Defining the join relationships determined which tables are the logical dimension tables and which is the logical fact table. A fact table is always on the many side of a logical join. You now have a logical star schema consisting of two logical fact table, “VW_FACT_TRADE_SMRY” and “VW_EXCH_BHV_CPY”,
and ten logical dimension tables REPORT_DATES,VW_CLIENT,VW_
To delete logical columns that are not needed in the business model, perform the following steps:
For the VW_CLIENT logical table in the Business Model and Mapping layer, use Ctrl + click to select the columns. Right-click either of the highlighted columns and select Delete to delete the columns. Alternatively, you can use the Delete key on your keyboard.
1. Click yes to confirm the delete.
2. Verify that the VW_CLIENT logical table now has only these logical columns.
3. Repeat the steps to delete the unnecessary columns in rest of the tables.
To use the Rename Wizard to rename the objects in the business model, perform the following steps:
1. Click Tools > Utilities.
2.
3. In the Utilities dialog box, click Rename Wizard and then Execute.
4. In the Rename Wizard, click the Business Model and Mapping tab and select the “Trade Data Summary” business model. Click on “Add” and “Add Hierarchy” and Click “Next”.
5. Check Logical Table and Logical Column. Click Next.
6. Add the desired changes to be made in sequence and then click on Next.
7. Kindly review changes.
8. Click on “Finish”.
Dimension hierarchies introduce formal hierarchies into a business model, allowing Oracle BI Server
to calculate useful measures and allowing users to drill down to more detail. In a business model, a
Dimension hierarchy represents a hierarchical organization of logical columns belonging to a single
Logical dimension table. Common dimension hierarchies used in a business model are time periods,
Products, customers, suppliers, and so forth.
Dimension hierarchies are created in the Business Model and Mapping layer and end users do not
See them in end user tools such as Oracle BI Answers or Interactive Dashboards. In each dimension
Hierarchy, you organize dimension attributes into hierarchical levels. These levels represent the
Organizational rules and reporting needs required by your business. They provide the structure that
Oracle BI Server uses to drill into and across dimensions to get more detailed views of the data.
Dimension hierarchy levels are used to perform aggregate navigation, configure level-based measure
Calculations, and determine what attributes appear when Oracle BI users drill down in their data
Requests.
To build the Channels dimension hierarchy in the “Trade Data Summary” business model, perform the following steps:
1. Right-click the Vw Location logical table and select Create Dimension.
2. Right-click the Location Detail level and select New Object > Parent Level.
3. In the Logical Level dialog box, name the logical level State and set the Number of elements at this level to 3.Click OK to close.
4. Drag the State column from the Location Detail level to the State level to associate the logical column with this level of the hierarchy.
5. Right-click State and select New Logical Level Key.
6. In the Logical Level Key dialog box, verify that State and Use for drilldown are selected.
The level key defines the unique elements in each logical level. Each level key can consist of one or
More columns at this level.
7. Click OK to close the Logical Level Key dialog box. The Channel Class column now displays with a key icon.
8. Right-click the State level and select New Object > Parent Level.
9. Add Region and Drag the Region from the Location Detail level to the Region level.
Make it a new logical key same as State.
10. Right-click the Location Name column and select New Logical Level Key.
11. In the Logical Level Key dialog box, check Use for drilldown.
12. The Location hierarchy would look as follows:
13. Repeat the same for other Dimensions as well.
14. The Report Dates hierarchy would look as follows:
15. Double click on Report Dates dim. Check Time Dimension.
16. The User hierarchy would look as follows
17. The Exch Cmdty hierarchy would look as follows
18. The Exch Smbl Mapng hierarchy would look as follows:
19. The Exchange hierarchy would look as follows:
20. The Member hierarchy would look as follows
To create a logical column, perform the following steps:
1. In the Business Model and Mapping layer, add new logical column in Report Dates
2. Add Name as ‘Max Month Traded days’, check on’ Use existing logical columns as the source’ and click on’…’
3. Click on Functions > Evaluate Functions > Evaluate
4. Write the following statement in Expression Builder, click on OK.
CAST (EVALUATE ('MM_TRADED_DAYS_MONTH_NAME(%
5. New logical columns as Max Month Traded Days are added.
6. Similarly, add the following logical columns with the corresponding Expressions:
Name: Max FY Traded Days
Expression Builder: CAST ( EVALUATE('FY_TRADED_DAYS_FY(%
Name: Max FQ Traded Days
Expression Builder: CAST ( EVALUATE('FQ_TRADED_DAYS_FQ(%
Name: Max Week Traded Days
Expression Builder: CAST ( EVALUATE('WK_TRADED_DAYS_WK(%
1. Double click on Total Value :
2. Select “SUM” as Aggregation Level.
3. Check the summation sign with “Total Value’.
4. Repeat the same for ‘Buy Value’ ,’Buy Volume’ ,’Sell value,’ Sell Volume’,’ Total Volume’.
5. Right click on ‘Total Value ‘ and click on duplicate.
- Double click on new logical column and name it as ‘Month Value’:
- In Level Tab, select Month in Logical level of report Dates dim.
- Drag the “Trade Data Summary” business model from the Business Model and Mapping layer to the Presentation layer to create the “Trade Data Summary” catalog in the Presentation layer.
- Expand the “Trade Data Summary” catalog in the Presentation layer. Notice that the tables and columns in the Presentation layer exactly match the tables and columns in the Business Model and Mapping layer.
Notice also that dimension hierarchies are not displayed.
Create an Initialization Block for Session Variables
Session variables are like dynamic repository variables in that they obtain their values from initialization blocks. Unlike dynamic repository variables, however, the initialization of session variables is not scheduled.
When a user begins a session, Oracle BI Server creates new instances of session variables and initializes them. Unlike a repository variable, there are as many instances of a session variable as there are active sessions on Oracle BI Server. Each instance of a session variable could be initialized to a different value.
A session is an instance of a user running the client application. The session starts when the application is started and ends when the application is exited.
To create an initialization block for session variables, perform the following steps:
In NCDEX_OBI repository,
Click Manage > Variables to open the Variable Manager.
Click Session > Initialization Blocks.
Right-click in the white space on the right and select New Initialization Block.
Implementing LDAP Authentication
In the Session Variable Initialization Block dialog box, type Auth in the Name field. Click on Edit Data Source.
To configure LDAP Server .select LDAP for dropdown.
Click on New to add LDAP Server
Fill in the details as shown:
Name: OBIEE_NCDEX
Host Name: 172.30.2.148
Port Number: 399
Base DN: DC=NCDEXLTD,DC=com
Bind DN: CN=Danish Shaikh,OU=TCS,OU=Consultants,
Bind password: <Windows Authentication Password for Danishs>
Confirm Password:
Click on Test Connection:
Click on the Advanced Tab and check ADSI, the screen would be as follows,
Click on Ok.
Click Edit Data Target to open the Session Variable Initialization Block Variable Target dialog box:
Click New to open the Session Variable dialog box.
In the Name field, type USER.
Click OK to close the Session Variable dialog box.
Click Yes when prompted about the USER session variable having a special purpose.
The USER variable is added to the Session Variable Initialization Block Variable Target dialog box. Type “sAMAccountName” In LDAP Variable: Click Ok.
To implement Other Session Variables:
In the Session Variable Initialization Block dialog box, type Group in the Name field. Click on Edit Data Source. Click on Edit Data Source.
Select “Database” as data Source Type. For Connection Pool, click on Browse
Select OBI_PROD > Connection Pool in the Select Connection Pool dialog box.
Double-click Connection Pool or click the Select button to add the connection pool to the Session Variable Initialization Block Data Source dialog box.
In the Default Initialization String field, type the following initialization string:
SELECT DISTINCT 'GROUP', DASHBOARD_GROUP
FROM OBI_PM_GROUP_MAPNG A, OBI_PM_USER_MAPNG B
WHERE A.GROUP_ID=B.GROUP_ID
AND B.BDM_ID = ':USER'
Click OK to close the Session Variable Initialization Block Data Source dialog box. The initialization string is visible in the Session Variable Initialization Block dialog box.
Click Edit Data Target to open the Session Variable Initialization Block Variable Target dialog box. Check Radio Button for “Row Wise initialization”. Click OK.
Repeat the above steps to Create “Commodity_State” with Default Initialization String as given below and check “Row Wise initialization” as well.
Select distinct 'obi_commodity_state', A.CMDTY|| ' ' || B.STATE
FROM OBI_PM_CMDTY_MAPNG A, OBI_PM_USER_MAPNG B
WHERE A.GROUP_ID=B.GROUP_ID
AND B.BDM_ID = ': USER’
The Session Variable Init Block is displayed in the Variable Manager.
To create a dynamic repository variable, perform the following steps:
Click Manage > Variables to open the Variable Manager.
Click Repository > Initialization Blocks.
Right-click the white space and select New Initialization Block to open the Repository Variable Init
Block dialog box.
Name the initialization block YestDate.
Click Edit Data Source to open the Repository Variable Init Block Data Source dialog box.
Click the Browse button to open the Select Connection Pool dialog box.
Double-click the OBI_PROD > Connection Pool object to add it to the Connection Pool field in the Repository Variable Init Block Data Source dialog box.
In the Default Initialization String field, type the following SQL:
Select date_skey from report_dates where date_skey
= (select to_char (get_working_day (trunc(sysdate), -1),'YYYYMMDD') from dual)
Click OK to close the Repository Variable Init Block Data Source dialog box. The connection pool
And initialization string are added to the Repository Variable Init Block dialog box.
Click Edit Data Target to open the Repository Variable Init Block Variable Target dialog box.
Use the New button to create a variable.
Click OK to close the Repository Variable Init Block Variable Target dialog box. The variables appear in the Variable Target field in the Repository Variable Init Block dialog box.
Click Test and verify you get the results in the picture.
Similarly Add the following variables with following details:
Name: YestMonth
Default Initialization String:
Select month_text from report_dates where date_skey = (select to_char(get_working_day(trunc(
Variable Name: YestMonth
Name: YestYear
Default Initialization String:
Select fyear_name from report_dates where date_skey = (select to_char(get_working_day(trunc(
Variable Name: YestYear
Name: PrevYear
Default Initialization String:
Select fyear_name from report_dates where date_skey = (SELECT TO_CHAR(add_months(get_
FROM dual)
Variable Name: PrevYear
Name: PrevFifthDay
Default Initialization String:
SELECT date_skey FROM report_dates WHERE date_skey =
(SELECT TO_CHAR(get_working_day(TRUNC(
)
Variable Name: PrevFifthDay
Name: Yesterday
Default Initialization String:
Select to_char(get_working_day(trunc(
Variable Name: Yesterday
Name: PrevFifthDay2
Default Initialization String:
SELECT TO_CHAR (get_working_day(TRUNC(
Variable Name: PrevFifthDay2
Name: Prev2Year
Default Initialization String:
Select fyear_name from report_dates where date_skey = (SELECT TO_CHAR(add_months(get_
FROM dual)
Variable Name: Prev2Year
Name: Prev3Year
Default Initialization String:
select fyear_name from report_dates where date_skey = (SELECT TO_CHAR(add_months(get_
FROM dual)
Variable Name: Prev3Year
Name: Prev4Year
Default Initialization String:
Select fyear_name from report_dates where date_skey = (SELECT TO_CHAR(add_months(get_
FROM dual)
Variable Name: Prev4Year
The Repository Variable Init Block are displayed in the Variable Manager.
Through this document, we can build an OBIEE RPD.
No comments:
Post a Comment