Listing Creating an OLAP Query File

Option Explicit

Sub CreateOQY()

Dim IFileNumber As Long Dim sText As String Dim oSettings As New Settings Dim sFileName As String

On Error GoTo ErrHandler

' Obtain a file number to use IFileNumber = FreeFile

' Determine the file name and folder location.

sFileName = QueriesPath & oSettings.Item("OQYName").Va1ue & ".oqy"

' Open the file. Note - this overwrites any existing file ' with the same name in the same folder Open sFileName For Output As #1Fi1eNumber

' Output the OQY details

Print #1Fi1eNumber, "QueryType=OLEDB"

Print #1Fi1eNumber, "Version=1"

Print #1Fi1eNumber, "CommandType=Cube"

Print #1Fi1eNumber, "Connection=Provider=MSOLAP.2;" & _

"Data Source="; oSettings.Item("Database").Va1ue & ";" & _

"Initial Cata1og=" & oSettings.Item("Database").Va1ue & _

";C1ient Cache Size=25;Auto Synch Period=10000"

Print #1Fi1eNumber, "CommandText=" & oSettings.Item("Cube").Va1ue

' Close the file Close IFileNumber

Set oSettings = Nothing

MsgBox "Your OLAP connection has been created.", vbOKOnly Exit Sub ErrHandler:

MsgBox "An error occured while creating your OLAP connection. " _ & Err.Description, vbOKOnly

End Sub

' The file should be stored in the Queries folder associated with ' the current user. For example, assuming user name = shansen, ' the OQY file should be store in: ' C:\Documents and Settings\shansen\ _ ' Application Data\Microsoft\Queries Function QueriesPath() As String Dim sLibraryPath As String

' Get the AddIns path associated with the current user sLibraryPath = Application.UserLibraryPath

' The Queries path is a peer of AddIns QueriesPath = Rep1ace(sLibraryPath, _

"\Microsoft\AddIns\", "\Microsoft\Queries\") End Function

Figure 15.17

The settings used by CreateOQY

Figure 15.17

The settings used by CreateOQY

D M 11 'Mil 1 V fl - ctedle ÜLAF OOY.kIs

BS8

Fib Edt V»bv Ertsert Format Ipols

Data Window Hdp

- 3 X I

ijüu

> fc - d

AS -r f,

A 1

B

I c j

D T-

1

HAME

VALUE

TYPE

DESCHPT10II

2

Database

NPD

2

3

Cube

RAD2

2

i

Server_.

tttg-ctev01

_2

5

OQYName

Testing2

2

S

1

I

7

a

9

10

11

II *

► M \ Sheetl \Settlrojs/

—J<

>J

\ Ready

I

NOTE Listing 15.6 requires the Setting and Settings classes along with the Settings worksheet that were presented in Chapter 12.

Figure 15.17 shows a picture of the Settings worksheet used for this utility.

CreateOQY creates a file with the following output when used with the settings shown in Figure 15.17.

QueryType=OLEDB

Version=1

CommandType=Cube

Connection=Provider=MSOLAP.2;Data Source=dtg-dev01; _

Initial Catalog=NPD;Client Cache Size=25;Auto Synch Period=10000 CommandText=RAD2

NOTE The Connection line has been split onto two lines for the purposes of displaying it in this book. The actual output does not split this line.

0 0

Responses

Post a comment