Business Intelligence

Real Time Business Intelligence Architecture Strategies

Author: Stephen Goldsmith Date: June 20th, 2011
Categories: Business Intelligence

The traditional Business Intelligence architecture involves the use of a data warehouse which is populated on a scheduled basis (typically nightly) with data from one-to-many source systems. Either custom scripts or ETL tools such as Oracle Warehouse Builder, Oracle Data Integrator or Informatica are used to copy the data. As the speed of business has increased, hardware and databases costs have decreased and hardware and database performance have dramatically increased, organizations are now looking to use tools like Oracle Business Intelligence to provide real-time reports and dashboards. 

» Read More

Integration of MapViewer with Oracle BI 11g

Author: Stephen Goldsmith Date: June 9th, 2011
Categories: Business Intelligence

Oracle BI 11g includes a new mapping feature. This feature is a powerful tool that allows for map-based representation of data. This blog entry includes an overview of the steps required to install the MapViewer. Oracle BI 11g includes a new mapping feature. This feature is a powerful tool that allows for map-based representation of data. This blog entry includes an overview of the steps required to install the MapViewer.  

» Read More

Implementing Oracle Business Intelligence Applications (OBIA) in 45 Days

Author: Stephen Goldsmith Date: May 26th, 2011
Categories: Business Intelligence

Oracle Business Intelligence Applications (OBIA) dramatically reduce total cost of implementations through a pre-built data warehouse, ETL, meta data and reports/dashboards. 

» Read More

Oracle Business Intelligence Applications 7.9.6.3 for OBIEE 11g

Author: Stephen Goldsmith Date: May 18th, 2011
Categories: Business Intelligence

Oracle has just announced that the Oracle Business Intelligence Applications (OBIA) will be available on the 11g platform.  Oracle Business Intelligence Applications include pre-built dashboards, reports, metadata, data warehouse schemas and ETL solutions, dramatically reducing the time to implement and deploy a Business Intelligence solution.

According to the Oracle press release, OBIA 7.9.6.3 will enable organizations to take advantage of the 11g’s enhanced mobile features, user experience, systems management, performance, and scalability.  Oracle BI Applications Release 7.9.6.3 benefits include:

• Alerts, dashboards and reports are available for secure access and interaction via iPad and iPhone to provide organizational visibility, insight and alignment;

• A new user interface featuring a wide range of interactive charting and prompting capabilities, including map visualization options directly linked to spatial data, providing a richer, more intuitive end user experience;

• An enhanced Common Enterprise Information Model with 25 conformed dimensions that provides alignment across the enterprise as well as high performance, federated queries against hundreds of data sources in relational, OLAP, and XML formats; and,

• Integrated system management services to simplify system configuration, testing, deployment, monitoring, and installation of patches to enable continuous availability, with unmatched query performance, scalability, and security.

Example of OBIA 7.9.6.3

Going Mobile with Oracle BI

Author: Stephen Goldsmith Date: May 16th, 2011
Categories: Business Intelligence

Circa 2005, two managers show up to a meeting, each with a report in hand.  The meeting intended to focus on why sales have declined in the Western Region turns into a debate about whose numbers are correct. After an hour, nothing is resolved.

Turn the clock to 2011.  The two mangers, now executives with gray hair, again show up to a meeting to discuss why sales have declined in the Western Region.  Now with iPads loaded with Oracle Business Intelligence, the conversation is a refreshing change from the 2005 debate about the accuracy of the numbers.  Now the executives not only are able to see that sales are declining, but are also able to drill into the data to uncover that manufacturing backlogs from a specific plant are inhibiting sales performance.  A quick email to the plant manager begins a dialog on how to resolve the problem.

Is it a fad or is mobile computing here for the long term?  Either way, Oracle Business Intelligence on an iPad or iPhone puts information at your fingertips.  Not to mention, it is an excellent justification for that new iPad or iPhone you always wanted.

Here are just a few examples of the mobile experience…

Oracle Business Intelligence on the iPad

What’s old is new again: Leveraging Legacy Apps

Author: Tom Connolly Date: May 4th, 2011
Categories: Business Intelligence

The following is an excerpt from a live broadcast on DM Radio, http://www.information-management.com/dmradio/-10019266-1.html

–

As an Advisory and IT Services Provider, we see companies grapple with the pros and cons of extending the life of their legacy applications on a frequent basis.  At different times and for different reasons companies typically come to a decision to pursue one of the following strategies, 

1. Classic Data Warehousing – build a warehouse and move data in a batch process to create an integrated view of data across the enterprise allowing for enterprise-wide reporting and development of sophisticated performance analytic models. 
   • Packaged Analytics – the classic custom-built warehouse is giving way to pre-built or out-of-the-box warehouses and pre-packaged analytic models which offer companies a shorter time to implement at a reduced cost and also allow companies to take advantage of “best practice” analytics across well-known industry areas.
