Power View for Multidimensional Models - Feature Drill Down

Power View for Multidimensional Models - Feature Drill Down

Rate This
  • Comments 10

On 11/29 we released Preview of Power View connectivity for Multidimensional Models (a.k.a “cubes”). This is achieved through support of tabular metadata of the cube and native support of Data Analysis Expressions (DAX) queries in Analysis Services Multidimensional Models to ensure optimal performance and functionality. We will take a deeper look at how this is achieved.

1. Connectivity

 In the Power View connectivity for Multidimensional Models CTP, connectivity to Multidimensional Models (cubes) is supported for Power View in SharePoint mode.  You can use SharePoint 2010 or SharePoint 2013. You need to create a Reporting Services shared data source (RSDS file) with Data Source Type set to “Microsoft BI Semantic Model for Power View” and specify the connection string to your cube as the one shown below.

Data Source=localhost;Initial Catalog=AdventureWorksDW-MD;Cube='Adventure Works'

It is important you specify the multidimensional cube name since each cube is exposed as a model in the tabular metadata. Depending on your environment, you may need to specify appropriate credentials in the RSDS file to connect to your cube.

SQL Server Analysis Services exposes the objects of a cube as tabular metadata. The tabular metadata is represented in CSDL with BI annotations and exposed as results of an XMLA schema rowset DISCOVER_CSDL_METADATA. Based on the tabular metadata and Power View visualizations used Power
View sends appropriate DAX Queries to retrieve the data as shown in Figure 1. 

                                                                                                                  

Figure 1: Power View Connectivity for Multidimensional Models

2. Tabular Metadata of Multidimensional Models

 The table below shows the correspondence between key multidimensional objects and the tabular metadata that is returned to Power View.  Power View queries the model for tabular metadata and, based on the returned metadata, executes appropriate DAX queries against Analysis Services when a Power View user creates a visualization such as a table, matrix, chart or slicer.

 

BISM-Multidimensional Object

Tabular Metadata

Cube

Model

Cube Dimension

Table

Dimension Attributes (Key(s), Name)

Columns

Measure Group

Table

Measure

Measure

Measures without associated Measure Group

Within Table called “Measures”

Measure group->Cube dimension relationship

Relationship

Perspective

Perspective

KPI

KPI

User/Parent-Child Hierarchies

Hierarchies

 

Figure 2 shows the Adventure Works sample cube’s objects (measure groups and cube dimensions) in SQL Server Data Tools and how they are shown in the Power View field list. The measure groups are exposed as tables with ∑ icon indicating they contain aggregating objects (measures). All the measure groups appear first in the Power View field list sorted alphabetically. Following the measure groups you will see the cube dimensions (with the table icon) also sorted alphabetically.

 

 

Figure 2: Adventure Works cube object in Power View field list

3. Measures, Measure Groups and KPIs

Measure groups in a multidimensional cube are exposed as tables with ∑ sign as shown in Figure 2 and Figure 3.  The measures within a measure group appear as measures as shown in Figure 3. If there are calculated measures that do not have an associated measure group then they are grouped under a special table called “Measures” in the tabular metadata. In a multidimensional model you can define a set of measures or KPIs in a cube to be located within a display folder. This helps simplify more complex models. Power View now recognizes display folders in tabular metadata and shows the measures and KPIs within the display folders.  Figure 3 shows the measures, KPIs and display folders for the Internet Sales measure group. KPIs in multidimensional databases support Value, Goal, Status Graphic and Trend Graphic. Power View now recognizes and supports the Trend Graphic as shown in Figure 3.

Figure 3: Measures and KPIs of a measure group in Power View field list 

4. Dimensions, Attributes, Hierarchies

 Dimensions in a cube are exposed as tables in tabular metadata. Figure 4 shows the Customer dimension in the sample Adventure Works cube. The attributes within a dimension are exposed as columns. You can see from Figure 4 that dimension attributes are shown as columns within the appropriate display folders in the Power View field list.  The dimension attributes that have the AttributeHierarchyEnabled property set to false (Example: Birth Date attribute in Customer dimension) or AttributeHierarchyVisible property set to false will not appear in the Power View field list. Multi-level hierarchies or user hierarchies (Example: Customer Geography in the Customer dimension) are exposed as hierarchies in Power View as shown in the field list. Hidden UnknownMembers of a dimension attribute are exposed in DAX Queries and in Power View.

Figure 4: Dimension, Attributes and Hierarchies in  SQL Server Data Tools and Power View field list       

4.1 Dimension Attribute Type and Power View

Multidimensional models support associating dimension attributes with specific dimension attribute types. Figure 7 shows the Geography dimension where the City, State-Province, Country and Postal Code dimension attributes have appropriate geography types associated with them. These are exposed in the tabular metadata. Power View recognizes the metadata enabling users to create map visualizations. This is indicated by the map icon next to the City, Country, Postal Code and State-Province columns in the Geography table in the Power View field list in Figure 5.

 

Figure 5: Dimension attribute geography types exposed in Power View

One of the key benefits of Power View visualizations is the ability to render images. One of the ways you can provide images to Power View is to expose columns containing URLs (Uniform Resource Locator) of the images. In this CTP, Analysis Services now supports tagging dimension attributes as type ImageURL as shown in Figure 6. Once an attribute is tagged as type ImageURL in SQL Server Data Tools, this information is provided to Power View in the tabular metadata. Power View downloads and displays the images from the URL within various
visualizations.

 

Figure 6: ImageURL Dimension Attribute Type 

4.2 Parent-Child Hiearchies

Multidimensional models support Parent-Child Hierarchies. The Parent-Child hierarchies are exposed as a hierarchy in the tabular metadata as shown in Figure 7. Each level of the Parent-Child hierarchy is exposed as a hidden column in the tabular metadata. The key attribute of the Parent-Child dimension is not exposed in the tabular metadata.

 

Figure 7: Parent-Child Hierarchies in Power View 

5. Power View Visualization of Multidimensional Models

You can use the various Power View visualizations against your connected multidimensional models. Figure 8 shows a simple view in Power View with slicers, map, and matrix visualizations. There are, in addition, some unique features of multidimensional models that now provide the right experience to the Power View user. We will see examples of how some of these multidimensional features appear in Power View in this section.

Figure 8: A view in Power View with various visualizations against a multidimensional BISM cube

5.1 Non-aggregatable Attributes/Hierarchies

 In a multidimensional model, attributes of a dimension can have the IsAggregatable property set to false. This means the modeler has specified that client applications should not aggregate the data across hierarchies (attribute or multi-level) when they query the data. In Power View this dimension attribute is exposed as a column for which sub-totals are not available. In Figure 9 you can see an example of a non-aggregatable hierarchy: Accounts. The topmost level of the Accounts Parent-Child hierarchy is non-aggregatable while the other levels are aggregatable. Hence in a matrix visualization of the Accounts hierarchy (first two levels) you see sub-totals in Power View for Account Level 02 but not for the top most level, Account Level 01.

 

Figure 9: Non-Aggregatable hierarchy in Power View 

5.2 Measures as Variants

 Measures in multidimensional models are Variants. This essentially means the measures are not strongly typed and can have different data types. An example is shown in Figure 9 where you have the measure Amount in the Financial Reporting table which is by default of type Currency but also has a string value “NA” for the sub-total of “Statistical Accounts” which is a string data type.  Power View now recognizes certain measures are variants and shows the right values and formatting in the various visualizations. 

5.3 Implicit Measures

Power View against tabular models provides the abilty to create simple measures (also called implicit measures) such as Count, Sum, Average and so on over columns. However for multidimensional models, due to the way the data is stored within the model for dimension attributes, querying such a measure can take a long time. To avoid end-users experiencing performance issues, Analysis Services provides additional metadata to Power View so that implicit measure operations on columns are not available in Power View.

5.4 Default Members

Multidimensional models support specifying default members for dimension attributes. The default member is used by Analysis Services when aggregating data for a query. The default member of a dimension attribute is exposed as default value or filter for the corresponding column in the tabular metadata. When Power View queries the data via DAX you will see the default filter being applied. Figure 10 shows the CustomeràCity column from the Adventure Works cube where the default member property of the dimension attribute City has been set to Seattle ([Customer].[City].&[Seattle]&[WA].). When you add a column to one of the Power View visualizations (table or matrix or chart) that contains a default filter then Power View adds all the default filters of the corresponding table and also show the default filter value in the filter pane as shown in Figure 10.

 

Figure 10: Default Member in Power View

6. Dimension Calculated Members

Multidimensional models support creation of various types of calculated members. There are two most common types of calculated members created:

  • Calculated Members on attribute hierarchies and not sibling of “All”
  • Calculated Members on user Hierarchies

Calculated members on attribute hierarchies is one of the most common type of calculated members modelers use. Multidimensional model exposes this type of calculated members as values of a column. There are a few additional options/constraints while exposing this type of calculated members as listed below:

  • Dimension attribute can have an optional “UnknownMember”.
  • An attribute containing calculated members cannot be the key attribute of the dimension unless it is the only attribute of the dimension.
  • An attribute containing calculated members cannot be a parent-child attribute.

The calculated members of user hierarchies are not exposed in Power View. A Power View end-user will be able to connect to a cube containing calculated members on user hierarchies but they will not be able to see calculated members if they do not meet the constraints mentioned above. Figure 11 shows a Power View report against a cube that contains time intelligence calculated members on a dimension attribute “Fiscal Date Calculations” in the Date dimension.

Figure 11: A Power View report with Calculated members. 

7. Perspectives and Translations

Multidimensional cubes can have various perspectives created by the modeler. Perspectives are views of the cubes where only certain dimensions or measure groups are visible in client tools. Power View supports connecting to a perspective of a cube via the connection string in the RSDS file.  You need to specify the perspective name as value to the Cube connection string property. For example, in the following connection string ‘Direct Sales’ is a perspective in the multidimensional model:

Data Source=localost;Initial Catalog=AdventureWorksDW-MD;Cube='Direct Sales'

Cubes can have metadata and data translations specified for various Languages within the model. In order to see the translations (data and metadata) you need to add the “Locale Identifier” property to the connection string in the RSDS file as shown below

Data Source=localost;Initial Catalog=AdventureWorksDW-MD;Cube='Adventure Works'; Locale Identifier=3084

When you launch Power View via an RSDS file that has a specific Locale Identifier and if a corresponding translation is contained in the cube, users will see the translations in Power View.

8. Security

Multidimensional models support dimension and cell level security via Roles. A user connecting to a cube via Power View is authenticated and evaluated for appropriate permissions. When a user has dimension security applied then the respective dimension members will not be seen by the user in Power View. However if a user has a cell security permission defined where certain cells are restricted then that user cannot connect to the cube via Power View.

9. Multidimensional Features not exposed in Power View

Power View supports and exposes most of the features of Multidimensional models. Two features not exposed in Power View are Actions and Named Sets. Power View users can still connect to the cubes containing Actions or Named sets and create reports.

10. Power View Pinned Filters

A Power View report can contain multiple views. An additional feature included in this CTP (for both tabular and multidimensional models) is the ability to create filters that apply across all views in a report. Figure 12 shows the “Pin filter” toggle button for a view filter. By default, a view filter is unpinned and applies only to that view. Pinning a view filter applies it to all views; unpinning it removes
it from other views.

 

Figure 12: Pinned Filters

Please  download the Preview of Power View connectivity for Multidimensional Models CTP from here. Have fun playing with the CTP and please provide your feedback (issues/suggestions) via MS Connect.

 

Sivakumar Harinath (sivah@microsoft.com)

Senior Program Manager, SQL Server Business Intelligence team

Leave a Comment
  • Please add 3 and 7 and type the answer here:
  • Post
  • Should I be able to use Latitude and Longitude data from an MDX Dimension?

  • I already have SQL 2012 installed and power view configured and working with SSAS tabular model in Sharepoint 2010.

    Do we have steps to install and use "The Power View for Multidimensional Models CTP"

  • Hi Virendra,

    The steps to configure "The Power View for Multidimensional Models CTP" is same as the steps for Powerview for Tabular Model. You use the following link as the reference msdn.microsoft.com/.../jj591528.aspx

    However ensure that you use the CTP version pointed in the above blog by Siva and install Multidimensional mode for SSAS

  • Any idea when this will be released officially ?

  • I have the same question as Kurt.  Do you have any idea how soon this will be released?  If it's by the June to July time frame it would affect what we are doing.  Thanks.

  • Hey T.K.! Long time no talking.

    Any public news about when are you expecting to release this? Maybe another CTP coming? :)

  • Sorry, we can't share release dates... all I can say is "soon". There won't be another CTP.

  • God post!!! Thanks for the advice, I want to reccomend you this page that bring you more benefics if you want to repair something in your house. With houston maid services you life are more easy... Click here!!

  • Hi,

    We are connected to MultiDimentional via Power View in SharePoint 2013.

    The Local Identifier Code is 1037 (Hebrew).

    The problem is that every time you check a field containing description in Hebrew

    the query is stuck, cause there is an automatic Order by clause on fetched fields

    in the query generated by the PowerView. For ex:

    EVALUATE

     FILTER(

       KEEPFILTERS(

         SUMMARIZE(

           VALUES('DW DIM CUSTOMERS'),

           'DW DIM CUSTOMERS'[BRANCH CD.Key0],

           'DW DIM CUSTOMERS'[BRANCH CD]

         )

       ),

       NOT(ISBLANK('DW DIM CUSTOMERS'[BRANCH CD.Key0]))

     )

    ORDER BY

     'DW DIM CUSTOMERS'[BRANCH CD], 'DW DIM CUSTOMERS'[BRANCH CD.Key0]

    If the order by on 'DW DIM CUSTOMERS'[BRANCH CD] (the desc of the branch)

    removed (manually in SSMS) the query runs without any performance issues.

    How this could be fixed?

    TY

  • Good Work! Thank you so much !

Page 1 of 1 (10 items)