Введение в OLAP. Часть 10
Листинг 1
Private Sub Command1_Click()
Dim cnCube As ADODB.Connection
Dim s As String
Dim Prov As String
Dim DS As String
Dim SourceDSN As String
Dim SourceDSNSuffix As String
Dim CreateCube As String
Dim InsertInto As String
Dim Sel As String
Prov = "PROVIDER=MSOLAP"
DS = "DATA SOURCE=d:\nw.cub"
SourceDSN = "SOURCE_DSN=""DRIVER=SQL Server;SERVER=MAINDESK;UID=sa;DATABASE=Northwind"""
CreateCube = "CREATECUBE=CREATE CUBE Sales( "
CreateCube = CreateCube & " DIMENSION [Country], LEVEL [All] TYPE ALL, LEVEL [Country], LEVEL [City], LEVEL [CustomerID],"
CreateCube = CreateCube & " DIMENSION [Salesperson], LEVEL [All] TYPE ALL, LEVEL [Salesperson],"
CreateCube = CreateCube & " DIMENSION [ShipperName], LEVEL [All] TYPE ALL, LEVEL [ShipperName],"
CreateCube = CreateCube & " DIMENSION [CategoryName],LEVEL [All] TYPE ALL, LEVEL [CategoryName], LEVEL [ProductName],"
CreateCube = CreateCube & " DIMENSION [OrderDate] TYPE TIME, LEVEL [All] TYPE ALL, LEVEL [Year] TYPE YEAR, LEVEL [Quarter] TYPE QUARTER, LEVEL [Month] TYPE MONTH, LEVEL [Day] TYPE DAY,"
CreateCube = CreateCube & " MEASURE [Sum Of ExtendedPrice] FUNCTION SUM, MEASURE [Sum Of Quantity] FUNCTION SUM )"
InsertInto = InsertInto & "InsertInto=INSERT INTO Sales ("
InsertInto = InsertInto & "[Country].[Country], [City], [CustomerID], [Sum Of ExtendedPrice], [Sum Of Quantity], [ShipperName].[ShipperName], [Salesperson].[Salesperson],"
InsertInto = InsertInto & "[CategoryName].[CategoryName], [ProductName], [OrderDate]) OPTIONS ATTEMPT_ANALYSIS "
Sel = " SELECT Invoices.Country, Invoices.City, Invoices.CustomerID, Invoices.ExtendedPrice, Invoices.Quantity, Invoices.ShipperName,"
Sel = Sel + "Invoices.Salesperson, Categories.CategoryName, Products.ProductName, Invoices.OrderDate "
Sel = Sel + "FROM Northwind.dbo.Categories Categories, Northwind.dbo.Invoices Invoices, Northwind.dbo.Products Products "
Sel = Sel + "WHERE Categories.CategoryID = Products.CategoryID AND Invoices.ProductID = Products.ProductID"
InsertInto = InsertInto & Sel
Set cnCube = New ADODB.Connection
s = Prov & ";" & DS & ";" & SourceDSN & ";" & CreateCube & ";" & InsertInto & ";"
cnCube.Open s
End Sub
