Expert Cube Development with SSAS Multidimensional Models (2014)

Appendix A. DAX Query Support

Microsoft released some major new functionality in Analysis Services 2012 SP1 Cumulative Update 4: the ability to run DAX (the query and calculation language of Analysis Services Tabular models and Power Pivot) queries against an Analysis Services Multidimensional cube. At the time of writing, this functionality has not been released in a service pack, but we expect it to be incorporated in Analysis Services 2012 SP2 at some point in the year 2014; it is only available to users of BI Edition and Enterprise Edition.

The reason this functionality has been implemented is that it allows Power View, Microsoft's new data visualization tool, to work with Analysis Services Multidimensional; Power View generates DAX queries, and this meant that originally it could only be used to query Analysis Services Tabular or Power Pivot data sources. At the time of writing, only the standalone version of Power View that can be launched from SharePoint has been updated to support Analysis Services Multidimensional as a data source, but we expect that at some point soon Power View sheets in Excel 2013 will be updated too.

Implementation details

For the most part, this functionality is not something you need to worry about as a cube designer: it just works. However, it is helpful to understand how Multidimensional objects map onto the concepts found in the DAX language.

Mapping Multidimensional objects to Tabular concepts

The following table shows how all of the major objects found in Analysis Services Multidimensional and MDX are translated to Tabular objects for the purposes of writing DAX queries:

The contents of this table can be summarized very easily: when you view a Multidimensional model through Power View, all measure groups and dimensions look like tables, measures are measures, and hierarchies are hierarchies.

Unsupported features

Some functionality in Analysis Services Multidimensional is not supported for DAX queries and Power View, but not much:

· Cell security is not supported at all. If a user is a member of a role that has cell security applied, they will not be able to connect via Power View or run DAX queries.

· Some measure format strings, including some of those that return string, date, or Boolean values such as True, False, On, or Off, will not work. Format strings applied to null values are likewise ignored.

· Calculated measures are fully supported, but calculated members on dimensions other than the measures dimension are only partially supported. Calculated members on attribute hierarchies of non-measures dimensions will only appear if they are a child of the All Member and there is at least one other real member, or if there is no All Member and there is at least one other real member. Furthermore, the attribute hierarchy with the calculated member cannot be the key attribute of the dimension unless the key attribute is the only hierarchy on the dimension. Calculated members on user hierarchies and parent/child hierarchies are not supported. This means that most of the Calculation dimension techniques described in Chapter 6, Adding Calculations to the Cubewill work with Power View.

· Parent/child hierarchies are supported, but they appear as flattened structures with repeating values. This is not very user-friendly.

· Certain DAX functions such as Path() are not supported.

· There is no way to use Actions in Power View.

New functionality in Analysis Services

There are two small new pieces of functionality in Analysis Services that support specific Power View features:

· To enable Power View to use an attribute in a map, you have to set the following:

· The dimension's Type property to Geography

· The attribute's Type property to something in the Geography category, such as Country

· To enable Power View to treat the name of a member on an attribute hierarchy as a URL pointing to an image so that the image can be displayed in a dashboard, set the attribute's Type property to Image\ImageURL

Connecting Power View to a Multidimensional model

To connect Power View in SharePoint to a Multidimensional model, you must follow these steps:

· Create a new data source of the type Microsoft BI Semantic Model For Power View.

· Create a connection string as normal, supplying the name of the instance and the database that you wish to connect to. However, there is one more important step: since Power View can only connect to one cube at a time, you also need to set the Cube connection string property to the name of your cube. Here's an example of what a connection string might look like: Data Source=MyServerName; Initial Catalog=MySSASDatabaseName; Cube=MyCubeName.

· You may optionally also choose to set other connection string properties. For example, if you have set up translations on your cube and you want Power View to show cube and dimension data in a given language, you can set the Locale Identifier connection string property.

Running DAX queries against a Multidimensional model

Rather than using Power View, in some cases, you may want to be able to run your own DAX queries against a Multidimensional model rather than using MDX. Scenarios where this might make sense include when you are building a SQL Server Reporting Services report and you find that a DAX query runs much faster than an equivalent MDX query, or you find that it's easier to write a calculation needed for your report in DAX rather than in MDX (although it is possible to declare DAX calculations in MDX queries—seehttp://tinyurl.com/DAXinMDX).

Executing DAX queries

DAX queries can be run from an MDX query window in SQL Server Management Studio, just like an MDX query. However, the problem with doing this is that you will see MDX metadata in the Metadata pane rather than DAX metadata, and this makes composing your query difficult. As an alternative, you can use DAX Studio, a free, community-developed Excel add-in that can be downloaded from http://tinyurl.com/DAXStudio. DAX Studio makes it easy to write DAX queries and displays the result of your query either in a grid or in an Excel worksheet. If you are using SQL Server Reporting Services, you have to use the DMX query editor to run your queries, though again you will not see any DAX metadata. Details on how to do this can be found in the following blog post:http://tinyurl.com/DAXSSRS. In all of these cases, when you are setting up your connection to Analysis Services Multidimensional, you must remember to specify the Cube connection string property, just as you do with Power View connections.

DAX queries and attributes

It is out of the scope of this book to provide a full description of the DAX query language; if you would like to learn more about it, a series of blog posts on the subject can be found here: http://tinyurl.com/DAXQueries. There is, however, one extra thing to take into account when using DAX queries on Analysis Services Multidimensional that is not relevant to DAX queries on Analysis Services Tabular: when you query a table representing a dimension, there are restrictions on the columns that you can use in your query. For example, the following DAX query returns all of the columns on the table representing the Date dimension in the Adventure Works database:

EVALUATE 'DATE'

Here are the results of the query:

Each of the columns returned by this query represents a column used in either the KeyColumns, the NameColumn, the ValueColumn, or a member property of an attribute on the dimension. However, if you only wish to return some of the columns and not all of them, you will need to remember that if you select one column, you will also need to select all of the columns built from the KeyColumns property from the same attribute for the query to run.

So, for example, trying to run the following DAX query will result in an error because only one of the columns that represent the Fiscal Year hierarchy on the Date dimension is included:

EVALUATE SUMMARIZE('DATE', 'DATE'[Fiscal Year])

However, the following query will run successfully because it includes the column 'Date'[Fiscal Year.Key0]:

EVALUATE

SUMMARIZE('DATE',

'DATE'[Fiscal Year.Key0],

'DATE'[Fiscal Year])

Here are the results of this query; all of the distinct key and name values used in the Fiscal Year attribute of the Date dimension: