OBIEE 10g Vs OBIEE 11g

 

Comparing the Oracle Business Intelligence 10g and 11g Security Models

The security policy for Oracle Business Intelligence 11g defines what individual users and users with certain application roles can access and do. In Oracle Business Intelligence 11g, the security policy definition is split across the following:

• Presentation Catalog - this defines which catalog objects and Oracle BI Presentation Services functionality given users and application roles can access.

• RPD - this defines which application roles and users have access to which items of metadata within the RPD. You define this security policy in the Administration Console.

• Policy Store - this defines which Oracle Business Intelligence Server, Oracle Business Intelligence Publisher and Real Time Decisions functionality can be accessed by given users or users with given application roles. Use Oracle Enterprise Manager to configure the default Oracle Business Intelligence Policy Store.

Oracle Business Intelligence10g and 11g security models differ in the following areas:

• Defining users and groups - in Oracle Business Intelligence 10g, it was possible to define users and groups within a repository file using the Oracle BI Administration tool. In Oracle Business Intelligence 11g, you can no longer define users and groups within a repository. The Oracle Business Intelligence Enterprise Edition Upgrade Assistant migrates users and groups from a 10g repository into the embedded LDAP server in an 11g installation.

• Defining security policies - in Oracle Business Intelligence 10g, security policies in the Web catalog and repository can be defined to reference groups within a directory. In Oracle Business Intelligence 11g, security policies are defined in terms of application roles, which are in turn mapped to users and groups in a directory. This allows an Oracle Business Intelligence 11g system to be deployed without changes to the corporate directory and eases movement of artifacts between development, test and production environments.

• Use of the Administrator user - in an Oracle Business Intelligence 10g installation, a special user named, Administrator has full administrative permissions and is also used to establish trust between processes within that installation. In Oracle Business Intelligence 11g there is no special significance to the name Administrator and there can be one or more users who are authorized to undertake different sets of administrative functions. In Oracle Business Intelligence 11g the identity used to establish trust between processes in an installation is configurable and independent.

• Repository encryption - in Oracle Business Intelligence 10g, certain sensitive elements within a repository are encrypted. In Oracle Business Intelligence 11g, the entire repository is encrypted using a key derived from a user supplied password. An 11g repository can only be opened with the password, and there is no mechanism to recover a lost password.

The following aspects of the Oracle Business Intelligence 10g security model remain in 11g:

• Oracle Business Intelligence Server Initialization Blocks - Oracle Business Intelligence Server 11g continues to support the use of initialization blocks for authentication and authorization. In 10g Oracle Business Intelligence Server falls back to use initialization blocks if a matching user cannot be found in the repository. In 11g Oracle Business Intelligence falls back to use initialization blocks if the user cannot be authenticated by the installation's configuration authentication provider.

• Presentation Catalog Groups - Oracle Business Intelligence 11g continues to support the definition of catalog groups within the presentation catalog. These groups are only visible within Oracle Business Intelligence Presentation Services. Oracle recommends that presentation catalog groups be used for backward compatibility only and that application roles be used instead for new installations.

• SA System Subject Area - Oracle Business Intelligence 11g supports the use of SA System Subject Area in combination with Oracle Business Intelligence Server initialization blocks to access user, group, and profile information stored in database tables.

Oracle Business Intelligence 11g

 

Logical Architecture

Oracle Business Intelligence Logical Architecture

The Oracle Business Intelligence domain contains three main components: a WebLogic domain, Oracle Business Intelligence System Components, and a database. Each main component is represented in its own rectangle contained within the Oracle Business Intelligence Domain rectangle. The Database component rectangle contains only a database, however the WebLogic domain and Oracle Business Intelligence System Components rectangles contain several subcomponents each.

Oracle Business Intelligence Directory Structure

A typical Oracle Business Intelligence installation consists of a Fusion Middleware home and the following subdirectories:

• wlserver_10.3: The WebLogic Server home, which contains Java components, one Administration Server, and one or more Managed Servers.

• oracle_home: The Oracle Home contains binary and library files for Oracle BI.

• oracle_common: The Oracle Common Home contains the binary and library files required for Fusion Middleware Control and Java Required Files (JRF). There can be only one Oracle Common home within each Middleware home.

Typical Oracle Business Intelligence Directory Structure on a Single host with default values. At the top of the directory structure is the Middleware Home, which contains the Product Oracle Home, the Oracle Common Home, and the WebLogic Home. Notice the domains and instance directories are de-coupled from the MW_HOME and ORACLE_HOME.

Ref: Oracle Business Intelligence documentation

Normalizing and loading Excel files using Informatica Powercenter

Sometimes it is a requirement where information is managed in so-called flat files (i.e.: .csv, .xls, etc) which are not user-friendly. The purpose of this article is to explain the process of loading such files in the data warehouse using informatica power center.

Scenario

A sample source file is shown below:

And the expected result to be loaded into the DW is as below-

Solution

One possible way to achieve this is explained below -

In Excel

1 – Define a range in Excel selecting the following area (as can be seen below) and name this “Budget”

In Windows

2 – Create an ODBC connection to the excel file where you defined the range in the previous point.

In Informatica Powercenter

3 – In Source Analyzer, import a table from the database choosing the ODBC connection defined in the previous point selecting the “Budget” table or the name given to the table in the point 1.

4 – Create a target table with the relevant target fields. Then in Target Designer, import the table where you want to load the data normalized. You can see a simplified version of the table below.

5 – In Mapping Designer, create a mapping which should look like the following.

Tips:

– We’ll use just the first 13^th port to link “Source Qualifier” and “Normalize” transformations. (From port called “Sales_Rep” to port called “Dec”, the columns that we want to load into the target table)

6 – Normalizing – Configuring the Transformation

As you can see in the snapshot, we have to create two ports inside the tab called “Normalizer”, one for the Sales Representative and the other for the Budgets.

Sales_Rep: is the first column that we have in the Excel file.

Budget: in the option called “occurs”, we have to divide it into 12 columns because it is the number of Budgets per year (monthly budget).

In the tab called “Ports” we can see that several ports were generated automatically.

7 – Configuring the “Expression”

We have to configure the expression as seen in the snapshot.

Tips:

Family_Group: a parameter has to be defined into the mapping, called $$Family and define it as Parameter type, because this mapping will be executed twice, but with different Families (Family1 & Family2)

GCID_Period_OUT: a new column with the following formula has to be created:

DECODE(GCID_Period_IN, 1,'Jan', 2,'Feb', 3,'Mar', 4,'Apr', 5,'May', 6,'Jun', 7,'Jul', 8,'Aug', 9,'Sep', 10,'Oct', 11,'Nov','Dec')

The values that we have in GCID_Period_IN, is sequential beginning from 1 up to the total number of occurrences (in this case 12) defined in point 6 which are basically the number of months.

In Workflow Manager

8 – Configuring the workflow

As can be seen in the snapshot below, the same mapping will be executed twice, taking into account the following configurations.

Session Configurations:

The first session:

