Oracle Business Intelligence Overview
Hi, Habr. At work, I have to deal with OBIEE (Oracle Business Intelligence Enterprise Edition) as a developer and administrator, so I decided to share the amount of knowledge that I had time to accumulate. I hope this will help someone in mastering the system. First I plan to talk about the system as a whole, then about the architecture and interaction of the components, and at the end to tell about the features that I had to face during my work.
Oracle Business Intelligence Enterprise Edition (OBIEE, OBI) is a software platform for solving business intelligence problems: interactive and published reports, KPI monitoring and business processes. It is a descendant of Siebel Analytics. The main functional parts available to users:
OBIEE, in fact, is an intermediary between data sources and users (user reports). The system does not store any data (with the exception of caches) - all reports are built on the fly by querying data sources (pictures from the OBI 11g Developer's Guide):
')
OBIEE supports many different data sources, including relational databases, OLAP systems, files, and apache hadoop. The system allows you to combine data from various sources in one report, combining them together according to specified rules.
In the center of the system is a single data model (also called a repository) - a description of the logical model of the business area and its binding to physical data sources. The model consists of three layers - presentation, business and physical. The business layer describes a logical model (which is understandable to end users), in the form of a multidimensional model — facts and measurements, and also describes the binding of logical attributes to physical sources. Presentation allows you to split the logical model into several subject areas and limit user access to various indicators and attributes. The physical layer contains a description of the data sources - tables, fields, keys, data cubes. Creating a data model (repository) is performed by the developer using a special program - the Oracle BI Administration Tool.
When a user opens a report, the presentation server (Presentation Server) generates a query in Logical SQL to the BI server. The BI server parses the query and translates it into queries to data sources in their “native” languages ​​- sql, mdx, etc. After receiving data from sources, the server combines them, performs various actions on the data (for example, calculates aggregates, if necessary), and returns the result to the presentation server. The presentation server, in turn, renders the data in the web interface or generates a static report.
The server part of OBIEE includes several separate components, some of which are controlled by the Weblogic application server, and the other part exists as normal (native) programs and is controlled by the oracle process manager (opmn) component. OBIEE also uses a relational database (Oracle, MS SQL, IBM DB2, or MySQL) to store part of the service data. The OBIEE domain is managed using the Weblogic Administration Console web interface and the Fusion Middleware Enterprise Management Console.
Suppose there is a need for business users to obtain analytical information about the status of any area of ​​the company’s activities (for example, about sales in a retail network of stores). How to meet user needs using OBIEE? The steps will be similar to the following (in this case, the interactive reports Answers):
Here I tried to describe some features of the system that caused the greatest frustration during development. The list, of course, is far from complete, but may be useful in planning the system architecture.
OBIEE also has mobile analytics tools (using mobile devices):
System overview
Oracle Business Intelligence Enterprise Edition (OBIEE, OBI) is a software platform for solving business intelligence problems: interactive and published reports, KPI monitoring and business processes. It is a descendant of Siebel Analytics. The main functional parts available to users:
- Answers (also called Interactive Dashboards) - build interactive reports that are available to users through the OBIEE web interface. The reporting unit is analysis - a simple report, as a rule, which includes one display (table, graph, chart, etc.). Analysis merges into information panels (Dashboards) that end users work with. Information panels can also include prompts — customization elements with which the user can interact with the panel.
- Publisher (also sometimes includes Delivers) is a tool for creating and sending out static reports. Since Because publisher has evolved from a separate product, it is possible to build reports on its own data model, which is independent of the OBI data model, but you can use the general model. It is also possible to send existing reports from the answers.
- The Action Framework is a set of tools for performing any automated actions from OBIEE — for example, sending reports on the achievement of a certain value by an indicator, a call to external web services, a call to java code, etc. Actions can be chained. It is possible to set up different channels for notifying users - e-mail, sms, pager, etc.
- Scorecard and Strategy Management - tools for tracking key performance parameters (KPI) and working with scorecards (Scorecard - http://en.wikipedia.org/wiki/Balanced_scorecard ). Used to visually display the status of the objectives (company, project). User access, as is the case with the answers, is done via the web interface.
- Marketing - Siebel Marketing integration tools.
- Office Tools - Microsoft Office integration tools.
Architecture
OBIEE, in fact, is an intermediary between data sources and users (user reports). The system does not store any data (with the exception of caches) - all reports are built on the fly by querying data sources (pictures from the OBI 11g Developer's Guide):
')
OBIEE supports many different data sources, including relational databases, OLAP systems, files, and apache hadoop. The system allows you to combine data from various sources in one report, combining them together according to specified rules.
In the center of the system is a single data model (also called a repository) - a description of the logical model of the business area and its binding to physical data sources. The model consists of three layers - presentation, business and physical. The business layer describes a logical model (which is understandable to end users), in the form of a multidimensional model — facts and measurements, and also describes the binding of logical attributes to physical sources. Presentation allows you to split the logical model into several subject areas and limit user access to various indicators and attributes. The physical layer contains a description of the data sources - tables, fields, keys, data cubes. Creating a data model (repository) is performed by the developer using a special program - the Oracle BI Administration Tool.
When a user opens a report, the presentation server (Presentation Server) generates a query in Logical SQL to the BI server. The BI server parses the query and translates it into queries to data sources in their “native” languages ​​- sql, mdx, etc. After receiving data from sources, the server combines them, performs various actions on the data (for example, calculates aggregates, if necessary), and returns the result to the presentation server. The presentation server, in turn, renders the data in the web interface or generates a static report.
The server part of OBIEE includes several separate components, some of which are controlled by the Weblogic application server, and the other part exists as normal (native) programs and is controlled by the oracle process manager (opmn) component. OBIEE also uses a relational database (Oracle, MS SQL, IBM DB2, or MySQL) to store part of the service data. The OBIEE domain is managed using the Weblogic Administration Console web interface and the Fusion Middleware Enterprise Management Console.
How is the work with the system
Suppose there is a need for business users to obtain analytical information about the status of any area of ​​the company’s activities (for example, about sales in a retail network of stores). How to meet user needs using OBIEE? The steps will be similar to the following (in this case, the interactive reports Answers):
- Identify available data for reporting
A business may have an already prepared and working data warehouse for receiving consistent and consolidated data. Then the task is simplified - we take data from the existing storefront and directly access it from OBIEE. Another thing is if there is no CD, and there are, for example, data from several separate systems. As a rule, such data are in a normalized form (3 NF or higher), and are very detailed, which is not good for reporting. In this case, it is necessary to design and build a data mart with a star or snowflake pattern (or several, depending on the complexity of the data). The showcase can be implemented in the form of tables or, for example, views (normal or materialized). Naturally, this requires a constantly working DBMS capable of withstanding a sufficiently large number of requests.
It is also possible that the data is available in the form of OLAP - then, as a rule, no modification is required, since this means that the multidimensional data model has already been built and is functioning. - Building a data model
Existing data needs to describe and associate logical attributes (for example, sales volume metrics) with physical attributes in a DBMS or OLAP server. Based on these data, a multidimensional model is built — a description of the data in terms of facts, dimensions, dimension attributes, and hierarchies. - Creating a repository
Now the developed data model must be transferred to the OBIEE repository. This is done in the Oracle BI Administration Tool (mentioned just above). Development takes place in three stages - the import of metadata sources, the construction of the business layer and the construction of the presentation layer. As a rule, there is only one data source, but there may be more complex scenarios, for example, with the combination of data from a relational DBMS and OLAP, or the combination of data with different levels of granularity from several DBMS. In these cases, the developer must also properly set up the "relationship" between sources. Building a business layer basically consists of transferring the attributes of the physical layer, describing hierarchies, choosing types of aggregations of metrics, and setting up sources for logical tables. The presentation level, as a rule, is a display of the “1 in 1” business layer, sometimes divided into separate areas (if, for example, you want to divide user access to data). It is also worth noting that OBIEE has some tools for joint development of repositories - it is possible to merge them from different versions, and the repository can be stored as a set of xml files, for the convenience of working with version control systems. - Report Development
After forming the repository and uploading it to the server, the main phase begins - the development of reports. First, separate analyzes are developed, then they are integrated into information panels. As a rule, each developer works on his own set of analyzes and dashboards, since there are no means for joint development (everything happens in the web interface), and at the same time editing one will erase the work of the other :)
Some personal experience
Here I tried to describe some features of the system that caused the greatest frustration during development. The list, of course, is far from complete, but may be useful in planning the system architecture.
- OBIEE does not always work correctly with snowflake patterns in the data model. This means that a valid SQL query from a report is not always generated. If possible, you need to translate such a scheme into a “star” at the business layer level. For example, if there is a “Client” table that refers to the “Client class” table (an individual, corporate client, etc.), then in the business layer they need to be combined into one “Client” logical table with a full set of attributes . The situation is complicated when there are links of fact tables across several dimension tables. In such cases, you need to monitor the correctness of the generation of requests.
- In OBIEE there is the possibility of compiling analyzes based on direct queries to the database. This requires changing the configuration file NQSconfig.INI. This feature often makes life easier if you need to implement clever display logic without unnecessarily complicating the data model. However, in this case, you need to remember what data business users should have access to and not to allow queries to the database for everyone.
- It is necessary to properly configure the caching of these tables. In the event that changes are planned in the data during the working day, which users should see in the reports, it is necessary either to disable caching, or manually (via WLST) to update the cache. It is also a good practice to “warm up” (seeding) the cache before the beginning of the working day of users, so that users can immediately use the full reports.
- It should be remembered that the functionality of information panels as a web application is severely limited. If serious interactivity of user interaction with the interface is required, it is better to look towards other BI tools, for example, the MS stack. All that can be obtained in OBIEE is the choice of filters, working with data in tables (sorting, column order, creating groups, adding totals, etc.) and so-called “master-detail” events — when the user can click on a cell in one table, and in the next graph or table, data is automatically filtered by the selected value in the cell. There is an action functional, but it is also very limited - there is only a transition by URL, a call to the REST method, and a transition to another information panel.
Mobile business analyst
OBIEE also has mobile analytics tools (using mobile devices):
- Oracle BI Mobile is an ios application that allows you to view dashboard and analysis content on a mobile device. The display is performed almost without changing the appearance of the reports, which is why everything looks a bit like 90-x :)
- Oracle BI Moblie App Designer - an application that is integrated into OBIEE, allows you to create HTML5 reports using a data model from OBIEE or Publisher. In fact, this is a web application generator - each report consists of several pages, with interactivity and transitions between them. The advantage of this solution is that applications will look the same in a full browser and on the device, as well as no need to install a separate application. The downside is that the data is not cached on the device; accordingly, it can only be used if there is an Internet connection. Mobile App Designer was released quite recently, and is not yet included in the main delivery of OBIEE.
Literature
Source: https://habr.com/ru/post/207926/
All Articles