2. Distributed Enterprise Reporting – Customers develop an Enterprise reporting layer on top of existing applications, typically through direct access to the legacy databases via a tool that can access and present a unified view of data across the enterprise.  A new spin on the data warehousing scenario with two significant benefits – alleviates the need for development of potentially expensive ETL programs and also provides the ability for real-time data access effectively blurring the lines between operational and analytical reporting.
3. Data Integration – Many companies are extending legacy application via batch or real-time integration to allow for end-to-end business flows which can cross application silos (i.e. new employee in HR automatically triggers provisioning to corporate portal or automatic tie form marketing campaign to CRM for opportunity management).
   • Web Services – Technology is now enabling a much wider degree of integration with legacy applications within the enterprise but also with external trading partners. 
4. Virtualization – Companies are making better use of their investment in hardware through virtualization which now allows for clustering a pool of servers to get much better overall utilization and also provide a significant degree of fault tolerance on older hardware platforms. 
5. Outsourcing / Cloud Computing – Yes, there is even a cloud play for a discussion on legacy applications as companies look to move these applications to an outsourced model – leveraging infrastructure as a service or potentially even finding a provider who can take over the full application management service. 
6. Hybrid approach – upgrade or replace a subset of the legacy application footprint (perhaps implement a SAAS application for HR or CRM).  This may require some degree of integration with the legacy application(s) as well. 

 Of course, at some point it may become advantageous to take that final step and upgrade or replace their legacy applications leveraging a more contemporary application architecture.

Informatica vs. ODI for ETL

Author: Stephen Goldsmith Date: May 2nd, 2011
Categories: Business Intelligence, ETL

A key component to a Business Intelligence (BI) architecture is the Extract, Translate and Load (ETL) process.  The ETL process is typically scheduled on a nightly basis and is responsible for moving data from one or more source systems into a data warehouse.  In early data warehouses, ETL was typically performed by custom-developed programs and scripts.  Over the past twenty years, ETL development has matured into configurable tools.  Two common tools used with Oracle Business Intelligence include Informatica Power Center and Oracle Data Integrator (ODI).  While these tools have similar features, there are considerations that can be used to identify the correct tool for a particular Business Intelligence environment.

Informatica Power Center is developed by Informatica (www.informatica.com), yet it is resold through Oracle.  Informatica is a well known ETL product in the market and is a foundation component of Oracle Business Intelligence Applications (OBIA).  Thus, Informatica should be used for BI projects where OBIA is part of the solution.

As compared to ODI, there are more resources in the market with Informatica skills and the UI is faster and more user-friendly.  Unfortunately, Informatica is more costly than ODI and its server based licensing may by a burden to smaller BI projects and it ads another vendor to the mix. Also, Informatica often requires performance tuning for complex queries.

ODI is less popular that Informatica, yet it is ideal for situations when much complex logic is required for large volumes of data. As ODI is free from Oracle, it is a cost effective solution as well.  Also, it is installed on target databases so performance is faster than Informatica.  Limitations of ODI have to do with the use of knowledge modules for Loading, Integration, Journalize, Check and Reverse. Modification to these modules is time consuming. It is rumored that in the Fusion architecture ODI will be an optional ETL solution for OBIA.

In summary, there are a variety of options for ETL.  When using OBIEE, Informatica, ODI and Informatica are strong contenders. Consideration of functionality, budget and other factors will lead to the right selection for your organization

Blog Author: Dip Jadawala

Dip is a contributing blog author on the BI Practice team at BizTech.

OBIEE Metadata Upgrade (10g to 11g)- Part 1, RPD

Author: Stephen Goldsmith Date: April 26th, 2011
Categories: Business Intelligence, Metadata

Like most software upgrades, generally the newer version would provide some type of backward compatibility. Well, with the recently released Oracle Business Intelligence Enterprise Edition (OBIEE) 11g, you will not have the ability to do so. Due to the major enhancements of the OBIEE toolset, there is no way to seamlessly migrate our metadata from 10g to 11g. The road ahead may be a little gloomy for 10g but fear not, there is still some light down the tunnel. Fortunately, bundled with OBIEE 11g is an upgrade utility called “Oracle Fusion Middleware Upgrade Assistant.”  

The Upgrade Assistant provides option for RPD and Presentation Catalog upgrades. In Part 1, we will tackle the RPD metadata upgrade. To see how to upgrade your Presentation Catalog metadata, please jump to Part 2 of our OBIEE Metadata Upgrade.  

Now to the juicy part — The Upgrade Assistant utility only has 8 steps and is extremely user-friendly: 

1.  Upload your 10g RPD onto the Box where you have previously installed OBIEE 11g

2.  Now navigate to $OracleBI_HOME/bin and execute the “ua” utility

