Category: obiee

OBIEE – Data Warehouse ”; Previous Next In today’s competitive market, most successful companies respond quickly to market changes and opportunities. The requirement to respond quickly is by effective and efficient use of data and information. “Data Warehouse” is a central repository of data that is organized by category to support the organization’s decision makers. Once data is stored in a data warehouse, it can be accessed for analysis. The term “Data Warehouse” was first invented by Bill Inmon in 1990. According to him, “Data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management”s decision making process.” Ralph Kimball provided a definition of data warehouse based on its functionality. He said, “Data warehouse is a copy of transaction data specifically structured for query and analysis.” Data Warehouse (DW or DWH) is a system used for analysis of data and reporting purposes. They are repositories that saves data from one or more heterogeneous data sources. They store both current and historical data and are used for creating analytical reports. DW can be used to create interactive dashboards for the senior management. For example, analytic reports can contain data for quarterly comparisons or for annual comparison of sales report for a company. Data in DW comes from multiple operational systems like sales, human resource, marketing, warehouse management, etc. It contains historical data from different transaction systems but it can also include data from other sources. DW is used to separate data processing and analysis workload from transaction workload and enables to consolidate the data from several data sources. The Need for Data Warehouse For example − You have a home loan agency, where data comes from multiple SAP/non-SAP applications such as marketing, sales, ERP, HRM, etc. This data is extracted, transformed and loaded into DW. If you have to do quarterly/annual sales comparison of a product, you cannot use an operational database as this will hang the transaction system. This is where the need for using DW arises. Characteristics of a Data Warehouse Some of the key characteristics of DW are − It is used for reporting and data analysis. It provides a central repository with data integrated from one or more sources. It stores current and historical data. Data Warehouse vs. Transactional System Following are few differences between Data Warehouse and Operational Database (Transaction System) − Transactional system is designed for known workloads and transactions like updating a user record, searching a record, etc. However, DW transactions are more complex and present a general form of data. Transactional system contains the current data of an organization whereas DW normally contains historical data. Transactional system supports parallel processing of multiple transactions. Concurrency control and recovery mechanisms are required to maintain consistency of the database. Operational database query allows to read and modify operations (delete and update), while an OLAP query needs only read-only access of stored data (select statement). DW involves data cleaning, data integration, and data consolidations. DW has a three-layer architecture − Data Source Layer, Integration Layer, and Presentation Layer. The following diagram shows the common architecture of a Data Warehouse system. Types of Data Warehouse System Following are the types of DW system − Data Mart Online Analytical Processing (OLAP) Online Transaction Processing (OLTP) Predictive Analysis Data Mart Data Mart is the simplest form of DW and it normally focuses on a single functional area, such as sales, finance or marketing. Hence, data mart usually gets data only from few data sources. Sources could be an internal transaction system, a central data warehouse, or an external data source application. De-normalization is the norm for data modeling techniques in this system. Online Analytical Processing (OLAP) An OLAP system contains less number of transactions but involves complex calculations like use of Aggregations − Sum, Count, Average, etc. What is Aggregation? We save tables with aggregated data like yearly (1 row), quarterly (4 rows), monthly (12 rows) and now we want to compare data, like Yearly only 1 row will be processed. However, in an un-aggregated data, all the rows will be processed. OLAP system normally stores data in multidimensional schemas like Star Schema, Galaxy schemas (with Fact and Dimensional tables are joined in logical manner). In an OLAP system, response time to execute a query is an effectiveness measure. OLAP applications are widely used by Data Mining techniques to get data from OLAP systems. OLAP databases store aggregated historical data in multi-dimensional schemas. OLAP systems have data latency of a few hours as compared to Data Marts where latency is normally closer to few days. Online Transaction Processing (OLTP) An OLTP system is known for large number of short online transactions like insert, update, delete, etc. OLTP systems provide fast query processing and also responsible to provide data integrity in multi-access environment. For an OLTP systems, effectiveness is measured by the number of transactions processed per second. OLTP systems normally contain only current data. The schema used to store transactional databases is the entity model. Normalization is used for data modeling techniques in OLTP system. OLTP vs OLAP The following illustration shows the key differences between an OLTP and OLAP system. Indexes − In an OLTP system, there are only few indexes while in an OLAP system there are many indexes for performance optimization. Joins − In an OLTP system, large number of joins and data is normalized; however, in an OLAP system there are less joins and de-normalized. Aggregation − In an OLTP system, data is not aggregated while in an OLAP database more aggregations are used. Print Page Previous Next Advertisements ”;

