OBIEE PLUS - Customizing User Interface

OBIEE lets you to customize the user interface. I’m going to demonstrate this customization and change the logo in the dashboard. The user interface files are located in the OracleBI installation folder. Path is “OracleBI\web\app\res”

First, I’m going to copy one of the existing folder. I used the copy of the s_Siebel77.

Then rename the new folder as “s_CustomStyle”. The folder name should start with “s_”.

You’ll find the banner file in the “s_CustomStyle”.

So I’m going to use paint.exe to modify the existing bg_Banner.gif file. After modifying this file, I restart the “Oracle BI Presentation Server” service.

Then I log in to the Presentation Server.

And edit the dashboard from the “Page Options”. In the dashboard properties I’m going to change “style” attribute to “CustomStyle” which is the name of the folder.

As a result, I can see the new logo in the dashboard.

OBIEE PLUS - Write Back Option

I’m going to explain the steps for enabling write back option in a request. It’s easy to implement this feature with OBIEE Plus.

First of all, I create a table named “Sales”. The output of the query is below:

Next step is to import the table definition into the repository of OBIEE. Important point is I disable the cache option of the physical table in the *.rpd file.

I add the physical table to the BMM layer and I don’t configure the default aggregation rule for the column “Dollars”. So aggregation rule is set to “None”. As it is below:

Then I add the logical table to presentation layer in order to let end users to access this table. I finish my task in the rpd file.

Now the next step will be creating a simple request in the “Answers” section.

Then I change column properties of “Dollars” and set the type property to “Write back” in the value interaction section.

Then I check the result set of the query by accessing the “Results” tab. I just enable the write back option.

After enabling write back option, I set the attributes of this feature:

And the last step in the Answers section is to save the request. So the view of the request is changed.

Now here is the important step. I create a xml file in the custom messages folder.

Of course the contents of the xml file is very important. I only want to update the existing row. So that’s why I only use “Update” statement in the XML file. This content of the file is case sensitive.

Then I restart Oracle BI Presentation Server.

And I can change the values in the tables by using OBIEE.

Milestones of Oracle (last 30 years)

1977 Larry Ellison, Bob Miner, and Ed Oates launch Software Development Laboratories, the predecessor of Oracle.

1978 Oracle Version 1, written in assembly language, runs on PDP-11 under RSX, in 128K of memory. Implementation separates Oracle code and user code. Oracle Version 1 is never officially released.

1979 Oracle Version 2, the first commercial SQL relational database management system (RDBMS), is released. The company changes its name to Relational Software Inc. (RSI).

1981 RSI begins developing tools for Oracle, including the Interactive Application Facility, a predecessor of Oracle Forms.

1982 RSI gets a new name—Oracle Systems—and hosts its first user conference, in San Francisco.

1983 Oracle Version 3, built on C, is the first RDBMS to run on mainframes, minicomputers, and PCs.

1984 Oracle Version 4, which supports read consistency, is released. Oracle ports Oracle Database to the PC platform. The MS-DOS version (4.1.4) of Oracle runs in only 512K of memory.

1985 Oracle releases Oracle Version 5, one of the first RDBMSs to operate in client/server environments.

1986 Oracle goes public on the NASDAQ exchange.

1987 Already the world's largest database company, Oracle launches an effort to build enterprise applications that take advantage of Oracle Database.

1988 Oracle Version 6 debuts with major advances: Row-level locking allows multiple users to work in the same table, by processing only the specific data used in a transaction. Hot backup reduces system maintenance overhead, by allowing employees to continue working in the system while administrators duplicate and archive data. PL/SQL enables users to process data while it remains in the database.

1989 Oracle moves into its world headquarters in Redwood Shores, California.

1990 The company launches Oracle Applications Release 8, which includes accounting programs designed for the emerging client/server computing environment.

1992 Oracle7 is released, with performance enhancements, administrative utilities, application development tools, security features, stored procedures, triggers, support for declarative referential integrity, and the PL/SQL procedural language embedded in the database.

1993 Oracle is the first software company to rewrite business applications for client/server environments, automating business processes from a centralized data center.

1994 Oracle earns the industry's first independent security evaluations, adding third-party assurance of the strength of Oracle's products.

1995 Oracle becomes the first major software company to announce a comprehensive internet strategy.

1996 Oracle delivers Universal Server, enabling customers to use Oracle Database to manage any type of data—text, video, maps, sound, or images.

1997 Oracle releases Oracle8.

1998 With Oracle8 Database and Oracle Applications 10.7, Oracle is the first enterprise computing company to embrace Java.

1999 Internet capabilities saturate every Oracle offering, from support for open standards technologies such as XML and Linux to the latest versions of Oracle product lines, such as Oracle Applications 11i and Oracle8i Database.

2000 Oracle ships Oracle E-Business Suite Release 11i, the industry's first integrated suite of enterprise applications.

2001 Oracle9i Database adds Oracle Real Application Clusters, giving customers the option to run their IT on connected, low-cost servers.

2002 Oracle launches the "Unbreakable" campaign to mark the unprecedented 15 independent security evaluations earned by Oracle Database.

