Microsoft Business Intelligence Stack 2012


Screenshot from…
Sharepoint WIKI:
SQL Server WIKI:
Excellent summary by Alan Whitehouse

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. 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 pub
lishing 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.
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. Not yet available and will be released with SQL Server 2012. Will require SharePoint 2010 to utilize. 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 (Denali) The next release of Microsoft SQL Server. Formerly known by the project name Denali. Due out sometime in 2012. 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 upcoming Business Intelligence Semantic Model and Power View in SQL Server 2012. With the release of SQL 2012 model utilizing Vertipaq will commonly be 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 will commonly be 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
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 based queries. Referred to as the language of cubes. Link
Data Analysis Expressions Language (DAX) The language of writing SQL Server Analysis Services Vertipaq based queries. Used heavily with PowerPivot for Excel and PowerPivot for SharePoint. Link

Lasted Updated: October 13, 2011


PowerPivot Technical Diagram: PowerPivot Client/Server Architecture


PowerPivot Technical Diagram: PowerPivot Security Architecture


SharePoint topologies poster


One thought on “Microsoft Business Intelligence Stack 2012

  1. Good Article. About Some more and advance knowledge of Data Mining and the Business Intelligence Stack. and better techniques of Presentation of Data Mining and Technique and so many more helpful topics for you..Thanks a lot..

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s