OBIEE – Multiple Logical Table Sources ”; Previous Next When you drag and drop a column from a physical table that is not currently being used in your logical table in BMM layer, the physical table containing such column gets added as a new Logical Table Source (LTS). When in BMM layer, you use more than one table as source table, it is called multiple logical table sources. You can have a Fact table as multiple logical table sources when it uses different physical tables as source. Example Multiple LTS are used to convert Snowflakes schema to Star schemas in BMM layer. Let us say you have two dimensions − Dim_Emp and Dim_Dept and one fact table FCT_Attendance in the Physical layer. Here your Dim_Emp is normalized to Dim_Dept to implement Snowflakes schema. So in your Physical diagram, it would be like this − Dim_Dept<——Dim_Emp <——-FCT_Attendance When we move these table to the BMM layer, we will create a single dimension table Dim_Employee with 2 logical sources corresponding to Dim_Emp and Dim_Dept. In your BMM diagram − Dim_Employee <———–FCT_Attendance This is one approach where you can use concept of multiple LTS in BMM layer. Specifying Content When you use multiple physical tables as sources, you expand table sources in BMM diagram. It shows all multiple LTS from where it is picking up the data in BMM layer. To see table mapping in BMM layer, expand the sources under logical table in BMM layer. It will open Logical table source mapping dialogue box. You can check all tables which are mapped to provide data in logical table. Print Page Previous Next Advertisements ”;

OBIEE – Business Layer ”; Previous Next Business Layer defines the business or logical model of objects and their mapping between business model and Schema in the physical layer. It simplifies the Physical Schema and maps the user business requirement to physical tables. The business model and mapping layer of OBIEE system administration tool can contain one or more business model objects. A business model object defines the business model definitions and the mappings from logical to physical tables for the business model. The business model is used to simplify the schema structure and maps the users’ business requirement to physical data source. It involves creation of logical tables and columns in the business model. Each logical table can have one or more physical objects as sources. There are two categories of logical tables − fact and dimension. Logical fact tables contain the measures on which analysis is done and Logical dimension tables contain the information about measures and objects in Schema. While creating a new repository using OBIEE administration tool, once you define the physical layer, create joins and identify foreign keys. The next step is to create a business model and mapping BMM layer of the repository. Steps involved in defining Business Layer − Create a business model Examine logical joins Examine logical columns Examine logical table sources Rename logical table objects manually Rename logical table objects using the rename wizard and delete unnecessary logical object Creating measures (Aggregations) Create Business Layer in the Repository To create a business layer in the repository, right-click → New Business Model → Enter the name of Business Model and click OK. You can also add description of this Business Model if you want. Logical Tables and Objects in BMM Layer Logical tables in OBIEE repository exist in the Business Model and Mapping BMM layer. The business model diagram should contain at least two logical tables and you need to define relationships between them. Each logical table should have one or more logical columns and one or more logical table sources associated with it. You can also change the logical table name, reorder the objects in logical table and define logical joins using primary and foreign keys. Create Logical Tables Under BMM Layer There are two ways of creating logical tables/objects in BMM layer − First method is dragging physical tables to Business Model which is the fastest way of defining logical tables. When you drag the tables from the physical layer to BMM layer, it also preserves the joins and keys automatically. If you want you can change the joins and keys in logical tables, it doesn’t affect objects in the physical layer. Select physical tables/alias tables under the physical layer that you want to add to Business Model Layer and drag those table under BMM layer. These tables are known as logical tables and columns are called Logical objects in Business Model and Mapping Layer. Second method is to create a logical table manually. In the Business Model and Mapping layer, right-click the business model → Select New Object → Logical Table → Logical Table dialog box appears. Go to General tab → Enter name for the logical table → Type a description of the table → Click OK. Create Logical Columns Logical columns in BMM layer are automatically created when you drag tables from the physical layer to the business model layer. If the logical column is a primary key, this column is displayed with the key icon. If the column has an aggregation function, it is displayed with a sigma icon. You can also reorder logical columns in the Business Model and Mapping layer. Create a Logical Column In BMM layer, right-click on logical table → select New Object → Logical Column → Logical Column dialog box will appear, click General tab. Type a name for the logical column. The name of the business model and the logical table appear in the “Belongs to Table” field just below column name → click OK. You can also apply Aggregations on the logical columns. Click Aggregation tab → Select Aggregation rule from the dropdown list → Click OK. Once you apply Aggregate function on a column, logical column icon is changed to show Aggregation rule is applied. You can also move or copy logical column in tables − In the BMM layer, you can select multiple columns to move. In the Sources for moved columns dialog box, in the Action area, select an action. If you select Ignore, no logical source will be added in the Sources folder of the table. If you click on Create new, a copy of the logical source with the logical column will be created in the Sources folder. If you select Use existing option, from the drop-down list, you must select a logical source from the Sources folder of the table. Create Logical Complex Joins / Logical Foreign Keys Logical tables in BMM layer are joined to each other using logical joins. Cardinality is one of the key defining parameter in logical joins. Cardinality relation one-to-many means that each row in first logical dimension table there are 0, 1, many rows in second logical table. Conditions to Create Logical Joins Automatically When you drag all the tables of the physical layer to business model layer, logical joins are automatically created in Repository. This condition rarely happens only in case of simple business models. When logical joins are same as physical joins, they are automatically created. Logical joins in BMM layer are created in two ways − Business Model Diagram (already covered while designing repository) Joins Manager Logical joins in BMM layer cannot be specified using expressions or columns on which to create the join like in the physical layer where expressions and column names are shown on which physical joins are defined. Create Logical Joins/Logical Foreign keys Using Join Manager Tool First let us see how to create logical foreign keys using Join Manager. In the Administration Tool toolbar, go

OBIEE – Components ”; Previous Next OBIEE components are mainly divided into two types of components − Server Components Client Components Server components are responsible to run OBIEE system and client components interact with user to create reports and dashboards. Server Components Following are the server components − Oracle BI (OBIEE) Server Oracle Presentation Server Application Server Scheduler Cluster Controller Oracle BI Server This component is the heart of OBIEE system and is responsible to communicate with other components. It generates queries for report request and they are sent to database for execution. It is also responsible for managing repository components which are presented to the user for report generation, handles security mechanism, multi user environment, etc. OBIEE Presentation Server It takes the request from users via browser and passes all requests to OBIEE server. OBIEE Application Server OBIEE Application Server helps to work on client components and Oracle provides Oracle10g Application server with OBIEE suite. OBIEE Scheduler It is responsible to schedule jobs in OBIEE repository. When you create a repository, OBIEE also create a table inside the repository which saves all schedule-related information. This component is also mandatory to run agents in 11g. All jobs which are scheduled by the Scheduler can be monitored by the job manager. Client Components Following are some client components − Web-based OBIEE Client Following tools are provided in OBIEE web-based client − Interactive Dashboards Oracle Delivers BI Publisher BI Presentation Service Administrator Answers Disconnected Analytics MS Office Plugin Non-Web based Client In Non-Web based client, following are the key components − OBIEE Administration − It is used to build repositories and has three layers − Physical, Business, and Presentation. ODBC Client − It is used to connect to database and execute SQL commands. Print Page Previous Next Advertisements ”;

