Oracle Beehive Business Views: Extending Its Reporting Capabilities

This article introduces you to the capabilities of Oracle Business Views and techniques to extend its reporting capabilities. In addition, it shows you how to integrate Oracle Business Views with Oracle Application Express.

Refer to "Oracle Beehive Business Reporting" in Oracle Beehive Application Developer's Guide from the Oracle Beehive Documentation Library for more information about Oracle Beehive Business Views.

The following topics are covered:

• Introduction
• Problem
• One Approach to the Problem
• Creating a User Interface

Introduction

Your manager, Cameron Hudson, has requested reports about workspace usage of Vision Corporation's professional communities. For a description of Vision' Corp.'s professional communities, refer to the section "Professional Communities." For example, Cameron would like to know the total number of workspaces in each professional community and in each region.

Cameron would also like you to create a Web application that would present this usage information in easy-to-read charts and tables. In addition, the Web application should display live information; it should connect to Oracle Beehive each time anyone uses the application to retrieve information.

Oracle Beehive Business Views enables you to obtain statistical information about workspaces and documents within a particular scope (enterprise, organization, or workspace).

Problem

To retrieve this workspace information with Oracle Beehive Business Views, you consider creating a hierarchy of organizations in Oracle Beehive to represent the structure of professional communities in your corporation.

You consider creating several organizations in Oracle Beehive, one organization to represent each region. Within each region organization, you would create an organization to represent each professional community. Each professional community organization would contain workspaces that pertain to it. The following diagram represents this organization structure.

You may now query information about all workspaces within a given region with Oracle Beehive Business Views. For example, to retrieve the total number of workspaces in the region France, you would use the following query:

SELECT COUNT(*) as total_workspaces
                              


  FROM bee_workspaces_rv
                              


  WHERE parent_eid = '
                              
<EID of France organization>';
                            

However, with this Oracle Beehive organization structure, you cannot query information easily about all workspaces within a particular professional community. For example, suppose you want to count the number of workspaces that are part of the Performance and Tuning professional community. You have the following options:

• Use a naming convention for those workspaces that are part of the Performance and Tuning professional community. You may then query the bee_workspaces view by workspace name. The problem with this solution is that it is difficult to enforce naming conventions, especially in a large, multi-lingual corporation.

• Use a different organization structure. Instead, you can create several organizations in Oracle Beehive, one organization to represent each professional community. Within each professional community organization, you can create an organization to represent each region. The following diagram represents this organization structure:

  

  However, with this organization structure, you cannot query information easily about all workspaces that belong to a particular region.

One Approach to the Problem

You instead choose to store region and professional community information for each workspace outside Oracle Beehive in another database table.

This approach involves using a separate table to store information about each workspace's region and professional community.

This approach involves the following steps:

1. Define clusters, one cluster for each region and professional community.
2. Populate the tables you have just created
3. Create SQL queries with Oracle Beehive Business Views and cluster tables

Step 1: Define clusters, one cluster for each region and professional community.

A cluster is simply a mapping between a workspace and a characteristic (in this case, either the professional community that its users belong to or the region that its users reside).

Define the following tables:

                               
CREATE TABLE "BEEHIVE"."PC_CLUSTER_NAME"
                                


   (       "ID" NUMBER,
                                


        "NAME" VARCHAR2(50 BYTE),
                                


    CONSTRAINT "PK_PC_CLUSTER_ID" PRIMARY KEY ("ID"))
                                


                                 


CREATE TABLE "BEEHIVE"."PC_WORKSPACES"
                                


   (        "WORKSPACE_EID" VARCHAR2(44 BYTE),
                                


   CONSTRAINT "PK_PC_WORKSPACES" PRIMARY KEY ("WORKSPACE_EID"))
                                


                                 


CREATE TABLE "BEEHIVE"."PC_WS_CLUSTER_MAP"
                                


   ( "ID" NUMBER,
                                


        "CLUSTER_ID" NUMBER,
                                


        "WORKSPACE_EID" VARCHAR2(44 BYTE),
                                


   CONSTRAINT "PK_PC_WS_CLUSTER_MAP" PRIMARY KEY ("ID"))
                              
                            

Refer to the file pc_cluster.txt for SQL commands that create these tables for a database more suited for a production-level deployment.

Step 2: Populate the tables you have just created

The following tables visually depict how you would fill these tables for Vision Corp.:

BEEHIVE.PC_CLUSTER_NAME: This table contains every region and every professional community in Vision Corp. Each row contains either a region or a professional community and a unique ID.

BEEHIVE.PC_WORKSPACES: This table contains all the EIDs of the workspaces that Vision Corp. professional communities use.

BEEHIVE.PC_WS_CLUSTER_MAP: Each row in this table contains the EID of a workspace, the ID of the cluster (region or professional community) it belongs to, and a unique ID. Note that a workspace EID will appear twice in the WORKSPACE_ID column since each workspace in this scenario belongs to a professional community and a region.

Step 3: Create SQL queries with Oracle Beehive Business Views and cluster tables

The following SQL queries demonstrate various ways to use Oracle Beehive Business Views in conjunction with the cluster tables you created and populated in previous steps:

Count the number of workspaces in each cluster

SELECT pc_cluster_name.name AS "Professional Community Cluster"
                              


  , bee_workspaces_s_rv.workspace_name AS "Workspace Name"
                              


  , bee_workspaces_s_rv.workspace_eid
                              


  , COUNT(*) AS "Membership Count"
                              


                               


FROM bee_user_marker_totals_s_rv
                              


  , bee_wspc_member_roles_s_rv
                              


  , bee_workspaces_s_rv
                              


  , pc_workspaces
                              


  , pc_cluster_name
                              


  , pc_ws_cluster_map
                              


                               


WHERE bee_wspc_member_roles_s_rv.accessor_eid = bee_user_marker_totals_s_rv.user_eid
                              


  AND bee_workspaces_s_rv.workspace_eid = bee_wspc_member_roles_s_rv.workspace_eid
                              


  AND bee_workspaces_s_rv.workspace_eid= pc_workspace.workspace_eid
                              


  AND pc_cluster_name.id = pc_ws_cluster_map.cluster_id
                              


  AND pc_ws_cluster_map.workspace_eid = pc_workspaces.workspace_eid
                              


                               


GROUP BY pc_cluster_name.name
                              


  , bee_workspaces_s_rv.workspace_name
                              


  , bee_workspaces_s_rv.workspace_eid
                              


                               


ORDER BY pc_cluster_name.name;
                            

• To determine the workspace name associated with the workspace EIDs stored in the cluster tables, bee_workspaces and pc_workspace are joined by workspace EID.
• To determine the name of the cluster associated with the ID stored in pc_ws_cluster_map, that table is joined to pc_cluster_name by the cluster's ID.
• To determine which workspace belongs to which cluster, pc_ws_cluster_map and pc_workspaces are joined by workspace EID.
• bee_user_marker_totals (which contains the login ID of Oracle Beehive users) and bee_workspaces are indirectly joined by bee_wspc_member_roles to ensure that entries retrieved by the query contain workspaces that have correct membership information.
• The result of the SELECT are grouped by cluster name, workspace name, and workspace EID.

List the top 10 workspaces with the largest membership

SELECT NULL, SUBSTR(workspace_name, 1, 15) AS "Workspace name"
                              


  , members
                              


                               


FROM bee_workspaces_s_rv
                              


  , (SELECT bee_workspaces_s_rv.workspace_eid
                              


  , COUNT(*)AS members
                              


  , RANK () OVER (ORDER BY COUNT(*) DESC) AS Rank
                              


                               


  FROM bee_user_marker_totals_s_rv
                              


    , bee_wspc_member_roles_s_rv
                              


    , bee_workspaces_s_rv
                              


    , pc_workspaces
                              


                               


  WHERE bee_wspc_member_roles_s_rv.accessor_eid = bee_user_marker_totals_s_rv.user_eid
                              


    AND bee_workspaces_s_rv.workspace_eid = bee_wspc_member_roles_s_rv.workspace_eid
                              


    AND bee_workspaces_s_rv.workspace_eid = PC_WORKSPACES.WORKSPACE_EID
                              


                               


  GROUP BY bee_workspaces_s_rv.workspace_eid) ranked
                              


                               


WHERE ranked.workspace_eid = bee_workspaces_s_rv.workspace_eid
                              


  AND ranked.rank < 10
                              


                               


ORDER BY rank;
                            

• To determine the names of the users who are members of any workspace, the user ID of bee_user_marker_totals is joined by the accessor EID of bee_wspc_member_roles. The accessor EID represents the entity (such as a user) who has access to (or is a member of) a particular entity (such as a workspace).
• To determine who is a member of which workspace, bee_workspaces and bee_wspc_member_roles are joined by workspace EID. To narrow the results to only professional community workspaces, bee_workspaces is also joined with pc_workspaces.

For each workspace, list each of its documents' creation date, latest update day, and size

SELECT workspace_name AS "Workspace Name",
                              


  document_name AS " Document Name",
                              


  TO_CHAR(document_created_on,'YYYY/MM/DD HH24:MI') AS "Created on",
                              


  TO_CHAR(document_modified_on,'YYYY/MM/DD HH24:MI') AS "Modified on",
                              


  document_data_size AS "Size"
                              


                               


FROM bee_workspace_documents_s_rv
                              


                               


WHERE workspace_type = 'wstm'
                              


                               


GROUP BY workspace_name
                              


  , document_name
                              


  , document_created_on
                              


  , document_modified_on
                              


  , document_data_size;
                            

This is a straightforward query on the bee_workspace_documents view. wstm represents a team workspace, wspr a personal workspace.

For each user, list the workspaces of which that user is a member and contains the documents of that user

SELECT bee_workspace_documents_s_rv.document_name
                              


  , bee_user_marker_totals_s_rv.user_name
                              


  , bee_workspace_documents_s_rv.workspace_name
                              


                               


FROM bee_user_marker_totals_s_rv
                              


  , bee_workspace_documents_s_rv
                              


  , bee_wspc_member_roles_s_rv
                              


                               


WHERE bee_user_marker_totals_s_rv.user_eid = bee_wspc_member_roles_s_rv.accessor_eid
                              


  AND bee_wspc_member_roles_s_rv.workspace_eid = bee_workspace_documents_s_rv.workspace_eid
                              


  AND bee_workspace_documents_s_rv.workspace_type = 'wstm';
                            

• To determine the names of the users who are members of any workspace, the user ID of bee_user_marker_totals is joined by the accessor EID of bee_wspc_member_roles. The accessor EID represents the entity (such as a user) who has access to (or is a member of) a particular entity (such as a workspace).
• To determine which documents belong in which workspace, bee_wspc_member_roles and bee_workspace_documents are joined by workspace EID.

For the specified workspace, list the roles for all of its users

SELECT bee_user_marker_totals_s_rv.user_name AS "User Name"
                              


  , bee_wspc_member_roles_s_rv.role_name AS "Role Name"
                              


                               


FROM bee_user_marker_totals_s_rv
                              


  , bee_wspc_member_roles_s_rv
                              


  , bee_workspaces_s_rv
                              


                               


WHERE bee_wspc_member_roles_s_rv.accessor_eid = bee_user_marker_totals_s_rv.user_eid
                              


  AND bee_workspaces_s_rv.workspace_eid = bee_wspc_member_roles_s_rv.workspace_eid
                              


  AND bee_workspaces_s_rv.workspace_eid =  
                              
<EID of the specified workspace>
                            

Specify the EID of the specified workspace in the last predicate of the WHERE clause.

Creating a User Interface

You may use any graphical user interface library to display information retrieved from Oracle Beehive Business Views as easy-to-read tables and charts. However, Oracle Database comes with Oracle Application Express (Oracle APEX), a tool that enables you to build Web applications that report on database data. With Oracle APEX, you can quickly generate HTML reports that display the results of SQL queries. You can also download and print reports in HTML, PDF, RTF (compatible with Microsoft Word), and XLS (compatible with Microsoft Excel) formats.

Implementing a User Interface with Oracle APEX and Oracle Beehive Business Views

Implementing a user interface with Oracle APEX for Oracle Beehive Business Views involves the following steps:

1. Create a user that access (read-only) to only Oracle Beehive Business Views
2. Configure Oracle APEX so it can connect to Oracle Beehive
3. Create an application with Oracle APEX and Oracle Beehive Business Views SQL queries

Step 1: Create a schema that access (read-only) to only Oracle Beehive Business Views

For better security, create a schema that has read-only access to only Oracle Beehive Business Views. Follow the steps described in the section "Creating Schema with Access Only to Oracle Beehive Views" in "Oracle Beehive Business Reporting" in Oracle Beehive Application Developer's Guide from the Oracle Beehive Documentation Library.

Step 2: Configure Oracle APEX so it can connect to Oracle Beehive

1. From the Oracle Database in which you installed Oracle APEX, create a public database link to the schema that has access only to Oracle Beehive Business Views:

   1. create public database link beeLink connect to bee_rviews identified by Welcome1 using 'beehive.example.com';
      

   2. create tablespace BeeRViews;
      

   3. create user BeeRViews default tablespace beeLink temporary tablespace temp identified by Welcome1;
      

   4. grant connect to BeeRViews;
      

   5. grant resource to BeeRViews;
      

   In these SQL commands,

   • beeLink is the name of the public database link
   • bee_rviews is the name of the schema with access to Oracle Beehive Business Views
   • Welcome1 is the password of the schema
   • beehive.example.com is the connect string of your Oracle Beehive database
   • BeeRViews is the user through which you will access Oracle Beehive Business Views in Oracle APEX

2. Create synonyms for each of the Oracle Beehive Business Views you want to use. For example, to create a synonym for bee_organizations_s_rv, use the following SQL command, where bee_orgs is the synonym name:

   create synonym bee_orgs for bee_code.bee_organizations_s_rv@BeeRViews;
   

Step 3: Create an application with Oracle APEX and Oracle Beehive Business Views SQL queries

Details regarding creating an application with Oracle APEX is outside the scope of this tutorial.

If you are new to Oracle APEX, refer to Oracle Database 2 Day + Application Express Developer's Guide. Refer to the following for more in depth information:

• Oracle Database Application Express Advanced Tutorials
• Oracle Database Application Express User's Guide