5
Creating and maintaining folders

Creating and maintaining folders

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

• "What are folders?"

• "What are simple folders?"

• "What are complex folders?"

• "What are custom folders?"

• "About dependencies between folders"

• "About sharing folders across business areas"

• "How to create simple folders from the database"

• "How to create complex folders"

• "How to create custom folders"

• "How to edit folder properties"

• "How to edit a custom folder's SQL statement"

• "How to delete folders from a business area"

• "How to assign folders to a business area"

• "How to assign a folder to multiple business areas"

• "How to view orphaned folders in the EUL"

• "How to remove an orphaned folder from the EUL"

• "How to sort folders in a business area"

• "How to sort items in a folder"

• "How to re-order folders in a business area"

• "How to validate folders in a business area"

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:

• simple folders, which contain items based on columns in a single database table or view

• complex folders, which can contain items based on columns from multiple database tables or views

• custom folders, which are based on SQL statements

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 5-1 How Discoverer Administrator represents a table/view

Text description of the illustration fdrtbl.gif

What are complex folders?

Complex folders contain items from one or more other 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 9, "What are joins?".

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

Text description of the illustration fdrcpx.gif

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
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"

• "Example 3: Set Operator in a custom folder definition"

• "Example 4: Subquery in a custom folder definition"

• "Example 5: Optimizer hint in a custom folder definition"

• "Example 6: CONNECT BY Clause in a custom folder definition"

• "Example 7: Column Expression in a custom folder definition"

• "Example 8: Using a custom folder to improve LOV performance"

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 Chapter 8, "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.

For example, if you copy the item 'Region' from the Store table and paste it into the complex folder 'Video Analysis Information' and then edit the properties of the copied item 'Region' in the complex folder 'Video Analysis Information'. You will notice that the Formula property of the Region item is itself a calculated item that uses the calculation Store.Region. The calculation Store.Region indicates that the item is dependant on the item Region in the folder Store. The conditions that were described in the previous paragraph will apply to the Region item that you copied into the complex folder 'Video Analysis Information'.

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

A folder represents a set of data that can be returned from the database. 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 made to the folder in every business area that uses the folder.

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".

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

6. Select a radio button for a database user to base the simple folder on.

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

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

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

10. 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

11. If you clicked Next in the previous step, do one of the following:
    • click Next to modify the 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

12. 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 5-3 Choose Join dialog

   
   
   Text description of the illustration fdrchj1.gif

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

Discoverer creates a complex folder.

Notes

• If you include an item from a simple folder in a complex folder, the new item in the complex folder is not totally independent of the original item. For more information, see "About dependencies between folders".

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 5-4 Custom Folder dialog

   
   
   Text description of the illustration csfldlg.gif
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

• To enable you to create custom folders before the underlying database objects have been created or made available, Discoverer Administrator permits you to save a custom folder even if the SQL is invalid. Note that Discoverer end users will not be able to query the database object until the SQL is valid.

• For examples of SQL statements you might enter, see "Examples of custom folders".

• Where the SQL references multiple database objects, you must define joins between those objects. For more information, see Chapter 9, "Creating and maintaining joins".

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 5-5 Folder Properties dialog

   
   
   Text description of the illustration fpropgn.gif

   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

• You can assign a folder to more than one business area. However, there is only ever one definition of the folder in the EUL and that definition is shared by all the business areas to which the folder is assigned. If you modify the folder definition, the change is reflected in all the business areas to which the folder is assigned. For more information, see "About sharing folders across business areas".

• You can change a folder's name at any time because Discoverer Administrator identifies folders using unique identification labels called identifiers (for more information, see Chapter 3, "What are identifiers?"). Changing a folder's name does not alter the structure of the business area. However, folder names must be unique within the EUL. Note also that item names must be unique within a particular folder.

• You can change the database user that owns the database object on which a simple folder is based without changing the name of the object itself. For example, you might want to do this when moving from a development to a production environment. To change the database user, click the button beside the Owner field to display the "Choose user dialog" and specify the new database user.

• You can base a simple folder on a table/view without specifying the database user that owns that table/view. You might want to do this:
  • to enable different Discoverer Plus users to access database tables/views that have the same name but which exist in their own database users (e.g. for Oracle Applications users)

  • to create/maintain an EUL for which the tables or table owners are not yet available

  • to create/maintain an EUL to which you as the Discoverer Manager do not have access

  • to move the EUL between different databases where the person who owns the data is different

  To base a simple folder on a table/view without specifying the database user name, clear the Owner field. When the Owner field is left blank, Discoverer Administrator generates SQL that does not include the owner before the table name.

  For example, if the Owner field contains the name of the schema that owns the table/view, the generated SQL statement might be:

  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 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

• To enable you to create custom folders before the underlying database objects have been created or made available, Discoverer Administrator permits you to save a custom folder even if the SQL is invalid. Note that end users will not be able to query the object until the SQL is valid.

• Where the SQL references multiple database objects, you must define joins between those objects. For more information, see Chapter 9, "What are joins?"

• Discoverer Administrator analyzes the modified SQL statement to assess the changes you have made. If Discoverer Administrator determines that the changes will affect existing items or create new items, Discoverer Administrator displays the "Impact dialog" that shows you each item that is affected. Use the Impact dialog to help you decide whether to confirm the changes you have made, make further changes, or abandon the edit.

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".

      Figure 5-6 Impact dialog

      
      
      Text description of the illustration impactb.gif

      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

• Before deleting a folder, you might find it useful to identify the business areas to which the folder is assigned. To identify the business areas, click on the folder you want to delete on the Data tab of the Workarea and choose Tools | Manage Folders to display the "Manage Folders dialog: By Business Area tab". The Manage Folders dialog contains a drop down list of other business areas that hold the selected folder.

• To delete an orphaned folder from the EUL, you choose Tools ! Manage Folders to display the "Manage Folders dialog: Orphaned Folders tab" where you can view and delete orphaned folders. For more information, see "How to remove an orphaned folder from the EUL".

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".

   You use the Business Area -> Folder 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 Folder -> Business Area 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: Orphaned Folders 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:

• Load Wizard, see Chapter 4, "How to create a business area using the Load Wizard"

• command line interface, see Chapter 21, "/load"

How to sort items in a folder

You can alphabetically sort items and conditions 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:

• Load Wizard, see Chapter 4, "How to create a business area using the Load Wizard"

• command line interface, see Chapter 21, "/load"

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:

• group folders that are logically connected

• move the most commonly used folders to the top of the list

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.