Schema Generation Wizard (Analysis Services)

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

SQL Server Data Tools supports two methods of working with relational schemas when defining OLAP objects within an SQL Server 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 SQL Server 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 SQL Server 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

Data Source Views in Multidimensional Models
Data Sources in Multidimensional Models
Supported Data Sources (SSAS - Multidimensional)