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
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
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.