Understanding the Database Schemas

Before it begins to generate the underlying relational schema, the Schema Generation Wizard validates the Analysis Services cubes and dimensions. If the wizard detects errors, it stops and reports the errors to the Task List window in SQL Server Data Tools (SSDT). Examples of errors that prevent generation include the following:

• Dimensions that have more than one key attribute.

• Parent attributes that have different data types than the key attributes.

• Measure groups that do not have measures.

• Degenerate dimensions or measures that are improperly configured.

• Surrogate keys that are improperly configured, such as multiple attributes using the ScdOriginalID attribute type or an attribute using the ScdOriginalID that is not bound to a column using the integer data type.

For each dimension, the Schema Generation Wizard generates a dimension table to be included in the subject area database. The structure of the dimension table depends on the choices made while designing the dimension on which it is based.

Columns

The wizard generates one column for the bindings associated to each attribute in the dimension on which the dimension table is based, such as the bindings for the KeyColumns, NameColumn, ValueColumn, CustomRollupColumn, CustomRollupPropertiesColumn, and UnaryOperatorColumn properties of each attribute.

Relationships

The wizard generates a relationship between the column for each parent attribute and the primary key of the dimension table.

The wizard also generates a relationship to the primary key in each additional dimension table defined as a referenced dimension in the cube, if applicable.

Constraints

The wizard generates a primary key constraint, by default, for each dimension table based on the key attribute of the dimension. If the primary key constraint is generated, a separate name column is generated by default. A logical primary key is created in the data source view even if you decide not to create the primary key in the database.

Note
An error occurs if more than one key attribute is specified in the dimension on which the dimension table is based.

Translations

The wizard generates a separate table to hold the translated values for any attribute that requires a translation column. The wizard also creates a separate column for each of the required languages.

For each measure group in a cube, the Schema Generation Wizard generates a fact table to be included in the subject area database. The structure of the fact table depends on the choices made while designing the measure group on which it is based, and the relationships established between the measure group and any included dimensions.

Columns

The wizard generates one column for each measure, except for measures that use the Count aggregation function. Such measures do not require a corresponding column in the fact table.

The wizard also generates one column for each granularity attribute column of each regular dimension relationship on the measure group, and one or more columns for the bindings associated to each attribute of a dimension that has a fact dimension relationship to the measure group on which this table is based, if applicable.

Relationships

The wizard generates one relationship for each regular dimension relationship from the fact table to the dimension table's granularity attribute. If the granularity is based on the key attribute of the dimension table, the relationship is created in the database and in the data source view. If the granularity is based on another attribute, the relationship is created only in the data source view.

If you chose to generate indexes in the wizard, a non-clustered index is generated for each of these relationship columns.

Constraints

Primary keys are not generated on fact tables.

If you chose to enforce referential integrity, referential integrity constraints are generated between dimension tables and fact tables where applicable.

Translations

The wizard generates a separate table to hold the translated values for any property in the measure group that requires a translation column. The wizard also creates a separate column for each of the required languages.

Schema Generation Wizard ignores data types in all cases except for columns that use the SQL Server wchar data type. The wchar data size translates directly to the nvarchar data type. However, if the specified length of a column using the wchar size is larger than 4000 bytes, the Schema Generation Wizard generates an error.

If a data item, such as the binding for an attribute, has no specified length, the default length listed in the following table is used for the column.

Concepts