Configuring Power View Connectivity to Multidimensional

/0 Comments/in /by

As I mentioned in my previous blog, CU4 for SQL Server 2012 SP1 includes the release bits of Power View Connectivity for Multidimensional (DAXMD). It allows you to create Power View reports from Multidimensional cubes. In this blog, I’ll show you how to configure this feature.

APPLYING CU4

You need to upgrade both Reporting Services in SharePoint mode and the Analysis Services instance that hosts your cubes. If SharePoint and SSAS are on the same machine, as in the screenshot below, you need to run the CU4 setup program only once. In this case, I decided to accept the default choice and upgrade all instances on the box although the required ones are highlighted. With distributed deployments, you need to install CU4 on the SharePoint server and on the SSAS server.

060213_2030_Configuring1

CREATING DATA SOURCE

Once CU4 is applied, you’re ready to set up a data connection in a SharePoint library with SSRS integrated and SSRS document types enabled. You need a Report Data Source connection type. You cannot use BI Semantic Model Connection because you’ll need to specify which cube or perspective you are connecting and the BI Semantic Model Connection UI doesn’t have this option.

060213_2030_Configuring2

Once you open the Data Source dialog box, choose the Microsoft BI Semantic Model for Power View data source type. Specify the connection string as usual. Notice that the Cube setting is mandatory and it can specify a perspective. Depending on your security setup, you can enable Windows Authentication (requires Kerberos for double-hop) or stored credentials.

060213_2030_Configuring3

Once the connection is created, you can just click it to launch Power View connected to the cube or perspective. Microsoft did an excellent job to bend Power View to support Multidimensional concepts. The following features are supported:

  • As with Excel, measure groups appears on the top of the Field List and are prefixed with the sigma sign.
  • KPIs are supported but there isn’t a separate KPI folder (as in Excel for example). Instead, KPIs are found within the corresponding Measure Group they are mapped to during design.
  • Calculated members appear in corresponding measure group (table).
  • Cell errors are exposed as strings.
  • Cell properties are supported.
  • Both static and dynamic (e.g. scope assignments) format strings are supported.
  • Display folders are supported.
  • Hierarchies have a special icon but you can’t drag the entire hierarchy to the report. Instead, you need to select the levels you need. Parent-child hierarchies are supported but are flattened and the Field List shows all levels so the user can choose which level they want to see on the report.
  • Dimension attributes are mapped as table columns. Certain attribute types provide enhanced visualizations. For example, if an attribute is tagged as a City type, Power View shows a Map icon to inform the user that it can be used for geospatial reporting. In addition, Image->ImageUrl type has been added to SSDT to allow visualizing images from URL.
  • All relationship types are supported, including M:M and Referenced.
  • Perspective and translations are supported via connection string settings.
  • Dimension data security is supported.
  • DAX now supports Variant measures. A variant measure is a measure whose data type is not known until runtime, e.g. a measure whose data type is Currency but could be overwritten in a scope assignment as “NA”.

The following features are not supported:

  • Named Sets
  • Actions since Power View doesn’t support actions
  • Cell Security is not supported. Users belonging to a role with cell security restrictions to a cube cannot connect via Power View.

For more information about the level of support with excellent screenshots, read the Power View for Multidimensional Models – Feature Drill Down blog by Siva Harinath.