Sql Server 2012 Business Intelligence

Key Points

  • SQL Server 2012 has a licence option specifically targeted for business intelligence
  • SQL Server 2012 extends the self service capabilities of the Microsoft platform with PowerView and the additional features of PowerPivot in the 2012 version
  • You may already hold licenses for an array of Microsoft Business Intelligence Products without realising it.
  • SQL Server 2012 is tightly integrated with SharePoint 2010 and Office 2010 and forthcoming Office 2013. Taken as a whole, the product set offers a solution for the vast majority of typical BI challenges

SQL Server 2012 has an array of tools to support all aspects of the Business Intelligence lifecycle. Contact CMBI for a demonstration of these capabilities, advice on deployment or solution design for any of the SQL 2012 or SharePoint 2010 components

About CMBI

Business Intelligence Tools

Many enterprise BI vendors, including Microsoft, now support a dizzying array of tools for business intelligence and data warehousing. The real trick is matching the correct tool with the business problem. We provide a brief description of some of the major components of the SQL Server 2012 stack below. For a more generic vendor neutral introduction to the purpose and use of each of the BI components see our articles on the Generic BI Solution Architecture, BI Technology Strategy, and BI User Tools.

SQL Server 2012 Components

SQL Server Database Engine

Build your data warehouse for large or small deployments. Table partitioning, compression, and the MERGE statement are key features that assist with typical data warehouse challenges.

Integration Services (SSIS)

Extract data from your operational systems, spreadsheets, and external sources and load them into the data warehouse. Enhance data quality with fuzzy logic and data cleansing tools. Schedule and manage simple or complex ETL loads.

Reporting Services (SSRS)

Create standard parameterised reports or more complex dashboards with navigation, drilldowns, KPIs and indicators. Schedule reports for delivery by email or create data driven subscriptions.

Analysis Services Cubes (SSAS)

Create a data model that can hold billions or records whilst being fast and easy to query for end users and analysts. Analysis Services databases can be queried using Excel Pivot Tables, Performance Point, Reporting Services, and many more third parties BI tools including Strategy Companion Analyzer and Tableau.

Analysis Services Data Mining (SSAS)

Create predictive models for grouping customers, market basket analysis, trend prediction, and text mining. One of the most undervalued tools in the SQL Server BI stack is the data mining. It is easier to use than you might think and can answers questions that would be very difficult to address with traditional query based analysis.


PowerPivot is a free Excel 2010 Add-In that allows end users to create and automate complex reports and analysis. PowerPivot can handle very large datasets - far bigger than you would traditionally use in Excel. PowerPivot users work within the Excel environment to extract data from databases and flat files, integrate the data, and create dashboards and reports through a Pivot Table interface. The user can access all the powerful formating and calculation features of Excel without the repeated manual effort of data integration and formating associated with traditional reporting in Excel.

SharePoint 2010 Business Intelligence Components

You can create a powerful business intelligence solution with standalone SQL Server 2012 products. However, if you add SharePoint 2010 into the mix you have an array of choices for collaboration, self services BI, publishing, and interactive dashboards. More than ever before SQL Server and SharePoint are tightly integrated.

PowerPivot Gallery

PowerPivot models and reports created in Excel can be deployed to SharePoint users over the intranet

Excel Services

Using Excel 2010 you can create interactive dashboards with Analysis Services or PowerPivot. But how do you publish these insights so that everyone else in the organisation can benefit? Excel Services is the answer. You can chose to publish a whole workbook, individual worksheet, or even a single chart. End users can browse and interact with your dashboard using their web browser but with no requirement for a local Excel install.

PerformancePoint Services

PerformancePoint is a high productivity tool for creating KPIs, indicators, and interactive dashboards. If you have an existing PowerPivot or Analysis Services cube then in just a couple of hours you can design a dashboard with a myriad of end user user interactivity options including the ability to change chart type, drill up/down/across, and explore the data behind any data point on the dashboard.


PowerView is new to SQL Server 2012 and is fully integrated into SharePoint 2010. It is an end user dashboard design and data exploration tool that allows you to create attractive charts and tables with just a couple of clicks. One popular feature is the ability to animate a graph to show the development of a trend over time.

About CMBI

CMBI designs and develops business intelligence and analytics solutions for clients in Sydney and regional NSW. CMBI technology expertise includes Power BI, Power Pivot, Excel, SQL Server, Analysis Services, SharePoint BI, and Tableau.


See Also