Solved

OLAP cube from EXCEL file

Posted on 2004-03-30
18
1,124 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
 

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now