Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

OLAP cube from EXCEL file

Posted on 2004-03-30
18
1,134 Views
Last Modified: 2009-12-16
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
Comment
Question by:ravi_net
  • 8
  • 5
  • 3
18 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10718093
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
 
LVL 13

Expert Comment

by:danblake
ID: 10718530
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
 
LVL 34

Expert Comment

by:arbert
ID: 10718743
as always, thanks for the clarification
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:ravi_net
ID: 10719004
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
 
LVL 13

Expert Comment

by:danblake
ID: 10721749
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
 

Author Comment

by:ravi_net
ID: 10724015
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
 
LVL 13

Expert Comment

by:danblake
ID: 10724281
Are you running Analysis Services SP3a ?

(I'm generating a test enviroment here, also..)
0
 
LVL 13

Expert Comment

by:danblake
ID: 10725944
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
 
LVL 13

Expert Comment

by:danblake
ID: 10725962
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
 
LVL 34

Expert Comment

by:arbert
ID: 10726782
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
 
LVL 13

Expert Comment

by:danblake
ID: 10728003
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
 

Author Comment

by:ravi_net
ID: 10751094
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
 
LVL 34

Expert Comment

by:arbert
ID: 10752477
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
 
LVL 13

Expert Comment

by:danblake
ID: 10752785
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
 
LVL 13

Accepted Solution

by:
danblake earned 150 total points
ID: 10752882
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
 
LVL 34

Assisted Solution

by:arbert
arbert earned 150 total points
ID: 10753292
"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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

838 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