X

Recent Posts

Technologies behind Oracle Transactional Business Intelligence (OTBI)

Oracle Transactional Business Intelligence (OTBI) is one of the business intelligence solutions provided as part of Fusion Applications.  To build a real-time BI, the major challenge is to make sure that it can perform and has no or minimum interfere to the core objective of the transactional application, the online processing. This is the reason why we need Oracle Business Intelligence Applications (OBIA) for Fusion Applications.  The idea is to keep the minimal processing of detecting changes and capturing changes in the transactional system and leave everything else, such as, preparing and consolidating the data for reporting, to BI Applications. Here are some of the technologies available to make OTBI possible: 1. SQL Trimming from ADF ADF stands for Application Development Framework.  It is the application development framework used in developing Fusion Applications.  In general, it is a declarative metadata driven framework to let the application developers to define the data model, define the data access layer, define the UI rendering, put the validation logic and processing in the middle tier. The underlying data model, in most of cases, is still the relational model defined in the Fusion Apps transactional database under the 3rd NF design. The key enabling technologies provided from ADF to OTBI is the “Composite VO” or “Composite View Object”.  For me, it can generate the database SQL for us based on the metadata.  Unlike the database technology using the database view, ADF engine can look further down to the entity objects included in the view object and selectively choose which entities are needed in a given SQL.  If the view object includes two tables (EOs), one primary EO for data at the line level, and the other EO for getting the parent data, When the query (Composite VO) does not include any column from the parent EO, the SQL generated by ADF will not include the table in the join.  This is a superior technologies, comparing to the old technologies of building the business views. If you are a Java programmer and would like to get the feeling about what Composite View Object looks like and how it works, here is a good blog post: Do you know what is a Composite View Object? 2. BI Platform – ADFQuery to Composite VO This enabling technology is provided by BI platform and available as a Java library. It adds a layer on top of the ADF composite VO.  Without writing the Java code, it generates the codes of creating the composite VO on the fly.  It allows us to query the data from the ADF engine by sending them a XML block called ADFQuery. This doc shows some of the ADFQuery XML blocks. http://docs.oracle.com/cd/E15586_01/fusionapps.1111/e20836/adf.htm#BIEMG3435 To see better examples, you can find them in NQQuery.log files. It is a query language like SQL.  You have the section  for the column projection, the join criteria using view links, and the filter using view criteria. Here are other enabling technologies behind OTBI. 3. ADFQuery generation from BI Server 4. SQL By Pass Database 5. Relational to Dimensional Mapping (Physical Layer to Business Model Layer) 6. SELECT Physical in initialization block 7. ADFQuery Initialization block 8. Physical Lookup function from BI platform 9. Logical Lookup function from BI platform 10. Data Security enabled at the VO layer via Fusion AppCore 11. Applcore Tree Flattening 12. Applcore Business Intelligence Column Flatten VO (BICVO) 13. BI Flexfield VO generator 14. BI Extender via Import Wizard 15. BI View Object created based on the BI EE Logical SQL (BIJDBC) 16. Effective Date VO with as of date filter 17. ADF Application Module to BI variable interface and more… Regardless, the goal of these technologies is to enable the users to get the real time data access to the Fusion Apps.  There is really little or no much we can do for providing the feature like data snapshot, pre-built aggregate, multiple currencies, data consolidation and conformance, cross subject area analysis, and the most important, the query performance with complexity logic to be available in a reasonable time without the interfere to the transactional system.

Oracle Transactional Business Intelligence (OTBI) is one of the business intelligence solutions provided as part of Fusion Applications.  To build a real-time BI, the major challenge is to make...

OTBI vs. OBIA

