|
import msado15.*;
// This example requires SQL Server 2000
with the Microsoft Analysis Services component.
// Also, the Analysis Services sample database, FoodMart2000, must be
installed.
// Register msolap.dll and MSADO15.dll using regsvr32
// Generate the proxies for MSADO15.dll
in a subdirectory called "msado15" using 'com2java' on
// MSADO15.dll, specifying "msado15"
as the package.
// This example will create a cube based on data contained in the FoodMart2000
database.
// The console output of this samples is as follows:
// 1. Connection string before being used to open cube
// 2. Confirmation of cube creation
// 3. Connection string as received from cube (should be the same as 1.)
// 4. Done!
public class
CreateCubeExample {
public
static
void
main(String[] args) {
String CUBE_FILE = "DATA SOURCE=c:\\temp\\CreateCubeSample.cub";
String s = "";
String strProvider = "";
String strDataSource = "";
String strSourceDSN = "";
String strSourceDSNSuffix = "";
String strCreateCube = "";
String strInsertInto = "";
try
{
com.linar.ocxhost.Container ocxHost = new
com.linar.ocxhost.Container();
msado15.Connection cnCube = new
msado15.Connection(ocxHost.create("ADODB.Connection"));
/*-------------------------------------------------------------------------------------
* To build a cube file the CREATE CUBE
statement is used to define the cubes structure
* and this structure is passed as the connection string for
processing.
* The connection string is a concatenated set of keyword=value pairs, delimited with
* semicolons. The
order of the keyword=value pairs is not important as long as all pairs
* necessary are present. The code to follow
is a sample of a connection string against
* a DSN which is based on the Sample FoodMart 2000 Microsoft
Access database.
*-------------------------------------------------------------------------------------
*-------------------------------------------------------------------------------------
* Add Provider, the name of the engine
that will process the connection string.
*-------------------------------------------------------------------------------------*/
strProvider = "PROVIDER=MSOLAP";
/*------------------------------------------------------------------------------------
* Add DataSource, the name of the cube
file (.cub) that will be created.
*-------------------------------------------------------------------------------------*/
strDataSource = CUBE_FILE;
/*------------------------------------------------------------------------------------
* 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=\\platoue1\Samples\Sales.MDB;";
*
*-------------------------------------------------------------------------------------*/
strSourceDSN = "SOURCE_DSN=FoodMart
2000";
/*------------------------------------------------------------------------------------
* 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 is somewhere in the
documentation.
* Note: The names are quoted with square
brackets.
*-------------------------------------------------------------------------------------*/
strCreateCube = "CREATECUBE=CREATE
CUBE Sample( ";
strCreateCube += "DIMENSION
[Product],";
strCreateCube += "LEVEL
[All Products] TYPE ALL,";
strCreateCube += "LEVEL
[Product Family] ,";
strCreateCube += "LEVEL
[Product Department] ,";
strCreateCube += "LEVEL
[Product Category] ,";
strCreateCube += "LEVEL
[Product Subcategory] ,";
strCreateCube += "LEVEL
[Brand Name] ,";
strCreateCube += "LEVEL
[Product Name] ,";
strCreateCube += "DIMENSION
[Store],";
strCreateCube += "LEVEL
[All Stores] TYPE ALL,";
strCreateCube += "LEVEL
[Store Country] ,";
strCreateCube += "LEVEL
[Store State] ,";
strCreateCube += "LEVEL
[Store City] ,";
strCreateCube += "LEVEL
[Store Name] ,";
strCreateCube += "DIMENSION
[Store Type],";
strCreateCube += "LEVEL
[All Store Type] TYPE ALL,";
strCreateCube += "LEVEL
[Store Type] ,";
strCreateCube += "DIMENSION
[Time] TYPE TIME,";
strCreateCube += "HIERARCHY
[Column],";
strCreateCube += "LEVEL
[All Time] TYPE ALL,";
strCreateCube += "LEVEL
[Year] TYPE YEAR,";
strCreateCube += "LEVEL
[Quarter] TYPE QUARTER,";
strCreateCube += "LEVEL
[Month] TYPE MONTH,";
strCreateCube += "LEVEL
[Week] TYPE WEEK,";
strCreateCube += "LEVEL
[Day] TYPE DAY,";
strCreateCube += "HIERARCHY
[Formula],";
strCreateCube += "LEVEL
[All Formula Time] TYPE ALL,";
strCreateCube += "LEVEL
[Year] TYPE YEAR,";
strCreateCube += "LEVEL
[Quarter] TYPE QUARTER,";
strCreateCube += "LEVEL
[Month] TYPE MONTH OPTIONS (SORTBYKEY) ,";
strCreateCube += "DIMENSION
[Warehouse],";
strCreateCube += "LEVEL
[All Warehouses] TYPE ALL,";
strCreateCube += "LEVEL
[Country] ,";
strCreateCube += "LEVEL
[State Province] ,";
strCreateCube += "LEVEL
[City] ,";
strCreateCube += "LEVEL
[Warehouse Name] ,";
strCreateCube += "MEASURE
[Store Invoice] ";
strCreateCube += "Function
Sum ";
strCreateCube += "Format
'#.#',";
strCreateCube += "MEASURE
[Supply Time] ";
strCreateCube += "Function
Sum ";
strCreateCube += "Format
'#.#',";
strCreateCube += "MEASURE
[Warehouse Cost] ";
strCreateCube += "Function
Sum ";
strCreateCube += "Format
'#.#',";
strCreateCube += "MEASURE
[Warehouse Sales] ";
strCreateCube += "Function
Sum ";
strCreateCube += "Format
'#.#',";
strCreateCube += "MEASURE
[Units Shipped] ";
strCreateCube += "Function
Sum ";
strCreateCube += "Format
'#.#',";
strCreateCube += "MEASURE
[Units Ordered] ";
strCreateCube += "Function
Sum ";
strCreateCube += "Format
'#.#')";
/*------------------------------------------------------------------------------------
*Add INSERT INTO. This defines where
the data comes from, and how it maps
* into the already-defined cube structure.
* Note:The SELECT clause might just be
passed through to the relational database.
* So I could pass in a stored procedure,
for example.
* Note: Columns in the SELECT can be in
any order. One merely has to
* adjust the ordering of the list of level/measure
names to match the SELECT ordering.
*-------------------------------------------------------------------------------------*/
strInsertInto += "INSERTINTO=INSERT
INTO Sample( Product.[Product Family], Product.[Product Department],";
strInsertInto += "Product.[Product
Category], Product.[Product Subcategory],";
strInsertInto += "Product.[Brand
Name], Product.[Product Name],";
strInsertInto += "Store.[Store
Country], Store.[Store State], Store.[Store City],";
strInsertInto += "Store.[Store
Name], [Store Type].[Store Type], [Time].[Column],";
strInsertInto += "[Time].Formula.Year,
[Time].Formula.Quarter, [Time].Formula.Month.[Key],";
strInsertInto += "[Time].Formula.Month.Name,
Warehouse.Country, Warehouse.[State Province],";
strInsertInto += "Warehouse.City,
Warehouse.[Warehouse Name], Measures.[Store Invoice],";
strInsertInto += "Measures.[Supply
Time], Measures.[Warehouse Cost], Measures.[Warehouse Sales],";
strInsertInto += "Measures.[Units
Shipped], Measures.[Units Ordered] )";
/*------------------------------------------------------------------------------------
* 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).
* Example:
* 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.
* OLAP Service will pass this through to
the source database if unable to parse it.
* Note: that for OLAP Service to analyze
the SELECT clause, each column must be
* qualified with the table name.
*-------------------------------------------------------------------------------------*/
strInsertInto += "SELECT
product_class.product_family AS Col1,";
strInsertInto += "product_class.product_department
AS Col2,";
strInsertInto += "product_class.product_category
AS Col3,";
strInsertInto += "product_class.product_subcategory
AS Col4,";
strInsertInto += "product.brand_name
AS Col5,";
strInsertInto += "product.product_name
AS Col6,";
strInsertInto += "store.store_country
AS Col7,";
strInsertInto += "store.store_state
AS Col8,";
strInsertInto += "store.store_city
AS Col9,";
strInsertInto += "store.store_name
AS Col10,";
strInsertInto += "store.store_type
AS Col11,";
strInsertInto += "time_by_day.the_date
AS Col12,";
strInsertInto += "time_by_day.the_year
AS Col13,";
strInsertInto += "time_by_day.quarter
AS Col14,";
strInsertInto += "time_by_day.month_of_year
AS Col15,";
strInsertInto += "time_by_day.the_month
AS Col16,";
strInsertInto += "warehouse.warehouse_country
AS Col17,";
strInsertInto += "warehouse.warehouse_state_province
AS Col18,";
strInsertInto += "warehouse.warehouse_city
AS Col19,";
strInsertInto += "warehouse.warehouse_name
AS Col20,";
strInsertInto += "inventory_fact_1997.store_invoice
AS Col21,";
strInsertInto += "inventory_fact_1997.supply_time
AS Col22,";
strInsertInto += "inventory_fact_1997.warehouse_cost
AS Col23,";
strInsertInto += "inventory_fact_1997.warehouse_sales
AS Col24,";
strInsertInto += "inventory_fact_1997.units_shipped
AS Col25,";
strInsertInto += "inventory_fact_1997.units_ordered
AS Col26 ";
strInsertInto += "From
[inventory_fact_1997], [product], [product_class], [time_by_day], [store],
[warehouse] ";
strInsertInto += "Where
[inventory_fact_1997].[product_id] = [product].[product_id] And ";
strInsertInto += "[product].[product_class_id]
= [product_class].[product_class_id] And ";
strInsertInto += "[inventory_fact_1997].[time_id]
= [time_by_day].[time_id] And ";
strInsertInto += "[inventory_fact_1997].[store_id]
= [store].[store_id] And ";
strInsertInto += "[inventory_fact_1997].[warehouse_id]
= [warehouse].[warehouse_id]";
/*------------------------------------------------------------------------------------
* Note:Since the cube is not created anywhere,
when the connection object is opened
* the cube is built on
the fly with the information in the connection string
* Set a New ADODB Connection Object
* Create the cube by passing concatenated
connection string to Open method of the connection object.
*-------------------------------------------------------------------------------------*/
System.out.println("CreateCubeExample");
System.out.println("---------------------------");
System.out.println("strDataSource:
" + strDataSource + ";
");
System.out.println("---------------------------");
System.out.println("strSourceDSN:
" + strSourceDSN + ";
");
System.out.println("---------------------------");
System.out.println("strCreateCube:
" + strCreateCube + ";
");
System.out.println("---------------------------");
System.out.println("strInsertInto:
" + strInsertInto + ";");
s = strProvider + ";"
+ strDataSource + ";"
+ strSourceDSN + ";"
+ strCreateCube + ";"
+ strInsertInto + ";";
cnCube.open(s, null,
null,
-1);
System.out.println("---------------------------");
System.out.println("Successfully
opened cube!");
System.out.println("---------------------------");
System.out.println(cnCube.getConnectionString());
System.out.println("---------------------------");
System.out.println("DONE!");
} catch
(Exception e) {
System.out.println(e.toString());
e.printStackTrace();
} finally
{
com.linar.jintegra.Cleaner.releaseAll();
}
}
}
|
