SQL SERVER – Introduction to Business Intelligence – Important Terms and Definitions – 2

Business intelligence comes with a lot of vocabulary, and most of it clicks once you see where each word fits. Here are the important terms and definitions I would learn first as a SQL Server person, grouped by the path data takes from the source system to the report on a manager’s screen.

Business intelligence, or BI, is the set of tools and practices a company uses to collect its data, store it in a shape that is easy to analyze, and turn it into reports and answers that support better decisions.

Where the data starts and how it moves

• OLTP (online transaction processing): the everyday systems that take orders, record payments and update stock, one small transaction at a time.
• ETL (extract, transform, load): pulls data out of source systems, cleans and reshapes it, and loads it into the warehouse.
• Staging area: a work space where data waits while ETL cleans it up.
• Data warehouse: a central store of cleaned, combined history from many systems, built for reporting, not daily transactions.
• Data mart: a smaller, focused store for one subject or one department, such as sales or finance.

How the warehouse is shaped and used

• Fact table: holds the numbers you measure, such as quantity or sales amount, plus keys that point to the dimensions.
• Dimension table: holds the descriptive details, such as customer, product or date, that you use to filter and group the facts.
• Measure: a numeric value in a fact table that you add up, count or average.
• Star schema: one fact table in the middle with the dimension tables around it, like the points of a star.
• Cube: a multidimensional structure, often with totals already calculated, so a total like sales by product by month comes back quickly.
• OLAP (online analytical processing): tools that let you explore a cube interactively, slicing it by any dimension and drilling down from a yearly total to monthly detail.
• Dashboard and KPI: a dashboard shows key performance indicators on one screen, usually comparing each one with a target.

In SQL Server, these ideas map to its own tools: Integration Services handles ETL, Analysis Services builds and serves cubes, and Reporting Services delivers the reports. My longer article has a much bigger glossary and a sample business scenario. Learn the short list above first, and the next BI meeting will sound a lot less like a foreign language.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.