For a typical learner, it will approximately take half
an hour (30 minutes) to successfully complete the tasks listed in this
lesson.
Viewing Screenshots
Move your mouse over this icon to show
all screenshots. You can also move your mouse over each individual icon to see
only the screenshot associated with it.
Prerequisites
In order to complete this module successfully, you should
have:
1.
Worked with Discoverer Plus and Discoverer
Viewer to create and view reports
OracleBI Discoverer Administrator is a component used
by Discoverer managers to create, maintain, and administer data in the End User
Layer, and to define how users interact with that data. All the administrative
tasks for the EUL objects (such as folders, business areas, summaries, and so on)
can be performed using Discoverer Administrator.
The current release of OracleBI Discoverer Administrator has new features
for better scalability and performance, apart from the UI enhancements. Exciting
(and most wanted) features such as complex folder reach through and indexed items
(for better query performance) have been added in the current version of Discoverer
Administrator. You can also see UI enhancements to handle large number of users
(better scalability). You will explore these new or enhanced features in this
lesson.
There are several instances where an
end user, while building queries, would like to select an item (which contains
text description, such as country name rather than country ID, product description
instead of product ID, and so on) for better readability of the data in the
workbooks. The user may not be able to do so, because such queries fetching
text descriptions take more time to run, thereby affecting the query performance.
As a Discoverer manager you can now improve the query performance, by associating
an indexed item with a descriptive item, on which the parameter is based. As
a result, the SQL that gets generated behind the scenes actually uses an associated
ID column in the database (such as country ID or product ID, which is more likely
to be indexed), though the end users may have selected a descriptive item (such
as country name or product description) in their queries. Associating
an indexed item to a descriptive item can be done from the Item
Properties dialog box, in Discoverer Administrator.
For example, for the Country Name item, you
can assign Country Id as the indexed item.
Therefore, whenever the Country Name item is used as a parameter, the query
uses the indexed Country Id item instead of Country Name to fetch the data,
thereby improving query performance.
Defining indexed items is also very useful when you have a
large facts table (such as Sales with millions
of rows) and a small dimension table (such as Products
containing a few rows). For example, end users may define a query to see the sales
revenues for a product, where a parameter is defined on product name. Note that
Sales table contains only Product_Id
column, but not Product_Name column. Therefore,
to find rows for a specific product, Discoverer must first look in the
Products table to match the product name with its product ID, and then
use the product ID to return matching rows from the Sales
table. This operation might be slow when the fact table that you are querying
contains hundreds of thousands, or millions of rows. So defining Product
Id as the indexed item for Product Name,
ensures that Discoverer now uses Product Id for queries based on the parameter
for Product Name.
You will start this exercise by opening a worksheet which
already has a parameter based on Country Name and see the SQL execution plan
for this query. Then you will assign Country Id as the indexed item for Country
Name, and you will open the same worksheet again to see if there is any difference
in the SQL execution plan for this query after assigning an indexed item.
1.
Connect to Discoverer
Plus as bi_user and open the
worksheet Profit Margins by Country
from the workbook Sales&Profits By Time
&Geography -XX. Select Americas
for the Region parameter,
and Canada, United States of America
for the Country parameter. Run
the query.
Note:This
is the same workbook that you saved with your initials (XX), while working
with the lesson on Discoverer Plus. Also, note that the worksheet Profit
Margins By Country has a parameter based on Country Name.
2.
Select the Tools>
Show SQL menu option, and open the Plan
tabbed page. Observe the execution plan for this query. Note that when
the query is being run, a full table scan is performed on the Countries
table, and the query is not making use of any index.
3.
Connect to Discoverer
Administrator as bi_user, and
open Sales History business area.
4.
It is necessary that the descriptive item
and the indexed item be in the same folder to assign one as the indexed
item to the other. Therefore, you will add Country
Id item to the Profits Analysis
folder as the next step. Select Window>New
Window to display the business area in two windows as shown in
the image below, so that it is easy to drag the item from one
folder to the other. Expand the Countries folder in one window and Profits Analysis
in the other.
5.
Drag the item Id
from the Countries folder to Profits
Analysis folder. Rename the item Id
in ProfitsAnalysis
folder as Country Id.
Note: The item Id
is actually based on the Country_Id
column of the Countries
database table.
6 .
Now open the Item
Properties dialog box for Country Name
item in the Profits Analysis folder.
Scroll down to see the Indexed Item
property in the General tabbed page.
Select Country Id from the drop-down
list of items as the indexed item, and click OK.
Note: Observe the index icon
appearing beside the item name (Country Id)
in the drop-down list, which indicates that an index is defined on the respective
column in the database.
This will display a message as shown below, click yes to create this
new List of Values for the item, for which you have defined an indexed
item
Note: You can invoke the
Item Properties dialog box from the short-cut menu of the item.
Alternatively, you can double-click the icon for the item.
7 .
Connect to Discoverer
Plus, and open the same worksheet Profit
Margins by Country from the same workbook. Also, select the same
parameter values (Americas for Region
parameter, Canada, United States of America
for Country parameter) as you have
selected in step 1 of the exercise, and run the query.
8.
Select the Tools> Show SQL
menu option and open the Plan
tabbed page. Note the difference in the execution plan for the
query now. You can see that after defining the indexed item for the country
name, the same query has caused an indexed search on the Countries
table, instead of performing a full table scan on the Countries
table.
This is the execution plan after defining the indexed item:
Note: The descriptive item and the
indexed item should have a one-to-one relationship. That is, a country
ID should have one and only one country name mapping to it. Similarly,
a product ID should have one and only one product name mapping to it.
Enabling Reach Through
for the Base Folders of a Complex Folder
Complex Folder Reach Through
is a new feature, that enables Discoverer Plus (and Discoverer Desktop) users
to include items from the base folders of a complex folder, in addition to the
items provided in a complex folder. ( In other words, this feature helps end
users extend their analysis by including additional items in the worksheets
from the base folders.) Using Discoverer Administrator, you can enable the reach
through for base folders from the Item Properties dialog box of a complex folder.
When an end user selects any item from a complex folder, other items in the
base folders for which reach through is enabled become available for selection.
Note that you can achieve the same result by creating
joins between the complex folder and its base folders. However, this approach
is undesirable because it may result in poor query performance, and you will
have additional joins to maintain as a Discoverer manager.
Complex folder reach through is particularly useful when using Discoverer with
Oracle Applications flexfields (that is, user defined items) to query a base
folder after a complex folder has been created. If the base folder is defined
as reach through enabled, then new flexfield items can be added to the base
folder without having to modify the complex folder definition.
In this exercise, you will enable reach through for some base
folders of the complex folder Profits Analysis,
and will add an item from one of these reach through folders to a worksheet
in Discoverer Plus.
1.
Connect to Discoverer Administrator as bi_user,
and open Sales History business area.
Note: Before enabling reach through,
you can connect to Discoverer Plus, open the same
workbook, and see that none of the base folders are enabled for selection
in the Available Items pane, as reach through is not enabled for these
folders. Observe the disabled folders in the following image:
2.
Open the Item Properties
dialog box for Profits Analysis complex
folder from the short-cut menu. Observe the tabbed pages.
3.
Open the Reach
Through tabbed page and select Customers,
Products, and Profits
folders to enable reach through for these folders.
4.
Connect to Discoverer Plus and open the
worksheet Channel Profitability by Product
Categories from the workbook Sales &
Profits by Time & Geography. Select Photo
as the parameter value for Product Category
and Camcorders, Cameras as values
for the Product Subcategory parameter.
Click OK to run the query.
5.
You can see that now the folders for which
you have enabled reach through (that is, Customers,Products, and Profits)
are enabled under the Available Items
pane. Expand the node for Products folder,
and drag Product Name item from the
Products folder in the Available
Items pane to the worksheet, and place it beside the other page
items (Product Category and Product Subcategory) in the worksheet.
The worksheet will now look like this: ( Different heading colors and
fonts are used for the Product Name item to distinguish it from other
page items.)
Note: The items
from the reach through folders will be available not only for the existing
worksheets, but also for the new workbooks, when the end user selects
any item from the corresponding complex folder. This way by enabling reach
through, you can give the end users and BI analysts the flexibility of
adding the items from the base folders for further analysis.
Discoverer Administrator has UI enhancements, and improved
ability to search, select, and display selected users without having to browse
through the entire list of database users. You can mention a search criterion
for the list of users that you want to see using the improved Select
User/Role dialog box. This new dialog box will help
you in selecting the required users, and is available when you access/invoke
any of the following options/wizards:
When you access
Users->Business Area tabbed page from the Tools>Security
option.
When you access
Privileges, Query Governor, or
Scheduled Workbooks tabbed pages from the
Tools>Privileges option.
When you access the Tools>Manage
Scheduled Workbooks option.
In this exercise, you will assign privileges to OBE4BI user.
Note: The OBE4BI user may not exist in the
database that you are using. Select any of the existing users in your database
and continue with the steps listed in this exercise.
1.
Connect to Discoverer Administrator as bi_user,
and open Sales History business
area.
Note that thisSales History business area and the
EUL for bi_user will be available after you have installed the sample
workbooks, and you need not have to perform any additional setup by using Discoverer
Administrator.
2.
Select the Tools>
Privileges menu option to assign privileges to other users. Click
Select. (Highlighted
in the image)
3.
This displays the Select
User/Role dialog box. Enter BI
as the search string to search for usernames or roles containing the string
BI. Click Go.
Note: You can also make the search
case sensitive by selecting the Case-sensitive
check box.
4.
This displays the list of all usernames containing
BI. Select OBE4BI, and click OK.
5.
This will return you to the Privileges
tabbed page. Observe that now the OBE4BI user is selected. Assign the
required Desktop/Plus or Administration
privilege for this user, by selecting the appropriate options. Click
Apply.
The image shows only end user level (no administration) privileges assigned
to this user.
6.
You can open the Query
Governor and Scheduled Workbooks
tabbed pages to see that in a similar way the users can be selected based
on a search criterion.
Similarly, when you want to provide access to the business areas to other
users, you can access the Tools> Security
option. You can invoke the Select User/Role
dialog box from the Users->Business Areas
tabbed page.
Summary
In this lesson, you have explored the new features of OracleBI
Discoverer Administrator, and also you have tested how these features are helpful
for the BI analysts and end users (users of Discoverer Plus) for building queries.
Move
your mouse over this icon to hide all screenshots.
appearing beside the item name (
