Business Intelligence Applications

In today's world many organizations identify opportunities to use business intelligence (BI) and analytics to monitor how the organization is progressing towards achieving goals and to identify areas where an organization has capabilities to compete in the industry by entering new fields of business.

In this respect, BI and analytics have many practical applications in diverse industries such as government, finance, sports, retail, technology, marketing etc.,

• Sports:- Selecting Players, finding competitor weaknesses etc.,
• Healthcare:- Diagnosing illnesses, proposing treatments etc.,
• Government:- Homeland security, Crime prevention etc.,
• Marketing:- Segmenting customers, Cross selling etc.,

There are many types of BI applications that are used based on the industry. In this blog, I will discuss Finance, Supply chain and Risk management business intelligence applications.

Finance BI Applications:-

In this type, data comes from payroll, budgeting, general ledger, account payables, account receivables etc., Particularly trend analysis is useful to help organizations manage their expenses to budgets and detect issues. Some examples of BI applications are:

• Asset Management
• Budget Management
• Cost Management
• Overhead reduction analysis

For designing these applications, few fact tables that can be considered are Asset Balance Fact, Budget Line Fact, Payment Fact, etc., Similarly, few dimension tables could be Date dimension, Facility Dimension, Geographic Area (Hierarchical Dimension).  Few metrics that go into the fact table are Asset balance amount, budget amount, payment amount etc.,

Supply Chain BI Applications:-

The analysis of data provides information for suppliers, manufacturers, transporters, wholesalers and retailers. The data comes from various sources such as procurement, inventory, and manufacturing systems.

Some supply chain BI examples are:-

• Supplier performance analysis
• supply chain forecasting and planning
• supply chain optimization
• Inventory & Sales analysis

For designing these applications, few fact tables that can be considered are Shipment Fact, Billing Fact, Procurement Fact, Inventory Snapshot/Transaction fact etc., In addition to the dimension tables listed in the Finance BI application, there are few additional tables that could be considered here. They are - Supply Chain partner dimension, Commodity dimension, Work order dimension, Shipment order dimension.  Few metrics that go into the fact table are Inventory balance amount, Procurement lead time, Manufacturing lead time, Unit production cost etc.,

Risk Management BI Applications:-

This encompasses various applications such as evaluating credit risks, scoring risks and detecting fraud. The data comes from various banking systems, customer data, credit unions and financial data.

Some risk management BI examples are :-

• Fraud detection
• Credit risk analysis
• Hedging analysis

Hence, BI space is growing day-by-day and its use in various industries for analyzing data and benefits from the analysis are proving advantageous for organizations to adapt to them quickly.

Readings:-
The Analytical Puzzle: Profitable Data Warehousing, Business Intelligence and Analytics by David Haertzen

Comparisons between Fact Table Types

Fact table contains measures of an event in a business process that is located at the center of the star schema/snow flake schema surrounded by dimensional tables. Fact tables are often defined by their grain. There are three basic fact tables. They are :- 1) Transaction 2) Periodic Snapshot and 3) Accumulating Snapshot Fact tables.

1) Transaction Fact Table:-  This fact table is a basic one that represents an event that occurs at a point in time. The grain for this fact table is defined as "one row per line in a transaction". Hence, this fact table helps us to analyze the data in most extreme detailed atomic level. But all business questions cannot be answered with this type. Example :- One order line of an order receipt per a customer transaction.

2) Periodic Snapshot Fact Table:- As the name implies, this fact table is used to see the performance of business over certain time period intervals. Unlike transaction fact table, periodic snapshot can help us take a picture of the activity at the end of a week, month and so on. There are many fact table rows in this fact table than transaction fact table due to the possibility of occurrence of various metrics during the time period. Example:- Sales performance of a product over a period of a month.

3) Accumulating Snapshot Fact Table:- This fact table is used to represent a process that has a definite beginning and definite end with milestones or standard intermediary steps in between. This type of fact table is most suitable for performing pipeline analysis. These snapshot tables have multiple date foreign keys representing milestones in the process. These date dimensions are handled by role-playing date dimensions. Fact row in this fact table also contains duration between milestones. Example:- Processing of an order.

