DSOCtrl Overview

Monday Nov 1st 1999 by Darren Green
Share:

Use this component to easily process OLAP cubes or shared dimensions via T-SQL, ActiveX or ASP. Full documentation & examples provided.

Overview | Reference | Examples

Now supports Dimension processing with two new methods, ProcessDimension and ProcessAllDimensions. Example scripts updated.

DSOCtrl is a small ActiveX DLL I have written to help with processing OLAP Cubes & Dimensions. This can be used from VB, ActiveX Script, T-SQL or ASP.

My main drive for writing this was to allow me to process cubes from T-SQL scripts or stored procedures. Using the SQL Server sp_OAxxxx I could not get a reference to a cube, despite a posting to several newsgroups, so I decided to create a VB component..

One benefit is that all connection and processing errors are handled by the component, much simplifying the T-SQL required. I have also implemented a very nice ProcessDuration property. The main parameters required like Server, Database & Cube can be supplied individually by setting the relevant properties, or when executing one of the methods. The Connect methods are not really required, as supplying the correct parameters in the Process methods will do the same job. I made it public so as to provide any easy method to add extra functionality if required. It also allows you to connect without processing and retrieve the LastProcessed property.

The DSOCtrl component was developed using Visual Basic 5 SP3. It utilises the Microsoft Decision Support Objects library. Please tell me of any problems you encounter, or any suggestions for improvement.

The object is fully documented on the Reference page, and a selection of Examples is also provided.


Installation

Download the archive below and extract DSOCtrl.dll to your system directory (WINNT\System32\). Run the following command to register it -

Regsvr32 DSOCtrl.dll

This component must be installed on the SQL Server machine when used via T-SQL (sp_OAxxxx). When using via sp_OAxxxx the MSSQLServer service must be started using a non-system account, which is a member of the "OLAP Administrators" group on the OLAP server.


Download

Please note that this program is provided without any warranty or assurances of any kind. Use it at your own risk. Commercial distribution or distribution for profit in any form is strictly forbidden without prior permission from the author.

Any comments would be greatly appreciated.
Download DSOCtrl (~13K).

Version History

Version 0.3.2 (31/10/1999) - New dimension processing methods and properties added (ProcessDimension, ProcessAllDimensions)

Version 0.2.9 (02/10/1999) - First Public Release Version

Overview | Reference | Examples

Methods

ConnectCube
Use this method to connect to a MS OLAP cube. Errors will be raised for invalid parameters. Use prior to retrieving a cube specific property.
object.ConnectCube [Server], [Database], [Cube]
Server MS OLAP Services Server Name
Database OLAP Database Name
Cube Cube Name

ProcessCube
This obviously processes the cube. If connection has not been made, it will be done now using ConnectCube method internally. In addition the processing type can be specified.
object.ProcessCube [Server], [Database], [Cube], [ProcessType]
Server MS OLAP Services Server Name
Cube OLAP Database Name
Cube Cube Name
ProcessType Process Types

ConnectDimension
Use this method to connect to a shared dimension. Errors will be raised for invalid parameters. Use prior to retrieving a dimension specific property.
object.ConnectDimension [Server], [Database], [Dimension]
Server MS OLAP Services Server Name
Database OLAP Database Name
Dimension Shared Database Dimension Name

ProcessDimension
This obviously processes a shared dimension. If connection has not been made, it will be done now using ConnectDimension method internally. In addition the processing type can be specified.
object.ProcessDimension [Server], [Database], [Dimension], [ProcessType]
Server MS OLAP Services Server Name
Database OLAP Database Name
Dimension Shared Database Dimension Name
ProcessType Process Types

ProcessAllDimensions
Used to process all shared dimensions within a database. This interrogates the database to find all shared dimensions. The ProcessDimension method is called internally for each dimension found. The processing type specified will be applied to all dimensions found.
object.ProcessAllDimensions [Server], [Database], [ProcessType]
Server MS OLAP Services Server Name
Database OLAP Database Name
ProcessType Process Types

UnInitialize
Use this method to close the connection with the server, but not destroy the object.
object.UnInitialize

Properties

Cube
Returns or sets the Cube name for the object. (Not validated until required by a method)
object.Cube [=value]
DataType String
Modifiable Read/Write

Database
Returns or sets the Database name for the object. (Not validated until required by a method)
object.Database [=value]
DataType String
Modifiable Read/Write

Dimension
Returns or sets the current database dimension for the object. (Not validated until required by a method)
object.Dimension
DataType String
Modifiable Read/Write

LastProcessed
Returns the date and time the cube/dimension was last processed.
object.LastProcessed
DataType Date Time
Modifiable Read Only

ProcessDuration
Returns the time taken to process the cube/dimension(s). Only available after ProcessCube, ProcessDimension, or ProcessAllDimensions has been executed, otherwise returns zero.
object.ProcessDuration
DataType Date Time
Modifiable Read Only

ProcessType
Returns or sets the process type for the object. Must be a valid processType.
object.ProcessType [=value]
DataType DSOCtrl.ProcessType
Modifiable Read/Write

Server
Returns or sets the Server for the object. (Not validated until required by a method)
object.Server [=value]
DataType String
Modifiable Read/Write

Constants

ProcessType
This is used to define the process performed by one of the Process methods. This is a replica of the Microsoft DSO ProcessTypes enumerator.
Constant Value
processDefault 0
processFull 1
processRefreshData 2
Only the above three DSO values are supported. Please see Microsoft SQL Server OLAP Services Production Documentation for full descriptions.

Overview | Reference | Examples

The first few examples are for Visual Basic and are designed to show the ways in which the component can be used. All examples are based on a local server with the Foodmart sample OLAP database. To use VBScript instead, substitue Dim objDSOCtrl As New DSOCtrl.OLAPProc for Set objDSOCtrl = CreateObject("DSOCtrl.OLAPProc"). Remember to declare objDSOCtrl, and change the process type to a numeric, as the constants will be unavailable.


VB 1:
Perform a default cube process, as no ProcessType is specified, and return the time taken.

Dim objDSOCtrl As New DSOCtrl.OLAPProc
objDSOCtrl.ProcessCube ".", "FoodMart", "Sales"
MsgBox objDSOCtrl.ProcessDuration


VB 2:
Assign properties individually and perform a default cube process.

Dim objDSOCtrl As New DSOCtrl.OLAPProc
objDSOCtrl.Server = "."
objDSOCtrl.Database = "FoodMart"
objDSOCtrl.Cube = "Sales"
objDSOCtrl.ProcessType = processDefault
objDSOCtrl.ProcessCube


VB 3:
Use the ConnectCube method and return the last processed. Then process the cube and return the date again, and the time taken.

Dim objDSOCtrl As New DSOCtrl.OLAPProc
objDSOCtrl.ConnectCube ".", "FoodMart", "Sales"
MsgBox objDSOCtrl.LastProcessed
objDSOCtrl.ProcessCube , , , processFull
MsgBox objDSOCtrl.LastProcessed
MsgBox objDSOCtrl.ProcessDuration


T-SQL:

I have created a two stored procedure samples as wrappers around the sp_OAxxxx code required to utilise DSOCtrl. Both require the two OLE Automation Return Codes and Error Information stored procedures detailed in SQL Server Books Online (msoleerror.sql)

sp_DSOCtrl_ProcessCube (spDSOCtrlProcessCube.sql)
This example will process the cube Sales in the FoodMart DB on the local server.

EXEC master.dbo.sp_DSOCtrl_ProcessCube ".", "FoodMart", "Sales"

sp_DSOCtrl_ProcessAllDimensions (spDSOCtrlProcessAllDimensions.sql)
This example will process all dimensions in the FoodMart DB on the local server. The process duration is returned as an output parameter and printed.

DECLARE @ProcessDuration datetime
 EXEC master.dbo.sp_DSOCtrl_ProcessAllDimensions ".", "FoodMart",
   @ProcessDuration OUTPUT
 PRINT CONVERT(char(8), @ProcessDuration, 108)
Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved