Differences Between Siebel Analytics & OBIEE

 Oracle BI Answers-Based Metadata Dictionary Feature:

This feature enables administrators to analyze metadata repository statistics in Oracle BI Answers. The OBIEE server can generate metadata dictionary which describes the metrics contained in a rep and the attribs of rep objects. Its an XML doc.

In OBIEE this can be accessed directly from BI Answers selection pane where specific metadata information will be shown to guide report construction. Once this BI Answers based functionality is been enabled an icon will appear next to each Subject Area, Table name, and Column shown in the selection pane for that Subject Area. Clicking on the icon will open up a specific Metadata Dictionary page to show information for that element and links to related repository information
 

Multi-Select Dashboard Prompts Features:

This feature enables users to analyze data more easily with an easier to use method. Prior to this, a multi-select dashboard prompt with a large number of values could become difficult to work with.
A new search feature adds the ability to better navigate and select values from a multi-select prompt when there are many values. Modifications to the existing multi-select dashboard prompt now allow the user to search through the available values using four matching schemes: begins with, ends with, contains, and is Like (Pattern Match). Wildcards like % can also be used in the query.

A more sophisticated paging mechanism is also now present which allows the user to page ahead according to a configured search set size as needed. Some other features are also added in multi select prompts like copy paste of values and selection of multiple value use shift key.

Integration with BI Dashboard Feature:

2 new Interactive Dashboard features have been added to provide additional interactivity with Microsoft Office.
1) Download to PowerPoint:

This new link is found within the “Report Links” Download menu and will download a static version of that report to PowerPoint. The Oracle BI Office Add-in does not need to be installed to use this functionality.

2) Paste reports directly into MS Office applications:
This functionality is also made available along with the other Dashboard Report Links configured using the Dashboard Editor. A new “Copy” link can be added to a report that will copy the XML definition of the current report to the Windows clipboard. 

Pasting this link using the Paste function found in the Oracle BI menu will convert the copied XML into an Office version of that report. This functionality will only work where the Oracle BI Office Add-in has been installed. Once pasted, this Dashboard report will then be translated into native Office objects that can be subsequently refreshed with live data at any point.

Import Oracle Catalog Feature:

Enables Admins to import Database objects through a native Oracle OCI connection. The OBI Admin Tool supports importing of physical Database objects through a native Oracle OCI connection and reuses the same OCI connection for queries. This feature mitigates the need to setup an ODBC connection to an Oracle Database purely for importing metadata objects into the Admin Tool.

Embedded Database Functions Feature:

Enables users & Admins to create more powerful reports by directly calling Database functions from either Oracle BI Answers or by using a Logical column (in the Logical Table source) within the Metadata (repository). Eg: Evaluate function etc.

Presentation Variable:

A new variable has been introduced which help accepting the dynamic values from the user in Oracle BI Answers. presentation variable must be declared in a dashboard prompt (using the Set Variable field), and its name and value are determined by the user.
format : @{variables.variable_name}

Time series Functions:

The Time series wizard has been removed from OBIEE and some new functions called Time Series functions have been introduced

Aggrigation Functions

The following list describes the aggregation rules that are available for measure columns in the "Layout pane" and for columns in the "Edit Column Formula dialog: Column Formula tab". The list also includes functions that you can use when creating a calculated item.

Default — Applies the default aggregation rule as in the Oracle BI repository or by the original author of the analysis. Not available for calculated items.
Server Determined — Applies the aggregation rule that is determined by the Oracle BI Server (such as the rule that is defined in the Oracle BI repository). The aggregation is performed within Presentation Services for simple rules such as Sum, Min, and Max. Not available for measure columns in the Layout pane or for calculated items.
Sum — Calculates the sum obtained by adding up all values in the result set. Use this for items that have numeric values.
Min — Calculates the minimum value (lowest numeric value) of the rows in the result set. Use this for items that have numeric values.
Max — Calculates the maximum value (highest numeric value) of the rows in the result set. Use this for items that have numeric values.
Average — Calculates the average (mean) value of an item in the result set. Use this for items that have numeric values. Averages on tables and pivot tables are rounded to the nearest whole number.
First — In the result set, selects the first occurrence of the item for measures. For calculated items, selects the first member according to the display in the Selected list. Not available in the Edit Column Formula dialog.
Last — In the result set, selects the last occurrence of the item. For calculated items, selects the last member according to the display in the Selected list. Not available in the Edit Column Formula dialog.
Count — Calculates the number of rows in the result set that have a nonnull value for the item. The item is typically a column name, in which case the number of rows with nonnull values for that column are returned.
Count Distinct — Adds distinct processing to the Count function, which means that each distinct occurrence of the item is counted only once.
None — Applies no aggregation. Not available for calculated items.
Server Complex Aggregate — Applies the aggregation rule that is determined by the Oracle BI Server (such as the rule that is defined in the Oracle BI repository). The aggregation is performed by the Oracle BI Server, rather than within Presentation Services. Not available for calculated items.
Report-Based Total (when applicable) — If not selected, specifies that the Oracle BI Server should calculate the total based on the entire result set, before applying any filters to the measures. Not available in the Edit Column Formula dialog or for calculated items. Only available for attribute columns.

