Skip Headers
Oracle® Business Intelligence Discoverer Administration Guide
10g Release 2 (10.1.2.1)
B13916-04
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

6 Creating and maintaining folders

This chapter explains how you create and maintain folders using Discoverer Administrator, and contains the following topics:

What are folders?

A Discoverer folder is a representation of result set data. The visual concept of a folder in Discoverer is analogous to a directory in Windows where folders are the containers and items are the files held in the folders. A Discoverer folder represents a group of related items. Discoverer end users select items from one or more folders to get information from the database. As the Discoverer manager, it is your responsibility to create suitable folders to enable Discoverer end users to access the information they need.

There are three types of folder:

To the Discoverer end user, the type of a particular folder is immaterial. Whether a folder is simple, custom, or complex is only important to the Discoverer manager. Even in Discoverer Administrator, there is very little difference in the behavior of these different types of folders. Folders can include items, calculated items, joins, conditions, item classes, and hierarchies. Items in a folder can be used in summary folders and to define hierarchies.

Discoverer end users work with folders within the context of business areas.

As the Discoverer Manager, you can assign a folder to one or more business areas. Note that a folder has a single definition, regardless of the number of business areas to which you assign it.

You can remove a folder from all business areas without deleting it from the EUL. Folders that exist in the EUL but which are not currently assigned to a business area are referred to as orphan folders.

What are simple folders?

Simple folders contain items based on columns in a single database table or view. Items in a simple folder can also represent calculations on other items in the folder.

You create a simple folder by loading a table definition or table metadata from the database or a gateway.

Figure 6-1 How Discoverer Administrator represents a table/view

Description of Figure 6-1  follows
Description of "Figure 6-1 How Discoverer Administrator represents a table/view"

What are complex folders?

Complex folders contain items from one or more folders. Complex folders enable you to create a combined view of data from multiple folders. This is analogous to a view in the database.

Using a complex folder enables you to simplify the business area without creating a new database view. For example, you can create a complex folder called Dept-Emp which has columns from both the DEPT and EMP tables. The user can select from one folder instead of two.

For two items from different folders to belong to the same complex folder, a join condition must exist between the two folders. For more information about joins, see Chapter 10, "Creating and maintaining joins".

Figure 6-2 How a complex folder groups items from multiple source tables

Description of Figure 6-2  follows
Description of "Figure 6-2 How a complex folder groups items from multiple source tables"

What are the benefits of using complex folders instead of database views?

You could produce the same result set using a database view instead of a complex folder. However, using a complex folder instead of a database view offers several advantages. You can:

  • create a complex folder without the database privileges required to create a database view

  • control access to a complex folder using the folder's business area

  • manage complex folders entirely within Discoverer Administrator, whereas database views can be complicated to maintain

  • improve query performance by taking advantage of Discoverer's ability to optimize the SQL it generates for items in complex folders

What are custom folders?

Custom folders are folders based on a SQL statement which could include SET operators (e.g. UNION, CONNECT BY, MINUS, INTERSECT) or a synonym that you type directly into a dialog.

By defining a custom folder, you can quickly create a folder that represents a complicated result set. When you save the custom folder, Discoverer Administrator creates items using the 'Select' part of the SQL statement you have entered.

In Discoverer Plus, there is no distinction between a custom folder and a simple folder. A Discoverer end user can use a custom folder to build queries in the same way as any other type of folder.

What are the differences between custom folders and simple folders?

Custom folders are very similar to simple folders, with the following exceptions:

Area Simple Folder Custom folder
Refresh Simple folders are refreshed when the business area is refreshed Custom folders are refreshed by editing and validating the existing SQL.

Note: When a custom folder is based on a view you must refresh the business area.

Item properties Items in simple folders have an Item Formula property. Items generated in a custom folder do not have an Item Formula property. In other words, the only way to edit the formula of an item in a custom folder is to edit the SQL for the whole folder.
Folder properties Simple folders have Database, Owner, and Object properties. Simple folders do not have a Custom SQL property. Custom folders do not have Database, Owner, and Object properties. Custom folders have a Custom SQL property that contains the SQL statement used to generate the custom folder.

