BUSINESS INTELLIGENCE IN DECISION SUPPORT SYSTEMS
A decision support system (DSS) is a computer-based information system that supports business or organizational decision-making activities; typically this results in ranking, sorting, or choosing from among alternatives. DSSs serve the management, operations, and planning levels of an organization (usually mid and higher management) and help people make decisions about problems that may be rapidly changing and not easily specified in advance.
There are several types of DSSs that include:
- Communication-driven DSS which enables cooperation, supporting more than one person working on a shared task; examples include integrated tools like Google Docs or Microsoft Groove.
- Document-driven DSS which manages, retrieves, and manipulates unstructured information in a variety of electronic formats.
- Knowledge-driven DSS provides specialized problem-solving expertise stored as facts, rules, procedures, or in similar structures
- Model-driven DSS emphasizes access to and manipulation of a statistical, financial, optimization, or simulation model. Model-driven DSS use data and parameters provided by users to assist decision makers in analyzing a situation; they are not necessarily data-intensive.
- Data-driven DSS (or data-oriented DSS) emphasizes access to and manipulation of a time series of internal company data and, sometimes, external data. A data-driven DSS, which we will focus on, emphasizes access to and manipulation of a time series of internal company data and sometimes external data. Simple file systems accessed by query and retrieval tools provide the most elementary level of functionality. Data warehouse systems that allow the manipulation of data by computerized tools tailored to a specific task and setting or by more general tools and operators provide additional functionality. Data-driven DSS with online analytical processing (OLAP) provide the highest level of functionality.
Data warehousing combines the best of business practices and information systems technology and requires the cooperation of both business and IT, continuously coordinating in order to align all the needs, requirements, tasks and deliverables of a successful implementation.
The need for a successful data warehouse implementation arises when reporting from a line of business database and a single report requires multiple table joins to get relevant data hence a slow rate of retrieval, when naming conventions are usually not enforced and thus it is difficult to know where the data you need is stored, when your organization may have several line of business applications working against a single or several databases and thus the data quality is low and not tracked over time.
Creation of a central repository for merged and historical data as opposed to the normalized relational schema represented in the above scenario has several advantages that include: simplified business reporting logic with performance gains, faster aggregations, and the ability to feed Online Analytical Processing systems with Star or snowflake schemas that cover multiple business areas.
The star schema separates business process data into facts and dimensions. Fact tables record measurements for a specific event generally consisting of numeric values, and foreign keys to dimensional data where descriptive information is kept. Fact tables are generally assigned a surrogate key to ensure each row can be uniquely identified. This key is a simple primary key not derived from source data. Dimension tables on the other hand are descriptive attributes related to fact data, usually having a relatively small number of records compared to fact tables, but each record may have a very large number of attributes to describe the fact data.
Dimensions can define a wide variety of characteristics dimension tables are generally assigned a surrogate primary key, usually a single-column integer data type, mapped to the combination of dimension attributes that form the natural key.
A snowflake schema, on the other hand, is an expansion and extension of a star schema to additional secondary dimensional tables. In a star schema, each dimension is typically stored in one table; the snowflake design principle expands a dimension and creates tables for each level of a dimensional hierarchy.
As far as the advantages go, Data Warehouses have demerits too and these include data integrity not being enforced as well as it is in a highly normalized database. One-off inserts and updates can result in data anomalies which normalized schemas are designed to avoid. Generally speaking, star schemas are loaded in a highly controlled fashion via batch processing or near-real time “trickle feeds”, to compensate for the lack of protection afforded by normalization.
The Star schema is also not as flexible in terms of analytical needs as a normalized data model. Normalized models allow any kind of analytical queries to be executed as long as they follow the business logic defined in the model. Star schemas tend to be more purpose-built for a particular view of the data, thus not really allowing more complex analytics Star schemas don’t support many-to-many relationships between business entities – at least not very naturally. Typically these relationships are simplified in star schema to conform to the simple dimensional model.
When a data warehouse is included as a component in a data-driven DSS, a DSS analyst or data modeler needs to develop a schema or structure for the database and identify analytic software and end-user presentation software to complete the DSS architecture and design. The DSS components need to be linked in an architecture that provides appropriate performance and scalability. In some data-driven DSS designs, a second multidimensional database management system (MDBMS) will be included and populated by a data warehouse built using a relational database management system (RDBMS). The MDBMS will provide data for online analytical processing (OLAP). It is common to build a data warehouse using an RDBMS from Microsoft and then use query and reporting and analytical software from a vendor such as Tableau or Business Objects as part of the overall data-driven DSS design. What some vendors call “business intelligence software” provide the analytics and user interface functionality for a data-driven DSS built with a data warehouse component.
FRANCIS CHEMORION, Business Intelligence Consultant, Pathways International Ltd.