See below table for important differences between these fact table types:-

Complementary Fact Table Types:- The above three fact table types are basic ones. However, to provide a complete view of business, transaction and Snapshot fact tables are frequently combined and modeled. 

References:-

• Kimball, R., & Ross, M. (2013). The data warehouse toolkit. (3rd ed.). Indianapolis, Indiana: John Wiley & Sons, Inc.,.

Understanding Analytics Capabilities

Today, many organizations and individuals understand the need of analytics but struggle with various capabilities, uses of analytics and how to derive value from them. Lately, I have also been hearing about types of analytics, and hence decided to describe different types of analytics starting with the definition of analytics and how organizations can use them to address their business needs. 

What is Analytics?

Analytics has acquired several different meanings over many years. Gartner defines analytics as "A discipline that applies logic and mathematics to data to provide insights for making better decisions".

What is Analytics used for?

• Giving insights into business
• Create simulation environments
• Obtain data-driven decision policies

Types of Analytics Capabilities

Gartner describes four analytic capabilities that helps businesses to take actions based on asking right data questions. They are as follows:-

Descriptive Analytics:- This is used to answer the question - "What happened?" by querying data and summarizing key performance metrics. Business leaders use this information and apply their judgement and experience to make a decision/take an action. Descriptive analytics leaves user to do much of decision making. Ex: A new business wants to see its sales performance, or a new website that needs to monitor downtime.

Diagnostic Analytics:-  This answers the question - "Why did it happen?" This can be considered as a detailed and interactive descriptive analysis to understand why outcomes, events or trends occurred. Applications such as OLAP (Online Analytical Processing), Visualization tools are used. In this, business leaders rely more on analytics and less on human input to make decision.

Predictive Analytics:- This answers the question - "What will happen?" This analytics provides forward looking insights by recognizing patterns and assessing likely outcomes with statistical or machine learning techniques. This is used to predict future behavior and estimate unknown outcomes. 

Prescriptive Analytics:- This answers the question - "What should I do?" Rather than a report, statistic, probability or estimate of future outcomes, prescriptive analytics as the name implies supplies a decision to business leaders. This is a more powerful analytic capability. Prescriptive analytics deliver a "best" decision when objectives, constraints or criteria are specified. 

Using all Capabilities for Decision Making:-

For any business, leaders can start with the present, to create awareness that a decision must be made using descriptive capabilities (for example, reports, dashboards and alerts) and perhaps some diagnostic analysis (for example, data visualization).Predictive analytics, simulation and forecasting can help make better estimates of possible outcomes. The next step in the process is identifying the best course of action. This can be done by gut-feeling or data-driven prescriptive analytics. In this way, all analytics methods are used together at different stages by organizations to make effective data-driven decisions in the presence of wider set of opportunities.

References:-

• Kart, L., Linden, A., & Schulte, W. R. (2013). Extend your portfolio of analytics capabilities. Gartner, Retrieved from www.gartner.com

What is the Balanced Scorecard ?

The Balanced Scorecard (BSC) is a strategic planning tool and performance management system that provides organizations to align business activities to the vision and strategy of the organization and monitor organization performance against strategic goals.

BSC answers two key questions for organizations : "Where do we want to be?" and "How well are we doing getting there?"

This tool was originated by Drs. Robert Kaplan and David Norton as a performance measurement framework that added strategic non financial performance measures to traditional financial metrics to give managers and executives of an enterprise a balanced view to steer the organization. This phrase was coined in early 1990s but the roots of this type of approach are found in 1950s with the pioneering work of  General Electric on performance measurement reporting and from the work of French process engineers, (who created the Tableau de Bord – literally, a "dashboard" of performance measures).

Kaplan and Norton describe the innovation of the balanced scorecard as follows:

"The balanced scorecard retains traditional financial measures. But financial measures tell the story of past events, an adequate story for industrial age companies for which investments in long-term capabilities and customer relationships were not critical for success. These financial measures are inadequate, however, for guiding and evaluating the journey that information age companies must make to create future value through investment in customers, suppliers, employees, processes, technology, and innovation."

Source: Adapted from Kaplan and Norton, "Using the Balanced Scorecard as a Strategic Management System," Harvard Business Review, July 2007

