Microsoft SQL Server 2012 with Power View for Multidimensional Models CTP

Important:

This article refers to a pre-release version of Power View for Multidimensional models. Information provided in this article is without warranty and subject to change in future releases.

 


This article can help you understand how you can use Power View, a browser-based Silverlight application launched from SharePoint Server, to interactively explore data and create dynamic visualizations from Analysis Services Multidimensional models.

When using Power View to visualize multidimensional models, it is important to keep in mind you are working with a tabular model type representation of a multidimensional model. Tabular models have objects such as tables and columns, and just like with multidimensional models, measures and KPIs.

When you or an administrator creates a shared data source connection in SharePoint, a cube name or perspective is specified in the connection string. Only one cube or perspective can be specified. The cube or perspective specified in the shared data source connection is exposed as a model in the Power View Field List. Objects in the model, are exposed as fields you can use in table visualizations in a view. There are, however, some differences in how certain multidimensional objects appear in Power View. Just like with tabular models, the Field List displays all of the objects you can use in a view.

Multidimensional models have dimensions. In this example, the Field List contains objects in the Product dimension. Product appears as a table in the Adventure Works model (cube). A table, or dimension, has other objects too.

Dimension objects

 

  1. Dimensions appear as tables and can be expanded to show other objects in the table (dimension). The Adventure Works cube has many tables, Account through Source Currency.
  2. Display folders further divide and classify how associated objects appear in client tools. Display folders can appear in the Field List below dimension tables and measure group tables.
  3. Dimension attributes appear as columns in a table. A single column (attribute) can appear both in a table, and again in a hierarchy, unless explicitly hidden.
  4. User and parent-child hierarchies are included in tables (dimensions). Hierarchies can be expanded to show columns (levels) in them. When you select a level, all levels above are also automatically selected. You can unselect higher levels to remove them from the visualization. This can be useful when certain fields are exposed only in a hierarchy.
  5. Objects with this icon indicate this object is a key.

 

Multidimensional models also have measure groups, also known as measure dimensions, which contain measures you can use to aggregate data in your analysis.

Measure groups objects

  1. Measure Groups appear as tables; however, unlike tables from a dimension, a table from a measure group is identified by a sigma icon.
  2. Measures appear in measure group tables and are identified with a calculator icon. If your cube has only one measure, it will be included in an associated measure group if there is one, or in a single table named Measures.
  3. KPIs are included in associated measure group tables, and are identified with a stoplight icon. For example, if in the Adventure Works model you expand Reseller Sales > Financial Perspective > Grow Revenue, you will see the Channel Revenue KPI and its four measures; Value, Goal, Status, and Trend.

 

Default member

Dimension attributes in multidimensional models have a unique property known as a default member. The default member property specifies a default value for the attribute. How dimension attributes with a default member and associated attribute relationships and hierarchies can be used as filters in Power View is different from how they can be used in Excel PivotTables.

For example, if in the Adventure Works multidimensional model’s Customers dimension, default member, Seattle, is specified for the City attribute, when in Power View, selecting City as a field, Seattle is applied as the default filter for City, Washington as the default filter for State-Province, and United States as the default filter for Country.  If you were to select only the State-Province or Country attribute, those too would default to Washington and United States, because the default member on the City attribute in the hierarchy is Seattle.

You can see how defaults are applied for each level above the default member, City, for the Customer-Geography hierarchy in the table filters.

 

It’s important to remember that when a default member is specified for an attribute in a hierarchy, the filters available for a particular attribute or level are dependent on those attributes or levels higher in the hierarchy order. To select attributes other than the default, in Table Filters, you must select (All) in a higher level filter. You can then narrow down lower level filters. For example, to filter on Brisbane, Queensland Australia, select (All) in Country, and (All) in State-Province.  If you then select (All) in cities, all cities for all countries and State-Provinces are shown. You can then select Brisbane from City filter. Australia for Country and Queensland for State-Province are then automatically selected as filters for those fields.

  

Note:

The sample AdventureWorksDWMultidimensional model does not have Default Member property set for the Customers dimension. The default member property has been set to [Customer].[City].&[Seattle]&[WA] for illustrative purposes.