Understanding Incremental Generation
Following the initial schema generation, you can change cube and dimension definitions by using Business Intelligence Development Studio, and then rerun the Schema Generation Wizard. The wizard updates the schema in the subject area database and in the associated data source view to reflect the changes, and retaining the data that currently exists in the tables to be regenerated, to the extent possible. If you changed the tables after the initial generation, the Schema Generation Wizard preserves those changes when possible by using the following rules:
If a table was previously generated by the wizard, the table is overwritten. You can prevent a table that was generated by the wizard from being overwritten by changing the AllowChangesDuringGeneration property for the table in the data source view to false. When you take control of a table, the table is treated like any other user-defined table and is not affected during regeneration. After you remove a table from generation, you can later change the AllowChangesDuringGeneration property for the table in the data source view to true and reopen the table for changes by the wizard. For more information, see Viewing or Changing Data Source View, DataTable and DataColumn Properties in a Data Source View (Analysis Services).
If a table was added to the data source view or to the underlying database by something other than the wizard, the table is not overwritten.
When the Schema Generation Wizard regenerates tables that were previously generated in the subject area database, you can choose to have the wizard preserve existing data in those tables.
As a general rule, the Schema Generation Wizard preserves data that is stored in the tables that it generated. In addition, if you add columns to tables that the wizard generated, the wizard preserves that data as well. You can use this capability to add or modify your dimensions and cubes and then regenerate the underlying objects without having to reload the data stored in the underlying tables.
If you are loading data from delimited text files, you can also choose whether the Schema Generation Wizard overwrites these files and the data contained in them during regeneration. Text files are either overwritten completely or not at all. The Schema Generation Wizard does not partially overwrite these files. By default, these files are not overwritten.
The Schema Generation Wizard cannot preserve existing data under some circumstances. The following table provides examples of situations in which the wizard cannot preserve all the existing data in the underlying tables during regeneration.
Type of data change
Incompatible data type change
The Schema Generation Wizard uses standard SQL Server data type conversions, whenever possible, to convert existing data from one data type to another. However, when you change an attribute's data type to a type that is incompatible with the existing data, the wizard drops the data for the affected column.
Referential integrity errors
If you change a dimension or cube that contains data and the change causes a referential integrity error during regeneration, the Schema Generation Wizard drops all data in the foreign key table. The data that is dropped is not limited to the column that caused the foreign key constraint violation or to the rows that contain the referential integrity errors. For example, if you change the dimension key to an attribute that has non-unique or null data, all existing data in the foreign key table is dropped. Furthermore, dropping all the data in one table can have a cascading effect and can cause other referential integrity violations.
Deleted attribute or dimension
If you delete an attribute from a dimension, the Schema Generation Wizard deletes the column that is mapped to the deleted attribute. If you delete a dimension, the wizard deletes the table that is mapped to the deleted dimension. In these cases, the wizard drops the data that is contained in the deleted column or table.
The Schema Generation Wizard issues a warning before it drops any data so that you can cancel the wizard without losing any data. However, the Schema Generation Wizard is not able to differentiate between anticipated data loss and unanticipated data loss. When you run the wizard, a dialog box lists the tables and columns that contain data that will be dropped. You can either have the wizard continue and drop the data, or you can cancel the wizard and revise the changes you made to the tables and columns.
When you change the properties of dimensions and cubes, the Schema Generation Wizard regenerates the appropriate objects in the underlying subject area database, as well as in the related data source view, as described in the following table.
If the Schema Generation Wizard cannot make the required change because of the presence of a user object in the subject area database (because the Database Engine returns an error), the Schema Generation Wizard fails and displays the error returned by the Database Engine. For example, if you create a primary key constraint or a non-clustered index on a table after the wizard generated the table, the Schema Generation Wizard does not drop that table because it did not create the constraint or the index.
When the Schema Generation Wizard is rerun, it reuses the same data source and data source view that it used for the original generation. If you add a data source or a data source view, the wizard does not use it. If you delete the original data source or data source view after the initial generation, you must run the wizard from the beginning. All previous settings in the wizard are also deleted. Any existing objects in an underlying database that were bound to a deleted data source or data source view are treated as user-created objects the next time you run the Schema Generation Wizard.
If the data source view does not reflect the actual state of the underlying database at the time of generation, the Schema Generation Wizard may encounter errors when it generates the schemas for the subject area database and the staging area database. For example, if the data source view specifies that the data type for a column is set to int, but the data type for the column is actually set to string, the Schema Generation Wizard sets the data type for the foreign key to int to match the data source view and then fails when it creates the relationship because the actual data type is string.
On the other hand, if you change the data source connection string to a different database from the previous generation, no error is generated. The new database is used, and no change is made to the previous database.