Repository Tunning In OBIEE10G

                           Oracle Business Intelligence Repository Tuning

Here I disuses The following are the few things:

Designing the OLAP data model -> Tuning of the database -> Design of the repository

1. The most important thing is obviously the data model. Typical OLAP systems are mostly De-normalized, preferably star schema.
2. The next important thing is that your database should be tuned to OLAP requirements.
3. The design has to take care of the loading mechanisms because a typical system can be optimized for a set of requirements. A system optimized for running huge analytical queries will not be good for the loading process and sufficient care has to be taken while designing the ETL process.
4. Proper load calculations should be done from time to time to take care of the increase in load as the system mature.
5. Bench marking the system at a particular point is required so that the same can be compared during problematic time.

The 3 steps mentioned in the above ladder are the base of an efficient system. Apart from the above steps a constant monitoring and change of strategy is required to meet the increasing requirements and load shift. Below ladder briefly mentions the important steps for this

Performing the DBA activities and scheduling it to benefit system performance -> Tuning of the repository -> Load distribution and planning -> Presentation server reporting strategy

We are trying to talk broadly about the repository tuning here.The following are the topics which are worth considering

1. Fragmentation:

Fragmentation is a process in which we advice the BI server to pick a particular table to satisfy a specific type of request. Aggregate tables are a subset of fragmentation where we advice the BI server to use a set of tables.

2. Aggregation:

We can either configure aggregation tables separately or can use the aggregation persistence wizard of OBIEE which would create scripts for the creation of aggregate tables and configure them in the repository. Aggregation is an extended functionality of caching.

3. Cache management

Cache can be managed at both the presentation server level and the BI server level. An intelligent cache management strategy can have serious impact on system performance.
BI server query cache: BI server saves the query result set on the disk and fulfills the request from this result set. This result might become stale with updates on the database and hence it is important to sync the cache management strategy with the load of OLAP database from ETL processes.
Various mechanisms like event pooling tables, disabling the cache for physical tables, manually removing the cache entries and using ODBC-extension functions (which provide a lot of liberty for programming cache management) can be used for cache management of BI server.
Cache seeding is an important mechanism to load the cache with the result set so that subsequent queries will hit the cache instead of the database. It is important to note that if a cache hit will happen if the subsequent query requires a subset of the query in the cache.
So it is an intelligent step to seed the cache with no filters selected. Delivers are one of the better options to seed cache because they can be scheduled at off peak hours.
Apart from the query cache in the BI server, there is a html cache in the presentation server. This cache also holds the charts that are generated in the result. These are sufficient parameters that can be configured in instanceconfig.xml file for this cache

4. Strategy to use the connection pools:

Conn pools reduce the overhead of connecting to the database for every new request and also allow multiple concurrent requests
We must calculate the sessions that we will allow for a particular connection pool because if this threshold is reached and other connection pools to the same data source are not available then the users will have to wait for getting their requests handled.
Again if this is set at a very high value then it might overload the underlying database and also of the BI server.
Since the time required for logging in the application is the most important metric to judge an application’s performance and since many initialization blocks are used during logging in so it is always advisable to have a separate connection pool for all the initialization blocks.

 We can use the permissions tab to streamline the users who would use a particular connection pool. This feature can be used to dedicate a connection pool to privileged users


Again persist connection pool property can be used to perform generalized subquery which can reduce the data movement between BI server and database. Again there is more than 1 thing to consider before using this property

5. Exchanging metadata with database

Oracle database summary manager can be used to create materialized views and index recommendations on optimizing performance A utility called SAMetaExport is used for this purpose. It takes an input file containing information like physical database, business model etc and generates a sql file with info on different business models (This file is generated in case of Oracle Database Metadata Generator). We can specify that we want to use Oracle Database Metadata Generator while executing SAMetaExport utility.

6. Using the session variable:

Session variables can be fed with values or an array of values if the values will remain the same throughout the session and if it is not affecting the login time. This session variables could then be used for various purposes in the reports.

7. Using complex join in the physical layer

If we do complex join in the physical layer then there is a high probability of unnecessary tables in the final query fired on the database. So we should avoid this practice

What is OBIEE

What is Oracle Business Intelligence Enterprise Edition:

Oracle Business Intelligence Suite Enterprise Edition (OBIEE) is a comprehensive suite of enterprise BI products that delivers a full range of analysis and reporting capabilities. Featuring a unified, highly scalable, modern architecture, Oracle BI EE Plus provides intelligence and analytics from data spanning enterprise sources and applications empowering the largest communities with complete and relevant insight.

