The initial population of a dimension with data can be achieved by generating a custom procedure and then use the right-click context menu to select the Execute Custom Procedure via Scheduler option.
The dimension should be analyzed once the custom procedure is completed so that the database query optimizer can make use of the indexes.
For smaller dimensions (e.g. less than 500,000 rows) run the normal Update procedure against an empty dimension table. There is however a danger in this action in that the query optimizer will not have any information on the table, and hence will do a full table pass when adding each new row. For a very small dimension this will not be an issue, but it will rapidly become a major problem as the row count increases.
The problem with the initial update is that the database does not know to use the index that has been created for the business key, and hence does a full table pass when attempting to update/insert a record.
To prevent this problem, the generated code will issue an analyze statement after 1000 rows have been added to the table. The statement used is as follows:
SQL Server: update statistics dim_table with sample 5 percent
Oracle: analyze table dim_table estimate statistics sample 5 percent for all indexed columns
DB2: runstats on table dim_tablewhere dim_table is the name of the dimension.
This command is issued whenever 1000 or more new rows are added to a dimension.
If this is not required then the code should be removed from the update procedure. It may not be required if a plan has been locked in for the table.