The Oracle Professional’s role in facilitating Business Intelligence

Changes in hardware technology always precede changes in software technology, and Oracle is no exception.  As disk platter devices experience an unprecedented fall in cost, disk has become insanely cheap, with over a terabyte available for under $10,000.  As a direct result of this hardware priced change, Oracle DBA’s are being told to keep larger and larger amounts of historical data, and to start providing management with tools to analyze and leverage this critical business information.

All Oracle databases collect information about ongoing business processes and it’s only a matter of time before management starts to seek specialized information about their data.  Eventually, all Oracle DBA’s are faced with these requests for analysis of operational data, and these requests form the foundation of Oracle data warehousing and business intelligence.

As disk becomes insanely inexpensive, business managers are now choosing keep their operational data over years and use this valuable data to help them plan and execute business processes.  Let’s take a quick look at the stages of this evolution:

• Data collection – This phase involved the ETL (Extract, test and Load) processes, taking operational data and saving it for analysis.  The Oracle Data Warehouse Builder tool provides a framework for data extraction and loading of advanced data warehouse applications.

•  Summary and aggregation – Managers start the BU process by making requests for summaries and rollups of critical business metrics.

•  Predictive analytics – At the most advanced stage, management will ask the DBA to extract historical data and make predictions using regression techniques.

•  Hypothesis testing – As the managers begin to see the value of their data they will start requesting the capability to perform hypothesis testing. 

Let’s take a close look at these processes.

Oracle Summaries and aggregation

It begins when managers request counts and roll-ups of historical data, and they often like this data presented in tabular form, like you might see in an Excel spreadsheet pivot table.  To service these types of requests, Oracle DBA’s will start by automating the delivery of well-structured summary data:

• Vanilla SQL – DBA’s will run SQL to extract comma-delimited csv files, which can be read by Excel spreadsheets. 
  

• Excel-DB - More advanced users will utilize tools like Excel-DB which empower managers by allowing them to execute their SQL directly inside a spreadsheet, and the data appears instantly in the spreadsheet, whenever they desire it.
  

• SQL Analytics – Starting in Oracle 11g, we see advanced business intelligence operators such as the “pivot” command which allows standard SQL to present data as-if it was in a pivot table form.  Oracle ACE and Oracle Certified Master Laurent Schneider discusses these techniques in his great book “Advanced Oracle SQL Programming”.

For more Oracle business intelligence tools, see this link for a full list of Oracle business intelligence tools.

Oracle Predictive analytics

The highest value of Oracle data is the ability to examine past trends and predict the future.  Oracle DBA’s are already familiar with performing Oracle predictive modeling using STATSPACK or AWR data, and using tools like Ion for Oracle to forecast Oracle workload trends.   Winston Churchill said “The farther that you can see into the past, the farther you can see into the future.”  This applies to almost all operational Oracle data and Oracle professionals will deploy a variety of business intelligence tools for operational forecasting:

·         Oracle BI suite – Formerly Oracle Express, the BI suite provides a complete framework for statistical forecasting and data analysis,

·    SAS – The old standby statistical analysis package, SAS and built-in API’s for Oracle to allow for fast extraction of Oracle data.

Oracle Hypothesis testing

Hypothesis testing involves the scientific method of testing a theory about the behavior of Oracle historical data.  These “what if” scenarios allow managers to use operational Oracle data to create simulation of real-world events such as an advertizing campaign or a price change.  Oracle has the Oracle Data mining (ODM) tool for this purpose and Dr. Carolyn Hamm has a great book titled “Oracle Data Mining” that describes the process of data mining with Oracle technology.

In sum, sooner or later, all Oracle DBA’s will be asked to preserve and aggregate operational data for analysis and all Oracle professionals should become familiar with the common tools and techniques for Oracle business intelligence operations.

---