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"