Monday, May 13, 2013

Microsoft Business Intelligence (BI) Offering

Microsoft offers some nice capabilities using tools like Excel and SharePoint that we are already familiar with. If your company is already vested in Microsoft technologies, the MS BI tools are worth looking at.

I have summarized (not pretty diagrams or anything) the basics below for easy consumption. I also have a PowerPoint slide with nice architctural diagrams, etc. Click here to view the PowerPoint slides.

Below is an overview of what they offer:

  • PerformancePoint
  • Excel Services
  • Power view
  • PowerPivot for SharePoint
SQL Server 2012
  •  SQL Server
  • Reporting Services
  • Analysis Services
  • Data Mining
  • Master Data Services
  • Data Quality Services
  • Integration Services
  • Data Warehousing
  • SQL Server Data Tools (formerly BI Development Studio)
  • Excel
    • Data Explorer for Excel
    • PowerPivot for Excel
    • Power View for Excel
    • Data Mining for Excel

Below are some more details on the specific products

Power View

  • For both SharePoint and Excel
  • Part of Microsoft SQL Server Reporting Services 2012
  • It provides an ad-hoc visualization experience in SQL Server Reporting Services
  • Power View provides intuitive data visualization of PowerPivot models and SQL Server Analysis Services (SSAS) tabular mode databases
  • Users can edit views or create new ones
  • Very interactive
  • Very easy to use
  • Encourages Data Exploration and Visualization
  • Also available in Excel as an Add-in
  • Click to filter technology
  • Automatically creates data model


  • For both SharePoint and Excel
  • PowerPivot is an add-in that lets end users gather, store, model, and analyze large amounts of data in Excel
  • Use Excel to create a PowerPivot
  • Save to SharePoint for others to access.
  • Viewable in the SharePoint Gallery
  • In-Memory technologies allows working with Millions of rows of data
  • Mashup data from different data sources
  • Create Pivot tables, Charts, and PKIs on millions of rows of data.


  • Aimed at Advanced PowerUsers, but more likely will be used by developers
  • Integrated into SharePoint
  • Provides designer as well
  • Left-click Drill down into data
  • Types of visualizations
    • Dashboard
    • ScorecardReports
  • Highly Interactive
  • Limited customization of the look and feel of report or dashboard
  • Uses OLAP data sources
  • Special designer accessible via SharePoint to design everything

Excel Services

  • Excel Services on the other hand is a very power user-friendly technology.  Those familiar with Excel and PivotTables should take very little time to be able to build very sophisticated reports.  SharePoint 2010 renders Excel reports and dashboards as web pages which makes this technology very easy to deploy. 
  • Good choice for self-service BI scenarios
  • Show an Excel sheet or workbook on a SharePoint site in a web part.
  • Can show Power View Excel sheets on the web and keep the high level of interactivity.

Data Mining add-in for Excel

  • Uses Analysis Services on the backend
  • Excel User interface
  • Supports:
    • Classify 
    • Estimate 
    • Cluster 
    • Associate 
    • Forecast 
    • Other advanced algorithms

Data Explorer add-in for Excel

  • Still in preview status 
  • Explore data 
  • Brings new data sources to Excel import options. 
  • Like SSIS, but for Excel 
  • ETL tool for Excel 
  • Extract: DB, Excel, Text file, web, OData, SharePoint lists, Active Directory, Multiple data source support 
  • Transform: Cleanse, apply business rules, aggregate, merge, append, mashups, Navigate through data (even joined data) 
  • Load: Load data into Excel once massage it to what we want

Master Data add-in for Excel

  • Connects to MDS data sources / models
  • Allows end users to create and maintain data in MDS using Excel

For lots of resource on the above click here.

No comments: