The Microsoft Business Intelligence Stack Cheat Sheet

Two questions I tend to get asked over and over again are:

1. Exactly what products are you talking about when you refer to the Microsoft BI stack?
2. Where do I get more information on a particular product in the stack?

So being the lazy and generally unsociable person I am, I decided to bite the bullet and create a post that I could just point people to rather than having to retype my answers over and over again.  So below are the components that I consider part of the Microsoft BI solution offering, as well as some links to more information on those products.  If I have missed anything or if you have some suggestions for some additional/better links drop me a line and I will add it to the list.  I am also going to pin this to my main navigation bar so people can find it easily.  So without further adieu here is the list:

Product Group	Component Item	Description/Usage	Link
Office	Excel	Used for spreadsheets, pivot tables, charts/graphs and general crunching of numbers.  Excel is probably the most widely used BI tool in the world.  As organizations get more sophisticated, they outgrow Excel, but continue to use it as it is all they know.  The upcoming release of Excel 2013 will add significant additional BI capabilities.	Link
	PowerPivot for Excel	A free add-in available for Excel that allows processing and analyzing of massive amounts of data within the Excel environment with easy linking to numerous different data sources.  The power and manageability of PowerPivot for Excel can be expanded by utilizing PowerPivot for SharePoint.	Link
	Data Mining Add-In for Excel	A free add-in available for Excel that adds data mining capabilities to Excel to aid in predictive analysis.  Usage requires the ability to link to an Analysis Services database within SQL Server.	Link
	Visio	A great tool for doing data visualization using shapes and process flows.  Key component for producing Strategy Maps via PerformancePoint Services within SharePoint.	Link
SharePoint Server	Excel Services	A native component of SharePoint Server that provides the ability to publish Excel documents to a SharePoint website to allow interaction and viewing without the need launch Excel.	Link
	Visio Services	A native component of SharePoint Server that provides the ability to publish Visio documents to a SharePoint website to allow interaction and viewing without the need launch Visio.	Link
	PerformancePoint Services	A native component of SharePoint Server that provides the ability to create and deploy dashboards, scorecards, strategy maps, charts/graphs and decomposition trees within the SharePoint environment.	Link
	SharePoint Chart Web Part	A web part that allows the user to present a chart/graph within SharePoint linked to various data sources.  Limited in its ability to drill down or pivot the data but good for simple presentation of data.	Link
	SharePoint Status Indicators	A special SharePoint web part that is designed to display Key Performance Indicators.  It can draw upon data found in other SharePoint lists, Excel, Analysis Services or data that has manually been entered.	Link
	PowerPivot for SharePoint	Allows for the publishing of PowerPivot documents created in PowerPivot for Excel to be deployed through the SharePoint environment.  Also provides central management and scheduling capabilities as well as the capability to harness the power of the server when doing analysis rather than the user’s desktop.	Link
	Power View (Project Crescent)	Formerly known as Project Crescent. A data visualization component using Silverlight and reading data from PowerPivot or a Business Intelligence Semantic Model database. Currently require SharePoint 2010 to utilize but will be natively integrated with Excel 2013.	Link
SQL Server	Database Services	What most people think of when they hear SQL Server.  This is the engine that stores data in a relational manner and in the BI world will be used mostly for hosting source data or a data warehouse.	Link
	Analysis Services	The heart of many business intelligence solutions is an OLAP cube.  Analysis Services is the engine within the SQL Server product line that processes and serves up cube data.  If you are going to do BI, then you best understand cubes.	Link
	Integration Services	Within SQL Server, Integration Services is the tool used to perform the ETL (Extract, Transform, Load) process that moves data from different host system to a central store that your business intelligence solution can draw upon.	Link
	Reporting Services	Reporting Services is used to generate reports based on data found within the relational or cube components of SQL Server.  Basic design of reports can be accomplished through the use of Report Builder while more complicated reports may require the use of Visual Studio or BIDS.	Link
	Master Data Services	Most organizations have multiple systems that they rely upon.  Over time the shared master data within these system gets harder and harder to manage.  Master Data Services provides functionality to help organizations manage their master records.	Link
	SQL Server 2012	The current release of Microsoft SQL Server. Formerly known by the project name Denali.	Link
	Business Intelligence Semantic Model (BISM)	A new technology to be released with SQL Server 2012 (Denali). It is a relational based model relying on in-memory processing to replicate the functionality of a traditional UDM analysis cube. It will be good for some situations but not a complete replacement for a UDM cube.	Link
	Vertipaq Model	A SQL Server Analysis Services deployment utilizing in-memory processing RAM to simulate traditional disk based UDM cubes. Utilized by PowerPivot for SharePoint and the Business Intelligence Semantic Model and Power View in SQL Server 2012. With the release of SQL 2012 models utilizing Vertipaq are now refered to as Relational models.	Link
	Unified Dimensional Model (UDM)	A SQL Server Analysis Services deployment utilizing traditional disk based cubes. With the release of SQL 2012 models utilizing UDM are refered to as Multidimensional models.	Link
Other	Bing Maps	Although best known for providing driving directions, the Bing Map platform can integrate with data to provide geospatial visualizations of the data. Certain types of trends in data are easier to spot if they are overlaid on geographic maps.	Link
	PivotViewer	A Silverlight-based data visualization tool designed to allow the user to navigate through large volumes of data (thousands at a time) and group them visually based on filter parameters the user controls.	Link
	Hadoop	Apache Hadoop is an open source project for processing large data sets across clusters of computers. Also known as “Big Data”.	Link
	Column Store	Relational databases traditionally store data in rows on a data page. In a column store, values from a single column, coming from multiple rows are stored together in a compressed form providing better performance. Often used for large data warehouses.	Link
Development Languages	Transact SQL	The language of writing SQL Server Relational based queries. Often referred to as T-SQL or TSQL.	Link
	Multidimensional Expressions Language (MDX)	The language of writing SQL Server Analysis Services UDM/Multidimensional based queries. Referred to as the language of cubes.	Link
	Data Analysis Expressions Language (DAX)	The language of writing SQL Server Analysis Services Vertipaq/Relational based queries. Used heavily with PowerPivot for Excel and PowerPivot for SharePoint.	Link

Lasted Updated: August 28, 2012