Two questions I tend to get asked over and over again are:
- Exactly what products are you talking about when you refer to the Microsoft BI stack?
- 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.
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. 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