Creating a local cube file using COM & VB (bypassing Excel)

Creating a local cube file using COM & VB (bypassing Excel)

Post by Paul Gascoign » Thu, 19 Jul 2001 02:37:56



I have a cube set up on my analysis server, I would like to routinely
reprocess this cube, preferably when triggered by a user visiting a specific
website (i.e. click here to receive latest cube file etc. Once the cube has
been processed, I would then like to create a local .cub file which can be
picked up and e-mailed as attachment to the person initiating the reprocess
command. I know it sounds a bit ambitious, but I feel certain someone out
there is clever enough to figure this out. The e-mail part is no problem, I
also suspect we are capable enough to set up a DTS package to reprocess the
cube, however, how do we create the local cube file by bypassing Excel (can
we manipulate the PivotTabel services with VB code).

Any help greatly appreciated, and if you live near Nottingham in England,
I'll buy you a pint. Cheers.....

 
 
 

Creating a local cube file using COM & VB (bypassing Excel)

Post by Thomas Page » Thu, 19 Jul 2001 04:52:13


Hi,

there are some other tools beside Excel which can create a local cube. There
is ProClarity (which uses VBA, so there might be a way to "remote control"
the creation of a local cube) or you can contact www.mediaconsulting.com,
they have a product called "Rubik" which uses VBA as well and can create
local cubes. I didn't try it but I saw a datasheet of it, sounds fine...

...and if you come over to Germany, we can talk about the beer again... ;-)

Thomas Pagel, Senior Consultant
-------------------------------------------------------------
MASP Business Consulting GmbH
Hauptstr. 35
33178 Borchen
Germany
Tel: +49 5251 54009 11
Fax: +49 5251 54009 99
Mobil: +49 172 5683361

Homepage: www.masp.de



Quote:> I have a cube set up on my analysis server, I would like to routinely
> reprocess this cube, preferably when triggered by a user visiting a
specific
> website (i.e. click here to receive latest cube file etc. Once the cube
has
> been processed, I would then like to create a local .cub file which can be
> picked up and e-mailed as attachment to the person initiating the
reprocess
> command. I know it sounds a bit ambitious, but I feel certain someone out
> there is clever enough to figure this out. The e-mail part is no problem,
I
> also suspect we are capable enough to set up a DTS package to reprocess
the
> cube, however, how do we create the local cube file by bypassing Excel
(can
> we manipulate the PivotTabel services with VB code).

> Any help greatly appreciated, and if you live near Nottingham in England,
> I'll buy you a pint. Cheers.....


 
 
 

Creating a local cube file using COM & VB (bypassing Excel)

Post by Gane » Thu, 19 Jul 2001 10:41:23


While I am not an expert, hope this will help.

You may want to check out the Localcubetask.com website which has a
custom DTS task to publish local cube. If there  can be a way to
launch DTS task off the web, that should do the trick.

rgds

Ganesh


> I have a cube set up on my analysis server, I would like to routinely
> reprocess this cube, preferably when triggered by a user visiting a specific
> website (i.e. click here to receive latest cube file etc. Once the cube has
> been processed, I would then like to create a local .cub file which can be
> picked up and e-mailed as attachment to the person initiating the reprocess
> command. I know it sounds a bit ambitious, but I feel certain someone out
> there is clever enough to figure this out. The e-mail part is no problem, I
> also suspect we are capable enough to set up a DTS package to reprocess the
> cube, however, how do we create the local cube file by bypassing Excel (can
> we manipulate the PivotTabel services with VB code).

> Any help greatly appreciated, and if you live near Nottingham in England,
> I'll buy you a pint. Cheers.....

 
 
 

Creating a local cube file using COM & VB (bypassing Excel)

Post by Ivic » Thu, 19 Jul 2001 19:44:23


Hi !

You can do this with VB and Pivot Table.
I work with that and it works fine.
Use must use CreateCube DDL stattment in ADO connection to make local cube.
BOL have basic explanations !

Ivica


Quote:> I have a cube set up on my analysis server, I would like to routinely
> reprocess this cube, preferably when triggered by a user visiting a
specific
> website (i.e. click here to receive latest cube file etc. Once the cube
has
> been processed, I would then like to create a local .cub file which can be
> picked up and e-mailed as attachment to the person initiating the
reprocess
> command. I know it sounds a bit ambitious, but I feel certain someone out
> there is clever enough to figure this out. The e-mail part is no problem,
I
> also suspect we are capable enough to set up a DTS package to reprocess
the
> cube, however, how do we create the local cube file by bypassing Excel
(can
> we manipulate the PivotTabel services with VB code).

> Any help greatly appreciated, and if you live near Nottingham in England,
> I'll buy you a pint. Cheers.....

 
 
 

Creating a local cube file using COM & VB (bypassing Excel)

Post by Paul Gascoign » Thu, 19 Jul 2001 21:41:51


Ivica, I have tried this but I get an error message (error while processing
a partition '').

The code sample from the BOL is almost identical to that which I have
followed in a Wrox OLAP book.

I can publish my sample code if you have time to take a quick look?

Paul


> Hi !

> You can do this with VB and Pivot Table.
> I work with that and it works fine.
> Use must use CreateCube DDL stattment in ADO connection to make local
cube.
> BOL have basic explanations !

> Ivica



> > I have a cube set up on my analysis server, I would like to routinely
> > reprocess this cube, preferably when triggered by a user visiting a
> specific
> > website (i.e. click here to receive latest cube file etc. Once the cube
> has
> > been processed, I would then like to create a local .cub file which can
be
> > picked up and e-mailed as attachment to the person initiating the
> reprocess
> > command. I know it sounds a bit ambitious, but I feel certain someone
out
> > there is clever enough to figure this out. The e-mail part is no
problem,
> I
> > also suspect we are capable enough to set up a DTS package to reprocess
> the
> > cube, however, how do we create the local cube file by bypassing Excel
> (can
> > we manipulate the PivotTabel services with VB code).

> > Any help greatly appreciated, and if you live near Nottingham in
England,
> > I'll buy you a pint. Cheers.....

 
 
 

Creating a local cube file using COM & VB (bypassing Excel)

Post by Sodapo » Fri, 20 Jul 2001 00:50:17


Paul,

If you have analysis server installed, check out the samples directory.
Especially VbAdoCreateCube might help you out.

good luck,

Ben


Quote:> I have a cube set up on my analysis server, I would like to routinely
> reprocess this cube, preferably when triggered by a user visiting a
specific
> website (i.e. click here to receive latest cube file etc. Once the cube
has
> been processed, I would then like to create a local .cub file which can be
> picked up and e-mailed as attachment to the person initiating the
reprocess
> command. I know it sounds a bit ambitious, but I feel certain someone out
> there is clever enough to figure this out. The e-mail part is no problem,
I
> also suspect we are capable enough to set up a DTS package to reprocess
the
> cube, however, how do we create the local cube file by bypassing Excel
(can
> we manipulate the PivotTabel services with VB code).

> Any help greatly appreciated, and if you live near Nottingham in England,
> I'll buy you a pint. Cheers.....

 
 
 

Creating a local cube file using COM & VB (bypassing Excel)

Post by Paul Gascoign » Sat, 21 Jul 2001 00:14:15


Thanks Ben

I've cracked it out, the errors I encountered were purely down to my SQL
syntax.

Paul


> Paul,

> If you have analysis server installed, check out the samples directory.
> Especially VbAdoCreateCube might help you out.

> good luck,

> Ben



> > I have a cube set up on my analysis server, I would like to routinely
> > reprocess this cube, preferably when triggered by a user visiting a
> specific
> > website (i.e. click here to receive latest cube file etc. Once the cube
> has
> > been processed, I would then like to create a local .cub file which can
be
> > picked up and e-mailed as attachment to the person initiating the
> reprocess
> > command. I know it sounds a bit ambitious, but I feel certain someone
out
> > there is clever enough to figure this out. The e-mail part is no
problem,
> I
> > also suspect we are capable enough to set up a DTS package to reprocess
> the
> > cube, however, how do we create the local cube file by bypassing Excel
> (can
> > we manipulate the PivotTabel services with VB code).

> > Any help greatly appreciated, and if you live near Nottingham in
England,
> > I'll buy you a pint. Cheers.....

 
 
 

Creating a local cube file using COM & VB (bypassing Excel)

Post by Chris Brandsm » Sun, 22 Jul 2001 00:55:40


ProClarity's local cube creator is an ActiveX object called KCreateCube.
You can include it into a Visual Basic project and run it straight from
there.  The object is fully documented and should be able to do anything you
need.

Here is some sample VB code to create a cub file from Foodmart 2000 Sales
cube

   Dim oCubeCreator As KCreateCube.KCubeCreator
   Dim oCube As KCreateCube.CCCube

   Set oCubeCreator = New KCreateCube.KCubeCreator

   oCubeCreator.CubFileName = "C:\Temp.cub"

   Dim oSource As KTMDLib.KSource
   Set oSource = New KTMDLib.KSource
   oSource.Login oSource.CurrentProvider, "Localhost"

   Dim oSession As KTMDLib.KSession
   Set oSession = oSource.Catalogs("Foodmart
2000").Cubes("[Sales]").CreateSession

   Set oCube = oCubeCreator.Cubes.Add(oSesion)

   Dim oDim As KCreateCube.CCDimension
   Dim oHier As KCreateCube.CCHierarchy
   Dim oLevel As KCreateCube.CCLevel

   For Each oDim In oCube.Dimensions
      oDim.Include = True

      For Each oHier In oDim.Hierarchies
         oHier.Include = True

         For Each oLevel In oHier.Levels
            oLevel.Include = True
         Next oLevel
      Next oHier
   Next oDim

   Dim oMeasure As KCreateCube.CCMeasure

   For Each oMeasure In oCube.Measures
      oMeasure.Include = True
   Next

   oCubeCreator.CreateCube

   Set oCubeCreator = Nothing
   Set oCube = Nothing
   Set oSource = Nothing
   Set oSession = Nothing


> Hi,

> there are some other tools beside Excel which can create a local cube.
There
> is ProClarity (which uses VBA, so there might be a way to "remote control"
> the creation of a local cube) or you can contact www.mediaconsulting.com,
> they have a product called "Rubik" which uses VBA as well and can create
> local cubes. I didn't try it but I saw a datasheet of it, sounds fine...

> ...and if you come over to Germany, we can talk about the beer again...
;-)

> Thomas Pagel, Senior Consultant
> -------------------------------------------------------------
> MASP Business Consulting GmbH
> Hauptstr. 35
> 33178 Borchen
> Germany
> Tel: +49 5251 54009 11
> Fax: +49 5251 54009 99
> Mobil: +49 172 5683361

> Homepage: www.masp.de



> > I have a cube set up on my analysis server, I would like to routinely
> > reprocess this cube, preferably when triggered by a user visiting a
> specific
> > website (i.e. click here to receive latest cube file etc. Once the cube
> has
> > been processed, I would then like to create a local .cub file which can
be
> > picked up and e-mailed as attachment to the person initiating the
> reprocess
> > command. I know it sounds a bit ambitious, but I feel certain someone
out
> > there is clever enough to figure this out. The e-mail part is no
problem,
> I
> > also suspect we are capable enough to set up a DTS package to reprocess
> the
> > cube, however, how do we create the local cube file by bypassing Excel
> (can
> > we manipulate the PivotTabel services with VB code).

> > Any help greatly appreciated, and if you live near Nottingham in
England,
> > I'll buy you a pint. Cheers.....

 
 
 

1. Create online cube from VB - how to port my local CREATE CUBE

I have a VB6 application, which uses the MSOLAP Provider (version 8.0), MDX
(CREATE CUBE) and ADO to create an offline (local) cube from relational data
held in an ORACLE or SQL Server data source.

This all works fine.

The question I have is how much change is needed to the code to have the
option of creating an online cube (using SQL Server 2000 Analysis Services
OLAP Server). Is it only the connection string? or is it the MDX statement?

Or do I need a completely different piece of code, using DSO, to do the same
thing.

If so, are there are any good sources of DSO/VB programming that explains
how to do this (I have MSDN).

Thanks.

Alastair

2. Oracle Sequence Numbers

3. Creating Local SQL7 Cubes using VB

4. VFP50 Using OLE

5. *writers/seeking/publication

6. Command-line SQL

7. create global cube vs. create local cube

8. Create Local Cubes with Excel 2000

9. Local Cubes Using Excel 2000 Beta

10. Local CUBE files and Excel 2000

11. Local cubes in Excel 2000 using AS