Oracle BI EE Plus also bundles key Oracle Hyperion reporting products for integrated reporting with Oracle Hyperion financial applications. (see OBIEE plus.)
Oracle Business Intelligence System includes:

• Comprehensive BI functionality built on a unified infrastructure includes interactive dashboards, full ad hoc queries and analysis, proactive intelligence delivery and alerts, enterprise and financial reporting, online analytical processing (OLAP) analysis and presentation, high-volume production reporting, real-time predictive intelligence, disconnected analytics, as well as integration with Microsoft Office.
• Pervasive insight to everyone, everywhere provides relevant insight to everyone, not just analysts. All levels of the organization can see information optimized for their role.
• Unified business model built on an enterprise information model that unifies metadata across the Oracle BI tools and analytical applications for the lowest TCO.
• Hot-Pluggable BI infrastructure integrates with any data source, extraction, transformation and load (ETL) tool, major business application, application server, security infrastructure, portal technology, other front-end and analytical tools, and databases including IBM DB/2, Teradata, Microsoft SQL Server; SAP Business Information Warehouse (BW), Microsoft Analysis Services; flat files; XML data; and unstructured data.

The components of OBIEE include:

• Oracle BI Answers: Ad-hoc query and reporting
• Oracle BI Interactive Dashboards: Highly interactive dashboards for accessing business intelligence and applications content
• Oracle BI Delivers: Proactive business activity monitoring and alerting
• Oracle BI Disconnected Analytics: Full analytical functionality for the mobile professionals
• Oracle BI Briefing Books: Snapshots of dashboard pages to view and share in offline mode
• Oracle BI Administrator

What Is OBIEE Plus

The Oracle Business Intelligence Suite Enterprise Edition Plus (EE) is a comprehensive suite of enterprise BI products, delivering the full range of BI capabilities including interactive dashboards, full ad hoc, proactive intelligence and alerts, enterprise and financial reporting, real-time predictive intelligence, disconnected analytics, and more. In addition to providing the full gamut of BI functionality, the Oracle Business Intelligence Suite EE Plus platform is based on a proven, modern Web Services-Oriented Architecture that delivers true next-generation BI capabilities.

Oracle Business Intelligence Enterprise Edition Plus has the following components:

• Oracle BI Answers: Ad-hoc query and reporting
• Oracle BI Interactive Dashboards: Highly interactive dashboards for accessing business intelligence and applications content 
• Oracle BI Delivers: Proactive business activity monitoring and alerting
• Oracle BI Disconnected Analytics: Full analytical functionality for the mobile professionals
• Oracle BI Publisher (formerly known as XML Publisher): Enterprise reporting and distribution of "pixel-perfect" reports
• Oracle BI Briefing Books: Snapshots of dashboard pages to view and share in offline mode
• Hyperion Interactive Reporting: Intuitive and highly interactive ad-hoc reporting
• Hyperion SQR Production Reporting: High volume, presentation-quality formatted report generation
• Hyperion Financial Reporting: Formatted, book-quality financial and management reporting
• Hyperion Web Analysis: Web-based online analytical processing (OLAP) analysis, presentation, and reporting

Aggregate Navigation In OBIEE10g

To import the aggregate tables, perform the following steps:

1.	Return to the SH repository, which should still be open in online mode.
2.	Select File > Import > from Database.
3.	In the Select Data Source dialog, select the SH data source, enter SH as User Name and Password, and click OK.
4.	In the Import dialog box, expand the SH schema and select the three aggregate tables:AGG_ST_CAT_DAY_SALES_F, AGG_CUSTOMER_STATE_D, and AGG_PRODUCTS_CATEGORY_D.
5.	Click Import.
6.	When the import process completes, close the Import dialog box.
7.	Expand the SH folder in the Physical layer and verify that the aggregate tables are added.
8.	Check in changes.
9.	Select the three aggregate tables in the Physical layer, right-click and select Update Row Count to verify connectivity. You should see the expected row counts after each table.
10.	Check in changes.
11.	Save the repository.

Back to Topic

To create physical joins for the aggregate tables, perform the following steps:

1.	In the Physical layer, use Ctrl+Click to select the three new aggregate tables and the Times table.
2.	Click the Physical Diagram icon on the toolbar.
3.	In the Physical Diagram, use the New Foreign Key button to create the following joins.  Click Yes when prompted to create matching table keys.: AGG_CUSTOMER_STATE_D.CUST_STATE_PROVINCE_ID = AGG_ST_CAT_DAY_SALES_F.CUST_STATE_PROVINCE_ID AGG_PRODUCTS_CATEGORY_D.PROD_CATEGORY_ID = AGG_ST_CAT_DAY_SALES_F.PROD_CATEGORY_ID TIMES.TIME_ID = AGG_ST_CAT_DAY_SALES_F.TIME_ID
4.	Close the Physical Diagram.
5.	Check in changes.
6.	Save the repository.