Examples of custom folders

This section consists of the following examples:

Example 1: Synonyms in a custom folder definition

SELECT BRAND FROM PROD

In this example, PROD is a synonym that points to the product table.

The underlying table or view that the synonym points to, can be switched without the need to change the custom folder definition in Discoverer (i.e. providing that the column names of the table/view that the synonym refers to, do not change).

Example 2: Database link in a custom folder definition

SELECT BRAND FROM PRODUCT@DATABASELINK

In this example, PRODUCT is a table in another database, and DATABASELINK is the database link to the other database.

Example 3: Set Operator in a custom folder definition

SELECT 'COMPANY1' COMPANY, ENAME, SAL FROM EMP@HQ UNION SELECT 'COMPANY2' COMPANY, ENAME, SAL FROM EMP@REGIONA

In this example, HQ and REGIONA are database links for remote databases. The result set is the union of all employees with a column named COMPANY1 to show which company they are from.

Example 4: Subquery in a custom folder definition

SELECT ENAME, SAL FROM EMP WHERE SAL > (SELECT AVG (SAL) FROM EMP)

In this example, the (SELECT AVG (SAL) FROM EMP) subquery is included in the folder definition.

Example 5: Optimizer hint in a custom folder definition

SELECT /*+ FULL(scott_emp) PARALLEL (scott_emp, 5) */ ename FROM scott.emp scott_emp;

In this example, the PARALLEL hint overrides the degree of parallelism specified in the emp definition.

Example 6: CONNECT BY Clause in a custom folder definition

SELECT EMPNO, ENAME, JOB FROM EMP CONNECT BY PRIOR EMPNO=MGR START WITH KING

In this example, the CONNECT BY clause defines a hierarchical relationship in which the EMPNO value of the parent row is equal to the MGR value of the child row (i.e. it filters each row where the PRIOR condition is true).

Example 7: Column Expression in a custom folder definition

SELECT ENAME, SAL*12+NVL(COMM,0) ANNUAL_SALARY FROM EMP

In this example, the alias ANNUAL_SALARY is required on the SAL*12+NVL(COMM,0) expression.

Although a custom folder can contain any valid SQL statement, any column expressions must be aliased in the same way that a SQL view definition would be aliased. In these cases, the alias will be used as the item name.

No alias is required on simple column expressions like ENAME.

Example 8: Using a custom folder to improve LOV performance

You can use a custom folder to create a list of values for an item in a folder where you know that the list values in the database will not change. This is more efficient than running a query using a list of values defined against an item in a folder that has a much larger number of rows than the number of distinct values.

If you have a smaller number of static values you can use a custom folder to create a local list of values within the End User Layer.

For example, if you want a list of values for North, South, East and West, create a custom folder called Region_lov and enter the following SQL:

SELECT 'NORTH' REGION FROM sys.dual UNION SELECT 'SOUTH' REGION FROM sys.dual UNION SELECT 'EAST' REGION FROM sys.dual UNION SELECT 'WEST' REGION FROM sys.dual

The above SQL creates one item called Region that you can use as a list of values to significantly improve performance.

For more information about lists of values, see "What are lists of values?".

About dependencies between folders

If you copy an item from a simple folder and paste the item into the same simple folder, the new item is completely separate from the original item and has a new name generated (e.g. region1). Changes in either item are never reflected in the other item.

If you copy an item from a simple folder and paste the item into a complex folder, the new item in the complex folder is still related to the original item. You can rename the new item and change its properties without the changes affecting the original item. However, the new item is dependent on the original item because the formula of the new item references the name of the original item. If you modify the formula of the original item, the data returned by the new item will also change. Similarly, if you delete the original item, the new item is also deleted because its formula property references the original item.

You can view a list of the dependencies for an item on the Dependencies tab of the Item Properties dialog.

If you apply a mandatory condition to a folder (e.g. where Year=2000), the set of data that can be returned will change. A complex folder built using this folder will reflect the restricted set of data of the source folder. If you later remove the mandatory condition from the source folder, the change is reflected in the complex folder.

About sharing folders across business areas

