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