Components of a Business Objects Universe

Nov 29, 2011 No Comments

Also known as a “semantic layer”, the Business Objects Universe is an intermediary layer between a database or data source and dependent reports, queries, or dashboards. Fundamentally, the universe enables the end-user of Business Objects to interact with data without having to understand the complexities of database logic or be familiar with where the physical data is stored. Moreover, the universe is built around common business terminology that describes the business environment and provides the end-user the ability to intuitively query and retrieve exactly the data that they need.

Fundamentally a Business Objects Universe is a file that contains:
•  Connection information to a database
•  Components that map to structures in the database
•  A structure and layout of classes, objects, tables, views, joins, and contexts.

 

Universe Component:  Class

Groupings or categories of objects within a universe are called classes.  The primary function of classes is to provide structure to the layout of universe. Typically, the general strategy is to group related dimension and detail fields into a class and place measure objects into a different class. This strategy can be extended through introducing sub-classes to break down objects into more granular subsets.

Three categories of classes exist …
• Dimension Classes:  Objects which are shared across transaction types to describe the transaction and provide summarization levels.
• Measure Classes:  Primary reporting objects for displaying numeric results (also known as facts).
• Application Classes:  Objects that support the reporting application, including customized lists of values, prompts, and condition objects.

BOBJ Universe Component - Classes

 

Universe Component:  Object

Objects refer to fields or columns within database tables or views. Properties of an object include a name, data-type, select statement, and sometimes a where statement. Objects can refer to a single database field or can be an expression of one or multiple database fields.

Three type of objects exist within a Business Objects universe …
• Dimension:  Text based or date values that are the primary describing values of a query.
• Detail:  Text based values that provides detailed information of a dimension. A detail is always attached to a dimension.
• Measure:  Numerical values that are the results of  a query and by which dimensions are compared.

BOBJ Universe Component - Objects

 

Universe Component:  Table

Tables refer to individual physical tables, views, or synonyms within a database that provide data to the universe.  Further, a universe represents a set of underlying tables.  Universe tables in Business Objects do not actually replicate any data from the underlying database tables and no data is stored in the universe itself..  Rather universe tables are pointers to fields in the database and universe tables let Business Objects know where to find the data in the underlying database.

In a data warehouse or data mart environment, primarily used for reporting and analytics, there exists two types of tables:  1) dimension tables and  2) fact tables.  Dimension tables can descriptive values such as customer, product, date, or location.  While fact tables contains numeric information that will be calculated in a query.

BOBJ Universe Component - Tables

 

Universe Component:  Join

Joins specify the characteristics of how tables, views, synonyms, or aliases relate to one another. In addition, joins allow the universe to combine information from multiple tables. A join is a condition that restricts the result set of a multi-table query.

There are four types of joins available in a universe …

• Equi-join (also known as inner join): A join based on column values in a column between two tables.
• Outer join (also known as right and left joins): A join that links two tables, one of which has rows that do not match those in the common column of the other table.
• Theta join: A join that links tables based on the relationship other than equality between two columns. Typically used to select records between a set of values.
• Self-Restricting join – A method used to set a restriction on a table in the universe structure. In essence it is a where condition created by a join property.

BOBJ Universe Component - Joins

 

Universe Component:  Context

A context is a group of tables and joins that define a specific path for a query and resolve loops that may occur within queries. Contexts are used to specify alternative routes and ensure that a single select statement only includes reference to columns from tables in one of those routes. In essence, a context is a rule by which the the report user can decide which path to choose when more than one join path exists in query from one table to another table. Without contexts, queries could contain multiple paths (also known as loops). And loops generally result in incorrect queries with fewer rows returned than expected. However, contexts make sure that there are clearly defined paths between tables and that no loops exist within a universe.

BOBJ Universe Component - Contexts

 

Universe Component:  Alias

An alias is an exact duplicate of a universe table but with a different name. The data in the table is exactly the same as the original table, but the different name allows the universe to accept the same table into its structure and identify the referenced instance of the table. A common use of alias is when a lookup table needs to be referenced several times in a query and when different join rules exist for the lookup table. The alias allows for the lookup table to be included multiple times in a universe and allows for the correct join rules to be associated with the lookup table.

BOBJ Universe Component - Aliases

 

Universe Component:  List of Values

A list of values is a data list with a universe object that contains the data values associated with that particular object. A list of values can contain data from two types of data sources: 1) database tables 2) external file sources. List of values contain a number of values that define how the data is presented in the list and define restrictions on the amount and type of data returned to the list.

BOBJ Universe Component - List Of Values

 

Universe Component:  Hierarchy

Hierarchy is an ordered sequence of dimensions which allow the users of Web Intelligence and Desktop Intelligence to perform multidimensional analysis on the data. In addition, hierarchies allow the user to observe data from various perspectives and hierarchies are critical in spotting trends or exceptions in the data. By default hierarchies are set based upon the order in which dimensions are placed in a class. However, universes also allow creation of custom hierarchies in which the sequence of dimensions is defined by developer based upon the business need. In essence, hierarchies allow for drilling of data.

There are four types of drilling capabilities available in Business Objects …

• Drill-Down: Displaying data at progressively more detailed level.
• Drill-Up: Displaying data at a less detailed level or a higher summery level.
• Drill-Through: Provides the ability to query the database directly and get the data at lower level than the lowest level in the hierarchy.
• Drill-By: Displaying data at a particular level in the hierarchy without having to visit each level in turn.

BOBJ Universe Component - Hierarchies

 

 

Share

BI Market: Vendors and Products (2011)

Jul 20, 2011 No Comments
Business Intelligence (BI) systems are software applications that enable better understanding of organizational data and provide the information organizations need to make enlightened decisions. Fundamentally, there are five market segments of entire business intelligence market space…
• Operational Reports
• Query & Analysis
• Dashboard Management
• On-line Analytic Processing (OLAP)
• Data Mining & Predictive Analytics

 

BI Market Segment:  Operational Reports

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.

BI Vendor Products - Operational Reporting (2011)

 

BI Market Segment:  Ad-Hoc Query & Analysis

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 Vendor Products - AdHoc Query & Analysis (2011)

 

BI Market Segment: Dashboard Management Systems

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 Vendor Products - Dashboard Management (2011)

 

BI Market Segment: OLAP

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 Vendor Products - OLAP (2011)

 

BI Market Segment: Data Mining and Predictive Analytics

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 Vendor Products - Data Mining (2011)

 

 

Share

Components of Oracle BI Enterprise Edition (OBIEE)

Jun 23, 2011 No Comments
Oracle Business Intelligence Enterprise Edition (OBIEE) is a comprehensive business intelligence platform produced by Oracle, Inc. that delivers a full range of analytic and reporting capabilities. Additionally OBIEE incorporates a suite of advanced business intelligence tools that are built upon a unified architecture.  The components delivered within the OBIEE suite include:
Oracle BI Logo
•  Oracle Interactive Dashboards
•  Oracle Answers
•  Oracle Publisher
•  Oracle Delivers
•  Oracle Disconnected Analytics
•  Oracle BI Server

Component of OBIEE – Oracle Interactive Dashboards

Oracle Interactive Dashboards provides versatile, web-based dashboards that displays information in a highly-intuitive format to help end-users’ decision making.  The user interface is completely graphical while access to the information is interactive and based on the individual’s role and identity. Further, the user has full capability for modifying and interacting with data utilizing the following controls …
•  Live reports
•  Charts
•  Tables
•  Prompts
•  Pivot tables

Additionally, the Oracle Interactive Dashboards provides the user with full capability of drilling, navigating, modifying, and interacting with data sets. This concept is commonly referred to as “guided analysis“.  Oracle Interactive Dashboards can retrieve and aggregate content from a wide variety of sources, including the traditional databases, data warehouses and data marts, shared file servers, and document repositories.

OracleBI - Dashboards

Oracle Interactive Dashboards provide personalized views of corporate and external information. A dashboard consists of one or more pages, which appear as tabs across the top of the dashboard. Pages can display anything that you can access or open with your Web browser, such as saved queries and request, alerts from Oracle BI Delivers, images, charts, tables, text, and links to web sites and documents.

 

Component of OBIEE – Oracle Answers

Oracle Answers provides ad-hoc query and analysis capabilities to end-users.  Users can processes data from multiple data sources in a pure web environment. In addition, users are isolated from underlying data structures as they view and work with a logical view of the information.  This logical view of the information is presented to the user in business terms.  The interface of Oracle Answers is highly-intuitive and enables end-users to create:
•  Queries
•  Data tables
•  Interactive charts and graphs
•  Pivot tables
•  Reports
•  Prompts

Oracle Answers provides a point-and-click interface that allows end-user to develop robust ad-hoc queries and reports. Users simply drag-n-drop fields to create layouts with reports, queries, and charts on the right-hand side of the application.  Data fields are subsequently on listed on the left-hand side.

Oracle BI Answers

In addition, Oracle Answers is tightly integrated Oracle Interactive Dashboards. Reports, prompts, tables, charts, & graphs created in Oracle Answers can be easily saved, shared, modified, formatted, or integrated within dashboards of the Oracle Interactive Dashboards application.

 

Component of OBIEE – Oracle Publisher

Oracle Publisher (formerly XML Publisher) is an enterprise-level operational reporting tool that separates the data from the presentation. This means that developers from the information technology department can define the data sources, and business users can specify the report layout. The same data source can be used by multiple report templates.  By separating the task of finding the data from the task of deciding how to present them, the business user can define a report template with exactly the format he or she wishes.

Oracle BI Publisher

Rather then having an independent report design environment, Oracle Publisher leverages both MS Word and Adobe Acrobat as a user interface for the creation of richly-formatted operational reports.

Oracle BI Publisher

 

Component of OBIEE – Oracle Delivers

Oracle Delivers provides activity monitoring and alerting that can reach users via multiple channels:
•  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.

Oracle BI Delivers

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.

 

Component of OBIEE – Oracle Disconnected Analytics

Oracle Disconnected Analytics provides all of the features of Oracle Interactive Dashboards and Oracle Answers (i.e. dashboards and ad hoc analysis) while off-line and disconnected from a corporate network. Moreover, the same user interface is utilized whether the user is working in a connected or disconnected mode. Oracle Disconnected Analytics leverages advanced data synchronization capabilities and allows for full and incremental synchronization of data with enterprise data sources. Data can be personalized for each user, maintaining all role-based security and visibility, and is compressed during synchronization, resulting in minimal data set size and fast sync times.

Features of Oracle Disconnected Analytics:
• Off-line and mobile business intelligence
• Full capabilities of Oracle Dashboards and Oracle Answers
• Intelligent synchronization of data

 

Component of OBIEE – Oracle BI Server

The foundation of the OBIEE platform is a  server that supports a full complement of access, analysis, and information delivery options, all in one fully integrated environment.  The Oracle BI server provides a common engine that retrieves date from its source, converts the data to a common format, and presents intelligence to all of the OBIEE applications that consume information, including Interactive Dashboards, Answers, Publisher, and Delivers.  All of these applications require rich access to broad sets of data across the enterprise, and they all require a sophisticated calculation and aggregation infrastructure that the platform provides to deliver value.

Capabilities of Oracle BI Server:
• Powerful middleware engine
• Presents intelligence back to other OBIEE components
• Integrates disparate data sources
• Sources data from multiple data sources

 

 

Share

Oracle BI Platform vs Oracle BI Analytical Applications

Jun 08, 2011 1 Comment

Oracle BI LogoThe business intelligence market is rapidly maturing and Oracle is leading the way with its delivery of Oracle Business Intelligence.  Initially Oracle just provided the Oracle BI Platform which provided businesses with the tools for developing custom business intelligence solutions. However, Oracle is now providing an option to its customers with Oracle BI Analytical Applications, a complete business intelligence and data warehousing solution that is built upon their own robust and feature-rich platform. Now businesses have the option of either custom building a BI solution from scratch or configuring a pre-packaged BI solution.

 

Oracle BI Platform

The Oracle Business Intelligence 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 …

Oracle BI - Dashboards

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.

Oracle BI - Platform

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.

 

Oracle BI Analytical Applications

Rather than just being a platform or development environment, Oracle Business Intelligence 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.

Oracle BI Analytical Application Modules

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

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.

 

Differences Between Oracle BI Platform vs. Oracle BI Analytical Applications

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.

Oracle BI Platform / Analytical Applications Components

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.

Oracle BI Platform 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 BI Analytical Applications Metadata

 

Conclusion

