Guide to SQL business intelligence, analytics and data visualization
A comprehensive collection of articles, videos and more, hand-picked by our editors
SQL Server 2012 delivered some new features that enhance and extend its capabilities well beyond SQL Server 2008...
and 2008 R2. This was especially true in the area of business intelligence (BI), where improvements delivered reporting and analytic capabilities more extensive than anything we've seen to date. Five of those SQL Server 2012 business intelligence features in particular make it well worth serious consideration for organizations looking to beef up their BI.
BI Semantic Model
SQL Server 2012 introduced the BI Semantic Model (BISM) to provide a conceptual framework for delivering BI to supported analytic and reporting platforms. Although the BISM itself is not a physical entity, it provides the structure for creating physical models in SQL Server Analysis Services (SSAS) and PowerPivot for Excel. In SSAS, you can create two types of BISM models: multidimensional and tabular. In PowerPivot for Excel, you can create only tabular models.
The multidimensional model is mostly business as usual. It is consistent with the Unified Dimensional Model (UDF) in earlier SSAS versions, with data organized into cubes, dimensions and measures. Also like UDF, the multidimensional model uses the Multidimensional Expressions (MDX) language to interact with multidimensional data stores, providing a powerful environment for performing complex analytical operations.
The real BISM story in SQL Server 2012 is the tabular model, which, unlike the multidimensional model, organizes data into tables with rows and columns, much like you find in a relational database. In addition, the tabular model uses the Data Analysis Expressions (DAX) language for data access, and can retrieve data from a variety of sources, including relational databases, SSAS cubes, text files and PowerPivot workbooks. The tabular model was introduced in SQL Server 2008 R2 with the release of PowerPivot for Excel and PowerPivot for SharePoint. The model uses the xVelocity engine (formerly the VertiPaq engine) to cache data in memory while taking advantage of state-of-the-art compression and scanning algorithms to support high-performing analytics.
The xVelocity technology brings us to the second of five top SQL Server 2012 business intelligence features: the nonclustered columnstore index. Like regular nonclustered indexes, a columnstore index is defined on one or more of a table's columns. Unlike nonclustered indexes, the data exists in a columnar format. In other words, the data in each indexed column is stored in its own column within the index. If a table contains more than a million rows, the index is broken into segments, but the columnar structure is preserved. Nonclustered indexes, on the other hand, store data by rows.
In addition to columnar storage, columnstore indexes use xVelocity's advanced storage and compression capabilities. Together, these features can significantly improve performance for queries that must retrieve and process large data sets, the sort of queries typically associated with data warehousing, where data is often grouped, filtered, aggregated and joined across multiple tables. You'll often see significant performance gains because only applicable columns are brought into memory, the data brought into memory is compressed more efficiently, and queries against the data are optimized for analytical operations.
Data Quality Services
To support effective mining, analysis, reporting and warehousing, the data itself must be correct. But pulling data from multiple systems in which different standards have been applied often results in errors, corruption and inconsistencies. That's where Data Quality Service (DQS) comes in. New to SQL Server 2012, DQS provides the tools needed to resolve data inaccuracies, inconsistencies and duplication. Data stewards and IT professionals alike can use DQS to cleanse data and ensure it's suited for their BI and other business needs.
The DQS environment is made up of two components: the Data Quality Server and the Data Quality Client. The server does all the heavy lifting. It hosts the DQS engine, stores project information and manages the knowledge bases. A knowledge base is a repository of information, or knowledge, that identifies potentially inaccurate data and offers possible corrections. The knowledge base is made up of one or more domains that each contains knowledge for a specific type of data. For example, a domain might contain the knowledge needed to ensure that Canadian provinces are all referenced in the same way and that only the correct provinces are included. The Data Quality Client provides an interface for administering DQS, managing the knowledge bases and running data quality projects that apply the knowledge to the data.
Another new addition to SQL Server 2012 BI features is Power View, a SQL Server Reporting Services (SSRS) add-in for SharePoint Server. Power View is built on the Silverlight application framework and provides end users with a Web-based tool for exploring data and building ad hoc reports that offer a rich assortment of data visualizations. Creating a Power View report is similar to building a pivot table or pivot chart in Microsoft Excel. Users always work with current data and never have to switch between views, unlike Report Builder and Report Designer in SQL Server Data Tools.
Users launch Power View from a SharePoint site configured with the SSRS add-in. Consequently, both SharePoint Server and SQL Server must be installed. In addition, a developer or administrator must set up at least one data model. The data model serves as an interface between the reports and the data sources, making it easy for users to access the data they need, without having to understand the underlying data structure. To create reports, users need only sign on to the SharePoint site (via a Silverlight-enabled browser), locate the applicable data model in a document library or PowerPivot gallery, and launch Power View from that model. They can then create their reports by using simple drag-and-drop operations.
SQL Server has its roots in structured, relational data, but more than ever, BI is incorporating unstructured data into its analysis and reporting operations. One way SQL Server accommodates the influx of unstructured data is by providing full-text search capabilities, built into the database. Full-text search allows queries to perform word searches against data in character-based columns, including FileStream columns that point to files of unstructured data. However, SQL Server 2012 takes this a step further with the introduction of Semantic Search, a feature that extends full-text search in order to extract and index statistically relevant key phrases in unstructured documents.
Semantic Search moves beyond full-text search by focusing on a document's meaning, rather than on simply its words. This makes possible such features as automatic tag extraction, discovery of related content and hierarchical navigation across similar content. As a result, Semantic Search can help users find documents that are similar or related to the ones they're searching for.
What makes Semantic Search even more powerful is the introduction of another new SQL Server 2012 feature: the FileTable. A FileTable is a type of table that extends FileStream capabilities in order to support Win32 application programming interfaces, or APIs. Every row represents a file or directory and can be accessed directly from a Windows application. From the application's perspective, the files and directories appear as if they're stored on the file system, rather than within the database. When combined with FileTable, Semantic Search becomes an important asset for supporting applications that work with unstructured data and mine relevant information.
SQL Server 2012 BI
The five SQL Server business intelligence features we've described here could prove invaluable tools when you implementing a BI platform. However, what we've covered represents only some of the BI-related features that have been updated or added to SQL Server 2012. For example, SSAS includes new DAX functions and has improved resource usage reporting for multidimensional databases. SQL Server Integration Services, or SSIS, now includes a DQS Cleansing transformation and has improved the memory usage for the Merge and Merge Join transformations. And SQL Server SSRS has re-architected its SharePoint integration in order to better leverage many of the SharePoint IT features. Yet it's the five SQL Server 2012 BI features we've described here that you might find particularly noteworthy, and they should certainly be part of the BI equation if you're thinking about moving to SQL Server 2012.
About the author
Robert Sheldon is a technical consultant and the author of numerous books, articles and training materials related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation.