Oracle

Oracle’s Materialized View Defined

September 18, 2012/0 Comments/in Data Warehousing, Posts /by Adam Getz

A materialized view within an Oracle database is an object that extends the functionality of an ordinary view and contains the results of a query (SQL select statement) in a persistent or physical format. Materialized views can use either tables, views, and other materialized views as its data source and can be both indexed and/or refreshed.

Within an Oracle database, a view is a virtual table representing the result of a database query or select command with a table or another view as its source. Whenever a select query or an update addresses a view, the database converts these statements into select queries or updates against the source tables of the view’s select query. Thus a view is just a stored query or virtual table with no storage of physical data, and the physical data is stored in a table. However, a materialized view takes a different approach in which the query result is cached as a concrete table that can be refreshed from the source tables of the select query. This enables better query performance, at the cost of some data being potentially out-of-date.

Because a materialized view is manifested as a physical table, anything that can be performed to a real table can be done to the materialized view. With the most important thing that can be performed is the ability to build indexes on any field, enabling drastic speedups in query performance time. In a view, it’s typically only possible to utilize indexes on columns that come directly from (or have a mapping to) indexed columns in the source table. In addition, a materialized view can contain denormalized data that physically exists in multiple tables, a subset of fields and records that exist in physical tables, pre-calculation of table fields, and data pre-aggregated at summary levels.

Materialized views are most useful in analytical data repositories such data warehouses or data marts, where query performance is important and data refreshes and updates occur infrequently. Materialized views are one of the single most important database tuning tools that exist allowing you to pre-join complex table and views and pre-compute summaries for fast response time of queries.

Create Materialized View Syntax

Create Materialized View DeptCheckMV

Start with Sysdate

As

Select * From EmployeeTbl

