Using the CREATE CUBE Statement

Analysis Services Programming

previous page next page

Analysis Services Programming

Using the CREATE CUBE Statement

The following topic describes the first two steps in creating a local cube: defining the local cube's dimensions, and defining the local cube's measures.

Defining Dimensions

The first step in creating a local cube is to define its dimensions and levels.

The example code contained at the end of this topic creates a local cube called C:\Warecube.cub from the sample FoodMart 2000 database (FoodMart 2000.mdb), which is provided with Microsoft® SQL Server™ 2000 Analysis Services. The cube has the following structure:

Store Dimension

Level	Data Type
All Stores	ALL
Store Country	Default
Store State	Default
Store City	Default
Store Name	Default

Store Type Dimension

Level	Data Type
All Store Types	ALL
Store Type	Default

Time Dimension

Column Hierarchy

Level	Data Type
Year	YEAR
Quarter	QUARTER
Month	MONTH
Week	WEEK
Day	DAY

Formula Hierarchy

Level	Data Type
Year	YEAR
Quarter	QUARTER
Month	MONTH

Warehouse Dimension

Level	Data Type
All Warehouse	ALL
Country	Default
State Province	Default
City	Default
Warehouse Name	Default

Defining Measures

The next step in building a local cube is to define the measures that will be used by that cube. The following table describes the measures used in the example at the end of this topic.

Measure	Function	Format
Store Invoice	Sum	#.#
Supply Time	Sum	#.#
Warehouse Cost	Sum	#.#
Warehouse Sales	Sum	#.#
Units Shipped	Sum	#.#
Units Ordered	Sum	#.#

After the dimensions and measures are defined, they must be populated. For more information about populating a cube's dimensions and measures, see Using the INSERT INTO Statement.

Examples

A. Defining a Local Cube's Dimensions

Use the following code to define the dimensions of a local 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 the provider that will process the connection string.
'*-----------------------------------------------------

strProvider = "PROVIDER=MSOLAP"

'*-----------------------------------------------------
'* Add the data source and the name of the cube file (.cub) 
'* that will be created.
'*-----------------------------------------------------

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

'*-----------------------------------------------------
'* Add the source DSN, the connection string for where the data comes from.
'* Quote the value so it is parsed as one value.
'* This can be either 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 2000"

'*-----------------------------------------------------
'* There may be some other parameters that you 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 
'* Analysis 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] ,"

B. Defining a Local Cube's Measures

In this example, each measure is named and assigned an aggregate function (an expression for a calculated measure) and a format for display.

strCreateCube = strCreateCube & "MEASURE [Store Invoice] "
    strCreateCube = strCreateCube & "Function Sum "
    strCreateCube = strCreateCube & "Format '#.#',"
strCreateCube = strCreateCube & "MEASURE [Supply Time] "
    strCreateCube = strCreateCube & "Function Sum "
    strCreateCube = strCreateCube & "Format '#.#',"
strCreateCube = strCreateCube & "MEASURE [Warehouse Cost] "
    strCreateCube = strCreateCube & "Function Sum "
    strCreateCube = strCreateCube & "Format '#.#',"
strCreateCube = strCreateCube & "MEASURE [Warehouse Sales] "
    strCreateCube = strCreateCube & "Function Sum "
    strCreateCube = strCreateCube & "Format '#.#',"
strCreateCube = strCreateCube & "MEASURE [Units Shipped] "
    strCreateCube = strCreateCube & "Function Sum "
    strCreateCube = strCreateCube & "Format '#.#',"
strCreateCube = strCreateCube & "MEASURE [Units Ordered] "
    strCreateCube = strCreateCube & "Function Sum "
    strCreateCube = strCreateCube & "Format '#.#')"

previous page start next page