• A string containing the location of the cube file to be created.

• The source DSN, which specifies the data source for the local cube.

• A string containing a CREATE CUBE statement.

• A string containing an INSERT INTO statement.

After you assign the combined value of the strings to the ConnectionString property, you simply issue an Open command on the Connection object, and the local cube is created.

The Location

The local file Location string contains the full path and file name of the local cube that is going to be created:

LOCATION= C:\Temp\20010320LocalCube.cub

The Source DSN

The source DSN contains a reference to the provider, data source, and the initial catalog. To create a cube from the sample FoodMart database located in Analysis Services on the local machine, you could use a source DSN like this:

SOURCE_DSN="Provider=MSOLAP;data source=LocalHost;INITIAL CATALOG=FoodMart 2000"

The Create Cube Statement

You specify each of the cube's dimensions, hierarchies, levels, member properties, measures, and calculated members in the Create Cube statement. Here's a sample of a Create Cube statement:

CREATECUBE=CREATE CUBE LocalCube
  (
  DIMENSION [Customers],
      LEVEL [All Customers] TYPE ALL,
      LEVEL [Country] OPTIONS(SORTBYNAME),
      LEVEL [State Province] OPTIONS(SORTBYNAME),
  DIMENSION [Product],
      LEVEL [All Product] TYPE ALL,
      LEVEL [Product Family] OPTIONS(SORTBYNAME),
      LEVEL [Product Department] OPTIONS(SORTBYNAME),
      LEVEL [Product Category] OPTIONS(SORTBYNAME),
      LEVEL [Product Subcategory] OPTIONS(SORTBYNAME),
  DIMENSION [Store],
      LEVEL [All Store] TYPE ALL,
      LEVEL [Store Country] OPTIONS(SORTBYNAME),
      LEVEL [Store State] OPTIONS(SORTBYNAME),
      LEVEL [Store City] OPTIONS(SORTBYNAME),
      LEVEL [Store Name] OPTIONS(SORTBYPROPERTY [Store Sqft]),
        PROPERTY [Store Sqft] TYPE REGULAR CAPTION "Store Sqft",
        PROPERTY [Store Manager] TYPE Person_Full_Name CAPTION "Manager",
        PROPERTY [Store Type] TYPE Regular CAPTION "Store Type",
  DIMENSION [Time] TYPE TIME,
      LEVEL [All Time] TYPE ALL,
      LEVEL [Year] TYPE YEAR OPTIONS(SORTBYNAME),
      LEVEL [Month] TYPE MONTH OPTIONS(SORTBYKEY),
  DIMENSION [Yearly Income],
      LEVEL [All Yearly Income] TYPE ALL,
      LEVEL [Yearly Income] OPTIONS(SORTBYNAME),
  MEASURE [Sales Count] Function COUNT FORMAT '#' TYPE DBTYPE_I8,
  MEASURE [Store Cost] Function SUM FORMAT 'Standard' TYPE DBTYPE_R8,
  MEASURE [Store Sales] Function SUM FORMAT 'Standard' TYPE DBTYPE_R8,
  MEASURE [Store Sales Net] Function SUM FORMAT 'Standard' TYPE DBTYPE_R8,
  MEASURE [Unit Sales] Function SUM FORMAT 'Standard' TYPE DBTYPE_R8,
  COMMAND (CREATE MEMBER LocalCube.Measures.[Profit]
     AS '[Measures].[Store Sales]-[Measures].[Store Cost]', FORMAT_STRING =
'Standard'),
  COMMAND (CREATE MEMBER LocalCube.Measures.[Sales Average]
     AS '[Measures].[Store Sales]/[Measures].[Sales Count]', FORMAT_STRING =
'Standard')
  )

The Insert Into Statement

The Insert Into statement has two clauses. The first clause specifies the specific cube structures that you are inserting into. The second clause is a Select statement that must provide a value for each of those structures.

You must provide a value for each level and member property that you have specified in the Create Cube statement. As the following example shows, you may supply both a value for the Name and the Key for each level.

This example loads the local cube from an Analysis Services cube. You can also write a Select statement that loads the local cube from a set of relational tables.

INSERTINTO=INSERT INTO LocalCube

  [Customers].[Country].NAME,
  [Customers].[Country].KEY,
  [Customers].[State Province].NAME,
  [Customers].[State Province].KEY,
  
  [Product].[Product Family].NAME,
  [Product].[Product Family].KEY,
  [Product].[Product Department].NAME,
  [Product].[Product Department].KEY,
  [Product].[Product Category].NAME,
  [Product].[Product Category].KEY,
  [Product].[Product Subcategory].NAME,
  [Product].[Product Subcategory].KEY,  

  [Store].[Store Country].NAME,
  [Store].[Store Country].KEY,
  [Store].[Store State].NAME,
  [Store].[Store State].KEY,
  [Store].[Store City].NAME,
  [Store].[Store City].KEY,
  [Store].[Store Name].NAME,
  [Store].[Store Name].[Store Sqft],
  [Store].[Store Name].[Store Manager],
  [Store].[Store Name].[Store Type],

  [Time].[Year].NAME,
  [Time].[Year].KEY,
  [Time].[Month].NAME,
  [Time].[Month].KEY,

  [Yearly Income].[Yearly Income].NAME,
  [Yearly Income].[Yearly Income].KEY,

  [Measures].[Sales Count],
  [Measures].[Store Cost],
  [Measures].[Store Sales],
  [Measures].[Store Sales Net],
  [Measures].[Unit Sales]
  )

SELECT

  [Sales].[Customers:Country],
  [Sales].[Customers:Country!KEY],
  [Sales].[Customers:State Province],
  [Sales].[Customers:State Province!KEY],
 
  [Sales].[Product:Product Family],
  [Sales].[Product:Product Family!KEY],
  [Sales].[Product:Product Department],
  [Sales].[Product:Product Department!KEY],
  [Sales].[Product:Product Category],
  [Sales].[Product:Product Category!KEY],
  [Sales].[Product:Product Subcategory],
  [Sales].[Product:Product Subcategory!KEY],

  [Sales].[Store:Store Country],
  [Sales].[Store:Store Country!KEY],
  [Sales].[Store:Store State],
  [Sales].[Store:Store State!KEY],
  [Sales].[Store:Store City],
  [Sales].[Store:Store City!KEY],
  [Sales].[Store:Store Name],
  [Sales].[Store:Store Name!Store Sqft],
  [Sales].[Store:Store Name!Store Manager],
  [Sales].[Store:Store Name!Store Type],

  [Sales].[Time:Year],
  [Sales].[Time:Year!KEY],
  [Sales].[Time:Month],
  [Sales].[Time:Month!KEY],

  [Sales].[Yearly Income:Yearly Income],
  [Sales].[Yearly Income:Yearly Income!KEY],

  [Sales].[Measures:Sales Count],
  [Sales].[Measures:Store Cost],
  [Sales].[Measures:Store Sales],
  [Sales].[Measures:Store Sales Net],
  [Sales].[Measures:Unit Sales]

FROM [Sales]

WHERE (([Sales].[Product:Product Family] = '[Product].[All Products].[Drink]')
     OR ([Sales].[Product:Product Family] = '[Product].[All Products].[Food]'))
AND (([Sales].[Customers:Country] = '[Customers].[All Customers].[Mexico]')
    OR ([Sales].[Customers:Country] = '[Customers].[All Customers].[USA]'))

The Connection String

You build the connection string as the combination of these four strings:

sConnection = sLocation & ";" & sSourceDSN & ";" & sCreateCube & ";" & sInsertInto

Sample VB Projects to Create Local Cubes

We have prepared two sample Visual Basic projects that create local cubes - one for SQL Server 7.0 and one for SQL Server 2000. Here are the steps you have to follow to make the code work:

1. Download the project and module files and open the project in Visual Basic 6.0.

2. Modify the location string so that it points to a valid path.

3. Modify the DSN so it points to a name of a valid Analysis Server (OLAP Server) that has the FoodMart sample database installed. (The code could fail if you have modified FoodMart from its original form.)

4. Execute the project. The local cube should be created.

5. Experiment with modifying the cube. Make sure you make changes in the appropriate places in the Create Cube and Insert Into strings. If, for example, you want to remove one of the levels of a dimension, remove the references to that level from the Create Cube command, from the Insert Into clause of the Insert Into command, and from the Select clause of the Insert Into command.

DOWNLOAD THE FILES FOR CREATE CUBE PROJECT FOR SQL SERVER 2000:

modCreateCube2000.bas     CreateLocalCube2000.vbp