Several people are curious about what are OTBI and OBIA, and what are the differences between OTBI and OBIA. I will discuss these in this article. - OTBI stands for Oracle Transactional Business Intelligence. - OBIA stands for Oracle Business Intelligence Applications. Let’s start with OBIA. OBIA is the pre-packaged BI Apps that Oracle has provided for several years. It is the data warehouse based solution. It is based on the universal data warehouse design with different prebuilt adapters that can connect to various source application to bring the data into the data warehouse. It allows you to conslidate the data from various sources and bring them together. It provides a library of metrics that help you measure your business. It also provides a set of predefined reports and dashboards. OBIA works for multiple sources, including E-Business Suite, PeopleSoft, JDE, SAP, and Fusion Applications. OTBI is different. First of all, it is a real time BI. There is no data warehouse or ETL process for OTBI. Second, it is for Fusion Apps only. OTBI is leveraging the advanced technologies from both BI platform and ADF to enable the online BI queries agains the Fusion Applications database directly. In addition, in some area, such as Financial, you can also connect to the Essbase cubes. Unlike OBIA, OTBI does not have a lot of prebuilt dashboards and reports. The reason is that for some advanced analysis, the data need to be prepared. You cannot get eveything you can get from the OBIA data warehouse in OTBI. Both OTBI and OBIA are available from the same metadata repository. Some of the repository objects are shared between OTBI and OBIA. It was designed to allow you have the following configurations: - OTBI only - OBIA only - OTBI and OBIA coexist If you implement Fusion Apps, you can enable OTBI. You can use the BI EE Answer to access the prebuild metadata and metrics those are built against the Fusion Apps. You may not get the full powerful prebuild dashboard and repost and prebuilt navigation workflow. However, you can start experiencing what the BI EE based reports look like. You can start bring the data out from your OLTP system. You can provide training to the users to get familar with the subject areas, some of which are shared with OBIA. If you enjoy OTBI and want to further get OBIA with a data warehouse based solution. You can implement OBIA later. Some of the OTBI reports maybe switched to run against OBIA. Some of OTBI reports can continue connecting to Fusion Apps directly. They can coexist in a single BI server and a single BI answer client. Both OTBI and OBIA are accessing Fusion Apps via the ADF. This is a more advanced topic.

Several people are curious about what are OTBI and OBIA, and what are the differences between OTBI and OBIA. I will discuss these in this article. - OTBI stands for Oracle Transactional...

DSS and BI

I found a very old book, called Decision Support Systems: An Organizational Perspective, in a library last weekend. It was written by Peter Keen,an author of several popular books, which help many business managersand users understand the value of information technology. His DSS bookdraw my attention because he is also the author of my textbook Network in Actions.The DSS book uses a very typical and conventional categorization system which puts the IT systems into three types:Transactional System, Structure Decision system, and Decision Support System.These categories are created based on the classification ofdecisions into structured, unstructured, and partially structureddecision. His focus is the 3rd category, DSS. Peter believes that a DSSshould assist in solving the semi-structured problems. A DSS shouldsupport, not replace, the managers.I feel that the above is a very good framework to view the role ofan analytics apps. A BI analytics application should be a DSS solution.However, BI analytics apps can do much more then just a decisionsupport system. BI may help the structured decision making.BI is not just a collection of reports. The design of a BI analyticsapps needs to consider what are the business decision need to make andwhat kind of information is helpful for making the decision.

I found a very old book, called Decision Support Systems: An Organizational Perspective, in a library last weekend. It was written by Peter Keen,an author of several popular books, which help many...

Data Warehouse Project Lifecycle

Here is the typical lifecycle for data warehouse deployment project:0. Project Scoping and PlanningProject Triangle - Scope, Time and Resource.Determine the scope of the project - what you would like toaccomplish? This can be defined by questions to be answered. The numberof logical star and number of the OLTP sourcesTime - What is the target date for the system to be available to the usersResource - What is our budget? What is the role and profile requirement of the resources needed to make this happen.1. RequirementWhat are the business questions? How does the answers of these questions can change the business decision or trigger actions.What are the role of the users? How often do they use the system?Do they do any interactive reporting or just view the defined reportsin guided navigation?How do you measure? What are the metrics?2. Front-End Design The front end design needs for both interactive analysis and the designed analytics workflow.How does the user interact with the system?What are their analysis process?3. Warehouse Schema DesignDimensional modeling - define the dimensions and fact and define the grain of each star schema.Define the physical schema - depending on the technology decision.If you use the relational tecknology, design the database tables4. OLTP to data warehouse mappingLogical mapping - table to table and column to column mapping. Also define the transformation rulesYou may need to perform OLTP data profiling. How often the data changes? What are the data distribution?ETL Design -include data staging and the detail ETL process flow.5. ImplementationCreate the warehouse and ETL staging schemaDevelop the ETL programsCreate the logical to physical mapping in the repositoryBuild the end user dashboard and reports6. DeploymentInstall the Analytics reporting and the ETL tools.Specific Setup and Configuration for OLTP, ETL, and data warehouse.Sizing of the system and databasePerformance Tuning and Optimization7. Management and Maintenance of the system Ongoing support of the end-users, including security, training, and enhancing the system.You need to monitor the growth of the data.

Here is the typical lifecycle for data warehouse deployment project: 0. Project Scoping and Planning Project Triangle - Scope, Time and Resource. Determine the scope of the project - what you would...