The Parameter Filename has to be set (paramfile_Family1.txt, which should be defined inside by the following parameter: $$Family = ‘Family1’

The second session:

Another Parameter Filename has to be set (paramfile_Family1.txt, which should be defined by the following parameters, $$Family = ‘Family2’ and VERY IMPORTANT, we have to override the SQL query into the session using the following:

Basically, you are changing the source columns to load into the second session, the Family2’s budget, as can be seen in the following diagram.

Oracle BI EE- Multi User Development (MUD)

 

MUD stands for Multi-User Development(MUD) for repositories. Typically when you have many data sources and lots of tables, it would make sense to distribute the repository development work to multiple users. MUD is basically a feature of the BI EE admin tool wherein multiple users can work on the repository at the same time and not worry about change control.

The below diagram shows how the MUD works.

Following are the pre-requisites for enabling MUD in an environment.

1. All the client machines should have the admin tool installed.

2. A shared drive to host the Master Repository

3. The client machines should have access to the master repository

For the MUD to work, the repository that is worked upon by all the users should be kept in a shared directory. This shared directory should be accessible to all the users. In each of the client’s Admin tool, enter the Shared Directory path.

The concept of MUD is based on Projects. Projects are basically subsets of objects within the Admin tool that can be assigned to individual users. From within the Admin tool once can navigate to Manage – Projects. Following are the steps involved in a MUD env –

1. Create Projects with subset of objects to be assigned

2. Assign the individual projects to different users

3. Users Checkout Individual Projects they have access to

4. Once done, Users merge the changes back to the local master repository.

5. Choose “Publish to Network” to copy the modified and merged local master repository to the shared drive.

Using Oracle BI Office in OBIEE for Microsoft Excel and Powerpoint

Installing the BI Office Client

To install the BI Office Client:

1. Log in to Oracle BI Answers or Oracle BI Interactive Dashboards with your Oracle BI EE user credentials.

2. Select the More Products link and then select Download Oracle BI for Microsoft Office.

   

3. A dialog box prompts you to save or run OracleBIOffice.exe. Save the file to your local directory.

4.Close the Excel or PowerPoint applications on your system, if open. Navigate to the saved location and double-click OracleBIOffice.exe.

InstallShield Wizard appears.
Note: Microsoft .Net Framework 2.0 is required for the BI Office client. If it is not installed on your computer, you are prompted to exit and install Microsoft .Net Framework 2.0 from the Microsoft Web site.

When the Welcome page appears, click Next.

5. Select the installation type. Typical performs an installation for both Excel and PowerPoint Add-Ins to C:\Program Files\Oracle\BIOffice. Custom enables you to select the install directory and choose the components to install. Select Typical as the installation type, and click Next.

6. Review the setup information and then click Next.

7.When the InstallShield Wizard completes, click Finish.

When Oracle BI EE was installed on your computer, the BI Office Server was installed automatically. The installer performs other configuration tasks such as the following:

Deploying the bioffice.ear file

Updating the instanceconfig.xml file with the location of the OracleBIOffice.exe client

Copying the OracleBIOffice.exe file to the location where Presentation Services is running, which makes the client installation available for download from the More Products menu (as performed in step 1)

Initializing values in the BI Office configuration file (bioffice.xml file)

The following image depicts a sample bioffice.xml file, which contains critical information used by the client to connect to Presentation Services. (Observe the highlighted line in the file, which defines the URL for the Presentation Services.) You create the connection in the BI Office client in the next step.

Note: If any of the above configuration tasks were not performed due to some reason, you can look up the documentation available here to perform these installation and configuration tasks manually.

9. Configure the BI Office client to associate the BI Office Add-Ins with Presentation Services:

Enter the following information in the Connection Detail dialog box:

Server Name: This is the name that you give to the BI Office Server connection. Enter localhost as the name.

BI Office Server: This is the BI Office Server URL. You can enter localhost because the BI Office Server is installed on your local machine.

Port: For a Java 2, Enterprise Edition (J2EE) deployment, the BI Office Server port is 9704. The default value is 80; change it to 9704.

Application Name: The default value is bioffice. When you deploy the BI Office Server application file to OC4J or to your supported J2EE container, you can provide any name for the application. In this case, the default value of bioffice was accepted.

Click Test Connection.

Note: If you have given a different name for the application than the default value of bioffice, enter the name of the application accordingly while creating the connection. You can also edit the connection details after it is created, select the connection from Oracle BI > Preferences, and then click Edit.

Using BI Office with Excel and PowerPoint

Now that you have successfully installed the BI Office client and configured the connections, you are ready to start using the BI Office Add-Ins. In this topic, you are guided to use the features in the BI Office Add-Ins that enable you to analyze and deploy Business Intelligence reports easily in Microsoft Office applications, Excel and PowerPoint.

Some key features provided by BI Office are listed here:

You can copy views from Answers and Interactive Dashboard pages and paste them into Excel and PowerPoint documents by using the Paste feature of BI Office Add-Ins. Copied views are pasted as Office documents (Excel or PowerPoint tables and charts). This functionality also extends to copying compound views from Answers and Dashboards. Compound views copied from Answers or Dashboards are pasted into Microsoft Office documents as native Microsoft Office tables and charts.

For every request, the flat data view provides the entire data set for the original request in a simple tabular format. This view is more suitable for use when users want to obtain only the data and then use the Excel functionality to perform further analysis on that data.

The table view displays results in a tabular format. Users can navigate through the results, add totals, customize headings, and change the formula or aggregation rule for a column.

The pivot table view, available in the BI Office Excel Add-In, presents data from BI Requests with page items as well as a section-based layout if these sections were defined in the BI pivot table view for the request in Answers.

A BI Presentation Catalog browser is provided in Excel and PowerPoint to browse requests (both user created and shared).

Support is provided for prompts defined in the BI request.

If a view selected for insertion has multiple levels from one or more dimensions, you can choose to limit the data for each dimension by deselecting the levels from a level-selection dialog box. You can also edit the prompts and levels after the view is inserted .

You can insert table and chart views into PowerPoint presentations as PowerPoint tables and charts (subject to the limitations of PowerPoint).

You can insert chart views as images in Excel spreadsheets and PowerPoint presentations that can be refreshed.

You can insert chart views as high-quality Flash objects in PowerPoint presentations that can be refreshed.

For BI chart views inserted as native Excel or PowerPoint charts, you can change the chart type and apply other formatting changes by using Excel and PowerPoint charting capabilities. These changes are preserved during refreshes.

You can define Excel-specific conditional formats to data from BI views. These conditional formats can be preserved during data refreshes.

You can add gauge and funnel views in Excel and PowerPoint for better analysis of the reports.

You can secure a slide or worksheet, or you can secure the entire presentation or workbook. When you secure a particular object in Excel or Powerpoint, all BI views on that object—that is, slide, worksheet, presentation, or workbook are secured. Users must authenticate themselves before they can refresh and view this secured BI data. Secure communication is handled through SSL.

Using BI Office with Excel

In this topic, you work with basic views such as table, chart, and pivot table. This topic shows you how to log in to BI Office in Excel, locate a table and insert the data as a list (flat data) into Excel (so that you can manipulate the data by using Excel capabilities), copy a chart from the Presentation Catalog, switch between Excel and Answers to modify the chart, and copy and paste this chart into Excel. You also insert the pivot table view into Excel.

1. To use BI Office Add-Ins, you must log in. Select Start > All Programs> Microsoft Office > Microsoft Excel 2007 to launch Excel.

From the Oracle BI menu on the Ribbon, select Login. (Observe the Oracle BI–specific options added in the toolbar. Note that these menu options are added in Excel during the installation of BI Office Add-Ins.)

2. The Login dialog box appears. Ensure that the connection you created before (localhost) is selected from the Connection drop-down box. Enter Administrator in both the User ID and Password fields, accept the default for the remaining fields, and click Login.

You are now connected to Presentation Services.

3. The Presentation Catalog appears in Oracle B I Task Pane on the right.

To view catalog details, click the plus sign to expand the Shared and Paint Demo folders.

Also note that the Presentation Catalog can be hidden or displayed by clicking the Oracle BI Catalog icon on the toolbar.

4.Click the plus sign () to expand Brand Analysis.

Click the plus sign () to expand the Sales by Brand for Current Year Trends request. (Observe the listed views available for this request.)

The following table identifies the types of request views that are supported, and can be inserted (or copied and pasted) into Excel from the Presentation Catalog:

Table view

Enables you to select levels before insertion into Excel, allowing you to limit the data. You can add Excel formatting to the inserted view. Use this view to see data in the grouped format defined in the Answers request.

Pivot table view

Offers the features of the table view and maintains features defined in Answers (such as page item lists and group sectioning)

Chart view

Inserts charts as defined in Answers

List view (Insert as List)

Available for all table views. This inserts the entire data set for the original request. Use this view when you want to perform filtering, pivoting, charting, and other manipulations in Excel. (In the previous version of BI Office, this was available as flat data view).

Funnel View
Displays results as a three-dimensional chart that represents target and actual values by using volume, level, and color. It is useful for depicting target values that decline over time, such as a sales pipeline.

Gauge View
Shows results as gauges, such as dial, bar, and bulb-style gauges. (Funnel and gauge are two new views, which are introduced in BI Office 10.1.3.4.0.)

Compound view
Provides an assembly of different views on a dashboard. This view must be composed of supported view types (those listed above) to be fully inserted. If the compound view includes unsupported views, only those from this list of views will be pasted into Excel or PowerPoint.

Right-click Table and select Insert as List from the shortcut menu. The Edit Prompts and Levels dialog box appears. Accept the default options and click Insert.

Note: The Edit View option launches Answers in your browser, which in turn enables you to modify the request.

The entire data set for the original request appears in the worksheet area. (Note that the worksheet is named tableView). All Excel capabilities are available for use with this data.

5. Click any cell in the data area. Pivot icons () are added to each column heading. These pivot icons allow you to sort the data, or filter unwanted data (as shown in the screenshots below).

7. In the Presentation Catalog that is already open in the right pane, right-click Chart under the Sales by Brand for Current Year Trends request, and select Insert from the shortcut menu.

Note: The "Insert as Image" option enables you to add the chart as a static image to Excel.

The 3D Line chart from the request appears in the newly added worksheet.

8. Double-click the chart to view data associated with the chart values.

Setting Properties in the bioffice.xml Configuration File

This section describes the properties that you can configure in the bioffice.xml file.

Following is a sample bioffice.xml file:

<bioffice>
 <!-- log -->
 <!-- LogDir. Default is [O4CJ dir]\j2ee\home\applications\bioffice\bioffice\WEB-INF\log -->
  <!-- <property name="LogDir">D:\BIOffice\Server\log\</property> -->
  <!-- LogLevel Never = 1; Error = 2; Warning = 3; Msg = 4; Debug = 5; -->
  <property name="LogLevel" type="int">3</property>
  <!-- saw -->
 <property name="SawBaseURL">http://localhost/analytics/saw.dll</property>
  <!-- Does SAW use SSO (Single Sign-On): yes = 1; no = 0; -->
 <property name="SawUseSSO" type="int">0</property>
  <!-- Specifies the maximum number of rows to be returned by SAW executeXMLQuery or fetchNext method. -->
  <property name="SawMaxRowsPerPage" type="int">5000</property>
 <!-- Parse hyperlink column: turn on = 1; turn off = 0; -->
 <property name="SawParseHyperLink" type="int">1</property>
 <!-- Parse percentage column: turn on = 1; turn off = 0; -->
 <property name="SawParsePercentageColumn" type="int">1</property>
 <!-- Fetch fresh data: turn on = 1; turn off = 0; -->
  <!-- Turn on will make SAW re-submits the query to refresh data, in stead of reading data from cache. -->
 <property name="SawFetchFreshData" type="int">0</property>
</bioffice>

Setting Up an SSO-Enabled Oracle BI Presentation Server for BI Office

Oracle BI Office currently cannot be fully integrated with SSO. If your Oracle BI Enterprise Edition implementation is SSO-enabled, users can use their SSO credentials from the Microsoft Excel or PowerPoint add-in to log in to the Oracle BI Office system. However, the following options from the client add-in insert menu are not supported:

• Edit View

• Insert as Image
  

• Insert as Flash

To enable the integration of BI Office with SSO-enabled Oracle BI, you must deploy a separate SAW bridge application (analytics.ear) and grant trusted IP address (where BI Office is installed) access privilege to the new SAW bridge without going through SSO authentication. You must configure the BI Office server to send Web service requests to this SAW bridge that bypasses SSO. Depending on the SSO server type, the configuration may differ. Following is an example of how to integrate BI Office with Oracle SSO-enabled Oracle BI:

1. On the same machine where BI Presentation Services Plug-in has been deployed, deploy another Presentation Services Plug-in using the file analytics.ear:

   Locate analytics.ear in the directory OracleBI_HOME/web.

2. Name the new Plug-in "analyticsSOAP". Make the same modifications to the web.xml file for this analyticsSOAP servlet that were made to the web.xml file for the default "analytics" servlet.

3. Make the following modification to the file mod_osso.conf to open analyticsSOAP to requests from the BI Office server:

   (The mod_osso.conf is located in the directory Oracle_HOME/Apache/Apache/conf.)
   

   <Location /analyticsSOAP>
   Require valid-user
     AuthType Basic
     Allow from <IP address of BI Office server>
     Satisfy Any
   </Location>

   where
   

   <IP address of BI Office Server> is the IP address of the BI Office server, for example: 10.155.324.7
   

4. Locate the Office Server configuration file (bioffice.xml). The configuration file bioffice.xml can be found in the directory:

   OAS_HOME\j2ee\home\applications\bioffice\bioffice\WEB-INF
   


5. Update the following properties:
   
   
   • "SawBaseURL" to point to the new analyticsSOAP deployed in the previous steps
     
   • "SawUseSSO" to indicate that you have enabled SSO for BI Office and Presentation Services. Set the value to "1". This setting suppresses the unsupported options from the client add-in insert menu.
     

   Example:

   <property name="SawBaseURL">http://localhost:port/analyticsSOAP/saw.dll</property>
   <!-- Does SAW use SSO (Single Sign-On): yes = 1; no = 0; -->
   <property name="SawUseSSO" type="int">1</property>