This wiki is a transcript of a previously recorded video.

Related content assets:

  • Presentation: Exploring SQL Server 2012 Analysis  Services Tabular Modeling (SQL11UPD06-DECK-02)
  • Video: Exploring SQL Server 2012 Analysis  Services Tabular Modeling (SQL11UPD06-REC-03)

Exploring SQL Server 2012 Analysis  Services Tabular Modeling

Welcome to this presentation, Exploring SQL Server 2012 Analysis Services Tabular Modeling. My name is Peter Myers. I’m a SQL Server MVP and a Mentor with SolidQ.

In this presentation, we’re going to explore in detail what tabular project development involves. Be aware that this is a follow-on presentation. We did explore in the previous presentation what is new with improvements in Analysis Services. Very much key to that presentation was the introduction of the BI Semantic Model (BISM). We explored that there are three different development approaches, being PowerPivot, tabular project, and multidimensional project. And really, the focus in this module and presentation is on the tabular approaches with tabular project and PowerPivot and the DAX expression language with its enhancements.

The SQL Server 2012 Analysis Services release embraces the tabular model development. This approach, introduced with PowerPivot in SQL Server 2008 R2, is now available to the IT professional and extends beyond the features available in PowerPivot. PowerPivot has also been enhanced with new model design features and improved SharePoint integration. And the expression language DAX is also available to both model development approaches, has been enhanced in this release.

So, focusing on the tabular project development, a tabular model can be developed using a tabular project template in SQL Server Data Tools (SSDT). Be aware that the model designer does not include the database capability, so what that means is that as you develop a model, and load data into it, you must have connectivity to an Analysis Services instance in Tabular mode. Basically, in sync as you develop your model, it will go ahead and synchronize those changes and data loads into the database.

When it comes to testing your model (and viewing the data), this will be achieved through Excel by using an Office Data Connection (ODC) file that will connect to the workspace copy of the database that you’re developing. Be aware also that the code base used by the model designer that is hosted in SQL Server Data Tools is the same code base used by the PowerPivot Window. The new tabular design feature’s been added to both designers in the SQL Server 2012 release.

And so here in this slide, we get to take a look at the new features. There’s a diagram view, so we now have options in the way we that we develop the model. Do we see the data in the data grid view, that you may well be familiar with in the 2008 R2 PowerPivot Window? Or do we switch across to diagram view and see tables, columns, measures, KPIs, relationships? And often, you can take your selection based on preference. However, as we will discuss, there are certain things you can and cannot do in each view, but for the majority of things you’re likely to be developing, they can be done in either view. Measures, these are things that are used for the aggregations of your model, are defined now in a calculation area that is visible in grid view. Be aware that all calculations, be they calculated columns or measures, are defined in grid view, not diagram view. There’s also the ability to define multiple relationships between tables. There is a rule with this, and that is that one relationship must be active and all others will be inactive. It’s up to you to choose which is active. The active relationship will be used by default for model navigation. Inactive relationships can only be used in explicit calculations using DAX. We’ll explore that feature and function later in this presentation.

We can now mark our date table as a date table. We can sort columns in a table by another column in the same table. We can define multi-level hierarchies, and hierarchies are only defined in diagram view. There is now support for binary columns, which is typically useful for storing image data, which are then helpful for Power View, as we’ll explore in a later presentation. A new aggregate function allows us to create measures using the DistinctCount calculation, and measure format properties can then be defined in the model and will be honored by all OLAP clients and Power View.

Table, column, and measure descriptions can be defined, and Power View, for example, will surface these as tool tips. Key performance indicators can be defined. Perspectives can be defined. These allow narrowing the definition of the model resources. Connecting to a perspective will show you what’s visible to that perspective, and this’s typically used when you have multiple subjects in a single model and users don’t want to be confused or distracted by too many resources.

Reporting properties allow us to configure and optimize the model for reporting applications. And we’ll see that this is very useful for optimizing for Power View. We’ll cover that in a later presentation in this series.

And then the final three that we’ll point out are available for tabular project only. So everything I’ve read to this point are available for all tabular development, whether it’s PowerPivot or Tabular Project. But for the IT professional, there are extended capabilities – for scalability, there’s the ability to define table partitions, which allows us then to more effectively process a subset of the table rather than the entire table.

Security roles for row-level security can also be defined. In this way we can limit access to data to different groups of users. There’s a support for static and even dynamic roles. For example, a dynamic role could be, the current user can only see the data related to the sales territory they belong to.

Finally, In-Memory and DirectQuery access modes are supported for Tabular Project. In-Memory is only supported for PowerPivot, but the pass-through mode of DirectQuery supports passing through. Essentially, Analysis Services queries the underlying data source to deliver low latency data results.

So when it comes to developing a tabular project, it’s pretty much this formula. Create the tabular project in SQL Server Data Tools (SSDT). Import data to create tables. Define relationships, enhance the model with calculated columns, hierarchies, measures, Key performance indicators, and perspectives. If necessary, hide tables, columns and measures from the client tools. Define table partitions; define roles. And then deploy that project to a tabular mode instance of Analysis Services.

When it comes to managing tabular databases in SQL Server Management Studio (SSMS), capabilities include restoring from a PowerPivot workbook, managing connection strings, adding and managing table partitions and adding and managing roles. We can process the database, its table and its partitions, and similar to multidimensional, you may be familiar with scripting. So using XMLA, we can script the database or specific objects or commands.

Now DirectQuery, available only for tabular databases, and configured in tabular projects. Similar to multidimensional models, tabular models can cache data or pass through. When we cache, we refer to this as In-Memory query mode. For pass through, this is referred to as DirectQuery. In-Memory requires processing to keep data up to date. But DirectQuery retrieves data direct results from the underlying data source. Now this can perform particularly well with a new feature delivered in SQL Server 2012 Enterprise Edition known as the columnstore index. Now, it’s using the exact same technology that PowerPivot uses, and that the In-Memory query mode uses on the tabular mode instance.

So what we can do is query a model that uses DirectQuery mode, and in turn, Analysis Services will query the underlying SQL Server tables that have columnstore indexes to find. So this doesn’t require duplication of data. We can ensure the equally high performance through the compression in columnstore that we get down at that data source level.

Benefits of DirectQueries. This is great for non-expert SQL professionals who simply want to build a model on top of data but don’t want to get involved in processing or maintaining the synchronization of data. So a great example here is, you could quickly create a model, configure DirectQuery mode, deploy, secure, and then allow users in Power View to go ahead and explore the data with relative ease. It also allows an easy and fast way to build intuitive data access on top of any SQL Server relational database.

Realizing investments in your data warehouse solutions by creating tabular model with DirectQuery. Avoid the complexity of maintaining a cache as well as the original data store, and it will deliver low latency query results. Be aware, however, that there are certain limitations of DirectQuery, in that the model may only be queried using DAX queries – no MDX queries are supported. And that would mean that a model using DirectQuery could not be queried by an OLAP client like a PivotTable. Permissions cannot be defined at the tabular database level – they must be defined at the relational store. Calculated columns in the model are not supported. And it only supports a single connection to a SQL Server relational database. Copy and paste tables are not supported. And hybrid mode is not supported whereby tables using different query modes.

In a separate recording we have a demonstration that shows how to develop a data project using SQL Server Data Tools. And I would encourage you to watch this to see, end-to-end, how to create a project, import and load data, create relationships, enhance the model with calculations, hierarchies, and then to go ahead and deploy this to the server and analyze it using Excel.

Moving on to the topic of PowerPivot. PowerPivot in SQL Server 2012 builds upon the strengths of the SQL Server 2008 R2 feature set. There are new capabilities and features for both of the add-ins, the Excel and SharePoint.

So focusing on the Excel add-in, it includes the same designer and the same features available to the IT professional in the tabular project, with the notable exception of these four things. In the PowerPivot window, you cannot configure table partitions, roles, DirectQuery query mode, and of course you can’t integrate this with source control. Supported features not available in the tabular project, however, include linked tables from the Excel workbook, and the ability to define implicit measures, as defined using the PowerPivot field list. In fact, this is not a great thing, but for the target audience that may not have a great appreciation of what measures are, when you add a numeric column to the values area of a PivotTable, it will implicitly create the measure. In the tabular project, you can only explicitly create measures. So note that it is possible to go ahead and restore a PowerPivot workbook directly on an Analysis Services instance, and then once you’ve restored it, you can go ahead and define partitions and roles or configure DirectQuery. But it’s a one-way process – you can’t take that PowerPivot workbook and extract it back out.

PowerPivot Window enhancements or perspectives and reporting properties are available, but they are hidden by default. They’re available on the Advanced ribbon, and you need to turn this on should you require the functionality. The way that you turn it on is that you then go to this menu and you switch to Advanced Mode. And when you switch to Advanced Mode, you can then select the Advanced tab, to configure perspectives, to show any implicit measures that you have in the model, and we’ll talk about the Summarized By and the Reporting Properties in a later presentation when introducing Power View and optimizing a tabular model for Power View.

Enhancements in Excel for PowerPivot. So the ribbon – the PowerPivot Ribbon in Excel – allows you now to create, edit and delete KPIs. The PowerPivot field list has been enhanced to allow the selection of a perspective if they are defined in the PowerPivot workbook. It also displays hierarchies and their levels, and KPIs, and the ability to create a KPI based on a measure. When creating measures inside Excel, the Measure Setting window does allow you to configure the format for the value.

And now switching to the PowerPivot for SharePoint add-in, this does require SharePoint Server 2010 Service Pack 1 Enterprise Edition. The add-in has been updated to include new administrative capabilities, new service application configuration options, a new set-up experience, and a Power View report authoring capability direct from the PowerPivot Gallery.

So, the new administrative capabilities. Lots of bug fixes, the default algorithm used for the help-based round-robin approach has been improved, and many more service application configurations have been added – new service application configurations options with the service application – so the amount of caching done on the application servers, cleanup of database caching, the ability to disable refresh jobs if they’re constantly failing, new capacity planning health rules for CPU, memory and disk space, support for transparent connectivity through .bism connection files (that we’ll talk about in a later presentation with Reporting Services) and a new mid-tier mini dump facility.

New setup experience. So there are improvements on the way you can set up and also repair a PowerPivot installation. So the PowerPivot configuration tool – when you load this, you can point to a SharePoint farm, interrogate the farm, and then it will provide the steps required to install or repair a PowerPivot installation.

When we take a look at the PowerPivot Gallery, when you see a workbook document, we have the three different options here. The ability to analyze in Excel has always been there, but this one, replacing the Report Builder report, is the ability to create a Power View report based on the workbook. The Manage Data Refresh that we have is the third option is the same as it was in the prior release.

The last topic then in the presentation is to explore DAX enhancements. So Data Analysis Expressions is fully supported in both in tabular BI Semantic Model project types, being the PowerPivot workbook and tabular project. The usefulness of DAX has been enhanced with the addition of new functions, and one existing function has been enhanced. DAX Query, a new query language, is also discussed in this topic.

So this presentation does go into some detail of some of the more prominent functions, but not all of them. So there are filter functions that have been added. I’ll point out the USERELATINSHIP() here. We talked about the ability to define multiple relationships between two tables, that there must be one active and the rest are inactive. The only way to make use of an inactive relationship is by using an explicit calculation that uses the USERELATIONSHIP() function.

Information functions. So USERNAME(), we can now look up the authenticated user, CUSTOMDATA(), we can pass data through a connection string and we can retrieve that through a DAX expression. And LOOKUPVALUE() allows us to look up a value in a column based on some criteria. So USERNAME() is particularly useful when it comes to dynamic security. If you store, for example, in your model, a list of your employees and their account, you can then use that to say, we’ll limit access to related sales information based on the employee’s account. That way, effectively, the user can only see their contributions.

Now, parent-child functions are added here to allow us to simulate parent-child hierarchies. Be aware that parent-child functionality and hierarchies is natively supported in multidimensional projects, and the best that we can do in tabular is to simulate them using this library of functions. So the PATH(), the PATHITEM(), the PATHITEMREVERSE(), the PATHLENGTH(), the PATHCONTAINS(), are a library of functions that allow us to naturalize a self-referencing relationship into fixed columns and then with those columns we can assemble multi-level hierarchies. It’s not true parent-child but it certainly facilitates the creation of hierarchies that allow us to explore across those relationships.

We have the SWITCH() function has been added, so it allows us to evaluate based on an index, and return, in this case if month is 5, it will return May. Note the unknown month number at the end, that if there is no match it returns that string by default.

Statistical functions. So DISTINCTCOUNT(). We’ve already referred to the ability to create measures that use DISTINCTCOUNT(), and that’s achieved with this new DISTINCTCOUNT() function. I’ll just point out that we have ranking; we also have standard deviation and variation and TopN.