Data that is important to one department is often useful to another. Discoverer Administrator therefore enables you to share a folder that you have created in one business area with other business areas. For example, you might want to share a Sales Facts folder that includes columns for Income and Costs in the business areas you create for both the Marketing Department and the Accounting Department.

If you make changes to a folder in one business area, the changes are reflected in the folder in every business area that uses it.

How to create simple folders from the database

To create simple folders from the database in an existing business area:

  1. On the "Workarea: Data tab", select the business area in which you want to create a simple folder.

  2. Choose Insert | Folder | from Database to display the "Load Wizard: Step 1 dialog".

  3. Click On-line Dictionary to specify the location of the table/view on which you want to base the simple folder.

  4. Click Next to display the "Load Wizard: Step 2 dialog":

    Discoverer displays the following warning dialog.

    Figure 6-3 Warning dialog

    Description of Figure 6-3  follows
    Description of "Figure 6-3 Warning dialog"

  5. Click Yes to display the "Load Wizard: Step 2 dialog".

  6. Specify the database that owns the database table/view on which you want to base the simple folder.

  7. Select a check box for a database user to base the simple folder on.

  8. (optional) Specify the name of the object in the Load objects that match field.

  9. Click Next to display the "Load Wizard: Step 3 dialog".

  10. Expand the + symbol in the Available list to see the objects owned by the user.

  11. Move the object on which you want to base the simple folder from the Available list to the Selected list.

  12. Do one of the following:

    • click Next to modify the default settings for the simple folder in the "Load Wizard: Step 4 dialog"

    • click Finish to create the simple folder based on the table you specified using default settings

  13. If you clicked Next in the previous step, do one of the following:

    • click Next to modify further default settings for the simple folder in the "Load Wizard: Step 5 dialog"

    • click Finish to create the simple folder based on the table you specified using default settings and those specified so far

  14. If you clicked Next in the previous step, click Finish to create the simple folder based on the table you specified using the settings you specified.

How to create complex folders

To create a complex folder:

  1. On the "Workarea: Data tab", select the business area in which you want to create a complex folder.

  2. Choose Insert | Folder | New to create a new complex folder.

  3. Click the new folder's icon on the Data tab and choose Edit | Properties to display the "Folder Properties dialog".

  4. (optional) Specify a more descriptive name for the new folder in the Name field.

  5. (optional) Specify a description for the new folder in the Description field.

  6. Close the Folder Properties dialog if it is open.

  7. On the "Workarea: Data tab", Drag an item from any folder in any open business area to the new folder.

    Hint: You might find it easier to drag items between folders if you have two Workareas open. To open a second Workarea, choose Window | New Window.

  8. Drag the items into the new folder as required.

    When you add an additional item to a complex folder, the folders that it comes from must be joined to the folder of at least one other item already in the complex folder. If this is not the case, Discoverer Administrator will display an error dialog.

    If you select items from two folders that are joined using more than one join, Discoverer displays the Choose Join dialog. For more information, see "About joining two folders using more than one join".

    Figure 6-4 Choose Join dialog

    Description of Figure 6-4  follows
    Description of "Figure 6-4 Choose Join dialog"

    Note: If you select an item from a simple folder that has a join that conflicts with existing items, Discoverer will display an error and you will not be allowed to add the item.

  9. (optional) Select one or more joins and click OK.

Discoverer creates a complex folder.

Notes

How to create custom folders

To create a custom folder:

  1. On the "Workarea: Data tab", select a business area (or any object within a business area).

  2. Choose Insert | Folder | Custom to display the "Custom Folder dialog".

    Figure 6-5 Custom Folder dialog

    Description of Figure 6-5  follows
    Description of "Figure 6-5 Custom Folder dialog"

  3. Specify the SQL statement on which the custom folder will be based.

    You can include comments as separate lines within the SQL statement by starting the comment line with two dash characters (i.e. - -).

  4. Specify the name of the custom folder in the Name field.

  5. Click Validate SQL to ensure you have entered valid SQL.

  6. Click OK to validate the SQL statement and save the custom folder.

Notes

How to edit folder properties

To edit the properties of a folder:

  1. On the "Workarea: Data tab", click on the folder to select it.

    You can select more than one folder by pressing the Ctrl key and clicking another folder.

  2. Choose Edit | Properties to display the "Folder Properties dialog".

    Figure 6-6 Folder Properties dialog

    Description of Figure 6-6  follows
    Description of "Figure 6-6 Folder Properties dialog"

    If you selected multiple folders, the Folder Properties dialog displays a value for each property that has the same value for all of the selected folders. If the value for a particular property is not the same for all of the selected folders, no value is displayed for that property.

  3. Make the changes as required.

    Hint: To save the changes you make as you make them, select the Automatically save changes after each edit check box. With this option selected, you do not have to click OK or Apply.

  4. Click OK to save the changes you have made.

Notes

select <column> from <owner>.<table>

If you clear the Owner field, the generated SQL statement would be:

select <column> from <table>

If the object is not in the current schema, a warning is displayed.

How to edit a custom folder's SQL statement

To edit a a custom folder's SQL statement:

  1. On the "Workarea: Data tab", select the custom folder.

  2. Choose Edit | Properties to display the "Folder Properties dialog".

  3. Click the Custom SQL field to display the "Custom Folder Properties dialog".

    Hint: You can resize the Custom Folder dialog to view more of the SQL statement.

  4. Make changes as required.

  5. Click Validate SQL to ensure you have entered valid SQL.

  6. Click OK to validate the SQL statement and save the custom folder.

Notes

What is complex folder reach through?

Complex folder reach through is a mechanism that enables Discoverer Plus and Discoverer Desktop users to add items to their worksheets in addition to those provided in a selected complex folder. When a user selects items from a complex folder, other items in other folders become available for selection.

Note that you could achieve the same result by creating joins between the complex folder and its base folders. However, this approach is undesirable because:

In Discoverer Administrator you can define one or more base folders within a complex folder as 'reach through enabled'. When a Discoverer Plus or Discoverer Desktop user selects an item from the complex folder, the associated reach through enabled base folders become available for selection in a worksheet.

Complex folder reach through is particularly useful when using Discoverer with Oracle Applications flexfields (i.e. 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 and new flexfield items can be added to the base folder without having to modify the complex folder definition. Oracle Applications users can select the new flexfield items from the base folder immediately, as they are reach through enabled.

Notes

How to define a complex folder to enable reach through to the base folders

You define a complex folder to enable reach through the base folders to enable Discoverer users to select items from base folders that are not in the complex folder.

Note: Users can select items from base folders that are not in the complex folder only if they first select an item from the complex folder.

To define a complex folder and enable reach through enabled base folders:

  1. Select a complex folder on the "Workarea: Data tab" and choose Edit | Properties to display the "Folder Properties dialog".

  2. Display the "Folder Properties dialog: Reach-Through tab".

    Description of fproprt.gif follows
    Description of the illustration fproprt.gif

  3. Click one or more checkboxes to select the base folders that you want to be reach through enabled.

  4. Click OK to save your changes and close the Folder Properties dialog.

The folders you selected in the "Folder Properties dialog: Reach-Through tab" are now reach through enabled for the selected complex folder and are available for an end user to select, once they have selected an item from the complex folder.

What rules and restrictions apply when you use complex folder reach through?

A number of rules and restrictions apply to complex folder reach through as follows:

What rules determines the availability of base folder items to end users?

Once the end user selects an item from a complex folder, the availability of base folder items is defined as follows:

  • if a base folder is defined within the complex folder as reach through enabled, all of its items will be available for selection

  • if a base folder is not defined within the complex folder as reach through enabled, items from that folder, that are not in the complex folder, will not be available for selection

What restrictions apply to joins in Discoverer Administrator when defining a base folder as reach through enabled?

The following restrictions apply to joins in Discoverer Administrator when you define a base folder as reach through enabled:

  • Discoverer Administrator does not allow you to reach through enable a base folder if a join already exists between the complex folder and the base folder. Discoverer Administrator displays an error if this is attempted

  • Discoverer Administrator does not allow you to create a join between a base folder and a complex folder, if that base folder is reach through enabled in the context of the complex folder. Discoverer Administrator displays an error if this is attempted

What restrictions apply to summary folders in Discoverer Administrator when defining a base folder as reach through enabled?

When you define a base folder as reach through enabled, and use Automated Summary Management (ASM) to create summary folders, Discoverer will not generate summary folder recommendations based on items from base folders that are defined as reach though enabled. For more information about using ASM, see "How to run ASM using the Summary Wizard".

What restrictions apply in Discoverer Plus and Discoverer Desktop when building a worksheet using items from a reach through enabled base folder

The following restrictions apply in Discoverer Plus and Discoverer Desktop when building a worksheet using items from a reach through enabled base folder:

  • When a complex folder is defined as reach through enabled in the context of another complex folder, only one level of reach through is available.

    For example, if a complex folder (e.g. Complex2) is defined as reach through enabled in the context of another complex folder (e.g. Complex1). Another complex folder (e.g. Complex3) is not allowed to be defined as reach through enabled in the context of Complex2. The user will only be able to select items from Complex1 and Complex2.

  • If a Discoverer user selects an item from a reach through enabled base folder, any existing joins between the base folder and other folders are not included.

    In other words, if a base folder is available to a Discoverer user because it is reach through enabled, the joins normally associated with the base folder are not available. Therefore items in folders that are joined to the reach through enabled base folder are not available to the Discoverer user.

What exceptions apply when Discoverer end users open and save workbooks that contain items from reach through enabled base folders?

When Discoverer users open or save workbooks that contain items from reach through enabled base folders, Discoverer displays these items without requiring an explicitly defined join. The reach through mechanism enables workbooks to open normally, with the following exceptions:

  • if all the items of a reach through enabled base folder have been removed from a complex folder, and the items are in the worksheet

    In this case, Discoverer treats the worksheet items as if there was no join and displays an appropriate error message when opening the worksheet.

  • if a base folder has its reach through enabled property revoked

    In this case, the join required to use the base folder item does not exist, and Discoverer displays an appropriate error message when opening the worksheet.

  • if you remove a join in order to define a base folder as reach through enabled

    In this case, existing workbooks that use the join will not open, and Discoverer displays an appropriate error message when opening such workbooks. Before you remove a join, always check the impact.

  • if the end user attempts to open a workbook containing items from reach through enabled base folders in a version of Discoverer prior to 9.0.4.1

    In this case, the workbook cannot be opened.

What rules apply to complex folder reach through when you import EUL objects?

The following rules apply to complex folder reach through when you import EUL objects:

  • If you import a reach through enabled base folder into an EUL in which a join already exists between a complex folder and the base folder, Discoverer imports the base folder and defines it as not being reach through enabled. Discoverer writes a warning to the import log.

  • If you import a complex folder that is joined to a base folder into an EUL in which the base folder is defined as reach through enabled, Discoverer imports the complex folder and the join. However, Discoverer redefines the base folder as not being reach through enabled.

How to delete folders from a business area

To delete a folder from a business area:

  1. On the "Workarea: Data tab", select the folder you want to delete.

    You can select more than one folder by pressing the Ctrl key and clicking another folder.

  2. Choose Edit | Delete to display the "Confirm Folder Delete dialog".

    The Confirm Folder Delete dialog enables you to choose how you want to delete the folder and to assess the impact of deleting the folder.

  3. Specify how you want to delete the folder by selecting one of the following radio buttons:

    • Delete from this Business Area

      Removes the selected folder from the current business area. Note that this option does not delete the folder from the EUL. If the folder is not shared by any other business area, it becomes an orphan folder.

    • Delete from the End User Layer

      Removes the selected folder from all business areas that contain that folder. Note that this option also completely removes the folder definition from the EUL.

  4. (optional) To see the objects that might be affected by deleting this folder:

    1. Click Impact to display the "Impact dialog".

      The Impact dialog enables you to review the other EUL objects that might be affected when you delete a folder.

      Note: The Impact dialog does not show the impact on workbooks saved to the file system (i.e. in .dis files).

    2. (optional) Select a row to view text at the bottom of the list, indicating what affect the current action will have on the EUL object.

    3. When you have finished reviewing the impact of deleting the folder, click Close to close the Impact dialog.

  5. When you are certain that you want to delete the selected folder, click Yes.

    The selected folder is deleted in the way that you specified.

Notes

How to assign folders to a business area

To assign folders to a specific business area:

  1. Choose Tools | Manage Folders to display the "Manage Folders dialog: By Business Area tab".

    Use the "Manage Folders dialog: By Business Area tab" to assign any number of folders (including orphan folders) to a specific business area.

  2. Select the business area to which you want to assign one or more folders from the Business area drop down list.

    By default, the Business area drop down list displays the business area currently selected in the Workarea.

  3. Move the required folders from the Available folders list to the Current folders list.

    You can select more than one folder by pressing the Ctrl key and clicking another folder.

  4. Click OK to save the changes you have made.

    The business area now contains the folders you specified in the Current folders list.

How to assign a folder to multiple business areas

To assign a folder to multiple business areas:

  1. Choose Tools | Manage Folders and display the "Manage Folders dialog: By Folder tab".

    Use the "Manage Folders dialog: By Folder tab" to assign a specific folder (including an orphan folder) to multiple business areas.

  2. Select the folder that you want to assign to one or more business areas from the Folder drop down list.

  3. Move the required business areas from the Available business areas list to the Current business areas list

    You can select more than one business area by pressing the Ctrl key and clicking another business area.

  4. Click OK to save the changes you have made.

    The folder is now assigned to the business areas you specified in the Current business areas list.

How to view orphaned folders in the EUL

To view orphaned folders in the EUL:

  1. Choose Tools | Manage Folders and display the "Manage Folders dialog: Orphaned Folders tab".

    Discoverer displays all the Orphaned Folders in the current EUL.

How to remove an orphaned folder from the EUL

To remove an orphaned folder from the EUL:

  1. Choose Tools | Manage Folders and display the "Manage Folders dialog: By Business Area tab".

    Use the Orphaned Folders tab to delete an orphan folder from the EUL.

  2. Select the folder that you want to delete from the Orphaned Folders field.

  3. Click Delete to remove the highlighted folder from the EUL

    You can select more than one orphaned folder by pressing the Ctrl key and clicking another orphaned folder.

  4. Click OK to save the changes you have made.

    The folder is now deleted from the current EUL.

How to sort folders in a business area

You can alphabetically sort folders in a selected business area.

To alphabetically sort folders in a business area:

  1. Select a business area in the "Workarea: Data tab".

  2. Choose Edit | Sort Folders.

    Note: Discoverer Administrator displays the "Alphabetical Sort dialog" indicating the number of folders that will be alphabetically sorted and stating that the existing order will be lost.

  3. Click Yes to continue.

Notes

Folders can also be sorted during bulk load when you load a business area. For information about sorting folders when using the:

How to sort items in a folder

You can alphabetically sort items in a selected folder.

To alphabetically sort items in a folder:

  1. Select a folder from a business area in the "Workarea: Data tab".

  2. Choose Edit | Sort items.

    Note: Discoverer Administrator displays the "Alphabetical Sort dialog" indicating the number of items that will be alphabetically sorted and stating that the existing order will be lost.

  3. Click Yes to continue.

Notes

Items can also be sorted during bulk load when you load a business area. For information about sorting items when using the:

How to re-order folders in a business area

The order in which you see folders displayed in Discoverer Administrator is the same order that Discoverer end users will see. By default, folders within a business area are displayed in alphabetical order. However, you might want to change the default order to:

To change the order of folders in a business area:

  1. Display the "Workarea: Data tab".

  2. Drag and drop the folder to the position where you want it to appear in the list of folders.

How to validate folders in a business area

Occasionally, you might encounter difficulties with Discoverer Administrator folders. For example, you might be able to see folders in Discoverer Administrator that Discoverer end users cannot access. Use the Validate Folders facility to help you diagnose the problem.

To validate the link between the folders in a business area and the database objects they refer to:

  1. Choose View | Validate Folders.

    The Validate Folders facility uses the database parser to check that the relevant tables exist in the database and the EUL owner has SELECT access to them.

    If there are any errors, a message is displayed alongside the relevant folder(s).