Designing Cube-Based Report Models
Report models are generated from SQL Server Analysis Services (SSAS) cubes by using Report Manager or Microsoft Office SharePoint Server 2007 when running in SharePoint integrated mode. Before you can create a report model from the SSAS cube, you must be an administrator of the Analysis Services database. After the model is generated, it cannot be modified. If you decide to change the contents of your database, regenerate the model again to incorporate your changes.
Connection Strings
When building a report model based on an Analysis Services database, your connection string appears similar to the following:
Data Source=<reportserver>;Initial Catalog=<database name>
Note
If your Analysis Services database contains cube translations, you can create translated versions of the report model. To create one model for each language, specify the locale identifier (LCID) in the connection string of the data source. To create a model in Chinese, for example, your connection string should look similar to Data Source=<reportserver>;Initial Catalog=<database name>;LocaleIdentifier=3012. For more information about cube translations, see Cube Translations.
Rules for Generating Models from Analysis Services Databases
The following is a list of generic rules applied when creating a model from a cube:
Measure groups are mapped to entities. A single report model includes all of the cubes within the Analysis Services database.
Dimensions are mapped to entities. Fact dimensions do not result in a different entity. For example, suppose you have a Sale measure group within a cube and a fact dimension called Sale Detail. When a model is generated from this cube, the model will generate a single entity that contains all the measures of Sale and all the dimension attributes of Sale Detail.
Relationships between measure groups and dimensions are converted to roles within the model. Referenced relationships (used for indirect relationships) and many-to-many relationships defined in the model as roles.
Measures are converted to entity attributes.
Dimension attributes are converted to entity attributes. Models do not have any concept of hierarchy. Hence, a dimension attribute is included in the model if it is visible, or if there is a visible hierarchy that contains a level that is based on it. The key attribute of a dimension is always included, even if it is marked invisible.
The entity attributes from measures and dimension attributes are organized into folders in accordance with any display folders defined in the cube.
Cube perspectives become report model perspectives. In addition, each cube becomes a perspective within the model. Therefore, Report Builder users must select a perspective within the model and not the top-level model.
Calculated measures (calculated members) become attributes on the entity corresponding to the measure group that the measures are associated with.
Named sets defined on the key attribute of a dimension are converted to a subtype of the entity. For example, the named set “Large Customers” results in a subtype of “Customer.” Named sets that are not based on a single key attribute, are ignored.
Key Performance Indicators (KPIs) are converted to attributes on the entity corresponding to the measure group with which the KPI is associated. Multiple attributes are created for each KPI, covering the different components of the KPI (Value, Goal, Status, and Trend). In addition, a variation attribute is created for Status and Trend that holds the StatusGraphic and the TrendGraphic attributes, respectively. The actual image is included in the report when you use these attributes.
Analysis Services Database Items Omitted from Report Models
The following SSAS items do not appear in the generated model:
Calculated members (that are not in the measures dimension).
Parent-child hierarchies do not convert to model attributes or roles. The Key attribute is still included, although the use of that attribute in a report will show measure values for the key member, not the value aggregated on the parent-child hierarchy. In addition, performance will be affected.
Actions. This includes drillthrough actions. Drillthrough functionality is always enabled on aggregate attributes, regardless of what drillthrough actions are defined in the cube. As such, when a user runs a Report Builder report off the model and clicks on an aggregate to display a clickthrough report, empty tables will be displayed.
Attribute relationships. A dimension results in a single entity, and any relationships between the attributes of the dimensions do not affect the report model.
Relationships from a measure group to a dimension are ignored if they are based on an attribute other than the key attribute of the dimension. For example, the Budget measure group might be related to Time at the Month level, rather than the Day level. In this case, the report model would not include any relationship between the Budget entity and the Time entity.
Cube Design Considerations
Consider the following when designing a cube for which you plan to generate a report model:
Calculated measures or KPIs that do not have an associated measure group will not appear in the report model. To configure the associated measure group for a calculated measure, you should use the Calculation Properties dialog box.
Queries sent by Report Builder will always request the member value of dimension members and will use the member value for sorting and filtering. By default in Analysis Services, if an attribute has a name binding, then the member value will be the same as the member name, and if the attribute has no name binding, then the member value will be the same as the member key. However, each attribute can have an explicit binding to a column that provides the member value, which should return the value in the “true” data type. For example, a Date attribute in Analysis Services might have a key that is the DateTime (e.g., “4/25/2008”) and a name/caption that is a textual description (“Friday, 25th April, 2008”). In this case, the cube designer should set MemberValue to the key, to ensure reasonable sorting and filtering. Although you should consider this for any attribute, it is particularly relevant for datetime attributes. For any datetime attribute, the generated model will contain two report-model attributes—one that is the caption, and a variant of it that is the true datetime value.
The dimension attribute property InstanceSelection is used to set the report model properties InstanceSelection (on entities) and ValueSelection (on attributes). This determines how a user will be able to select instances in Report Builder (for example, by using a drop-down list).
The dimension attribute property GroupingBehavior is used to set the DiscourageGrouping model attribute property.
Any dimension attributes that are images must have the Image data type set on the dimension attribute binding.
Drillthrough capability is always enabled on attributes resulting from measures, but only minimal details are included in the default drillthrough reports. Custom drillthrough reports should be added as necessary to tailor this.
If translations are included in the cube, it is necessary to create one data source per translation to expose them in the report model by setting the LocaleIdentifier property as appropriate in the connection string. One model is then generated for each data source, and will contain the metadata from the associated translation.