--- title: "Understanding the Database Schemas | Microsoft Docs" ms.custom: "" ms.date: "06/13/2017" ms.prod: "sql-server-2014" ms.reviewer: "" ms.technology: "analysis-services" ms.topic: conceptual helpviewer_keywords: - "Schema Generation Wizard, database schema" - "database schema [Analysis Services]" - "relational schema [Analysis Services], database schema" - "subject area schema options [Analysis Services]" - "staging area schema options [Analysis Services]" - "denormalized schemas" ms.assetid: 51e411f9-ee3f-4b92-9833-c2bce8c6b752 author: minewiskan ms.author: owend manager: craigg --- # Understanding the Database Schemas The Schema Generation Wizard generates a denormalized relational schema for the subject area database based on the dimensions and measure groups in [!INCLUDE[ssASnoversion](../../includes/ssasnoversion-md.md)]. The wizard generates a relational table for each dimension to store dimension data, which is called a dimension table, and a relational table for each measure group to store fact data, which is called a fact table. The wizard ignores linked dimensions, linked measure groups, and server time dimensions when it generates these relational tables. ## Validation Before it begins to generate the underlying relational schema, the Schema Generation Wizard validates the [!INCLUDE[ssASnoversion](../../includes/ssasnoversion-md.md)] cubes and dimensions. If the wizard detects errors, it stops and reports the errors to the Task List window in [!INCLUDE[ssBIDevStudioFull](../../includes/ssbidevstudiofull-md.md)]. 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. ## Dimension Tables 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. ## Fact Tables 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 nonclustered 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. ## Data Type Conversion and Default Lengths Schema Generation Wizard ignores data types in all cases except for columns that use the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] `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. |Data item|Default length (bytes)| |---------------|------------------------------| |KeyColumn|50| |NameColumn|50| |CustomRollupColumn|3000| |CustomRollupPropertiesColumn|500| |UnaryOperatorColumn|1| ## See Also [Understanding Incremental Generation](understanding-incremental-generation.md) [Manage Changes to Data Source Views and Data Sources](manage-changes-to-data-source-views-and-data-sources.md)