SQL Server 2012 offers a plethora of new features, benefits, and functionality for Microsoft Business Intelligence solutions. This blog will identify these features and provide high level descriptions. Throughout the year, we’ll be posting more in-depth descriptions of these features in separate postings and linking to those postings from this master feature list.
These are the major enhancements to Business Intelligence that SQL Server 2012 brings to Microsoft BI strategists, architects, and developers and then ultimately to the end users and to the business. These features will drive efficiency, productively, and competitive advantage not only in the year 2012, but also for the coming years.
BI Semantic Model
The BI Semantic Model is one model for all end user experiences – reporting, analytics, scorecards, dashboards, and custom applications. All client tools in the Microsoft BI stack – Excel, PowerPivot, SharePoint Insights and Reporting Services (including Power View) – operate on this model. BI professionals can create the model in Visual Studio and deploy it to an Analysis Services server. The model can also be created by business users with PowerPivot for Excel and shared via PowerPivot for SharePoint.
Analysis Services Tabular Mode
Tabular models are in-memory databases in Analysis Services. Using state-of-the-art compression algorithms and multi-threaded query processor, the VertiPaq™ engine delivers fast access to tabular model objects and data by reporting client applications such as Microsoft Excel and Microsoft Power View. Tabular models are authored in SQL Server Data Tools (SSDT) using new tabular model project templates. You can import data from multiple sources, and then enrich the model by adding relationships, calculated columns, measures, KPIs, and hierarchies. Models can then be deployed to an instance of Analysis Services where client reporting applications can connect to them. Deployed models can be managed in SQL Server Management Studio just like multidimensional models. They can also be partitioned for optimized processing and secured to the row-level by using role based security.
The second version of Power Pivot builds on the first version and now offers diagram view, hierarchies, relationships in diagram view, multiple relationships, new DAX functions, perspectives, and KPIs. These are just some of the new features.
Power View (SSRS)
Power View provides intuitive ad-hoc reporting for business users such as data analysts, business decision makers, and information workers. They can easily create and interact with views of data from models based on PowerPivot workbooks or tabular models deployed to SQL Server 2012 Analysis Services (SSAS) instances. Power View is a browser-based Silverlight application launched from SharePoint Server 2010.
Self-Service Data Alerts (SSRS)
Reporting Services data alerts are a data-driven alerting solution that informs you about changes in report data that are of interest to you, and at a relevant time. Data alerts messages are sent by email. Depending on the importance of the information, you can choose to send alert messages more or less frequently and only when results change.
Interest in Hadoop is driven primarily by the need to handle large volumes of loosely or inconsistently structured data such as social network feeds, Web logs, email, documents, and other text-centric information. These data types can be used for applications such as customer sentiment analysis, but they could not be effectively managed with SQL Server until now.
The SQL Server columnstore index feature, code-named Apollo, stores data by columns instead of by rows, similar to a column-oriented DBMS. The column store index speeds up data warehouse query processing in SQL Server 2012 in many cases by a factor of 10 to 100.
Spatial data represents information about the physical location and shape of geometric objects. These objects can be point locations or more complex objects such as countries, roads, or lakes. A spatial index can more efficiently perform certain operations on a column of the geometry or geography data type. More than one spatial index can be specified on a spatial column.
Unstructured Data QueriesUnstructured Data refers to information that either does not have a pre-defined data model and/or does not fit well into relational tables. Unstructured information is typically text-heavy, but may contain data such as dates, numbers, and facts as well. This results in irregularities and ambiguities that make it difficult to understand using traditional computer programs as compared to data stored in fielded form in databases.
Data Quality Services
The data-quality solution provided by Data Quality Services (DQS) enables a data steward or IT professional to maintain the quality of their data and ensure that the data is suited for its business usage. DQS is a knowledge-driven solution that provides both computer-assisted and interactive ways to manage the integrity and quality of your data sources. DQS enables you to discover, build, and manage knowledge about your data. You can then use that knowledge to perform data cleansing, matching, and profiling. You can also leverage the cloud-based services of reference data providers in a DQS data-quality project.