Analyzing OBIEE Data and Creating Reports
The following are the steps of creating such an EUL with the help of Discoverer Administrator:
- Launch SQLPlus, connecting to the database /as sysdba Then, issue the following GRANT command: GRANT CREATE VIEW TO usr;
- After issuing the above command, you can quit SQLPlus
- Launch Discoverer Administrator by clicking Start | Programs | Oracle Business Intelligence Tools-BIToolsHome1 | Oracle Discoverer Administrator.
- In the dialog, Connect to Oracle Business Intelligence Discoverer Administrator, enter the usr/usr username/password pair to connect to the database. In this dialog, you’ll also need to enter the service name of the underlying database.
- After you click Connect, Discoverer Administrator will check if you have access to at least one EUL. If not, it will ask you to create one now. Click Yes, which will open the EUL Manager dialog. If you have access to at least one EUL, you will be immediately connected to Discoverer Administrator. In that case, you can open the EUL Manager dialog by clicking the Tools | EUL manager… menu.
- In the EUL Manager dialog, you should click the Create a EUL … button to proceed to create a new EUL. This will launch the Create EUL Wizard.
- In the first step of the Create EUL Wizard, you can select a user from the list of database users. This can be done in the Select User dialog, which you can open by clicking the Select … button in the wizard screen.
- In the Select User dialog, click the Go button to see a list of all users available in the Results box. In this box, choose usr by double-clicking it.
- After a user has been selected, the Finish button in the Create EUL Wizard should become available. Click it to make Discoverer Administrator create the EUL. This process will continue behind the scenes and should take less than a minute. Once it’s completed, you should see a message informing you about it.
- Close the EUL Manager dialog by clicking Close.
The following steps walk you through how to create a business area in Discoverer Administrator:
- After closing the EUL Manager dialog in Discoverer Administrator, you should see the Administration Tasklist dialog shown in the following screenshot. If you don’t see this dialog, click the View | Tasklist menu from the menu bar of Discoverer Administrator.
As you can see in the screenshot, the list of tasks you can accomplish with the Discoverer Administrator is not limited only to creating business areas. Once a business area is created, you can perform tasks on its items such as creating joins, calculated items, and conditions.
- In the Administration Tasklist dialog, click the Create business areas This should launch the Load Wizard dialog shown in the following screenshot:
In Load Wizard, click the Create a new business area button. As a result, the Next button of the wizard should become available. Click the Next button to move on to the Step 2 screen of the wizard.
- In the Step 4 screen of the wizard, click the Next button to move on to the last screen of the wizard.
- In the Step 3 screen of the wizard, in the Available box expand the usr list, select the orders item, and click the ► button to move orders to the Selected After that, click the Next button to proceed to Step 4 screen of the wizard.
- In the Step 2 screen of the wizard, choose the usr user in the Select the users whose tables you want to load box, and click Next to move on to the Step 3 screen of the wizard.
- In the Step 5 screen of the wizard, enter OrdersBusinessArea in the What do you want to name this business area? box, and click the Finish As a result, a four-tab window called the End User Layer USR should appear:
- Exploring the End User Layer USR window tabs, you can familiarize yourself with the structure of the newly created business area. For example, if you move on to the Hierarchies tab and expand the Orders BusinessArea list, you should see how the orddate field inherited from the underlying orders table was shredded into the default date hierarchy
- Now you can quit Discoverer Administrator by clicking the File | Exit menu item.
Exploring the Discoverer Plus IDE:
Before you can start exploring the Discoverer Plus IDE, you need to launch it. To do this, point your browser to the following URL:
http://yourhostname:7777/discoverer/plus
After a while, the Discoverer Plus IDE will be loaded and the first screen of the Workbook Wizard shown in the following screenshot should appear:
The Workbook Wizard shown previously will help you create a Discoverer workbook upon the underlying database data.
- On the first screen of the Workbook Wizard, leave all the settings at their defaults and click Next to move on the second screen.
- On the second screen of the wizard shown in the following screenshot, select the Orders folder in the Available box and click the > button to move all the Orders folder’s items to the Selected Then, click Next to move on to the next screen of the wizard.
- On the fourth screen of the wizard, you can select a column or columns to sort in the worksheet. Then, click Next to continue.
- On the third screen of the wizard, you should see the layout of the information in the worksheet being created along with this workbook. Here, you can not only look at the layout but also change the order of columns as necessary. Then, click Next to proceed to the next screen.
- On the fifth screen of the wizard, you can add a parameter or parameters to the worksheet being created. Parameters allow you to perform worksheet analysis by entering dynamic input values. For example, the user can be asked about the region to analyze each time the worksheet is opened or refreshed. Then, click Finish to complete the wizard. On the fourth screen of the wizard.
- Save the newly created workbook by the File | Save as… menu. This should open the Save Workbook to Database dialog. In this dialog, enter a name for the workbook, say, WorkbookOrders in the New name field, and then click Save
Once the Workbook Wizard is completed, you should see the newly created workbook’s worksheet with the data derived from the orders underlying table. The worksheet, along with the other components of the Discoverer Plus IDE, is shown in the following screenshot:
Analyzing data:
The following steps will walk you through the process of creating such a Region parameter:
- In the Discoverer Plus menu, select Tools | Parameters… to open the Edit Worksheet dialog at the Parameters
- On the Parameters tab of the Edit Worksheet dialog, click the New… button to open the New Parameter dialog shown in the next screenshot:
- In the New Parameter dialog, type in Region in the What do you want to name this parameter?
- In the New Parameter dialog, select Regid in the Which item do you want to base this parameter on?
- In the Edit Worksheet dialog, click OK to complete the creation of the parameter and come back to the worksheet.
- In the New Parameter dialog, you can leave the other settings at their defaults and click OK to come back to the Parameters tab of the Edit Worksheet
- In the New Parameter dialog, make sure that the Create condition with operator: checkbox is set and the operator in the list on the right is =.
- In the New Parameter dialog, type in Which region do you want to analyze? in the What prompt do you want to show for this parameter?
- On the Parameters tab of the Edit Worksheet dialog, in the Available parameters box, you should see the Regid
- The worksheet will be displayed, the Edit Parameter Values dialog shown next will be displayed, prompting you to enter the region id:
- You can enter, for example, NA, and click OK. As a result, you will see the worksheet containing only the records with the NA region id, as shown in the listing immediately after these steps.
- To save the parameter, so that it persists the current session, select the File | Save menu item.
Using Discoverer Viewer:
Discoverer Viewer is another Discoverer component. Although it’s much more lightweight than Discoverer Plus, you still might want to use it for analysis and reporting. The main advantage of Discoverer Viewer is its easy-to-use, intuitive user interface.
To launch Discoverer Viewer, point your browser to the following URL:
http://yourhostname:7777/discoverer/viewer.
After you’ve successfully connected to Discoverer Viewer, you should see the following window in your browser:
As you can see, Discoverer Viewer automatically found the workbook you have created in Discoverer Plus. You can open this workbook by clicking the + button on the left of the book’s icon located in the Result List section.
Using Oracle Reports
Oracle Reports is another intriguing Oracle Business Intelligence component, which comes with the Oracle Business Intelligence suite.
Starting up the Reports Server:
Oracle Reports uses Reports Server to listen for client requests and then processes them, generating requested reports or fetching the completed ones from the reports cache. Before you can make use of Reports Server, you have to start it up. This can be done with the following commands issued from an operating system prompt.
On Windows:
rwserver server= reports_server_name
On Linux, you would run the following command instead:
rwserver.sh server=reports_server_name
This should output the following dialog:
As you can see in the figure, you can always shut down the running server instance by clicking the Shutdown button.
Before you go any further, it’s always a good idea to verify that the server is running and ready to process user requests. You can easily do this by pointing your browser to the following URL:
http://yourhostname:7777/reports/rwservlet/getserverinfo?server=rep_server_name
Building reports with Reports Builder:
Reports Builder is the Oracle Reports’ development component. With the Reports Builder’s Reports Wizard, you can easily build a report, which you can then deploy to Report Server.
- Start Reports Builder by issuing the rwbuilder command from an operating system prompt. As an alternative, on Windows you could start it by selecting Start | Programs | Oracle Business Intelligence Tools – BI ToolsHome1 | Reports Developer | Reports Builder.
- When Reports Builder has been loaded, you should see the Welcome to Reports Builder dialog shown in the figure below:
- In the Welcome to Reports Builder dialog, make the Use the Report Wizard radio button is selected, and then click OK.
- On the first screen of the Report Wizard called Welcome to the Reports Wizard, click Next to move on to the Choose the type of layout you would like to generate screen shown in the figure below:
- On the Choose the type of layout you would like to generate screen, select Create Paper Layout only, and click Next.
- On the next screen of the wizard, you can specify the title for the report, say, List of orders in this particular example, and then select the report style. In this example, you might choose Group Left, so that you can group the report data later. Click Next to continue.
- On the next screen called Choose a data source type below, select SQL Query and then click Next.
- On the Select data that you will use in your report screen, click the Query Builder… button to open the Query Builder.
- Before the Query Builder becomes available, you’ll be asked to connect to the underlying database, entering the user name, password, and the database name as defined in the tnsnames.ora file.
- After a connection is established, you will be asked to select tables in the Select Data Tables dialog. Select orders and click Include to include this table to the Query window of the Query Builder, and then click Close to close the Select Data Tables dialog.
- In the Query window of the Query Builder, mark all the columns of the orders table as shown in the figure below, and then click OK.
- On the next screen called Select the fields that you would like to designat as group fields…, move the regid field from the Available Fields pane to the Group Fields pane. As a result, you should see the regid field under the Level 1 node in the Group Fields pane
- On the Select data that you will use in your report screen, click Next.
- In the Group Fields pane, select the Level 1 node. Then, move the orddate field from the Available Fields pane to the Group Fields pane. As a result, the orddate field should appear in the Group Fields pane under the Level 2 node, as shown in the figure below:
- On the Select the fields that you would like to designate as group fields… screen, click Next to move on to the next screen.
- On the Select the fields that you would like to display in your report screen, move all the available fields from the Available Fields pane to the Displayed Fields pane, and then click Next to continue.
- On the Select the fields for which you would like to calculate totals screen, in the Available Fields pane select the total field and click the Sum button to move that field to the Totals pane. Then, click Next to continue.
- On the Modify the labels and widths for your fields and totals as desired screen, you can leave the settings at their defaults, and click Next.
- On the Choose a template for your report screen, leave the settings at their defaults and click Next.
- On the final screen of the wizard, click Finish to complete creating of the report. As a result, the Report Editor – Paper Design window looking like the following figure will appear:
- Close the Report Editor – Paper Design window.
- In the Object Navigator, select the MODULE1 node, and then select the File | Save as… menu of Reports Builder.
- In the Save dialog, save the report as a JSP document to disk, naming it, say, orders.jsp.
Now that you have created the report, how can you run it against your Reports Server? To do this, just point your browser to the following URL:
http://yourhostname:7777/reports/rwservlet?report=c:/myreports/
orders.jsp&userid=usr/usr@orcl&destype=cache&desformat=pdf
The userid parameter provides information about the underlying database containing the data for the report. And the des format parameter specifies the format to use for the output. As a result, the following pdf document should be generated, invoking Acrobat Reader within your browser:
Now you can save the generated pdf document to disk, using standard Acrobat Reader means.