SEARCH() is the one enhanced DAX function that includes an optional string parameter that is returned when a substring match is not found.

To the topic of DAX Query. DAX supports a new query syntax – as we now understand is used by Power View. Though tabular, the syntax is very, very different from SQL. Essentially, a DAX table expression is evaluated. These queries could define helper objects, such as measures, variables or views, as part of their final table expression. And queries can also be parameterized.

So here we see the syntax, and in its shortest form, a DAX Query can consist of an evaluate clause and a DAX table expression which could simply be the name of a table. And that will return a two-dimensional results set that can be consumed in the same way that a SQL Server relational result sets can be consumed.

For the developers, what can you do with tabular project development? Well, I think a great example is that you can easily develop proof of concept tabular data models, and this way you can show to a potential customer what they can achieve with this technology very, very quickly, within a matter of minutes once you know what you’re doing. You can develop tabular models based on existing line-of-business solutions, you can support self-service data modeling. So for those in particular that are using Power View, you can facilitate the creation of models by exposing database views, developing customer data services, and libraries of data feeds to support their model creation.

As developers, when it comes to embedding query results into solutions, for multidimensional, we still have ADOMD.NET, and for tabular you’ll be using OLE DB .NET data provider. To automate model management into solutions, we have Analysis Management Objects (AMO) that works for both tabular and multidimensional databases.

There is a demonstration that will go through two examples of the new improvements to Data Analysis Expressions. We’ll take a look at using the USERELATIONSHIP() function to recreate calculations based on an inactive relationship. And we’ll also construct a parent-child hierarchy, so to speak, by using the PATH() and PATHITEM() functions. This is not truly a parent-child hierarchy, but it naturalizes the relationship to produce a fixed-level hierarchy. And in this demonstration, you’ll see how that can be put together.

So in summary, the tabular project model designer presents a very rich, interactive and intuitive interface developed using the same code base as the PowerPivot Window. We saw that the DirectQuery storage mode can deliver low latency query results and is available to tabular databases. The PowerPivot for Excel add-in includes many new features and capabilities that are also available to the tabular project model designer. The PowerPivot for SharePoint add-in includes new administration and set-up capabilities. There are numerous new DAX functions that have been added to extend the usefulness of the expression language. And DAX Query supports retrieving tabular query results.

The resources include the TechEd North America 2011 session recording. The Analysis Services Team blog is a great and rich resource for what’s coming, for challenges that you might discover, and just for an understanding of how the team developed things, and why they did so. Books Online for SQL Server include comprehensive documentation for Analysis Services. For working with PowerPivot, there’s an additional TechEd North America recording. There’s a PowerPivot website. The Analysis Services and PowerPivot Team blog and Dave Wickert’s PowerPivot Geek blog is a great source of information about PowerPivot and what you can achieve with both add-ins.

In addition, enriching BI Semantic Models with DAX, there’s a presentation on this. Kasper de Jong’s PowerPivot blog goes beyond into great detail, and especially the new functions that are delivered in SQL Server 2012. And finally, the DAX whitepaper in the tabular BI Semantic Model includes comprehensive documentation ideal for beginners in DAX to take them through the understanding about what DAX is designed to do and how to implement solutions with DAX. It also has been extended beyond the 2008 R2 release to include the new features and functions delivered in SQL Server 2012. Notably it includes a great sample Excel workbook that allows you to see any of the examples described in the whitepaper, and you can also play around with the sample data there and test out the functionality for yourself.

There are a series of Hands-On Labs on developing a Tabular BI Semantic Model that is a very, very comprehensive, what we have considered to be a 90-minute lab that takes you right through the process of creating a tabular project right through to deployment. It includes the discussion and examples on partitions and security roles.

There’s also a lab on creating a tabular BI Semantic Model with PowerPivot. And then three additional labs using Excel, Reporting Services, and PerformancePoint Services (PPS) show that we can base solutions off using a tabular BI Semantic model. And this just reinforces the point that all BI Semantic Models expose a multidimensional interface, and that, existing OLAP clients, like these three, can continue to be used against a tabular BI Semantic Model.

Thanks very much for watching this presentation.

Return to SQL Server 2012 Developer Training Kit BOM (en-US)