Using the INSERT INTO Statement
This topic describes the next three steps necessary to build a local cube: populate the local cube's dimensions, populate the local cube's measures, and map the source data for the dimensions and measures onto the local cube's structure.
Populate the Dimensions
Populating a local cube with dimension members and measure data is accomplished using the INSERT INTO statement in the connection string, which follows the CREATE CUBE statement.
Caution If the name of the cube file to be created does not exist (as defined by the DBPROP_INIT_DATASOURCE property), it is created during the processing of this statement. If a cube file with that name already exists, it is overwritten with the new cube structure and data. If the name is not specified, a temporary name is assigned.
Use the portion of the INSERT INTO statement before the SELECT clause to identify the elements of the cube that will be populated from the data source. For more information, see INSERT INTO Statement.
Before using the INSERT INTO statement, the cube structure must be defined with a CREATE CUBE statement. For more information, see Building Local Cubes.
Populate the Measures
The measures of a cube are populated in the same way the dimensions are populated.
Map the Dimensions and Measures into the Cube Structure
Use a SELECT clause within the INSERT INTO statement to populate the dimension and level structures. This clause identifies the source tables and columns from the fact table.
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.
Examples
A. Populating the Dimensions
This example populates the elements of the cube defined in Building Local Cubes:
'*-----------------------------------------------------
'* Note: In some circumstances the SELECT clause may be passed through
'* to the relational database: For example, a stored procedure
'* could be passed in.
'* Note: Columns in the SELECT can be in any order. Just
'* adjust the order of the list of level/measure names to
'* match the order of columns in the SELECT clause.
'*-----------------------------------------------------
strInsertInto = strInsertInto & "INSERTINTO=INSERT INTO Mycube ( Product.[Product Family], Product.[Product Department],"
strInsertInto = strInsertInto & "Product.[Product Category], Product.[Product Subcategory],"
strInsertInto = strInsertInto & "Product.[Brand Name], Product.[Product Name],"
strInsertInto = strInsertInto & "Store.[Store Country], Store.[Store State], Store.[Store City],"
strInsertInto = strInsertInto & "Store.[Store Name], [Store Type].[Store Type], [Time].[Column],"
strInsertInto = strInsertInto & "[Time].Formula.Year, [Time].Formula.Quarter, [Time].Formula.Month.[Key],"
strInsertInto = strInsertInto & "[Time].Formula.Month.Name, Warehouse.Country, Warehouse.[State Province],"
strInsertInto = strInsertInto & "Warehouse.City, Warehouse.[Warehouse Name], Measures.[Store Invoice],"
strInsertInto = strInsertInto & "Measures.[Supply Time], Measures.[Warehouse Cost], Measures.[Warehouse Sales],"
strInsertInto = strInsertInto & "Measures.[Units Shipped], Measures.[Units Ordered] )"B. Populating the Measures
The following code shows how to populate of the measures with data from the fact table:
strInsertInto = strInsertInto & "inventory_fact_1997.store_invoice,"
strInsertInto = strInsertInto & "inventory_fact_1997.supply_time,"
strInsertInto = strInsertInto & "inventory_fact_1997.warehouse_cost,"
strInsertInto = strInsertInto & "inventory_fact_1997.warehouse_sales,"
strInsertInto = strInsertInto & "inventory_fact_1997.units_shipped,"
strInsertInto = strInsertInto & "inventory_fact_1997.units_ordered "
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]"C. Mapping the Dimensions and Measures onto the Local Cube
The following code shows how to populate dimensions and levels. It includes the SELECT clause.
'*-----------------------------------------------------
'* Add some options to the INSERT INTO if you 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 concatenated onto the end of
'* the INSERT INTO statement.
'* Analysis Services passes this
'* through to the source database if unable to parse it.
'* Note: For Analysis 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,"