Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1158
  • Last Modified:

OLAP cube from EXCEL file

Hi,
I was wondering how i could create a Cube using the EXCEL file as the datasource. The data into the cube directly comes from this excel file.
I know how to see the cube in EXCEL using pivot table but trying to work the other way around.

Thanks in advance
Ravi
0
ravi_net
Asked:
ravi_net
  • 8
  • 5
  • 3
2 Solutions
 
arbertCommented:
If you mean take your data from excel and put it into an OLAP cube in analysis services, you'll have to import the data into a SQL table first and then use Analysis services to create the cube.
0
 
danblakeCommented:
Arbert -- no.

First the easy part....
Create a system DSN or file DSN to your file.
using the Excel Driver of your choice
Create the Datasource Name / Description, under the select workbook option select your file (Excel one that you wish to use)...Then save this/test this.

In Analysis Services, in the OLAP Database.
Click on datasources, Right Click, then select add new data source
Then select use : Microsoft OLE DB Provider for ODBC Drivers

You will be able to select the datasource name you created earlier with a system DSN (or file DSN -- untested)
You can then select the workgroup in the initial catalog to use selection list.

Quad erat demonstratum Q.E.D.
0
 
arbertCommented:
as always, thanks for the clarification
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ravi_netAuthor Commented:
HI Danblanke,
Thank you for your response.
I have done this before and created the Datasource.
But i could not create a dimention or cube with this datasource.
When i say new dimention, i get the connection on the left and no matter what i do, i don't see the Next button activated.
Then i thought i may in the wrong direction

I have done this again as you said and still struck at the same place.
Thanks in advance.
Ravi
0
 
danblakeCommented:
I was wondering how i could create a Cube using the EXCEL file as the datasource. The data into the cube directly comes from this excel file.
-----------------------
When you create a new cube,


Create Cube, (with wizard -- Just for demo here),
Select your data-source (2nd screen)
What happens if you double click on the data-source (to expand it, and then select one of the subcolumns) ?

(This should allow you then to work with the datasource)...

You have to expand any SQL Server DB data-source to select a table.... the same goes for the EXCEL file.
0
 
ravi_netAuthor Commented:
Hi,
When i expand the datasource usually i see the tables on the right panel.But with this excel datasource i don't see any tables. I see the DSN name and some info on a gray panel(right window) and the next button is inactive. I think i may be missing something. I tried creating System DNS, File DSN, and User DSN with Microsoft Excel Driver and the data conneciton with ole db with odbc provider.

thank you.
Ravi
0
 
danblakeCommented:
Are you running Analysis Services SP3a ?

(I'm generating a test enviroment here, also..)
0
 
danblakeCommented:
OK, we seem to have a problem with Excel as a data-provider:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agref_4vlf.asp

This does not explain why, you can create any data-source for a cube (I can browse the data within the browse data) linked to an excel DSN on cube creation.

I get the error: Unable to retrieve a list of columns from table_name table.
0
 
danblakeCommented:
I am presuming that it is something to do with the problem of retrieving data-types/file type inputs from any file (where a database specifies the input column format).

0
 
arbertCommented:
You can do it if you name a Table Range in your excel sheet first.  I still don't recommend doing this because of the data type problems with excel--just like trying to use DTS against Excel, SQL Server has problems with the datatypes....
0
 
danblakeCommented:
FYI:
To get the expansion of the Excel connection within Analysis Manager.  I needed to rename the worksheets from the standard sheet1,sheet2,...
to things like Power UpWatts ,  Power DownWatts , Power DownTimings, Power UpTimings
this allowed the expansion.

I'm going to try to play with my Excel test file to generate the necessary cube..and then post my results later

Alternativly... longer---shot here...(if pressed for time, and really require a link to an excel file)
Could we not create a linked server (in our RDBMS...), to create the data-types of each individual column to ensure processing from Excel using the convert function on the fields ?

A quick-throw away idea/conjecture...
I am presuming that when Excel goes fully to the XML standard data-types, integration problems will be removed to SQL Server.
0
 
ravi_netAuthor Commented:
HI Arbert and Danblake,
Thank you for your comments.

I believe this should not be a problem with the datatypes(incase Excel as data provider is supported). create a simple excel file with two columns (ID, Value) and fill few rows and try to have a cube...

I could create a data source, but when i say new dimention and try to expand the datasource(DSN), i am not getting any tables. I tried changing the sheet names from their default names(Sheet1)...

Can i now assume that EXCEL as dataprovider is not supported???

I would be thankfull for any further comments.
Thanks
Ravi
0
 
arbertCommented:
LIKE I SAID ABOVE--When you ceate the excel file, create a named table (range) within excel--this will show up as a table name for you to choose from in AS.
0
 
danblakeCommented:
If you are wondering how to define a named table range, click on the column header and the rows in the column in the listing of your table. (I was earlier...thanks arbert for the pointer...)
Then On the menu bar select Insert -> Name -> Define.

My Excel file is defined as: (Tab delimited (if you need to copy it to a new spreadsheet-- Sheet name: Test Data Source)
Year      Amount      Interest Rate      Month
2001      10      10      1
2002      20      15      2
2003      30      2      3
2004      25      3      4

All fields are defined as number (with 0 decimal places).

(I am running gold release here, so there may be a difference between arberts installation and mine a few bugs are going to be present).

The only issue is here, as I have defined each column as a named defined instance I am having them listed seperatly.  I am going to now create a named data-range across all the columns/rows to be able to access them in one go as a cube.
0
 
danblakeCommented:
Creating a defined named instance across the entire range, has allowed the column names to be picked up correctly and used within a cube (I have managed to create a cube here from Excel).

I believe this should not be a problem with the datatypes(incase Excel as data provider is supported). create a simple excel file with two columns (ID, Value) and fill few rows and try to have a cube...

--> From a training course I attended on BI, there is some metadata information that is required for SQL Analysis Services to work (or that was what I was told).  What I am trying to do is provide this by formatting each column as a particular data-type (as much as possible), and the defined named data range.  The person who I trained this course did not know how to import an excel file (I asked about this- about a year ago.)  This is new information on how to make this process work.  Please bear with us, as we are trying to acomplish something that is not normally done -- I am very happy to be able to colabarate with albert on this to generate some more detailed instructions to make this possible/test file/etc... to demonstrate this in practice working.  All my field inputs have been processed into the system as doubles.


Can i now assume that EXCEL as dataprovider is not supported???
No.  It looks like it is supported, I know of no other location which provides instructions on how to do this apart from this question thread.  (At the moment)
0
 
arbertCommented:
"I believe this should not be a problem with the datatypes(incase Excel as data provider is supported). create a simple excel file with two columns (ID, Value) and fill few rows and try to have a cube..."


You can still run into problems with datatypes--depending on the column contents.  SQL can assume that a column is a numeric column when the top portion of rows contain numeric data and non numeric data appears after......
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 8
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now