If you’re using SQL Server 2012 for data analysis, you have several new options available to use. The ones that have gained most headlines are Power View for displaying data in web apps and PowerPivot for data cube analysis in desktop apps, but there are deeper changes that will probably prove more useful for developers.

If you’re using SQL Server 2012 for data analysis, you have several new options available to use. The ones that have gained most headlines are Power View for displaying data in web apps and PowerPivot for data cube analysis in desktop apps, but there are deeper changes that will probably prove more useful for developers. Of course, as a developer you now work with SQL Server using SQL Server Data Tools. This is a single shared set of tools for SQL Server and SQL Azure database and application development. 

The old SQL Server Analysis Services has been split in two in the new version, with separate handling of data in in-memory tables and multidimensional data. Multidimensional models offer great ways to analyse data, but setting up the models correctly isn’t easy and many companies ignore the analysis options so miss out on the chance to explore the trends and patterns in their data. SQL Server 2012’s tabular model is designed to overcome this by giving an easier to understand alternative. Tabular models are in-memory databases that make use of the xVelocity in-memory analytics engine that was called VertiPaq in the pre-release Denali test version of SQL Server.

The idea behind VertiPaq is that in many cases, columns contain many copies of the same data - if you’re storing gender data, for example, you’ll have lots of entries that are either Male or Female. If the data engine can store similar data in a more space efficient way and compress the data, it’s possible to keep the entire table in memory so you get extremely fast data retrieval compared to disk-based storage. PowerPivot is based on the same engine, and you can work with both using the DAX language. Both PowerPivot and Tabular Models in this release provide support for hierarchies, KPIs and perspectives.

You might have encountered the idea of a Key Performance Indicator (KPI) in business as a quantifiable measurement for gauging business objectives. In Tabular Models, a KPI is used to work out how well the actual values for something being measured meet a target. Hierarchies, as the name suggests, can be used to define the relationships between columns. An example would be a Calendar hierarchy, where you have the Calendar Year as the highest ‘parent’ level, with Month, Week, and Day as child levels going down the hierarchy. When used for analysis a business user could select the Calendar Year from a field list to get all the levels for their PivotTable, or expand the hierarchy and pick a particular level for inclusion.

Perspectives are designed to give a simpler view of a tabular model so that business users who only need to work with a small part of the model can do so. In a perspective, tables, columns, and measures (including KPIs) are defined as field objects, and you can then set up perspectives for different user groups, so a sales department could be given a perspective that doesn’t have any financial or employee data, for example.

Tabular models have two data access modes, Cached and DirectQuery. Cached mode lets you bring data from multiple sources including relational databases, data feeds, and flat text files. DirectQuery mode gives you a way to let client applications work with the underlying data directly in the SQL Server relational source.

SQLServer and SharePoint Connections

If you’re keen to find out more about getting the best from SQL Server 2012, or you want to dig deep on SharePoint, the sessions at the SQLServer and SharePoint Connections conference are designed with you in mind. The conference at Berchtesgaden in Germany has sessions by some of the industry’s top international speakers. The twin topics of SQL Server and SharePoint will be the focus of two days of sessions on Tuesday, May 8th 2012 and Wednesday, May 9th 2012.

In a session at the conference called “What’s New in SQL Server Analysis Services 2012”, Chris Webb looks at the two difference models of working in SQL Server Analysis Services. He discusses the changes to the Multidimensional model, gives an overview of the Tabular model, then looks at their relative strengths and weaknesses so you can decide which one is appropriate for your project.

There’s also a useful looking session providing an introduction to DAX queries and calculations, again from Chris Webb. DAX is Microsoft’s new multidimensional query and calculation language, used in PowerPivot and the Analysis Services 2012 Tabular model. This session will introduce basic concepts in DAX and show how to write DAX queries and calculations.

ColumnStoreIndexes are another addition to SQL Server 2012 that comes courtesy of the xVelocity technology. In an xVelocity memory optimized columnstore index, the data is grouped and stored for each column, then all the columns are joined to create the whole index. This is the opposite way of working to a traditional index where the data for each row is grouped and stored, then the rows are all joined to create the index. Depending on your data, a columnstore index can give you much faster query execution times, especially if you’re working with large data warehousing data sets.

In a session called “Doing fast! Optimizing Query performance with ColumnStoreIndexes in SQL Server 2012”, Margarita Naumova will show how columnstore indexes work, and how they can limit or eliminate the need to rely on pre-built aggregates, including user-defined summary tables, and indexed (materialized) views.

There are lots more SQL Server sessions, but as a final example of what sort of areas are covered, those of you who were inspired by Ian Stirk’s articles on DMVs might be interested in “DMVs - Best of the Bunch: Troubleshoot Your Data Platform Like a Pro” by Satya Jayanty. Dynamic Management Views (DMVs) give you a way to access information about the way your database is working within SQL Server. When you run a query on a SQL Server database, various pieces of information about the query and its performance are stored by SQL Server, and can be accessed by you. DMVs are basically SQL views that can be used to analyse queries with the aim of improving performance, troubleshooting problems, or gaining a better insight into how SQL Server works.

The session will look at some of the most useful DMVs you can use to help you tackle performance problems, learning about your instance’s current health, and to monitor what your server is doing. The session covers the DMV capabilities of both SQL Server 2008 R2 and SQL Server 2012.

Other interesting looking sessions at the conference include two T-SQL offerings by Constantin Klein. In the first session, “New T-SQL Features in SQL Server 2012”, Constantin looks at new T-SQL features that have been added such as SEQUENCE, THROW, OFFSET ... FETCH NEXT, with info on how they work and what you can do with them.

The other T-SQL session by Constantin, “SQL Server for Developers”, is a more general look at what T-SQL has to offer a developer working with SQL Server, with tips and tricks to make your job developing for SQL Server easier.