Customer Dimension and CRM related Analytics

Why is the customer dimension so important?A well-maintained, well-deployed conforming customer dimension is the cornerstone of sound customer centric analysisWhat are special characteristics of the customer dimension?Customer dimension is extremely deep. It may have million rows.Customer dimension is extremely wide. It has hundreds of attributes.Customer dimension changes often. It is not really a slowlychanging dimension. It is sometimes called as rapidly changing monsterdimension.Keeping the historical images of the customer data is important.The data in the customer dimension come from multiple sources.Matching, de-dup, standardization among different sources is criticalfor successfully conforming the dimension.The customer dimension with data may be enriched from the external data.Customer data is the source for other dimensions - geography, industry, lines of business, etc.You have to comply with the privacy regulation.The data from external source may be only licensed for a period oftime. The external data need to be associated with your own internaldata for analysis for special purposes and the data need to be removedfrom your database.The customer dimension itself is actually a source of fact tables.For example, # of customer living in California with the breakdown byage groups.

Why is the customer dimension so important? A well-maintained, well-deployed conforming customer dimension is the cornerstone of sound customer centric analysis What are special characteristics of the...

Oracle's 11g Launch Impresses - Intelligent Enterprise

The Intelligence Enterprise's weblog has a new article about Oracle 11g.  One thing worth highlighted is the feature he feel impressed about on the advancement of OLAP."Materialized views are a technique forspeeding multidimensional queries, such as those exploring sales acrossregions, products and customers. However, as the number of materializedviews mounts along with query volumes and complexity, managing thoseviews becomes difficult. In 11g, Oracle is using an OLAP cube to storeup to millions of materialized views so they can be managed moreefficiently."One of the problems of the old materialized view is that  you haveto know the level you are going to reported by and create thematerialized view accordingly.  The benefit of using an OLAP engine isthat the aggregation can be available at many different levels andyoudo not need to created multiple specific materialized views for storingthe summaries.   However, the OLAP cubes was stored in a differenttechnology and populated in different languages.    You need to writecodes to map the data from your source system or warehouse to thecubes.  The process needs to be run periodically in order to have therefreshed data from OLAP.  Also, querying the data from OLAP requires adifferent programming interface, unless you create theOLAP-to-Relational mapping view.The new feature of 11g seems combining the best from both world and solve all the problems!

The Intelligence Enterprise's weblog has a new article about Oracle 11g.  One thing worth highlighted is the feature he feel impressed about on the advancement of OLAP. "Materialized views are a...

Oracle BI Applications and Embedded BI, Part 1

This is my first article on blogs.oracle.com.  I want to choose a topic related to what I learned lately.  This is the 4th month I joined the Oracle BI applications development.  The data warehouse and BI architecture is not new to me any more. Oracle BI Applications is build on top of the platform from the Oracle BI Enterprise Edition.  The dashboard and report components can be seamlessly embedded into Oracle Applications, like how it is integrated with Siebel application.  However, it is also a very typical data warehouse architecture.  Sometime, people feel that only those BI application built directly on top of the OLTP schema can be embedded into the OLTP applications and provide the real time BI.  Many also believe that only the people build the OLTP application can understand how the BI is used in the business flow.  In fact several years ago, I have both of these views. After I worked for Oracle BI Applications for a couple of months and get deeper into it, now I think that both views are wrong.Embedding the BI into operational flow and empower everyone in the enterprise is absolutely needed.  However, the data stored in the typical OLTP schema is still need to be summarized, computed, aggregated to become meaningful business metrics.  All these processes require computer resources including the processing power and disk space.  The space growth pattern is very different from the space management for OLTP.  A data warehouse requires you to hold the historical data which can grow very fast while the data is not frequently updated.  Unlike the typical OLTP process, which typically include individual transactions and each of them add or update records in several tables, the data warehouse process involves much more data in each run and the batch process is required.  You do not want the transactional system's performance to get impacted.A key driver is that a typically enterprise system is running ahetergeneous environment.  The data can actually sitting in manydifferent places. The embedded BI solution directly based on top of the OLTP tables assumes only a single OLTP application to be used.  It is typically not the case.  The consumer apps of the "intelligence" data is very likely a different apps from the provider apps.   Even worse case is that the intelligence data can only be meaningful and valuable when you combine data from multiple systems!(TBD)

This is my first article on blogs.oracle.com.  I want to choose a topic related to what I learned lately.  This is the 4th month I joined the Oracle BI applications development.  The data warehouse...