Power Pivot and SSAS (Tabular) Restore

One really cool feature of SSAS in SQL 2012 SSAS (tabular mode) is the ability to restore a SSAS database from a powerpivot workbook. This can separate the design and administration functions of SSAS.

The restore can be done through the object explorer (right click on the Database node as select ‘Restore from PowerPivot’), which opens the restore GUI (see below). Simply select the workbook and the name of the SSAS database (which will import the powerpivot book). If the SSAS database exists, and select ‘Allow database overwrite’, if not, the ‘Allow database overwrite’ option can be left unchecked.

When the restore is run from the UI the standard script generation () will not produce the XMLA to restore the database from file (an XMLA restore command cannot be used (see below)).

There is no need to process the model after the restore however, I have noticed that the browse UI did not recognise the database until the 1st server reconnect was hit (that is, button [1] in the screen shot below). The query reconnect (button [1] did not refresh the schema metadata).

Alternatively, changing from design to text query modes ([3]) refreshed the metadata.

XMLA

The default script for restoring a SSAS database specifies the file and database (as below);

<Restore  xmlns=http://schemas.microsoft.com/analysisservices/2003/engine>
<File>some_file.abf</File>
   <DatabaseName>Target Database</DatabaseName>
   <AllowOverwrite>true</AllowOverwrite>
</Restore>

The restore of a powerpivot workbook cannot be done by using the excel workbook as the file name. The <File> tag of the command must be an .abf file
and substituting an excel extension (for the file) will throw an error.

In order to restore the database from XMLA, an image load command must be used (with both database id and name specified). However, in this case an AllowOverwrite tag cannot be used (the database must not exist);

<ImageLoad xmlns=http://schemas.microsoft.com/analysisservices/2003/engine
xmlns:ddl100=http://schemas.microsoft.com/analysisservices/2008/engine/100
xmlns:ddl200_200=http://schemas.microsoft.com/analysisservices/2010/engine/200/200
xmlns:ddl100_100=http://schemas.microsoft.com/analysisservices/2008/engine/100/100>
<ddl200_200:ImagePath>some_file.xlsx</ddl200_200:ImagePath>
<ddl100:ReadWriteMode>ReadWrite</ddl100:ReadWriteMode>
<DatabaseName>Target Database</DatabaseName>
<DatabaseID>Target Database</DatabaseID>
</ImageLoad>

One of the biggest drawbacks of SSAS (multidimensional) has been the thin line between development and administration (especially for small-mid enterprises). The ability to separate the design of tabular models (leaving this in the hands of business) and server administration is a powerful feature.

Cresent Goes Mobile

A quick note from PASS on the mobile direction of BI. Project cresent (the new silver light data exploration tool) will be marketed under the name `power viewer`.  Theres nothing really ground breaking in this.  What is ground breaking is the mobile support. Power viewer will be supported in mobile devices that include ios. This is going to be very nice for ipad\iphone users. The ability to easily build mobile bi apps will be met with enthusiasm.

SSAS Relationships and Granularity

The creation of measure groups at varying levels of granularity is used when two related measures differ by some dimensional level. A classic example of this occurs in budget comparisons to actual reporting. In this situation, the budget data is typically stored at a higher level of granularity. For example, it may exclude product information (or be set at high level product classes) and be targeted at monthly totals. Actual data is presumed to be recorded at a detail sale level (where { date x product x store x ref# } gives { revenue & quantity sold }). This post looks at how attribute relationships determine how data is aggregated in dimensions where measure groups are related to dimensions through non key attributes.

Situation

To illustrate the significance of relationships in dimension design and the effect they have on aggregation, the examples shown in this post use a very simple cube structure. There are two measure groups (one for actual and one for budget) and only one dimension (product). We assume that actual data is recorded at the product level and budgets are set at subcategory. Note that the related attribute for the Actual measure group is [Product] and the related attribute for the budget measure group is [Sub Category] (as below);

The product dimension has similar characteristics to that of adventure works but includes only three attributes (a product, sub category and category). The natural (business) hierarchy for product is [product] à [sub category] à [category]. That is, that a group of products will role-up to a [sub category] and several [sub categories] will role-up into a [category].

In this example, we consider only two subcategories which are [Mountain Bikes] and [Forks]. [Mountain Bikes] maps to the category [Bikes] and [Forks] maps to [Components]. This can be seen in the following hierarchy browse and script;

To illustrate how the use of relationships effect aggregations, we will assume that the [Mountain Bikes] sub category has a budget of 100 and the [Forks] sub category has a budget of 200. With these values, the budget for the [Bikes] category should be 100 and [Components] should be 200.

These examples uses MDX update statements to update the underlying fact data however, this is only for simplicity. The examples in this blog will work the same had the underlying fact been updated by other means.


Defined Relationships

When relationships are correctly defined between attributes according to the business definition (below), values at the [Sub Category] attribute are correctly aggregated to the parent attribute. For example, when the [mountain bikes] subcategory is updated, the value is aggregated to the parent (Bikes). This is demonstrated in the script below and screen shots below. When [Mountain Bikes] is updated and category [Bikes] is updated by the same amount. This can be seen in the following example;

Relationship :
Update Script : /* updating script */
update [Granularity Example] set

([Product].[Product By Category].[Mountain Bikes],[Measures].[Bgt Amount])=100,
([Product].[Product By Category].[Forks],[Measures].[Bgt Amount])=200
;

Query / Result select [Measures].[Bgt Amount] on 0,
{ [Product].[Product By Category].[Bikes]
,[Product].[Product By Category].[Components]
} on 1
from [Granularity Example];

Non Defined Relationships

When relationships are not defined between product attributes, both parent categories (Bikes and Components) contain the same value. The value shown is the total for all members and can be seen below.

Relationship :
Query / Result

Clearly, without the relationship between the parent and child, the values for the [sub category] level are not aggregated to [category] as expected.

The reason for this is that a correct relationship (as it was displayed in the first example) specifies how members are aggregated to higher levels in the hierarchy. In this case, how a [category] is made up of several [sub categories]. Without an ‘upstream’ relationship to the [sub category] attribute, the [category] attribute can only show total values regardless of the member data is displayed through the hierarchy. In a previous post on aggregation usage, we showed how lower level aggregations could be used when higher levels of the relationship were required. This is exactly the same (except we are not concerned with aggregation use). Note that I have used the words relationship and hierarchy interchangeably. In this situation, we assume that a hierarchy mimics the relationship structure (business hierarchy).

Although aggregations and relationships improve performance, the problem does not present itself when the fact is joined to the key of the dimension. This is because there is an explicit relationship between the dimension key and every other attribute (hierarchy).

Conclusion

The ability to include measure groups from varying levels of aggregations is an important feature of SSAS because it permits the ability to combine data that would otherwise be disparate. However, this flexibility comes with caution because measure groups joined to dimension non-key attributes aggregates data only to the extent that relationships are defined in the dimension structure. If this structure is poorly defined, not understood by the user or not managed through perspectives, data may be misinterpreted where hierarchies are provided without supporting relationships and non-key attributes are used.