Creating a Local Cube

Concepts for creating local cubes are described in Creating Local Cubes. This topic describes the code required to perform this task.

To create a local cube, use the CREATE CUBE statement. This statement defines the cube’s structure and is passed in the connection string for processing. The connection string is a concatenated set of property name=value pairs, separated with semicolons. The order of the pairs is not important, provided that all required pairs are present. For more information about CREATE CUBE, see CREATE CUBE.

The following sample code, in combination with the sample code in the following topics up to and including Populating Cube Data, provides a complete sample Microsoft® Visual Basic® program for creating a local MOLAP cube. (It can be modified easily to create a local ROLAP cube as described in Populating the Dimensions Structure.) This sample code creates a cube from the sample FoodMart database (foodmart.mdb), which is provided with Microsoft SQL Server™ OLAP Services.

The code in this topic includes the CREATE CUBE statement. After the CREATE CUBE statement, an INSERT INTO statement must be used to populate the cube.

Dim cnCube As ADODB.Connection

Dim s As String

Dim strProvider As String

Dim strDataSource As String

Dim strSourceDSN As String

Dim strSourceDSNSuffix As String

Dim strCreateCube As String

Dim strInsertInto As String

On Error GoTo Error_cmdCreateCubeFromDatabase

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

'* Add Provider that will process the connection string.

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

strProvider = "PROVIDER=MSOLAP"

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

'* Add DataSource, the name of the cube file (.cub)

'* that will be created.

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

strDataSource = "DATA SOURCE=c:\warecube.cub"

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

'* Add Source DSN, the connection string for where the data comes from.

'* We need to quote the value so it is parsed as one value.

'* This can either be an ODBC connection string or

'* an OLE DB connection string.

'* (As returned by the Data Source Locator component.)

'*

'* strSourceDSN = "SOURCE_DSN=""DRIVER=Microsoft Access Driver (*.mdb);DBQ=\\machue1\Samples\Sales.MDB"";"

'*

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

strSourceDSN = "SOURCE_DSN=FoodMart"

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

'* We may have some other parameters that we want applied

'* at run time, but not stored in the cube file,

'* or returned in the output string.

'* Example:

'* strSourceDSNSuffix = "UID=;PWD="

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

'* Add CREATE CUBE. This defines the structure of the cube,

'* but not the data in it.

'* The BNF for this statement is in the

'* Microsoft SQL Server OLAP Services documentation.

'* Note: The names are quoted with square brackets.

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

strCreateCube = "CREATECUBE=CREATE CUBE Mycube( "

strCreateCube = strCreateCube & "DIMENSION [Product],"

strCreateCube = strCreateCube & "LEVEL [All Products] TYPE ALL,"

strCreateCube = strCreateCube & "LEVEL [Product Family] ,"

strCreateCube = strCreateCube & "LEVEL [Product Department] ,"

strCreateCube = strCreateCube & "LEVEL [Product Category] ,"

strCreateCube = strCreateCube & "LEVEL [Product Subcategory] ,"

strCreateCube = strCreateCube & "LEVEL [Brand Name] ,"

strCreateCube = strCreateCube & "LEVEL [Product Name] ,"

strCreateCube = strCreateCube & "DIMENSION [Store],"

strCreateCube = strCreateCube & "LEVEL [All Stores] TYPE ALL,"

strCreateCube = strCreateCube & "LEVEL [Store Country] ,"

strCreateCube = strCreateCube & "LEVEL [Store State] ,"

strCreateCube = strCreateCube & "LEVEL [Store City] ,"

strCreateCube = strCreateCube & "LEVEL [Store Name] ,"

strCreateCube = strCreateCube & "DIMENSION [Store Type],"

strCreateCube = strCreateCube & "LEVEL [All Store Type] TYPE ALL,"

strCreateCube = strCreateCube & "LEVEL [Store Type] ,"

strCreateCube = strCreateCube & "DIMENSION [Time] TYPE TIME,"

strCreateCube = strCreateCube & "HIERARCHY [Column],"

strCreateCube = strCreateCube & "LEVEL [All Time] TYPE ALL,"

strCreateCube = strCreateCube & "LEVEL [Year] TYPE YEAR,"

strCreateCube = strCreateCube & "LEVEL [Quarter] TYPE QUARTER,"

strCreateCube = strCreateCube & "LEVEL [Month] TYPE MONTH,"

strCreateCube = strCreateCube & "LEVEL [Week] TYPE WEEK,"

strCreateCube = strCreateCube & "LEVEL [Day] TYPE DAY,"

strCreateCube = strCreateCube & "HIERARCHY [Formula],"

strCreateCube = strCreateCube & "LEVEL [All Formula Time] TYPE ALL,"

strCreateCube = strCreateCube & "LEVEL [Year] TYPE YEAR,"

strCreateCube = strCreateCube & "LEVEL [Quarter] TYPE QUARTER,"

strCreateCube = strCreateCube & "LEVEL [Month] TYPE MONTH OPTIONS (SORTBYKEY) ,"

strCreateCube = strCreateCube & "DIMENSION [Warehouse],"

strCreateCube = strCreateCube & "LEVEL [All Warehouses] TYPE ALL,"

strCreateCube = strCreateCube & "LEVEL [Country] ,"

strCreateCube = strCreateCube & "LEVEL [State Province] ,"

strCreateCube = strCreateCube & "LEVEL [City] ,"

strCreateCube = strCreateCube & "LEVEL [Warehouse Name] ,"

strCreateCube = strCreateCube & "MEASURE [Store Invoice] "