To map existing logical columns to aggregate columns and set aggregation content, perform the following steps:

1.	Map existing logical columns to new sources by dragging columns from the Physical layer to corresponding columns in the Business Model and Mapping layer: Logical Column Physical Column Sales Facts.Amount Sold AGG_ST_CAT_DAY_SALES_F.AMOUNT_SOLD Customers.Cust State Province AGG_CUSTOMER_STATE_D.CUST_STATE_PROVINCE Customers.Country AGG_CUSTOMER_STATE_D.COUNTRY_NAME Customers.Country Subregion AGG_CUSTOMER_STATE_D.COUNTRY_SUBREGION Customers.Country Region AGG_CUSTOMER_STATE_D.COUNTRY_REGION Customers.Country Total AGG_CUSTOMER_STATE_D.COUNTRY_TOTAL Products.Prod Category AGG_PRODUCTS_CATEGORY_D.PROD_CATEGORY Products.Prod Total AGG_PRODUCTS_CATEGORY_D.PROD_TOTAL
2.	In the Business Model and Mapping layer, expand Sales Facts > Sources and verify that a new logical table source, AGG_ST_CAT_DAY_SALES_F, is created.
3.	Double-click the AGG_ST_CAT_DAY_SALES_F logical table source to open the  Logical Table Source dialog box, click the Column Mapping tab, and verify the mapping.
4.	Click the Content tab.
5.	Use the drop-down menu in the Logical Level field to set the following logical levels: CustomersDim = State Province ProductsDim = Category TimesDim = Times Detail You are setting aggregation content for the fact table to the corresponding levels in the dimension hierarchies. In a subsequent step you set similar levels for the dimension table aggregate sources. Later, when a user queries against a particular level, Oracle BI Server will access the aggregate tables instead of the detail tables. For example, after setting aggregation content, if a user queries for Amount Sold by Country Region, the server will access the AGG_ST_CAT_DAY_SALES_F aggregate fact table and the corresponding aggregate dimension table, AGG_CUSTOMER_STATE_D. If a user queries for a level lower than the levels specified here, for example, Subregion or Country, then the server will access the detail tables. If a user queries for higher level, for example, Country Total, the aggregate tables will be used as well, because whenever a query is run against a logical level or above, the aggregate tables are used.
6.	Click OK to close the Logical Table Source dialog box.
7.	Expand Customers > Sources and verify that a new logical table source, AGG_CUSTOMER_STATE_D, is created.
8.	Expand Customers > Sources and double-click the AGG_CUSTOMER_STATE_D logical table source,  click theColumn Mapping tab, and verify the mappings.
9.	Click the Content tab.
10.	Use the drop-down menu in the Logical Level field to set the following logical level to specify  what level of detail is stored in the aggregate table: CustomersDim = State Province
11.	Click OK to close the Logical Table Source dialog box.
12.	Expand Products > Sources and verify that a new logical table source, AGG_PRODUCTS_CATEGORY_D, is created.
13.	Double-click the AGG_PRODUCTS_CATEGORY_D logical table source, click the Column Mapping tab, and verify the mappings.
14.	Click the Content tab.
15.	Use the drop-down menu in the Logical Level field to set the following logical level to specify what level of detail is stored in the aggregate table: ProductsDim = Category
16.	Click OK to close the Logical Table Source dialog box.
17.	Check in changes.
18.	Check consistency. Fix any errors before proceeding.
19.	Save the repository. Note that you did not need to make any changes to the Presentation layer. You made changes in the business model that impact how the queries are processed and which sources are accessed. Based on how you specified the aggregation content, Oracle BI Server automatically uses the new sources when appropriate.

To test in Answers and examine the query log, perform the following steps:

1.	Return to Answers.
2.	Reload Server Metadata.
3.	Create the following query: Customers.Country Region, Sales Facts.Amount Sold.
4.	Hold down the Ctrl key and click Times > Calendar > Calendar Year. The Create/Edit Filter dialog box opens.
5.	Create the filter Calendar Year is equal to / is in 2001.
6.	Click OK to close the Create/Edit Filter dialog box. The filter is added to the query.
7.	Click Results.
8.	Click Settings > Administration.
9.	Click Manage Sessions.
10.	Locate the last query run under Cursor Cache and click View Log.
11.	Examine the log and verify that the aggregate tables AGG_CUSTOMER_STATE_D andAGG_ST_CAT_DAY_SALES_F were accessed.
12.	Close the query log.
13.	Click Finished to close the Session Management screen.
14.	Click Close Window to close the Oracle BI Presentation Services screen.