OBIEE – Schema ”; Previous Next Schema is a logical description of the entire database. It includes the name and description of records of all types including all associated data-items and aggregates. Much like a database, DW also requires to maintain a schema. Database uses relational model, while DW uses Star, Snowflake, and Fact Constellation schema (Galaxy schema). Star Schema In a Star Schema, there are multiple dimension tables in de-normalized form that are joined to only one fact table. These tables are joined in a logical manner to meet some business requirement for analysis purpose. These schemas are multidimensional structures which are used to create reports using BI reporting tools. Dimensions in Star schemas contain a set of attributes and Fact tables contain foreign keys for all dimensions and measurement values. In the above Star Schema, there is a fact table “Sales Fact” at the center and is joined to 4 dimension tables using primary keys. Dimension tables are not further normalized and this joining of tables is known as Star Schema in DW. Fact table also contains measure values − dollar_sold and units_sold. Snowflakes Schema In a Snowflakes Schema, there are multiple dimension tables in normalized form that are joined to only one fact table. These tables are joined in a logical manner to meet some business requirement for analysis purpose. Only difference between a Star and Snowflakes schema is that dimension tables are further normalized. The normalization splits up the data into additional tables. Due to normalization in the Snowflake schema, the data redundancy is reduced without losing any information and therefore it becomes easy to maintain and saves storage space. In above Snowflakes Schema example, Product and Customer table are further normalized to save storage space. Sometimes, it also provides performance optimization when you execute a query that requires processing of rows directly in normalized table so it doesn’t process rows in primary Dimension table and comes directly to Normalized table in Schema. Granularity Granularity in a table represents the level of information stored in the table. High granularity of data means that data is at or near the transaction level, which has more detail. Low granularity means that data has low level of information. A fact table is usually designed at a low level of granularity. This means that we need to find the lowest level of information that can be stored in a fact table. In date dimension, the granularity level could be year, month, quarter, period, week, and day. The process of defining granularity consists of two steps − Determining the dimensions that are to be included. Determining the location to place the hierarchy of each dimension of information. Slowly Changing Dimensions Slowly changing dimensions refer to changing value of an attribute over time. It is one of the common concepts in DW. Example Andy is an employee of XYZ Inc. He was first located in New York City in July 2015. Original entry in the employee lookup table has the following record − Employee ID 10001 Name Andy Location New York At a later date, he has relocated to LA, California. How should XYZ Inc. now modify its employee table to reflect this change? This is known as “Slowly Changing Dimension” concept. There are three ways to solve this type of problem − Solution 1 The new record replaces the original record. No trace of the old record exists. Slowly Changing Dimension, the new information simply overwrites the original information. In other words, no history is kept. Employee ID 10001 Name Andy Location LA, California Benefit − This is the easiest way to handle the Slowly Changing Dimension problem as there is no need to keep track of the old information. Disadvantage − All historical information is lost. Use − Solution 1 should be used when it is not required for DW to keep track of historical information. Solution 2 A new record is entered into the Employee dimension table. So the employee, Andy, is treated as two people. A new record is added to the table to represent the new information and both the original and new record will be present. The new record gets its own primary key as follows − Employee ID 10001 10002 Name Andy Andy Location New York LA, California Benefit − This method allows us to store all the historical information. Disadvantage − Size of the table grows faster. When the number of rows for the table is very high, space and performance of table can be a concern. Use − Solution 2 should be used when it is necessary for DW to keep historical data. Solution 3 The original record in Employee dimension is modified to reflect the change. There will be two columns to indicate the particular attribute, one indicates original value and other indicates the new value. There will also be a column that indicates when the current value becomes active. Employee ID Name Original Location New Location Date Moved 10001 Andy New York LA, California July 2015 Benefits − This does not increase the size of the table, since new information is updated. This allows us to keep historical information. Disadvantage − This method doesn’t keep all history when an attribute value is changed more than once. Use − Solution 3 should only be used when it is required for DW to keep information of historical changes. Normalization Normalization is the process of decomposing a table into less redundant smaller tables without losing any information. So Database normalization is the process of organizing the attributes and tables of a database to minimize data redundancy (duplicate data). Purpose of Normalization It is used to eliminate certain types of data (redundancy/ replication) to improve consistency. It provides maximum flexibility to meet future information needs by keeping tables corresponding to object types in their simplified forms. It produces a clearer and readable data model. Advantages Data integrity. Enhances data consistency. Reduces data redundancy and space required. Reduces update cost. Maximum flexibility

OBIEE – Architecture ”; Previous Next OBIEE Architecture involves various BI system components which are required to process the end user’s request. How OBIEE System Actually Works? The initial request from the end user is sent to the Presentation server. The Presentation server converts this request in logical SQL and forwards it to BI server component. BI server converts this into physical SQL and sends it to database to get the required result. This result is presented to the end user through the same way. The following diagram shows detailed OBIEE Architecture − OBIEE Architecture contains Java and non-Java components. Java components are Web Logic Server components and non-Java components are called Oracle BI system component. Web Logic Server This part of OBIEE system contains Admin Server and Managed Server. Admin server is responsible for managing the start and stop processes for Managed server. Managed Server comprises of BI Plugin, Security, Publisher, SOA, BI Office, etc. Node Manager Node Manager triggers the auto start, stop, restart activities and provides process management activities for Admin and Managed server. Oracle Process Manager and Notification Server (OPMN) OPMN is used to start and stop all components of BI system. It is managed and controlled by Fusion Middleware Controller. Oracle BI System Components These are non-Java components in an OBIEE system. Oracle BI Server This is the heart of Oracle BI system and is responsible for providing data and query access capabilities. BI Presentation Server It is responsible to present data from BI server to web clients which is requested by the end users. Scheduler This component provides scheduling capability in BI system and it has its own scheduler to schedule jobs in OBIEE system. Oracle BI Java Host This is responsible for enabling BI Presentation server to support various Java tasks for BI Scheduler, Publisher and graphs. BI Cluster Controller This is used for load balancing purposes to ensure that the load is evenly assigned to all BI server processes. Print Page Previous Next Advertisements ”;