Oracle Business Intelligence (OBI) is a suite of technology and solutions that provide an integrated, feature-rich reporting and analytics environment that can be deployed at an enterprise level to organizations seeking to understand their data in more detail. Traditionally, Oracle provided a platform with all of the tools for organizations to custom build a solution. However recently Oracle has developed pre-built analytical applications that provide oganizations with complete business intelligence and data warehousing solutions. Now organizations have an option of how they want to deploy Oracle Business Intelligence to solve the analytical and reporting requirements. Businesses can now conduct either customization with the Oracle BI Platform or configuration with the Oracle BI Analytical Applications.

 

 

Share

Data Integration Techniques (ETL and Data Federation)

May 31, 2011 2 Comments

Data integration is a fundamental, yet deceptively challenging, component of any organization’s business intelligence and data warehousing strategy.  Data integration involves combining data residing in different data repositories and providing business users with a unified view of this data.  In addition, companies face a challenge of ensuring that data being reported is current and up-to-date.  Companies are now increasingly incorporating both traditional batch-oriented techniques for query performance and real-time data integration to eliminate the annoyance of out-of-date data.  The top batch-oriented technique that companies utilize is known as ETL while one of the popular real-time techniques is known as Data Federation.

 

Data Integration Technique – Extraction, Transformation, & Loading (ETL)

Steps within ETL Processing

Steps within ETL Processing

The term ETL which stands for extraction, transformation, & loading is a batch or scheduled data integration processes that includes extracting data from their operational or external data sources, transforming the data into an appropriate format, and loading the data into a data warehouse repository.  ETL enables physical movement of data from source to target data repository. The first step, extraction, is to collect or grab data from from its source(s).  The second step, transformation, is to convert, reformat, cleanse data into format that can be used be the target database.  Finally the last step, loading, is import the transformed data into a target database, data warehouse, or a data mart.

ETL Step 1 – Extraction
The extraction step of an ETL process involves connecting to the source systems, and both selecting and collecting the necessary data needed for analytical processing within the data warehouse or data mart. Usually data is consolidated from numerous, disparate source systems that may store the date in a different format.  Thus the extraction process must convert the data into a format suitable for transformation processing. The complexity of the extraction process may vary and it depends on the type and amount of source data.

ETL Step 2 – Transformation
The transformation step of an ETL process involves execution of a series of rules or functions to the extracted data to convert it to standard format. It includes validation of records and their rejection if they are not acceptable. The amount of manipulation needed for transformation process depends on the data. Good data sources will require little transformation, whereas others may require one or more transformation techniques to to meet the business and technical requirements of the target database or the data warehouse. The most common processes used for transformation are conversion, clearing the duplicates, standardizing, filtering, sorting, translating and looking up or verifying if the data sources are inconsistent.

ETL Step 3 – Loading
The load is the last step of ETL process involves importing extracted and transformed data into a target database or data warehouse. Some load processes physically insert each record as a new row into the table of the target warehouse utilizing a SQL insert statement.  Whereas other load processes include a massive bulk insert of data utilizing a bulk load routine.  The SQL insert is a slower routine for imports of data, but does allow for integrity checking with every record. The bulk load routine may be faster for loads of large amounts of data, but does not allow for integrity check upon load of each individual record.

ETL Tool Providers
Here is a list of the most popular commercial and freeware (open-source) ETL Tools.
Commercial ETL Tools:
•  IBM Infosphere DataStage
•  Informatica PowerCenter
•  SAP Business Objects Data Integrator (BODI)
•  SAP Business Objects Data Services
•  Oracle Warehouse Builder (OWB)
•  Oracle Data Integrator (ODI)
•  SAS Data Integration Studio
•  Microsoft SQL Server Integration Services (SSIS)
•  Ab Initio
•  SyncSort DMExpress
•  iWay DataMigrator
•  Pervasive Data Integrator
Freeware, Open Source ETL tools:
•  Pentaho Data Integration (Kettle)
•  Talend Integrator Suite
•  CloverETL
•  Jasper ETL

 

Data Integration Technique – Data Federation

Data Federation
Data federation is a category of data integration technology that provides the ability to query and aggregate data from disparate sources in a virtual database so it can be used by business intelligence, reporting, or analysis applications in real-time.  The virtual database created by data federation technology doesn’t contain the data itself.  Instead, it contains information or metadata about the actual data and its location. The actual data is physically left in place within its source data repository.

Data federation is used to create virtualized and integrated views of data and allows for execution of distributed queries against multiple data sources (relational databases, enterprise applications, data warehouses, documents, XML) at the same time.  Data federation allows for accesses to data without physical movement of data and provides  a layer of abstraction above the physical implementation of data.

Data federation is synonymous with other technologies and commonly referred to as …
•  Data Virtualization
•  Enterprise Information Integration (EII)
Pros of Data Federation
•  Access current and transactional data stored in multiple sources
•  Does not require movement of data (No ETL)
•  Only requested data returned
•  Real-time data access
•  Quicker development time – supports incremental development
•  Reduces data storage and data transfer

Cons of Data Federation

•  Still queries against original data sources
•  Only contains as much data as source system contains
•  If data is archived off source, data is no longer available in federation tool
•  Query performance is not as good as a data warehouse
•  High system performance transferred to an application server from a database server
Top Data Federation Tools
Below a list of the most popular enterprise data integration tools providing the data federation features:
•  SAP BusinessObjects Data Federator
•  Sybase Data Federation
•  IBM InfoSphere Federation Server
•  Oracle Data Service Integrator
•  SAS Enterprise Data Integration Server
•  JBoss Enterprise Data Services Platform
•  iWay Data Hub

 

 

Share

Dashboards and Scorecards

May 23, 2011 No Comments

All members of an organization need information they can use to focus on accomplishing the same organizational goals.  But maintaining consistent views of operations through all lines and divisions of an organization can be a challenging task. To this end, organizations are relying on dashboards and scorecards to provide relevant metrics, indicators, alerts, and monitoring tools.  In addition, these dashboards and scorecards are utilized to effectively monitor and manage business activities, operations, and strategies.

…dashboards and scorecards are the key tools in a business performance management discipline that enables executives to communicate business strategies in a customized way to each employee and monitor the execution of those objectives to fine-tune the organization and keep it on track.

Wayne W. Eckerson,  Director of Research and Services,  TDWI

Dashboards

Xcelsius Dashboard

Sample Dashboard

Unlike operational reports and ad-hoc reporting environments, a dashboard provides a fixed and rapid method to deliver relevant and timely information to key decision-makers within a highly-intuitive interface. Dashboards inform organizational decision-makers real-time the state of their organizational performance and significantly increase organizational performance. Dashboards typically provide graphical interfaces that include either key performance indicators (KPIs) or business metrics that represent the key information about the organization. Dashboards are typically easy-to-use, easily personalized, and can alert decision makers when business metrics approach and exceed accepted ranges and targets. Dashboards may also provide basic controls that can alter the view of the data and allow for business users to collaborate.

The graphical format of dashboards can vary, and business users have the option to pick the format that is most relevant to their data and customize to their specific needs. Formats of dashboards can include gauges, barometers, thermometers, charts, graphs, maps, trend lines, metric trees, and traffic lights.

The key value of dashboards is the ability to display actionable information in an intuitive format and conduct meaningful analysis of the data. Dashboards are typically used by managers and staff in lower levels of the organization, are updated in real-time or near real-time, and use charts and tables to symbolize events for the purpose of measuring performance.

 

Scorecards

A scorecard is a solution that allows organizations to manage their organizational performance by linking operations with strategy. The scorecard monitors the execution of strategic objectives at each level of the organization and ensures a consistent understanding at all levels of the organization’s priorities and expectations.
Sample Scorecard

Sample Scorecard

Scorecards can be utilized at all levels of an organization including executives, managers and staff. They are usually updated in periodic snapshots, use graphical symbols and icons to present summaries of organizational performance, and chart overall progress over time for the organization.

Scorecards provide value to the organization by linking key performance indicators (KPIs) within tactical and operational levels in an organization to the overall strategy of the organization. In addition, the scorecard establishes accountability to appropriate individuals and enables visibility to up and down an organization from executive management to operational levels. The scorecard can be a powerful tool to allow for assignment of goals and objectives to all individuals and focuses accountability on the relevant individuals and business units.

Typically scorecards provide internal and industry benchmarks, goals, and targets that assist an individual’s understanding of their own unique contribution to the organization. Often, the scorecard spans strategic, tactical, and operational aspects and decisions of the organization and supports the specific demands of varying levels of management.

