Schema Generation Wizard (Analysize Services)

SQL Server Data Tools (SSDT) supports two methods of working with relational schemas when defining OLAP objects within an Analysis Services project or database. Generally, you will define OLAP objects based on a logical data model constructed in a data source view within an Analysis Services project or database. This data source view is defined based on schema elements from one or more relational data sources, as customized in the data source view.

Alternatively, you can define OLAP objects first, and then generate a data source view, a data source, and the underlying relational database schema that supports these OLAP objects. This relational database is referred to as the subject area database.

This approach is sometimes called top-down design and is frequently used for prototyping and analysis modeling. When you use this approach, you use the Schema Generation Wizard to create the underlying data source view and data source objects based on the OLAP objects defined in an Analysis Services project or database.

This is an iterative approach. You will most likely rerun the wizard multiple times as you change the design of the dimensions and cubes. Each time you run the wizard, it incorporates the changes into the underlying objects and, as much as is possible, preserves the data contained in the underlying databases.

The schema that is generated is a SQL Server relational database engine schema. The wizard does not generate schemas for other relational database products.

The data that is populated in the subject area database is added separately, using whatever tools and techniques you use to populate a SQL Server relational database. In most cases, the data is preserved when you rerun the wizard, but there are exceptions. For example, some data must be dropped if you delete a dimension or an attribute that contains data. If the Schema Generation Wizard must drop some data because of a schema change, you receive a warning before the data is dropped and can then cancel the regeneration.

As a general rule, any change that you make to an object that was originally generated by the Schema Generation Wizard is overwritten when the Schema Generation Wizard subsequently regenerates that object. The primary exception to this rule is when you add columns to a table that the Schema Generation Wizard generated. In this case, the Schema Generation Wizard preserves the columns that you added to the table, as well as the data in these columns.

In this section

The following table lists additional topics that explain how to work with the Schema Generation Wizard.

See Also

Reference

Schema Generation Wizard F1 Help (Analysis Services - Multidimensional Data)

Concepts

Data Source Views in Multidimensional Models

Data Sources in Multidimensional Models

Supported Data Sources (SSAS - Multidimensional Models)

Other Resources

Data Source Views (Analysis Services - Multidimensional Data)