Huge data structure in Excel

[Milo]

Hi,
I wonder if Excel is the right app for this. I'll try and be brief.

I have some aircraft data: latitude, logitude, height, time/date and a
crucial number called vertical acceleration in m/s (call it VA). I have this
data for 40 aircraft spanning 10 years and so the number of these records is
several million.

Now, I would like to set up a multidimensional structure where I can view
histograms of VA for any height band (say 9000 to 10000 feet) for any
month/year for any aircraft for a particular latitude/longitude square (say 2
x 2 degrees).
I was thinking in terms of multiple workbooks (one for each aircraft) and
multiple sheets in each workbook, a sheet one for each month where each sheet
would contain lat/lon height columns and rows would contain numbers of VA
occurances from which the VA histograms could be built.
How fast would such a structure be if I wanted to sum over several years for
a particular number of aircraft for a given height band, month and location
and come up with a histogram?
Put simply is this feasible in Excel?
Any suggestions welcome, Milo.

 

[Jim Thomlinson]

Excel is not really the right tool for something like this (IMO). What you
are wanting to do is mutli-dimensional data modeling within an OLAP cube.
Essbase, Cognos or MDX would probably be the best. You could (probably)
however get away with querying Access from an Excel Pivot Table...

 

[Robin Hammond]

Milo,

For what you want to do this sounds very much like a database. A few million
rows is not a problem in a db, but is in Excel.

Once you data is in a database, it becomes quite easy to generate queries
into Excel, and to use the charting engine in Excel to generate your end
product.

It would go something like:

SELECT VA FROM MyDataView WHERE AircraftID = 1 AND (Altitude Between 9000
AND 9999) AND MONTH(RecordDate) = 1 AND YEAR(RecordDate) = 2006 AND
(Latitude BETWEEN 180 and 182) and (Longitude BETWEEN 180 and 182)

Doing that in Excel would take an awful lot of logic if split across
multiple sheets.

To get you started it looks as if you are looking at a table structure like
this (in SQL):

CREATE TABLE aircraft_records(
AircraftID NOT NULL,
RecordDate NOT NULL,
Altitude NOT NULL,
VA NOT NULL,
Latitude NOT NULL,
Longitude NOT NULL,
CONSTRAINT
pk_aircraft_records
PRIMARY KEY(
AircraftID, RecordDate))

Robin Hammond
www.enhanceddatasystems.com

 

[Rudy Escoto]

Let's approach Milo's concern to eventually use Excel as Milo's analysis tool
however correct (also IMO) to store the large volume of data into a database
server e.g. SQL. Thus, Milo, to answer your question if analysis of data is
feasible in Excel: Yes. The big BUT however is the solution to provide
implementation of directly reading the data from a source SQL data server by
your Excel application.
That said, it wouldn't be a big But but a smaller one then. You may use the
data connectivity tools of Excel to data server SQL. In Excel, press F1
(help) then type to search keyword ODBC connectivity.
Good luck.

 

[Milo]

To all of you, thank you very much for your contributions. They are all
sensible.
However,
1. I do not have access to SQL server only Access.
2. I only did db design as subject years ago and never realy used Access,
although I might have to start now!
Currently all of this data is contained in yearly Excel workbooks in
multiple aircraft sheets in raw form (i.e. the VA values are not grouped into
histogram bins. Some of these workbooks are > 100Mb in size.
Thus I have to somehow transfer this data into csv files, bin them, and then
load into Access?
3. This is why I wanted to stay in Excel if possible. But it seems by your
suggestions that this may not be feasible.

Regards Milo.

 

[Rudy Escoto]

Point by point:
On 1. It's alright to use only Access in place of SQL to store that huge
data. Access is guaranteed capable to handle storage.
2. I agree, you will experience exciting thing and learn more along the way
while restarting your Access usage. There no need for additional step of
transferring Excel files to CSV,instead transfer/load these excel files
direct to Access.
3. Yes, not possible to store data in multiple excel sheets thus step 1.
However again, you may use Excel to do your analysis yet connectivity issue
arises. I'll start with Access with more database possibilities.
Thanks Rudy

 

[NickHK]

Milo,
Trying to work with data spread across a number of Excel files 100MB+ will
make your life difficult.
Don't be daunted about Access. There a number wizards to help you.
The easiest way to get you data into Access is File>External Data>Import,
then follow the steps. Excel>Access direct.
The first time you'll need to create a new table. Subsequently you dump the
other data into that table.
Obviously all the data has to be in a compatible format, but this is the
case even if you leave it in Excel, otherwise RIRO.

Once you have you data in Access, to analysis in Excel is a case of Data>Get
External Data>New Database Query, or use Pivot Tables.

NickHK