This BSC suggests that we see the organization from four critical success factors, develop metrics, collect data and analyze it relative to these perspectives:

Scorecarding and Strategy Management Solution Providers:

Many vendors provide solutions for strategy management and scorecarding- ranging from BI vendors, stand-alone strategy and performance management suite providers to enterprise application vendors. The vendor landscape has become a hybrid market composed of megavendors such as IBM (Cognos), SAP (Business Objects) - that offer a portfolio of capabilities.

BSC is broadly being used by various businesses, organizations, and government across various industries worldwide.Once a BSC has been developed and implemented, however performance management software can be used to get the right performance information to right people at right time. Both together help transform corporate data into information and knowledge and help senior leadership team to take strategic organizational decisions.

Sources:-

• Opening the BPM Methodology Toolbox: The Balanced Scorecard , Gartner, 15June2012, John Dixon
• Solutions for Scorecards and Strategy Management, Gartner, 02April2010, Neil Chandler
• Balancedscorecard.org

"Start small but think BIG"

Introduction to Data Warehousing, Business Intelligence and Dimensional Modeling

First, to understand how this had begun, let me ask a question - "How Data Warehousing (DWH) had started"?

Today, Organizations and Corporations have large amounts of data in their operational systems. But how do the senior management and executives make strategic decisions for the growth of the company? The strategic decisions are largely based on the analysis of various performance reports, customer trends, historic operational information reports etc., But, how are these reports generated? The answer is through a "Data Warehouse".

Ok, So how are these reports generated through a DWH? Before answering this, let me talk about "Business Intelligence" (BI).

In a ten thousand foot level, BI is a methodology, technology, architecture that transfers raw data into meaningful information for making business decisions.

Relation between ETL, DWH and BI

ETL (Extract Transform Load) system extracts and transforms data from operational systems where all transactions of business operations are stored and loads it into the DWH. With the help of various BI applications, reports are generated, queries are triggered as requested by business users to make strategic decisions and other analytic applications are used by managers based on the business requirements.

Dimensional Modeling (DM)

The logical data design of the DWH evolved from the end user requirements and this technique is known as DM. This technique to deliver data is preferred because of its fast query performance, simplicity and understandability - these are essential tenets of BI for end users.

DM is a four step process as shown below:-

1) Selecting the business process:- In this step, we select a business process of an organization to use in the Dimensional Modeling. For Example- If we want to analyze how a product is being sold in different markets, to make strategic decision about that product, choose sales data as a business process.

2) Declaring the grain:- Second step is to determine granularity (grain) of the business process. This is the lowest level of detail for measurement of the process and dimensions depend on this level. Example, for sales data, grain could be sales per month, per year, per day etc.,

3) Identifying the dimensions for dimension tables:- Dimensions determine the context associated with the business process. For sales example, the dimensions could be product, location, store information, customer info etc,,

4) Identifying the facts for fact table:- Facts are business measures for an event. A single row in a fact table must relate to one row from all associated dimension tables. Example- Sales Amount of a product of a location of a customer.(Sales Amount is the Fact ; Product, Location, Customer are dimensions)

The above Dimensional Model is connected together in the form of a star with Fact table in the center and Dimension tables surrounding the fact table. Hence, it is known as Star Schema.

In conclusion, today, BI is being used by organizations to understand the performance of business, observe market trends to make faster, meaningful strategic decisions. These capabilities are derived by integrating and standardizing the data into an enterprise data warehouse, and by using analytical methods to extract information. Dimensional Modeling is a design technique to represent data in DWH system.

We will talk about other BI topics in my future posts.

References:-

• Dimensional databases. (n.d.). Retrieved from http://pic.dhe.ibm.com/infocenter/idshelp/v117/topic/com.ibm.whse.doc/ids_ddi_354.htm
• Kimball, R., & Ross, M. (2013). The data warehouse toolkit. (3rd ed.). Indianapolis, Indiana: John Wiley & Sons, Inc.,.
• Ponniah, P. (2001). Date warehousing fundamentals. New York, NY: John Wiley & Sons, Inc.,.

"It's not what happens to us is important; it's how we handle what happens to us is important"