Populating the Dimensions Structure

Use a SELECT clause within the INSERT INTO statement to populate the dimension structure. This clause identifies the source tables and columns.

If you are creating a local ROLAP cube,

• Precede the SELECT clause with an OPTIONS DEFER_DATA clause. (If the OPTIONS DEFER_DATA clause is omitted, a local MOLAP cube is created.)
• Remove the AS Coln clauses.

Note The order of columns in the SELECT clause must match the order of cube elements in the preceding INSERT INTO clause. So, the first column in the SELECT clause populates the first cube element in the INSERT INTO clause, the second populates the second, and so on.

The code in this topic includes the SELECT clause.

'*-----------------------------------------------------

'* Add some options to the INSERT INTO if we need to.

'* These can control if the SELECT clause is analyzed

'* or just passed through,

'* and if the storage mode is MOLAP or ROLAP (DEFER_DATA).

'* Examples:

'* strInsertInto = strInsertInto & " OPTIONS DEFER_DATA"

'* strInsertInto = strInsertInto & " OPTIONS ATTEMPT_ANALYSIS"

'*-----------------------------------------------------

'*-----------------------------------------------------

'* Add the SELECT clause of the INSERT INTO statement.

'* Note: SELECT is merely concatenated onto the end of

'* the INSERT INTO statement.

'* Microsoft SQL Server OLAP Services will pass this

'* through to the source database if unable to parse it.

'* Note: For OLAP Services to analyze the SELECT clause,

'* each column must be qualified with the table name.

'*-----------------------------------------------------

strInsertInto = strInsertInto & "SELECT product_class.product_family AS Col1,"

strInsertInto = strInsertInto & "product_class.product_department AS Col2,"

strInsertInto = strInsertInto & "product_class.product_category AS Col3,"

strInsertInto = strInsertInto & "product_class.product_subcategory AS Col4,"

strInsertInto = strInsertInto & "product.brand_name AS Col5,"

strInsertInto = strInsertInto & "product.product_name AS Col6,"

strInsertInto = strInsertInto & "store.store_country AS Col7,"

strInsertInto = strInsertInto & "store.store_state AS Col8,"

strInsertInto = strInsertInto & "store.store_city AS Col9,"

strInsertInto = strInsertInto & "store.store_name AS Col10,"

strInsertInto = strInsertInto & "store.store_type AS Col11,"

strInsertInto = strInsertInto & "time_by_day.the_date AS Col12,"

strInsertInto = strInsertInto & "time_by_day.the_year AS Col13,"

strInsertInto = strInsertInto & "time_by_day.quarter AS Col14,"

strInsertInto = strInsertInto & "time_by_day.month_of_year AS Col15,"

strInsertInto = strInsertInto & "time_by_day.the_month AS Col16,"

strInsertInto = strInsertInto & "warehouse.warehouse_country AS Col17,"

strInsertInto = strInsertInto & "warehouse.warehouse_state_province AS Col18,"

strInsertInto = strInsertInto & "warehouse.warehouse_city AS Col19,"

strInsertInto = strInsertInto & "warehouse.warehouse_name AS Col20,"

strInsertInto = strInsertInto & "inventory_fact_1997.store_invoice AS Col21,"

strInsertInto = strInsertInto & "inventory_fact_1997.supply_time AS Col22,"

strInsertInto = strInsertInto & "inventory_fact_1997.warehouse_cost AS Col23,"

strInsertInto = strInsertInto & "inventory_fact_1997.warehouse_sales AS Col24,"

strInsertInto = strInsertInto & "inventory_fact_1997.units_shipped AS Col25,"

strInsertInto = strInsertInto & "inventory_fact_1997.units_ordered AS Col26 "

strInsertInto = strInsertInto & "From [inventory_fact_1997], [product], [product_class], [time_by_day], [store], [warehouse] "

strInsertInto = strInsertInto & "Where [inventory_fact_1997].[product_id] = [product].[product_id] And "

strInsertInto = strInsertInto & "[product].[product_class_id] = [product_class].[product_class_id] And "

strInsertInto = strInsertInto & "[inventory_fact_1997].[time_id] = [time_by_day].[time_id] And "

strInsertInto = strInsertInto & "[inventory_fact_1997].[store_id] = [store].[store_id] And "

strInsertInto = strInsertInto & "[inventory_fact_1997].[warehouse_id] = [warehouse].[warehouse_id]"