Using the Schema Generation Wizard
The Schema Generation Wizard requires a limited amount of information during the generation phase. Most of the information that the Schema Generation Wizard requires for generating relational schemas is extracted from the Analysis Services cubes and dimensions that you design, but you can customize how the subject area database schema is generated and how objects in the schema are named.
Opening the Schema Generation Wizard
You can open the Schema Generation Wizard from Business Intelligence Development Studio in several different ways:
- You can open the wizard from a variety of places within an Analysis Services project. You can right-click the Analysis Services project object, and then click Generate Relational Schema from the context menu. You can also click the Analysis Services project object, and then click Generate Relational Schema from the Database menu.
- You can open the wizard from within either the Dimension Wizard or the Cube Wizard by clicking the Generate Schema Now check box on the last page of either of those wizards.
Specifying Targets
You must specify the data source view in which you want the Schema Generation Wizard to generate the schema for the subject area database. Although you can select an existing data source view, typically you create a new data source view based on a data source. You can create the data source based on an existing or a new connection, or based on another object. The Schema Generation Wizard generates the schema for the subject area database in the database referenced by the data source, as well as the data source view. The Schema Generation Wizard does not create the subject area database itself; instead, the wizard creates the relational schema to support the cubes and dimensions in an existing database that you specify.
When the Schema Generation Wizard generates the underlying objects, it binds the Analysis Services dimensions and cubes to the generated tables and columns by using data source view-style bindings. For more information, see Defining and Configuring Dimensions, Attributes, and Hierarchies.
Note
To unbind Analysis Services dimensions and cubes from previously generated objects, delete the data source view to which the Analysis Services cubes and dimensions are bound, and then define a new data source view for the cubes and dimensions by using the Schema Generation Wizard.
Specifying Schema Options for the Subject Area Database
The Schema Generation Wizard provides a number of options for defining the schema that is generated for the subject area database. You can specify these options on the Subject Area Database Schema Options page of the wizard.
Specifying the Schema Owner
You can specify the owner of the schema by setting the value of Owning schema to a valid string. The default owner of the schema is the Analysis Services project, but you can specify any desired schema owner.
Specifying Primary Keys, Indexes, and Constraints
The Schema Generation Wizard by default creates a primary key constraint in each dimension table in the subject area database. The primary key corresponds to the attribute that is designated as the key attribute in the corresponding Analysis Services dimension. This constraint improves processing performance in most environments, with minimal cost. Logical primary keys are always created in the data source view, even if you choose not to create the primary key in the subject area database. To define primary key constraints on dimension tables, select Create primary keys on dimension tables.
The wizard by default also creates indexes on the foreign key columns in each fact table. These indexes improve processing performance in most environments. Performance is typically improved because the processing queries that Analysis Services generates to retrieve new data from the subject area database typically include a significant number of join statements between the fact table and the dimension tables. To define indexes on the foreign key columns in each fact table, select Create indexes.
Finally, the wizard by default enforces referential integrity between the fact table and each of the dimension tables. If you choose not to enforce referential integrity, the Schema Generation Wizard still creates these relationships in the database and the data source view. To enforce referential integrity, select Enforce referential integrity.
Preserving Data for Incremental Generation
The Schema Generation Wizard by default attempts to preserve data when the database schema is regenerated. If the Schema Generation Wizard has to delete any rows because of a schema change, you receive a warning before the rows are deleted. For example, rows may have to be deleted to solve referential integrity issues because you dropped a dimension or because a data type changed when you changed a dimension attribute. To preserve data when the database schema is regenerated, select Preserve data on regeneration.
Specifying Naming Conventions
You can define the naming conventions that the Schema Generation Wizard uses when generating certain objects in the subject area database on the Specify Naming Conventions page of the wizard. For more information about the options available on the Specify Naming Conventions page, see Specify Naming Conventions (Schema Generation Wizard) (SSAS).
The values for these options should be set to match the desired conventions for your environment.
See Also
Concepts
Understanding the Database Schemas
Understanding Incremental Generation
Managing Changes to Data Source Views and Data Sources