Our Blog

Business Intelligence for Beginners

Author: Houston Neal
Jan 06, 2012

 

The business intelligence (BI) software market can be tough to navigate. Not only do organizations have to sort through hundreds of available products, they also have to make sense of the jargon, trends and other technical aspects of BI tools (e.g. “OLAP” or “in-memory processing”).

Getting a handle on these concepts early in your research process is critical. You’ll want to be literate when you discuss your next purchase with your management team, IT staff and eventually sales representatives.

Fortunately there are several online resources to help you get up to speed. For example, Software Advice, a website that reviews and compares business intelligence software vendors, recently put together an introduction guide to three BI tools: data warehouses, ETL tools and OLAP tools.

In their post, they explain the function that each tool performs, and why and when companies need to use them. They also discuss four prominent trends currently playing out in the market: predictive analytics, SaaS, “Big Data” and in-memory processing. To continue reading, visit “Business Intelligence 101 | A Beginner’s Guide to BI Software."

 


New read only functions in release 12.1.3

June 22, 2011

 

New read-only functions are now available in 12.1.3 for Help ' Diagnostics. To implement this functionality, set the "Hide Diagnostics menu entry" profile option to "No" at the appropriate level. Then assign one or more of the following functions to the menu associated with the responsibility where this functionality is needed.

 

 


OBIEE 10g/11g Performance Tuning Options

March 11, 2011

The intent of this article is to expose available optimization methods within OBIEE for the OBIEE Community, to then guide the search on the interested methods on the web and Forums, for more details for their deployments. The ultimate goal is to implement an optimized OBIEE environment for our clients and to make report results delivery an effortless activity. There are a number of methods that can be employed in an effort to optimize the BI system. In heavy reporting environments, performance is a major issue as the BI server often times may become over burdened with activities, pulling hundreds of thousands of rows of data mainly in reporting and calculated summary reports. As Oracle Developers and DBAs, we all have quite a few options to optimize the Database, whether it is Partitioning, Materialized Views, Indexing and others. At the same time, the BI Server within OBIEE offers quite a few options, which can work in conjunction with Database optimization techniques, as well as independent in nature. Documentation that is available unfortunately is not cohesive enough or organized in a fashion that is easily accessible. One will have to know what to look for before starting the search.

 

Content Mapping for LTS

Content Mapping Dimension levels within each Logical Table Source (LTS) controls which table to query by BI Server. For Aggregates and Summary Tables, the content mapping for the Dimension level can be set at a much higher level (depending on the grain of the Aggregate tables), and the transactional or a detailed table LTS can be set at a lower grain. Depending on the measure and the dimension column End-Users choose in the query, BI Server will pick either an Aggregate table or a Detail table. The Content mapping set for each of the LTS determines the table to be queried by the BI Server.

 

For example for a given Fact, a Detail level Fact$ can be at the Transaction Date level of a Date dimension, and the Aggregate table can be rolled up to the Month or Year level. If the end-user selects Year and the Fact$ from the Fact, BI Server chooses the Aggregate table in the query, therefore avoiding running the query through the detail records, and then rolling them up, therefore providing a significant performance gain. Content mapping will have to be configured at the Business Mapping layer (BMM). NQQuery.log or the Session log provides the query to determine the BI Server execution path.

 

Fragmentation Concept in OBIEE – History VS Current

The idea is to keep Historical and Transactional data together in one Fact table in BMM, allowing the BI Server to manage querying Historical or the Transactional table depending on end-user dimension filter selection. For example, Historical LTS and Transactional LTS are joined to a Period dimension, and the Fragmentation content is set to query GL_BALANCES table for Year > 1999 from the Period dimension as shown below. So, depending on the filter value the end-user selects in the Answers or the Dashboard query, BI Server will choose either GL_BALANCES or GL_BAL_HISTORY based on the Fragmentation content definition. The concept is similar to Database partitions, however the recommended approach is to continue with Database partitions, as indexes and such can be setup within each local partition, and are easier to manage. Fragmentation has issues as it expects including the common dimension (Period dimension) in the Answers query, otherwise it does a UNION ALL and therefore could be counter-productive. Fragmentation however could be a better option when the two data sources are different and are merged into one Fact in the BMM.

 

 

Caching

BI Server has the capability to Cache Answers queries and results set on the server in the form of file structures. However, the challenge is in clearing the Cache and replacing with ever changing data. Although there are several ways to refresh Cache, the recommended approach is to use the Event Polling Table concept within OBIEE to automate Cache Management. Event Polling Table can be used to track Dimension and Fact table refreshes, by inserting a record into the table for every Dimension or Fact data change (via ETL or a SQL script), and the polling frequency can be set in OBIEE as shown, so the BI Cache Manager reads the Event Polling Table, and refreshes Cache of those entries in the table when it polls at the set interval. We have learned from our experience that this is by far the most effective and efficient way to refresh Cache entries.

 

 

 

With that said, the first and foremost place to address performance options is at the Database level. Database must be tuned first to the best. BI Server optimization options discussed above compliment Database optimizations.

 

Consider the following when modeling the Database and the OBIEE rpd

  • Consider database partitions for tables with large data volume
  • Usage of bit-map indexes on low-cardinality Key columns of both Facts and Dimensions in a Star-Schema. BI Repository modeling to use joins between bit-mapped columns
  • Aggregates and Summary tables
  • Materialized Views as the LTS within rpd
  • OLAP cubes as LTS both with OBIEE 10g and 11g

Author: Naren Thota, Partner/BI Architect at Infosemantics, Inc., a premier Oracle EBS and Business Intelligence (Oracle, IBM) solutions provider.

 

Please reach out to the author at naren.thota@infosemantics.com to learn of configuration details or any questions on the above discussion.

 

 


OBIEE and BI Publisher

 

The purpose of this document is to outline the process to build/create BI publisher reports using OBIEE as data source that has been set up at my client place to deliver the reports in the form of PDF and CSV. Assuming readers of this document are familiar with integrating OBIEE with BI Publisher. Oracle BI Publisher Integration offers easy and flexible reports development for highly formatted reports, Scheduling and delivery , generating report outputs in a wide range of formats. After configuring Oracle BI Server Security Model under Security Configuration in BI Publisher by login as Admin user the next step would be adding Data Source. In order to add data source here are the steps as follows

 

1. Login as Admin user in Oracle BI Publisher, go to Admin -> Data Sources -> JDBC Connection - > Add Data Source and configure settings

 

 

Where Database Driver Class is BI_SERVER_SECURITY_DRIVER value in xmlp-server-config.xml file(OracleBI\xmlp\XMLP\Admin\Configuration).

 

And Connection String is BI_SERVER_SECURITY_URL value in xmlp-server-config.xml file. Here the value is jdbc:oraclebi://hostname:port number/

 

Create/Develop BI Publisher Report:

1: Data Template:- Data Template is simply the sql Query with XML tags included, which is one of the best functionality to generate simple to complex XML in very proficient and scalable manner. Sample Data Template shown below

 

 

2: Parameters:- Used parameters within Data template query to limit the output.

 

3: Layouts:- There are various out put formats available to view the data. Ex: PDF, CSV, HTML etc.

 

4: RTF & eText Templates:- Build RTF template using BI Publisher’s MS-Word ad-in feature and feed the XML data generated by Data template and upload to BI Publisher’s Report to get PDF output and the same way build the eText Template and upload to the report to get CSV format output as shown below screens.

 

 

 

Bursting: We used bursting feature at our client place to burst multiple plans using Bursting Query. With this feature we can split a single report based on a key in the report data and deliver the report based on a second key in the report data. For each and individual plans there are number of reports associated, data will be spitted by using two result sets SPLIT BY and DELIVER BY.

 

Where Split By element is the data element from the report file that you wish to split the report by

The Deliver By element is the data element from the report file by which to determine the delivery method. Example here is based on Plan_Dates, where Plan_Dates is data base column

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


There are few things which are considered for better performance:
  • Try to use JDBC/JNDI connection to go directly against BI Server to use OBIEE data source instead of using Answers Request as a data source. If Answers as data source needed then copy the code generated by answers request and paste it into query builder in BI Publisher
  • Include most of the simple to Complex logic within Data Template query only and try to limit logic in RTF templates
  • Advisable to set scalable mode property to ON while developing Data template to prevent exceptions due to high volume of data or out of memory.

  • <properties>
    <property name="scalable_mode" value="on" />
    </properties>

  • Use short element names to reduce XML file size that would be useful for large data sets. And Avoid empty tags in the XML as summations over fields that contain empty tags may fail with a ‘not a number’ errors.
  • Take advantage of scheduling to run reports simultaneously and disable AUTO RUN and RUN REPORT ONLINE options under BI Publisher report properties