Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge data structure in Excel
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge data structure in Excel
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... -- HTH... Jim Thomlinson "Milo" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge data structure in Excel
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 "Milo" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge data structure in Excel
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" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge data structure in Excel
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. "Milo" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge data structure in Excel
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 "Milo" wrote: 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. "Milo" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge data structure in Excel
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 FileExternal DataImport, then follow the steps. ExcelAccess 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 DataGet External DataNew Database Query, or use Pivot Tables. NickHK "Milo" wrote in message ... 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. "Milo" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Excel PivotTable for a huge DataBase from External Data | Excel Discussion (Misc queries) | |||
Excel File Huge with no data | Excel Discussion (Misc queries) | |||
How to Reduce the Excel File Size? & Make it work with huge data | Excel Discussion (Misc queries) | |||
huge huge excel file... why? | Excel Discussion (Misc queries) | |||
Huge Memory Leaks using ODBC Drivers from Excel to retrieve data | Excel Programming |