Unlike dashboards that tend to monitor the performance of operational processes, scorecards focus on charting the progress of tactical and strategic goals. Further scorecards present results in a graphical format using symbols and icons to represent the status of key metrics, and tend to show data in a linear format.

 

 

Share

Business Intelligence (BI) Maturity Model

Dec 08, 2010 3 Comments

 

The six levels of the Business Intelligence (BI) Maturity Model are measured by the value provided to the business vs the sophistication of the tool suite. The lowest level of business intelligence maturity (level 0) is characterized by fractured reporting at different times using different data sources and rules for defining metrics within an organization. Thus creating a disjointed and somewhat inaccurate view of an enterprise. While the highest level of business intelligence maturity (level 5) is characterized by strategic, tactical, and operational decision-making in situations where numerous factors and variables are included. Organizations utilizing level 5 tools are able to effectively model their business model and accurately project future results.

 

Level 0 – Limited BI / Spreadsheets

Example Spreadsheet
A spreadsheet is a computer application that simulates a paper, accounting worksheet. It displays multiple cells that together make up a grid consisting of rows and columns, each cell containing alphanumeric text, numeric values or formulas.  Because spreadsheets are easy to work with and are widely available, many businesses rely on them to take the place of a more sophisticated business intelligence tool.  Instead of providing a consistent view of the organizational data, spreadsheets have decentralized views of limited amounts of data. In addition, spreadsheets are typically developed by an individual user, consist of little or no organizational standards, and encompass limited system analysis and quality assurance before being used as an official reporting source.  It is typically up to the spreadsheet developer to decide what metrics are important, what data needs to be included, how the data is formatted, and what level of aggregation is necessary.  Spreadsheets become isolated and inconsistent data silos, and are difficult for analysts to extract, transform and load data into a central database to be interpreted at an enterprise level.
Characteristics of Spreadsheets
•  Widely used personal desktop application
•  Reporting conducted in rows and columns
•  High degree of individual use
•  High degree of inaccuracy and variability
•  Limited security
•  Limited collaboration

 

Level 1 – Operational Reports

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. The tools provide task-oriented line-item information on individual transactions at the very granular level of detail required for operational management. (source: information management magazine)

Sample Operational Report

Characteristics of Operational Reports
•  Presents data in a logical format
•  Data is distributed in s highly-formatted manner
•  Reports can be published on a regular schedule
•  Distribution of organized listings of data
•  Enables users to understand transactional and/or detailed level data
•  Typically developed by information technology (IT) personnel and/or knowledgeable power users

 

Level 2 – Query & Analysis Solutions & Environments

Also known as “ad-hoc” querying, 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. The tools within this category 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 needs in 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.

Sample Adhoc Query and Analysis Solution

Characteristics of Query & Analysis Environments
• Utilized primarily by business users
• Drag-n-drop interfaces to create queries and simple reports
• Rapidly and intuitively generate queries with minimal help from IT professionals
• Highly-interactive methods to query data
• Users ask business questions to develop queries
• To produce queries, users only need to understand their own business terms
• Users can independently dive into the details of their data

 

Level 3 – Dashboard Management

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.

Sample Dashboard

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 the ability to drill into particular aspects of the display and/or rapidly switch between views of the data.  To make this possible, dashboards are typically composed of advanced data visualization tools including charts, grids, gauges, and maps that allow for various ways of presenting information and interacting with the data.  Moreover, dashboard management systems are characterized by providing users with graphical methods to view and interact with key data elements and get a snapshot of organizational performance.

Characteristics of Dashboard Management
•  Presents a number of organizational metrics in a single consolidated view
•  Utilizes graphics, charts, grids, gauges, and maps
•  Monitors organizational metrics and key performance indicators (KPI)
•  Enables real-time visibility into data
•  Primarily utilized by senior management and key decision-makers
•  Rapidly displays a snapshot of organizational performance

 

Level 4 – On-line Analytical Analysis (OLAP)

On-line analytical processing (OLAP) is a technique 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.

Example of On-line Analytical Analysis (OLAP)

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 andallows the users to conduct advanced “What-If” analysis.