Where Salary > (Select avg (salary) from EmployeeTbl

BI Market: Data Mining & Predictive Analytics (Vendors and Products) – 2011

July 22, 2011/1 Comment/in Business Intelligence, Posts /by Adam Getz

Data Mining & Predictive Analytic solutions provide the capabilities of analyzing large data sets in order to find patterns that can help to isolate key variables to build predictive models for management decision making. In addition, data mining applications help discover hidden patterns and relationships in data in order to effectively project and predict future results. In order to accomplish this goal, data mining application utilize statistics, algorithms, advanced mathematical techniques, and sophisticated data search capabilities. Moreover, these sophisticated tools provide answers to questions that may never have been asked and they are effectively able to determine relative amounts of correlation between data elements. Further, the predictive features of these data mining tools enable organizations to exploit useful patterns in data that may have otherwise been difficult to determine.

In 2011, the market leading vendors for data mining systems include: IBM SPSS, SAS, SAP Business Objects, Oracle, MicroStrategy, ThinkAnalytics, Pentaho, & Angoss.

BI Market: OLAP (Vendors and Products) – 2011

July 20, 2011/3 Comments/in Business Intelligence, Posts /by Adam Getz

On-line analytical processing (OLAP) solutions provide advanced techniques for rapidly visualizing and analyzing business metrics across different points of view. OLAP is a term used to generically refer to software and applications that provide users with the ability to store and access data in OLAP cubes (also called a ‘multidimensional cube’ or a hypercube) with this “cube” being made up of numeric facts, called measures, and text values, called dimensions.

Moreover, OLAP systems provide users with insight into past performance and they enable a deep understanding of the reasons behind why previous events have occurred. Fundamentally, OLAP systems allow users to rapidly view and analyze data from many perspectives or dimensions and allows the users to conduct sophisticated “What-If” analysis.

In 2011, the market leading vendors for OLAP systems include: SAP Business Objects, Oracle, IBM Cognos, MicroStrategy, Microsoft, SAS, Pentaho, & Jaspersoft.

BI Market: Dashboard Management (Vendors and Products) – 2011

July 20, 2011/0 Comments/in Business Intelligence, Posts /by Adam Getz

Dashboard management systems are intended to facilitate and support the information and decision-making needs of management by providing easy access to key business information in a highly graphical and intuitive format. Fundamentally, a dashboard is a graphical business tool that displays a set of KPIs (key performance indicators), metrics, and any other relevant information to a business user, manager, or key decision-maker in a single consolidated view and allows for organizational performance to be easily measured and monitored. Dashboard data is often displayed as aggregate information and contains data that consolidated from multiple data sources scattered throughout an organization. Dashboards are commonly interactive and provide users users the ability to drill into particular aspects of the display and/or rapidly switch between views of the data.

In 2011, the market leading vendors for dashboard management systems include: SAP Business Objects, Oracle, IBM Cognos, MicroStrategy, Microsoft, SAS, QlikTech, LogiXML, Corda, Dundas, iDashboards, Pentaho, & Jaspersoft.

BI Market: Ad-Hoc Query & Analysis (Vendors and Products) – 2011

July 19, 2011/0 Comments/in Business Intelligence, Posts /by Adam Getz

Query and analysis solutions enable business users and analysts to rapidly generate business queries and reports from enterprise data based upon business question of the data. Also known as “ad-hoc” querying, these tools typically provide intuitive, graphical interfaces that shields users from technical complexities and allows users to leverage business terminology instead of the more technical database names. The business focus of these tools allows nontechnical professionals to be comfortable with their data and allows them to quickly and efficiently satisfy their own information needsin real time with minimal assistance from system developers. Commonly, query and analysis environments include a middleware layer that converts database conventions into business nomenclature that is more intuitive and understanding to end-users. Moreover, query & analysis environments give users the capability to access and analyze data in a unique and personal manner. Utilized primarily by business users, query and analysis solutions provide an environment that enables interactive methods to query data, present data in an ad-hoc manner, and find information on an as-needed basis.

In 2011, the market leading vendors for ad-hoc query and analysis include: SAP Business Objects, Oracle, IBM Cognos, MicroStrategy, Microsoft, Information Builders (IBI), Pentaho, Jaspersoft, & SAS.

BI Market: Operational Reporting (Vendors and Products) – 2011

July 12, 2011/0 Comments/in Business Intelligence, Posts /by Adam Getz

Operational reports are pre-designed business reports that focus on listings of data at the detailed level with data presented within a highly-structured format. Also known as “canned reports”, operational reports enable organizations to present data in a logical format and are designed to support the day-to-day activities of an organization at the transaction level. They are typically developed by information technology (IT) departments and/or advanced report users that have a good understanding of reporting tools, business rules, and database concepts. In addition, operational reports can be scheduled, refreshed, and distributed on a regular basis. Fundamentally operational reports are used by people with the responsibility for improving their organization’s operations.

In 2011, the market leading vendors for operational reporting include: SAP Business Objects, Oracle, IBM, MicroStrategy, Microsoft, Information Builders (IBI), Pentaho, JasperSoft, Actuate, and LogiXML.

Components of Oracle BI Enterprise Edition (OBIEE) – Oracle Delivers

June 22, 2011/0 Comments/in Business Intelligence, Oracle BI, Posts /by Adam Getz

Oracle Delivers is a component of the Oracle Business Intelligence Enterprise Edition suite that provides activity monitoring and alerting that can reach users via multiple channels such as:

• Email

• Dashboards

• Mobile devices

Oracle Delivers includes a web-based self-service alert creation and subscription portal, and a workflow engine that allows for initiation and passing of contextual information to other alerts. Furthermore, it can dynamically determine recipients and personalized content to reach the right users at the right time with the right information.

In a nutshell, Oracle Delivers notifies business users with relevant reports and data when events occur. Notifications can be sent and received utilizing most common communication channels.

Differences in Oracle BI (Platform vs. Analytical Applications)

June 9, 2011/0 Comments/in Business Intelligence, Oracle BI, Posts /by Adam Getz

A key way to understand the differences between the Oracle BI Platform and Oracle BI Analytical Applications is to identify that the platform contains the environment and tools for custom building business intelligence solutions, while the analytical applications contain complete, pre-built solutions that can be rapidly configured. Oracle BI Analytical Applications utilize all of the tools within the Oracle BI Platform as well as several additional tools, and all of the components of the Oracle BI Platform are bundled within Oracle BI Analytical Applications. Moreover, within the Analytical Applications, Oracle has leveraged its experience in providing industry-specific business solutions to deliver comprehensive and fully-inclusive business intelligence solutions for numerous common business functions.

Another important concept in the differentiation of the Oracle BI Platform and Oracle BI Analytical Applications is the amount of content delivered in the metadata or middleware layer. In a nutshell, the metadata layer within Oracle BI creates a semantic model over an organization’s entire data set, consists of the information that characterizes data, and describes how the organizational data should be presented by the user interface of the business intelligence environment.

The Oracle BI Platform is delivered without any pre-existing metadata as it only contains the tools for developing metadata.

However, Oracle BI Analytical Applications come delivered with a rich set of metadata elements at all three layers (physical, logical, and presentation) and come delivered with the mappings between the levels already being developed and configured as well as physical connections already being defined.

Oracle Business Intelligence (BI) Analytical Applications

June 7, 2011/1 Comment/in Business Intelligence, Oracle BI, Posts /by Adam Getz

Rather than just being a platform or development environment, Oracle Business Intelligence (BI) Analytical Applications are fully inclusive business intelligence solutions that incorporate all of the key metrics, workflows, and business processes for a particular business function. Bundled within theses solutions are numerous pre-built components including:: • Dashboards; • Metrics; • Reports; • Drill-down paths; • Dimensional models; • Naming standards; • Database objects; • ETL routines; • Metadata; • Security

In addition, Oracle BI Analytical Applications contain universal adapters that allow for rapid integration and direct connections with leading commercial-off-the shelf (COTS) packages including SAP, Oracle E-Business Suite, PeopleSoft, and Siebel applications systems.

Oracle BI Analytical Applications come bundled with best practices and industry standards built-in. Additionally, they include all of the functionality required to conduct business intelligence for many common business functions including financials, human resources, sales, service, contact centers, marketing, supply chains, order management and fulfillment business areas.

Fundamentally, Oracle BI Analytical Applications are built upon the Oracle BI Platform and provide complete end-to-end, prebuilt business intelligence solutions that deliver intuitive, role-based intelligence to all members of an organization including senior executives, mid-level managers, and front-line employees. So rather than developing custom business intelligence solutions for each business area and function, the use of Oracle BI Analytical Applications allows an organization the ability to rapidly configure a ready-built solution utilizing the complete Oracle BI Platform.

Oracle BI Analytical Applications come bundled with two main additional pre-built back-end repositories:: • Business Analytics Warehouse; • ETL (Extract-Transform-Load) Repository

The Business Analytics Warehouse (BAW) is a completely pre-built data warehouse that physically contains all of necessary dimension and fact table needed for the business intelligence applications. The BAW is fully-compliant with the dimensional modeling methodology developed by Ralph Kimball and supports many advanced techniques including slowly changing dimensions, conformed dimensions, aggregate tables, hierarchy tables, and surrogate keys.

The ETL repository includes all of the routines for extracting of data to a staging area, transforming the data into a common format, the loading of date into data warehouse tables, changed data capture, and seeding data for common dimensions. In addition, the powerful ETL repository consist of two main components, Informatica which is the ETL engine that contains the data integration routines, and the DAC (Data Warehouse Application Console) which is the “ETL orchestration tool” that controls application configuration, execution & recovery, and monitoring.

Oracle Business Intelligence (BI) Platform

June 3, 2011/0 Comments/in Business Intelligence, Oracle BI, Posts /by Adam Getz

The Oracle Business Intelligence (BI) Platform is a set of functions and tools that incorporate a number of advanced business intelligence products. In addition to providing a broad set of functions and tools, the Oracle BI Platform is built upon a proven, modern web services-oriented architecture that provides the foundation of the construction of complete business intelligence solutions. In addition, the platform delivers a full range of capabilities related to business intelligence capabilities including…: • Dashboards; • Ad-hoc queries; • Alerts and notifications; • Operational reports; • Predictive intelligence; • Disconnected analytics; • Microsoft Office integration

Fundamentally, the Oracle BI Platform comes with the tools and functions for developing sophisticated custom business intelligence environments like the one pictured here …

At its core, the Oracle BI Platform is an innovative next-generation business intelligence environment that offers the software tools for building enterprise BI solutions from data spanning both Oracle and non-Oracle data sources and applications. The Oracle BI Platform has been meticulously designed to allow for pervasive use within an organization. This sophisticated environment includes a full range of easy-to-use and intuitive end-user tools such as interactive dashboards, advanced reporting and publishing, full ad-hoc analysis over the web, proactive detection and alerts, mobile analytics, Microsoft Office integration, web services and business process integration.

The Oracle BI platform provides transparent and seamless access to numerous heterogeneous data sources. This highly developed platform also allows for development of custom business intelligence implementations that access data from traditional relational databases, OLTP & ODS systems, data warehouses and data marts, flat files, and web services. Further in 2008, Oracle released the Oracle BI Enterprise Edition Plus Platform, which provides direct connections to Hyperion data sources including Essbase and its related financial performance management applications.

The Oracle BI Platform has been developed over several years and includes the most advanced features from the former NQuire and Siebel Analytics products recently acquired by Oracle Corporation.