Creating a local OLAP cube

Posted on 2004-09-10
Medium Priority
Last Modified: 2012-06-27
This is a question about creating an OLAP cube with
MS Access data. I hope this is the correct topic area.
If not, please let me know which.

The problem:

I am trying to create a local olap cube (.cub file) and
populate it with data from a MS Access database (I believe
the database could be anything, such as Oracle), but
without using Microsoft Analysis Services.

The cube so created is intended for use in a product called
ProClarity which can work with .cub files.

My problem is that I am unable to create the cube - my VB
code (given below) gives an error. The question of
populating the cube hasn't been addressed yet, until I cross
the creation stage.

In my code given below I have created an Access DSN.

My source of info:
I am following the guidelines shown at the site:
I have also seen a general example under
ProgramFiles\ Microsoft Analysis Services\Samples\VbAdoCreateCube
(although I said I do NOT want to use MS Analysis Services, this
example is a good general guideline)

What I have tried so far:
These two examples work fine! But my own code does not.
I get the error: "Syntax error in column definition"

The error occurs at the statement -

cnCube.open s

The connectivity part of the cube seems to be working,
but I feel the error has something to do with the syntax
of the create cube or the Inert into statements.

If that diagnosis is correct, I guess I need help with
the syntax. Any comprehensive reference could be very useful.
Especially in terms of assigning dimension names and level names.
Are these names required to be related to the column names and
table names in the database?

For example: my table name in MS-Access is SSTORE and field
names are STORE_NAME, STORE_SALES, etc. Now when I specify my
level name for the cube, does it need to be the same like the
column name - i.e STORE_NAME  instead of STORE NAME as in the
code given below.

Help on all of the following will be much appreciated:

1. Correction in my code so that it works
2. Pointers to comprehensive syntax for cube-related statements
3. Rules for creating and naming dimensions, levels, measures for
   the cube and the Insert Into statements.

The code I have written is here :

' #######################CODE#################################33
Private Sub cmdClick_Click()

Dim cnCube As ADODB.Connection
Dim s As String
Dim strProvider As String
Dim strDataSource As String
Dim strSourceDSN As String
Dim strSourceDSNSuffix As String
Dim strCreateCube As String
Dim strInsertInto As String
Dim strSource As String
On Error GoTo Error_cmdCreateCubeFromDatabase_Click

strSource = Connection(1)
strCreateCube = getCreateCube()
strInsertInto = getInsertInto()

Set cnCube = New ADODB.Connection
s = strSource & ";" & strCreateCube & ";" & strInsertInto & ";"
cnCube.Open s

Exit Sub

Screen.MousePointer = vbDefault
    MsgBox Err.Description
    If Err.Number <> 0 Then
   msg = "Error # " & Str(Err.Number) & " was generated by " _
         & Err.Source & Chr(13) & Err.Description
   MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
    End If
End Sub

Private Function Connection(idbtype As Long) As String
strProvider = "PROVIDER=MSOLAP"
strDataSource = "DATA SOURCE=c:\OlapCube.cub"
strSourceDSN = "SOURCE_DSN=AccessLoyd"

Connection = strProvider & ";" & strDataSource & ";" & strSourceDSN
End Function

Private Function getCreateCube() As String
Dim strCreateCube As String
strCreateCube = "CREATECUBE=CREATE  CUBE cbia( "
strCreateCube = strCreateCube & "DIMENSION[STORE] ,"
strCreateCube = strCreateCube & "LEVEL[STORE Name] ,"
strCreateCube = strCreateCube & "MEASURE [STORE Sales] "
strCreateCube = strCreateCube & "Function Sum "
strCreateCube = strCreateCube & "Format '#.#')"
getCreateCube = strCreateCube
End Function

Private Function getInsertInto() As String
Dim strInsertInto As String
strInsertInto = strInsertInto & "INSERTINTO=INSERT INTO cbia( SSTORE.[STORE Name],"
strInsertInto = strInsertInto & "Measures.[SSTORE Sales])"

strInsertInto = strInsertInto & "SELECT SSTORE.STORE_NAME AS COL1,"
strInsertInto = strInsertInto & "SSTORE.STORE_SALES AS COL2"
strInsertInto = strInsertInto & "FROM [SSTORE]"
getInsertInto = strInsertInto

End Function

' ###########END OF CODE ##############

thanks in advance!

- stochastic
Question by:stochastic
  • 10
  • 9
  • 2
  • +1
LVL 34

Expert Comment

ID: 12024678
Dim cnCube As ADODB.Connection
Dim rsCube as ADOBD.Recordset

Set cnCube = New ADODB.Connection
cnCude.connection =  Connection(1)

set rsCude = new adodb.recordset
rsCube.open sSQL, cnCube



Expert Comment

ID: 12026276
HI flavo,

I am not too clear with solution  you have suggested for this problem.

Would you mind giving  a brief explanation of what you are trying to suggest.

Specifically these lines :
rsCube.open sSQL,cnCube

What I have understood is that :

1)You are creating the connection object and the recordset object but the line
is not very clear

Should it be :
 cnCube.ConnectionString=Connection(1)    ?

2) In the statement -:
  rsCube.open sSQL,cnCube
  Is the variable 'sSQL'  eqivalent to the string variable ' s ' in my code  which  holds the 3 strings  :-
     strSource & ";" & strCreateCube & ";" & strInsertInto & ";"

So is my assumption true or am i wrong ?

So kindly correct me if  i am wrong and if possible please add a line, briefly explaning the
the  lines
cnCude.connection =  Connection(1)
rsCube.open sSQL, cnCube

Thanks a million


Author Comment

ID: 12026765
just a clarification: Captive1180 is my colleague at work. It's really his
question. He will be posting additional info about the question.
So please do respond to his queries.

thanks in advance :-)

- sto
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 12

Expert Comment

ID: 12027306
If the problem is really as stated (create a local cube populated from an Access database) and not that you have to use Access to populate the cube, then this is much easier to do from Excel.

Open Excel.  Activate a blank worksheet.  Goto Data -> Get External Data -> New Database Query.  On the Databases tab, pick MS Access Database and click OK.

Navigate to you Access database.  Follow the rest of the wizard.  It's pretty straightforward, especially if you're already familiar with the general concepts of cube building.  

Choose the third option ("Save a cube file containing all data for the cube.") to use the result with ProClarity.  The screen that follows requires you to save the query too, which may facilitate reuse.

I would image that you could do this via some kind of Access-Excel automation if you needed to.
LVL 12

Expert Comment

ID: 12027383
Sorry, meant to post this too:  concept from "Microsoft SQL Server 2000 Analysis Serivces", Reed Jacobson, Microsoft Press c 2000, Chapter 5.  Good intro to OLAP in a Microsoft SQL environment.  Also, this method won't support Parent-Child relationships and without further research, I have no suggestion how to work through/around that limitation.

Expert Comment

ID: 12033388
hi pique_tech,

Thanks for showing interest in my problem.

Its true that I want to populate data from an access database. But the end result  i.e the cube formed should be a local cube of the type ( .cub ) .

As I want to use it in proclarity and as proclarity accepts only .cub files the method you have suggested won't help my cause becaue the cube file being created is of the type ( .ogy)  file.

Anyways, thanks for the suggestion and incase you come across something related to  this problem  please let me know

LVL 12

Expert Comment

ID: 12034578
Ok.  My statement wasn't very clear.

It saves the .oqy as an Excel(?) query as the last step, but it also creates a local data cube (.cub) in the location you choose in the next-to-last step.  This .cub should be accessible from any client that can use local data cubes.

Expert Comment

ID: 12044334
hi pique_tech,

Thanks for the method you have suggested.

Its shows  signs of solving my problem to some extent.

The main objective behind this problem although is populating  data from the oracle database.

Anyways, this soultions of yours seems to be leading me  close to the goal.

 I am working on the solution suggested by you and after testing this method I will be able to tell you whether, this indeed is the best solution to my problem or not .

So after I have tested this method under all paramters I'll let you know  how good your solution is.

Thanks once again

LVL 12

Expert Comment

ID: 12044475
I have limited experience directly with Oracle, but I do have one Oracle connection to develop against.  I have the Oracle client installed on my workstation and one DSN connection configured, and its name appears in the Excel Data -> Get External Data -> New Database Query Databases list.  So in theory, you should be able to build your .cub file directly from the Oracle source via Excel.  The only possible pitfall is if you need to filter the data, but I think the Excel local cube wizard may even let you filter the source data used to create the cube.

Hope this helps.  I work in a setting where I don't have great support for these kinds of questions, so I wish you a speedy solution.

Expert Comment

ID: 12111490
Hi pique_tech,

Its been a while since I have replied to your last comment.

I have been testing the solution you suggested earlier.

The option you have shown works well. But there is 1 problem in it.

At the time of creating queries , the wizard dumps the data into the excel sheet.

I have been able to  create the olap cube using the wizard,but that is possible till the dataset  is small in size. Say around 64000 records.

But now when the dataset is very large i.e around 80000 and beyond , my cube creation process fails at the last step of the wizard.

What  conclusion I draw from this problem is , the limitation of excel sheet to hold 64000 records.Once the dataset increases  the cube creation fails.

So do you have a solution to this problem,  where the wizard process fails when the dataset is very large.

Thanks in advance
LVL 12

Accepted Solution

pique_tech earned 2000 total points
ID: 12111902
Excel does have a limit per worksheet of 65536 rows (2^16), so any flat file bigger than that will fail.  But I'm never actually using Excel to display the data, only to build the cube.  Here are the steps I take, in detail.  (Sorry if this is repetitive)
From Excel, Choose Data -> Get External Data -> New Database Query.
Choose Datasource window:  Choose the datasource
Query Wizard - Choose Columns:  I selected a table with approximately 2 million rows on a SQL server
Query Wizard - Filter Data:  I didn't apply any filters
Query Wizard - Sort Order:  I didn't specify a sort order
Query Wizard - Finish:  I selected the third option, Create an OLAP Cube from this query

Welcome to the OLAP Cube Wizard -> Next
OLAP Cube Wizard Step 1 of 3:  pick measures.  I selected a sum of money and a count of orders
OLAP Cube Wizard Step 2 of 3:  pick dimensions.  I selected geography (County) and Product Type
OLAP Cube Wizard Step 3 of 3:  I chose "Save a cube file..." and specified my desktop as the location

I accepted the default location to save the Excel query.  I looked at this point, the local cube is not created yet, so you have to save the query.

"Creating offline cube" appears, takes a little while to process
"Creating offline cube" disapppears, Excel opens Pivot Table and Pivot Chart Wizard

At this point, I confirmed that the local cube was saved to my desktop and was useable by an OLAP client (I use Microsoft Data Analyzer).  I tried cancelling the Excel pivot table wizard, and no data was imported into Excel.  I tried NOT cancelling the Pivot Table and Pivot Chart Wizard, and Excel functioned as expected, allowed me to build a pivot table from my data.

I'm not sure how you're actually getting data into Excel, but after the "Creating offline cube" message disappears, your cube should be available and should work as expected.

This process actually just uses Excel as a "shell" to create the cube.  

Now I'm very curious about your situation.  Please be sure to let me know how you progress.


Expert Comment

ID: 12112709
hi  pique_tech ,

Thanks for the interest you are showing to solve my problem.

I am writing down the steps that I am following and the step where the problem arises. I hope this will help you get a clue if something is wrong  in the procedure I am following.

1)I enter the new data source name
2)Select the driver for oracle - on my machine its  Oracle in OraHome92
3)Click connect
4)I then select the data source which I have just created and click the ok button
5)Then I select the columns which have to be added to my queries
6)It then asks me to join the tables manually in microsoft query by dragging the fields to join between the tables.
7)As I  join each table as per the need,just below the relation diagram in the microsoft query,I can see some data being added to the fields.( I wonder what is that)
8)After joining the tables I save the file. i.e a .dqy file
9) There is another step showing some range  to paste the data ( I ignore it and  click on 'NO')
10)I close the microsoft query and go back to my excel sheet where I again click->Data->Get External Data->New Database Query
11) Then I click the query tab and select the query file I just created and click 'open'.
12) I click  NEXT on the immediate screen which has the option to select columns for the query,
      again I click NEXT at the filter option,then click 'NEXT' again at the sort option
13) Then I select the option 'CREATE AN OLAP CUBE FROM THIS QUERY' and click FINISH

Now the olap cube wizard begins

14)I select the fields that I want to summarize and click next
15)Then I create the dimensions and again click next
16)Then I click the last option on the scren i.e Save a cube file containing all the data for the cube
17) When I click on finish , I am asked to save a file (.oqy file)
18) After saving this file, there is a msg on the screen saying 'creating cube file'.

Now is the problem:
After 20 to 30 seconds I get a message saying ---
 " cube file cannot be written.Edit the cube defintion to try again.If updating the existing cube file ,make sure the file is not being used by excel or some other application"

Though the .oqy file is also created, its only the cube file .cub that is not created.

I hope my explantion will  help you figure out the mistake I am commiting while creating the cube.

Thanks once again.

LVL 12

Expert Comment

ID: 12113874
My hunch is that you are trying to create a dimension which has more than 64,000 members.  This is a limitation of any Microsoft OLAP tool (documented here:  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/aglevels_946r.asp).  The only workaround is to add additional intermediate dimensions so that no intermediate dimension has more than 64,000 members.  

I don't think this will differ whether using Excel or using code directly.  

Can you try sample of your data that you know to be smaller if you haven't already?  That would tell us whether my hunch is correct or not.

Expert Comment

ID: 12124074
Hi pique_tech,

I think I am very near to solving this problem.

The problem arises in particular when I include fields like customer_id,product_id etc.
So if customer_id has 12000 unique values ,then a problem arises internally.

I think it  performs some  calculations for each unique value of this type, in all tables which are taken as dimensions.

Now I am creating the cube without these fields i.e customer_id,product_id,promotion_id etc.

Actually these fields donot help in  any special analysis of the data ,but I had a feeling that they were important from the point  of joining tables .

Now I have created a cube without these fields  i.e  the ones which have some id associated with them, example customer_id,product_id,promotion_id  and the cube works fine.

So I get a feeling that I am very close to the solution and will soon by concluding this question.

I am perfroming some other test on this and will soon get back to you.

Thanks a lot for the assistance you have given me till now.

Have a nice day


Expert Comment

ID: 12167780
Hi pique_tech,

I am still facing the same problem I had mentioned earlier.

At the last stage of the wizard, it gives me this error :

" Cube file cannot be written.Edit the cube defintion to try again.If updating the existing cube file ,make sure the file is not being used by excel or some other application"

So could you suggest any solution to this or can you reason out ,why this error occurs.

This time round , my records are just 566 to 1000.



Expert Comment

ID: 12169991
Hi pique_tech,

I just found the cause of the error.

1st - The error msg does not reveal the correct error that takes place.(That's bad on the part of microsoft query).

Now the actual find-  

If any of my columns has a value as 0 in it ,then microsoft query throws the error -

" Cube file cannot be written.Edit the cube defintion to try again.If updating the existing cube file ,make sure the file is not being used by excel or some other application"

eg- if a fact coloumn , say totalchests have a 0 in any of its rows , the cube file won't get created.

Have you come accross this problem,and do you have a solution to this.

Thnx for being there.

LVL 12

Expert Comment

ID: 12170532
I've looked at what you posted earlier this morning, haven't had a chance to do much experimenting.  I will look at your second message too, but it may be this evening before I have a chance to respond (bad day).  Sorry for the delay but will follow up as soon as I can.
LVL 12

Expert Comment

ID: 12216255
I haven't abandoned this, a bad day turned into a long bad week.  I will look at this later today or tomorrow to see if I can duplicate the error and maybe help understand.  Sorry for the delay.

Expert Comment

ID: 12216334
Hi pique_tech,

I wish u good luck so that your week long problems are solved.

At my end ,I 've found another limitation in this method.

If a column's datatype is numeric and it has a provision for decimal values , example  netkgs number(6,3) .

Now when I use this method , the value which I see in the pivot table is only the half value. example if the orignal value was
1567.06 , on viewing the cube data in the pivot table or any other tool the value displayed is .06.

But when i change the data type of the columns from number(6,3) to number(6,0) the method works fine and displays the correct values.

Just keep this in  mind incase you come across some find for this problem.

Hope to hear from you soon.

LVL 12

Expert Comment

ID: 12248958
I have tried to reproduce both of the behaviors you described (the zeros causing the cube to fail and the decimal mis-displaying, and I haven't been able to.  So I'm not sure if there's something I'm missing in your problem or some difference between your office installation and mine.  I don't think there's anything more I can do  down that path.

I did wonder, though, if you're using ProClarity, don't they provide some tool to generate and save local cubes?  Or is your organization like mine where they didn't get everything you (the developer) really needs to help them solve their business problems...  ; )

I hope you find some solution.  I really thought the Excel method could save you some time and effort, and it works just like it's supposed to for me, but maybe it's not quite right for you.  

Sorry for the extremely slow responses lately.  I have a few days off from work now, so I'm trying to catch up on the few very interesting problems (yours is one) to which I owe responses.

Expert Comment

ID: 12249311
hi Pique_tech,

Hey thnx for trying out things to help me solve my problem.

My place of work is really good and one is not bond in restrictions. If something is needed to solve a problem ,care is taken that, that particular thing is provided .

Even I am surprised at this sort of an error, that too in microsoft query.

At the moment even I am trying to work on similar data which was causing such problems.

I Hope to find a reason to this  problem.

Thnx once again . I'll get back to you soon

Author Comment

ID: 12482376

thanks for your extensive help. Your suggestions were very useful and
were used in large measure in solving the problem.

- stochastic

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question