OLAP connection made through MS Query 1

[blom0344]

When working with offline OLAP cubes from Excel one must create a new connection with MS Query.
Is this connection a file that can be distributed?

Ties Blom
Information analyst

 

[xlbo]

the connection is a property of the querytable in excel and can be manipulated as any text string

the object model is:

Worksheet>QueryTable

so

msgbox worksheets("SheetName").Querytables(1).connection

will pop up a message box telling you what the current connection is for the 1st querytable in the specified worksheet

to change it, you can use something like:

worksheets("SheetName").Querytables(1).connection = "This is the new connection string"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[blom0344]

Geoff,

I think I found the OLAP connections as .oqy files locally on my PC.
I need to get offline cubes working for about 15 salesreps and I wanted to check If I can distribute cube,connection and the Excel sheet with the pivottables.

Otherwise I have to configure every laptop by hand (at least other people have to) and that is pretty awkward ...

Ties Blom
Information analyst

 

[xlbo]

you could do but it might be easier to distribute the cube and the excel sheet - as long as they are in the same folder it should be pretty straightforward to set up the connection programmatically.

Can you give an example of the connection string to an OLAP cube ? I ask because I think it will involve 2 parameters - the 1st will be the type of connection - this will specify whether it is an OLAP cube or a database table etc. The 2nd should be the location of the cube - this should be the only part of the connection you need to modify

I am thinking that the string will be along the lines of:

.Connection = "OLAP;DSN=[COLOR=red]DatabaseType[/color];DBQ=
[COLOR=red]Path:\To\Location\Of\Cube\And\CubeName
[/color];DefaultDir=[COLOR=red]Path:\To\Location\Of\Cube
[/color];DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

This can be altered to:

myPath = Thisworkbook.path
myFileName = "OLAP Cube Name + Extension"
.Connection = "OLAP;DSN=[COLOR=red]DatabaseType
[/color];DBQ=" & myPath & "\" & myFileName & ";
DefaultDir=" & myPath & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

Thisworkbook.Path will refer to the path of wherever the excel workbook is saved - given that and a known OLAP cube name, you should be able to set this connection property in the workbook_OPEN event of the workbook

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[blom0344]

I am pretty confused now..
The .oqy file contains the following:

QueryType=OLEDB
Version=1
CommandType=Cube
Connection=Provider=MSOLAP.2;Data Source=D:\OLAP\OLAP_SALES_SALES_ALL.cub;Initial Catalog=SALES_ALL;Client Cache Size=25;Auto Synch Period=10000
CommandText=SALES_ALL

I use MS Query to set up the connection. I do not use VBA to create the connection..
In your example, would it be possible to create the connection without having MS Query installed?

Ties Blom
Information analyst

 

[xlbo]

I understand what you are saying but there are many ways to skin a cat - you can set everything up through MSQuery and that will indeed create a .dqy or .oqy file which holds that info

What I am saying is that you can reference all this information within VBA - you will need to amend the part of the connection that relates to the path of the cube ie:

D:\OLAP\OLAP_SALES_SALES_ALL.cub

will need to be wherever the cube is to be saved - you can manipulate this using VBA via the connection property of the querytable object.

AFAIK, you do not actually need the .oqy file as the properties are all saved with the querytable in the excel file

Are you familair with VBA at all ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[blom0344]

Yes, I have worked with VBA before.
However we purchased a special tool for creating offline cubes that specified MS Query for setting up the connection.
You were the first to point out that VBA would be a different approach.
I like that, cause MS Query itself is crap. I keep running into problems anyway.
The finished Workbook should contain about 10 pivottables (the users not being able to create them on the fly)
All these pivottables will fetch data from the same cube.
Can I set up the connection in VBA for all those objects?

The idea is that they get a new cube every month,replace the old cube and voila, get the new data once entering the Excel worksheet.

I know I am wearing you down a bit , but my schedule is very tight. (But since we have such related jobs, perhaps you do not mind )

Apart from the VBA/MS Query bit, we have started developing with Excel2002 (which seems to work MUCH better with OLAP), but can't get the proper upgrade of office to everyone in time (so I am stuck with Excel2000)

Thanks for time spent on this...

Ties Blom
Information analyst

 

[xlbo]

Ties (or Blom - apologies I don't know which is your 1st name) - do not worry about time - if I didn't have enough, I wouldn't be posting - besides which, you have given me helpful information on BOBJ in the past - I am happy to help in any way I can

ok - here's the deal

You set up the query using MSQuery
This creates a QueryTable object in an excel worksheet
This QueryTable object has properties that can be accessed by VBA
One of these properties is the connection property

So - you set up ONE workbook with the appropriate links to the OLAP cubes and therefore the Querytables

Within the workbook, there is an OPEN event which fires when the workbook is opened. You can use this event to run code that changes the connection string of the querytable to look in the same directory that the workbook is saved in

so - you set up the queries manually and then use VBA to alter the connection settings when the workbook is opened..


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[blom0344]

Okay Geoff, I'm dutch, so firstname is Ties (from Titus)

The following seems to work - more or less - :

Private Sub Workbook_Open()
Dim i As Integer
i = 1
Rem With ActiveWorkbook.PivotCaches(1)
For Each pc In ActiveWorkbook.PivotCaches
    With ActiveWorkbook.PivotCaches(i)
    .LocalConnection = _
        "OLEDB;Provider=MSOLAP.2;Data Source=D:\OLAP\OLAP_SALES_SALES_ALL.cub"
    .UseLocalConnection = True
    pc.RefreshOnFileOpen = True
    End With
Rem End With
i = i + 1
Next
End Sub

Somehow refreshing the data with MS Query is not possible anymore. I am pretty sure we had that working with Excel2002, but my PC may be having troubles with both Excel2000 and Excel2002

I'm not at all an Excel guy, so creating pivottables is something mostly done by the superusers.
I guess that looping through all the caches will take care of all pivottables created...


Ties Blom
Information analyst

 

[SkipVought]

Private Sub Workbook_Open()

For Each pc In ActiveWorkbook.PivotCaches
    With pc
      .LocalConnection = _
        "OLEDB;Provider=MSOLAP.2;Data Source=D:\OLAP\OLAP_SALES_SALES_ALL.cub"
      .UseLocalConnection = True
      .RefreshOnFileOpen = True
    End With
Next
End Sub

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[xlbo]

Thanks for the clarification Ties - only thing you will need to watch out for is that everyone will need to save their cube files in D:\OLAP\

If this can change, I would suggest that you use something like:

Data Source=" & thisworkbook.path & "\OLAP_SALES_SALES_ALL.cub"

which will make the connection look in the same folder as the workbook is saved in

If they will all save in the appropriate folder then you don't need to do this

Glad you seem to have got a solution anyway !

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[blom0344]

Geoff/Skip,

Refreshing through VBA seems to work okay, however for every object (pivottable) there is a pop-up 'cannot connect to the source'.
However pivottables are refreshed after clicking away the message.
I have tried to surpress the error by adding a line to the VBA code, but without success.
Any idea for a workaround/cause?



Ties Blom
Information analyst

 

[xlbo]

application.displayalerts = false
....
code
....
application.displayalerts = true

should do the trick

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[blom0344]

Geoff,

I tried that one, but the error popup still comes through.
I have a feeling it may be something originating from MS query instead of Excel.
If I take the code out and refresh through MS Query the same error pops up...

Ties Blom
Information analyst

 

[xlbo]

what line causes the message to appear ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[blom0344]

None, the VBA runs okay. AFAIK it is not a message generated by VBA at all.
To be honest, I think things have become messed up on my machine that had both Excel2000 AND Excel2002 installed.
I will try to test what I have got on a laptop with just Excel2000..

Ties Blom
Information analyst