2003 Oracle debuts Oracle Database 10g, the first grid computing product available for the enterprise. Oracle grid computing serves computing power across the enterprise as a utility, automatically shifting processing loads based on demand.

2004 Oracle provides a single customer view from multiple datasources with Oracle Customer Data Hub.

2005 Oracle completes the acquisition of PeopleSoft and announces its intention to acquire Siebel Systems.

2006 Oracle deepens a 30-year commitment to open standards computing with Oracle Unbreakable Linux—giving customers the same level of support for Linux that they receive for other Oracle products. The move in effect certifies the operating system for enterprise computing.

2007 Oracle launches five application product lines and acquires Hyperion Solutions, a provider of performance management software.

OBIEE PLUS

New comprehensive suite of enterprise BI products is released. It bundles Oracle Hyperion reporting products for integrated solution with Oracle Hyperion financial applications.

It has following components:

- Oracle BI Server: Common enterprise business model.

- Oracle BI Answers: Used for ad-hoc queries and reporting.

- Oracle BI Interactive Dashboards: Highly interactive dashboards for accessing reports.

- Oracle BI Delivers: Used for proactive monitoring and creating alerts.

- Oracle BI Disconnected Analytics: Full functional, used by mobile professionals.

- Oracle BI Publisher: Used for pixel perfect reports.

- Oracle BI Briefing Books: Snapshots of dashboard pages for offline users.

- Hyperion Interactive Reporting: Highly interactive ad-hoc reporting.

- Hyperion SQR Production Reporting: Quality formatted report generation.

- Hyperion Financial Reporting: Formatted financial and management reporting.

- Hyperion Web Analysis: Web based OLAP analysis, presentation and reporting.

BUSINESS INTELLIGENCE III

And now here is the important question: Which model will be used? Star Schema or Snowflake Schema. The answer depends on the requirements. The frequency that the end users access the data will be the important reason which will effect the design. If the columns that are stored in the dimension tables, will be accessed so often, then you better choose Star Schema model. But if the columns accessed very rarely, then snowflake schema will be the best solution for this requirement. As you see, a solution depends on the requirements.

So as a result, here is a description of a datawarehouse:

• Subject oriented: It gives information about a particular subject of a company.
• Integrated: Data stored in different sources integrated into one database.
• Time variant: Data is identified with a particular time period.
• Non volatile: Data is persistent, stable. New data is always appended to the datawarehouse.

This description was made around 15 years ago. So by the time passes, the requirements change, too. Now we called subject oriented data as Data Marts and when it comes to collection of Data Marts: They are datawarehouses. In addition data can be volatile. Because of the large sized databases, it’s not easy to store the last 20 years data. In general, a period of data is stored in the datawarehouses. What about the historical data? Of course, they are stored in historical archives.

After designing the datawarehouse, next step will be about how to load data into the tables. Of course, a free solution will be best. Writing the code for ETL. But this solution doesn’t work always so we better try to find a solution. Because let’s suppose that there are 2 OLTP sources. And 1000 tables on each server. What are we going to do? Create a script of all tables and then build a routine for loading the data into DW everyday? It will definitely take a long time. You won’t be able to finish the project cause maintenance will start to take developer’s time. That’s why we’ll need a customizable and functional tool. It’s Oracle Data Integrator (ODI). Next article will be about ODI.

ORACLE BUSINESS INTELLIGENCE II

The implementation is based on star schema model in the first figure. As you see, dimension tables are directly connected to fact table. So when a result set is needed dimension and fact tables will be both accessed.

The other option is called Snowflake Schema Model. You can see this model in the following figure. Actually this model is a extension of star schema model. There are some dimension tables that are not joined directly to fact table

ORACLE BUSINESS INTELLIGENCE I

Business Intelligence projects are very crucial for all kinds of companies at every scale. BI projects become one of the most important projects especially after any crisis,. Unfortunately there are many unsuccessful BI projects. I’m going to write several reasons about this in the coming articles.

So I’ll start from the first step which is designing the datawarehouses.

PHYSICAL ARCHITECTURE
Mostly you can find different kinds of data sources. One data source for one module or application. So the problem we’ll need to create reports based on these different technologies, different sources. These different sources should be consolidated in a location. In that point, we’ll need a datawarehouse. Of course consolidating of the data sources into one central database doesn’t mean that copying only the data exactly like they are originally stored.

As a result, we have to use another physical server to populate the datawarehouse. After creating the database, the next step is creating tables that we’ll use in the BI project. This design is the most important part of this project. All the business requirements (report requirements) must be defined. Analysis phase will be completed so we’ll be able to create these tables. Design of the tables will be based on Star Schema and/or Snowflake Schema.

We’ll have to create one fact table and one or more dimension tables in the database in order to implement Star/Snowflake Schema. Dimension tables will contain information about a specific business entity. Like product information. Product category, subcategory, manufacturer etc.. Fact tables stores the transaction details. There should be Foreign Key and measure columns in the fact table. Foreign keys will be referenced to primary key columns that are stored in Dimension Tables.