3.  This utility is available in both Windows and Linux

           – Check “Upgrade Oracle BI RPD and Presentation Catalog” and click “Next” 

4.    Since we are only upgrading our RPD, check “Upgrade Repository (RPD)” on the next screen  

 5.   Provide the Username/Password for Administrator (i.e. Administrator/Administrar  

6.  Starting OBIEE 11g, there is a new security feature: The RPD now has a password as well!!!! This security is partially to protect the RPD access, but largely to prevent overwriting the RPD on the server itself. Users without RPD password will not be able to push a new or updated copy of RPD to the BI Server. With that in mind, provide RPD password (i.e. SADMIN) and click “Next” 

 7.   In this version, weblogic is bundled with OBIEE, so generally it is installed on the same box, if not, specify the required connection details: 

8.   Review your upgrade configuration and complete your upgrade process 

9.   Pat yourself on the back; you have completed the OBIEE 10g to 11g upgrade. 

Wait! Don’t be too excited though, you are not done yet!
There are some issues with joins in the RPD’s physical and logical diagram.
Here’s the painful part: In order for you to fully complete this upgrade, all your joins may have to be recreated manually. 

Once you have recreated all joins, your RPD is fully upgraded and ready for many years to come.
Enjoy! 

 Blog Author: Alvin Vu 

Alvin is a contributing blog athor for the BI Practice at BizTech

Creating Time Hierarchy for Time Series Functions

Author: Stephen Goldsmith Date: April 21st, 2011
Categories: Business Intelligence, Metadata

Calculating time series functions in Oracle Business Intelligence (OBI) requires development of a time hierarchy.  This time hierarchy is necessary because while OBI can calculate the previous period it does not know the context of the period.

 The time hierarchy should be built using unique or specific keys at each level of the hierarchy so that OBI can determine the correct period for the time series function and a path to follow. 

The OBI calendar shown below can be used as a basis to understand the problem and the resolution.

In the calendar, 2008 has quarter 1 and 2009 has quarter 1 which are not year-specific. Thus, if the current period is Q2 2009, OBI does not know whether the previous period is CAL_QTR 1 of 2008 or CAL_QTR 1 of 2009.  A year-specific calendar key is required for OBI to navigate to the correct path. Accordingly,  PER_QTR_NAME should be used instead of CAL_QTR which would specifically indicate 2009 – Q1.

 The time hierarchy should be built with the Time Dimension enabled for the hierarchy and the detail level having the chronological key enabled.  The diagrams below show example hierarchies that can be employed.

As shown, each time category (i.e., Year, Half, Quarter, Month and Week) includes period name (PER_NAME_XXX) that specifies the period parent.  For example, in the case of a Quarter, the period name might be 2008-Q1.  With this approach OBI can use the PER_NAME to calculate the previous period.

Blog Author: Somya Kancheti
Somya is a contributing blog author on the BI team at BizTech

KPI Demystified Series

Author: Lee Tsao Date: April 21st, 2011
Categories: Business Intelligence, KPIs

Just about every for profit business organization generates it precious life blood (revenue) from sales, and the current SFA/CRM market is estimated by Gartner to exceed $US 9 Billion in annual spend.  However, a 2009 study revealed that only 39% of corporate executives believe their employees have the right tools and authority to solve SFA/CRM related client issues (source:  Strativity Group, Inc 2009).  So an astounding 61% do not feel they have the right tools in place.

We here at BizTech believe that having the right level of monitoring and measurement by deploying relatively simple sales related key performance indicators can help identify problems and enable corrective action thereby maximizing the value from SFA/CRM investments.

Over the next weeks we will showcase some Sales related KPIs that we feel are simple to measure, and provide high value back to the organization, so let’s start with some Top sales related KPIs.

KPI Name:  Average sales per customer or transaction.  

KPI Description:  Total sales for a given period divided by the number of customers or transactions for the same period.

Measure Frequency:  Monthly or Quarterly is suitable for most organizations

Causes for Variability:  If your average sales per transaction was $100K and now it’s $85K, it is an alert for your team to investigate if (1)  The sales price is being lowered by product marketing, (2)  Your sales team is giving away higher discounts due to product quality or competition, (3)  Your business is subject to seasonality and now entering a lower sales transaction season, or it could be many other reasons.

Why it’s important:  Measuring this trend helps you to determine the “Return” on your sales efforts.  Understanding what influences this KPI can help maximize your return on each dollar invested in Sales, Marketing, Promotions, and Customer retention initiatives.  

What to do next:  Ensure your SFA/CRM/ERP system is capturing sales by sales order and sales by customer over time.  Seasonal trends are well represented in horizontal line charts.  You can contact Lee Tsao (KPI Crusader) at ltsao@biztech.com and I am happy to explore this with you further.  Until next time, keep up the KPI crusade!