Characteristics of OLAP
•  Queries from OLAP cubes rather than database tables
•  Enables advanced insight into past performance
•  Provides accurate and precise “What-If” analysis
•  Queries perform extremely rapidly
•  Primarily used by business areas concerned with financial and resource planning

 

Level 5 – Data Mining & Predictive Analytics

Sample Data Mining Application
According to data-mining-guide.net, data mining is the process 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 these tools 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.

Characteristics of Data Mining & Predictive Analytics
•  Leverages statistics, advanced techniques, algorithms, and sophisticated data search capabilities
•  Extrapolate past performance to forecast future events
•  Provides answers to questions that may never have been asked
•  Calculates levels of correlation between data elements
•  Implementers are experts of statistic analysis and/or processing of large queries

 

 

Share

Example Charter for a BI Center of Excellence (BI COE)

Nov 25, 2010 No Comments

In order to ensure a predictable and repeatable usage of Business Intelligence (BI) services across organization X, and to maximize the value of licensing cost, organization X’s information technology department has adopted the formation of a Business Intelligence Center of Excellence (BI COE). This BI COE performs at an enterprise level and serves as a cross-functional team with a permanent, formal organization structure. The team includes defined tasks, roles, responsibilities and processes for supporting and promoting the effective use of business intelligence across the organization.

In addition, organization X has chosen to standardize on an organization-wide reporting and decision support platform from SAP Business Objects to provide analysts and decision-makers across the enterprise with easy and flexible access to critical data. While Business Objects is being made available to the users through-out the enterprise, there is a need to establish and uphold best practices, guidelines, policies, standards and training to ensure the usability, sustainability and maintainability of the environment.

The BI COE is now responsible for the application administration and support of the newly acquired and implemented Business Objects environment. This includes support of the entire suite of tools from Business Objects (Web Intelligence, InfoView, Crystal Reports, and Xcelsius). Moreover, the BI COE ensures success of every Business Objects implementation that occurs within Amtrak by providing technical oversight, quality assurance, security planning, and post-production support. As the BI COE is ultimately responsible for the success of business intelligence within Amtrak, the BI COE ensures that the Business Objects environment optimally performs, is highly secure and stable, and ensures that all projects are implemented with quality and best practices. In addition to providing support for the business intelligence infrastructure, this technical team has also been chartered to work closely with the various project teams throughout the organization to implement and deploy high quality business intelligence solutions.

Sample Business Intelligence Center of Excellence (BI COE)

Sample BI COE

The business objectives of the BI COE are enabled by increasing support and service capabilities, by redesigning and implementing new design, implementation, service, and support processes. Moreover, the services provided by the BI COE projects span the whole project development life-cycle, from initiation, analysis, design, construction, deployment, and post-production support with the primary purpose of helping projects bring well-engineered solutions to the consumers of the product.

Fundamentally, the BI COE at organization X has three major responsibilities:

1. Application/security administration of Business Objects environment
2. Management and support of Business Objects application infrastructure
• Web Intelligence
• InfoView
• Crystal Reports
• Xcelsius Dashboards
3. Oversight, development, and implementation of enterprise business intelligence projects

 

 

Share

Definition of BI Center of Excellence (BI COE)

Nov 24, 2010 Comments Off on Definition of BI Center of Excellence (BI COE)

Business Intelligence Centers of Excellence (BI COE) enable the design, development, and implementation of strategic, enterprise, department, and tactical Business Intelligence solutions from one centrally managed team within a corporation or organization.

Fundamentally, the BI COE merges the traditional roles and responsibilities of program management offices, functional expertise of subject matter experts, and the technical expertise of  systems personnel. As a result, the BI COE allows organizations to leverage best practices and standards, conduct application administration, and provide enterprise wide management of technical and functional resources.

In practice, the BI COE allows for a single or set of business intelligence products to become an enterprise standard, and the BI COE group becomes the primary team for conducting implementations, management, and support of BI solutions across an organization.

The foundation a well-formed BI COE includes four primary components
– Enterprise Coordination
– Common Solution Design
– Infrastructure
– Asset Management
While a number of secondary components can be incorporated into the primary components …
– Advice and Consulting
– Standards
– Support
– Knowledge Sharing
– Quality Assurance
– Security
– Governance
– License Management
– Training
– Repeatable Services

 

 

Share
Facebook Like Button for Dummies