Excel 2010 Mega PC

[TheBigDog]

I thought I posted this already, but I guess it got lost in the repairs. :eek_big:

 

I have been working on a project for the past two years reinventing how my company reports production information. I have been running a prototype on a PC that sits on my desk which every hour goes out and gathers data from 32 different databases and compiles it together. It then takes the compiled data and puts it on a SQL Server. Users then query the SQL Server with Excel and use pivot tables to do analysis and make reports. The pilot is being replaced by a data warehouse solution but the data is so enormous that it is pushing the capability of our technical and developmental staff and it has the minimum amount of information in it that it ever will.

 

The limitation of the pilot was the shitty SQL server that they let me post the data to, it could only handle 90 days of information, and the memory limitations of Excel. Loading information for all of our North American operations I can get about 15 to 20 days of data loaded. For a single site I can load 100 or more days of information before Excel maxes out memory.

 

That brings me to my topic. Excel 2010 will come in a 64 bit version that will be able to handle a whopping 8 Terabytes of memory for a workbook. 8 terabytes. That is just a tad bit more than the 2 gig it maxes out at in the 32 bit version. So, is there a computer on the market that can come close to providing 8 terabytes of RAM? What computer can I get my hands on that gets me closest to that? I want to be able to report YEARS of data and let this thing grow like a weed.

 

So what is the most memory I can pack into a 64 bit machine today?

 

Bill

[C1ay]

Just wondering, as an aside, are you utilizing OLAP cubes for your pivot tables? If not, you can get better performance on them with what you have in the mean time.

[Jay-qu]

A quick search of my local computer shop website yielded a motherboard that can handle 6x4Gb sticks of RAM, so 24Gb. As far as I could see there are only up to 4Gb sticks of ram.

 

Obviously this is a very 'off-the-shelf' estimate and undoubtedly there are server-type solutions that could do much more than that.

[Pyrotex]

Yeah, we get so used to servers being just some old PC loaded with Apache, that we forget that that is just the "poor man's solution" to having a server.

 

They make REAL servers out there. The low end jobs look just like a PC but have several times the speed, automatic RAM switch-over, automatic hard drive switch-over, higher speed drives and multiple ethernet sockets (maybe). The very cheapest go for about double what a new PC costs. Or triple.

 

And we're not talking high-end servers, no. We're talking just a high-end PC tower that's been souped up, speeded up, and has other fault-tolerance features. $10,000 max. That should give you several times the bandwidth and capacity that you have now, without breaking the bank. And it will give you time to search for a better option.

 

Pyro, the Corporate Portal Meister, Slayer of Bad Servers

[TheBigDog]

Just wondering, as an aside, are you utilizing OLAP cubes for your pivot tables? If not, you can get better performance on them with what you have in the mean time.

Not using OLAP, although I played with it. I will look into that again. Thanks!

 

Bill

[TheBigDog]

Yeah, we get so used to servers being just some old PC loaded with Apache, that we forget that that is just the "poor man's solution" to having a server.

 

They make REAL servers out there. The low end jobs look just like a PC but have several times the speed, automatic RAM switch-over, automatic hard drive switch-over, higher speed drives and multiple ethernet sockets (maybe). The very cheapest go for about double what a new PC costs. Or triple.

 

And we're not talking high-end servers, no. We're talking just a high-end PC tower that's been souped up, speeded up, and has other fault-tolerance features. $10,000 max. That should give you several times the bandwidth and capacity that you have now, without breaking the bank. And it will give you time to search for a better option.

 

Pyro, the Corporate Portal Meister, Slayer of Bad Servers

This is the rub. I know that I should be looking into a server, but that is so mired in red tape and BS that it is not worth it. It does provide the better solution, but I am at the mercy of our IT service provider if I go that route. All I can do is present requirements and let them provide the solution, and it is always less than I want for more than it should cost. If it is a PC then I have a route to just procure it and manage it myself. Out of curiosity, how much memory can you jam into a typical Windows server?

 

Bill

[C1ay]

Check out the AMD Whisperstation from Microway. It is a workstation as opposed to a server :eek_big:

 

P.S. The sell preconfigured clusters too :shrug:

[TheBigDog]

Check out the AMD Whisperstation from Microway. It is a workstation as opposed to a server :eek_big:

 

P.S. The sell preconfigured clusters too :shrug:

That might be just what the doctor ordered! Thanks Clay

 

Bill

[Boerseun]

Do what we did a few years ago...

 

We were playing around with a server solution that had absolutely no bearing on our company, or any of the projects we had running at the time. Yes - we wanted to play around with a kick-*** game server, play around with it some, see what it can do. All for the sake of science, of course.

 

So we contacted HP (at the time, they loved our company and wanted to sell lotsa stuff to us). We bullshitted them about some big-*** project, and that we needed to simulate the environment in our lab. So they gave us three of their top-range servers (the specs evade me now, it was a few years ago - but they were maxed out in every respect, processors, RAM, the works). They gave us three servers, and three months.

 

So we played ourselves silly for three months, and gave back the servers, telling them that our client have decided to go another route.

 

Ethics? Bah! Lotsa fun, though.

[CraigD]

Excel 2010 will come in a 64 bit version that will be able to handle a whopping 8 Terabytes of memory for a workbook. 8 terabytes. That is just a tad bit more than the 2 gig it maxes out at in the 32 bit version. So, is there a computer on the market that can come close to providing 8 terabytes of RAM?

I’m a bit perplexed at the claim that a new Excel can use 8 TB, when as best I can tell, the highest physical RAM supported by any 64 bit Windows OS is 2 TB (for Server 2003 or 2008). For the sort of OEM packaged OS most likely on an "ordinary" 64 bit PC, the “most professional/ultimate/etc” XP or 7, it's 128 and 192 GB. (source: MSDN topic “Memory Limits for Windows Releases”)

 

Even 2 TB, however, would be a staggering amount of memory. The highest-end motherboards I’ve seen (when windowshopping builders stores, not in my own unworthy hands :secret:) have 6 DIMM slots for a max memory of 24 GB, and a bit of rough imagining of a PC cabinet with 500 4 GB DIMMS in it brings to mind a very weird looking machine with at least 10 times the board footprint as an ordinary PC. :shrug: Since US$100 is a good price for a 4 GB DIMM, we’re talking about at least US$50,000 worth of memory for 2 TB.

 

I also suspect you’d need some unusual architecture to support this much typical dynamic random access memory, as it would need more memory refreshing than a 24 GB machine. Since modern supercomputers use over 100 TB of DRAM (eg: IBM’s Roadrunner), I don’t think 2 or 8 TB of DRAM is an unachievable limit, but there might be good sense in using SRAM instead – though, last I checked, it’s many times more expensive than DRAM

 

In short, I’m not sure that, short of buying a true server, or some masterful building with some architecting thrown in, you can get a PC with more than a couple of hundred GB of memory.

 

If you’re absolutely committed to having a 8 TB Excel spreadsheet, maybe the route to go is running it on a true virtual PC that’s actually keeping its memory space on a high-speed storage network – though I’m really talking over my head here, as I’m not sure this is even possible with any sort of commercially available virtualization software.

[alexander]

Newegg.com - Kingston 8GB 240-Pin DDR3 SDRAM ECC Registered DDR3 1333 Server Memory Model KVR1333D3D4R9S/8G - Server Memory

Newegg.com - Intel S5520HCR Dual LGA 1366 Intel 5520 SSI EEB Dual Intel Xeon 5500 and Quad-Core/Six-Core 5600 Series Server Motherboard - Server Motherboards

Newegg.com - Intel Xeon E5530 Nehalem 2.4GHz 4 x 256KB L2 Cache 8MB L3 Cache LGA 1366 80W Quad-Core Server Processor - Processors - Servers

 

Is probably the cheapest you can go... thats, lets see 7.5k before case/psu, drives but thats a dual quad core xeon 2.4 with 96Gb of RAM, and i am not sure that motherboard can support all of it, but only one way to find out... ask intel

 

Anyways, why would you do analysis with excel anyways, doesn't that just sound like it has a bad idea written all over it..? (no offense)

 

I would much rather write a python, php or ruby script to do that, and export the results out to excel, at many times fewer resources, relative ease of use, with all the math libraries you can fathom, and no need for an absolutely insane box for it... I mean our call accounting records are in an MsSQL database (migrating at the moment and cleaning it up, optimizing the database, actually i shaved off something like 42 bytes/record when designing the new database), and yeah it would crush excel if we were to do the analysis via excel, i mean we are a small company, but that database still averages out at about 120k records/day...

[CraigD]

...

Newegg.com - Intel S5520HCR Dual LGA 1366 Intel 5520 SSI EEB Dual Intel Xeon 5500 and Quad-Core/Six-Core 5600 Series Server Motherboard - Server Motherboards

…

Is probably the cheapest you can go... thats, lets see 7.5k before case/psu, drives but thats a dual quad core xeon 2.4 with 96Gb of RAM, and i am not sure that motherboard can support all of it, but only one way to find out... ask intel

So, so far in this thread, we’ve collectively found the makings of a Windows PC with 6 DDR3 DIIM slots. Though I don’t understand the details of DIIMs, this wikipedia article states that they have a theoretical maximum of 16 GB. 16GB DDR3 DIIMs have been commercially available for slightly over a year, and according to this price comparison webpage, can be had for $939+ each – call it $6K for 6.

 

So we’ve got a 96MB PC – half of Windows7 Ultimate’s 192GB, about 1/20th of Windows Server 2008’s 2TB, and barely a start toward this thread’s original target of 8TB.

 

I’m beginning to think 96MB may be the best that can be had COTS. :shrug:

Anyways, why would you do analysis with excel anyways, doesn't that just sound like it has a bad idea written all over it..? (no offense)

 

I would much rather write a python, php or ruby script to do that, and export the results out to excel, at many times fewer resources, relative ease of use, with all the math libraries you can fathom, and no need for an absolutely insane box for it...

From my own slowly dawning appreciation of how popular Excel is with so many users, I’ve learned never to underestimate how well received native Excel can be, and to be accommodating of importing and exporting data to and from it. For a few months in 2001, I pushed the idea of using host-served html tables for everything folk were doing in Excel, before finally admitting the reality of Excel’s popularity and making standard comma-separated values (.csv) format input and output devices to our main biz systems.

 

CSV is OK, in that nearly any and absolutely all our managed PCs browsers associate it and automatically open it with Excel, but it lacks the fancy formatting of Excel’s default .xls format. One of these days, I really should add XMLSS support to allow for fancier formatting and other extras – for quirky managed PC reasons, my org’s only had versions of Excel that supported it on or managed PCs for a few years, long after I wrote the CSV stuff, even though it appeared in Excel2002.

 

What anybody intends to do with it’s mostly beside the point for me in this thread, though – I’m just curious to know how much RAM you can actually get in a more-or-less CTOS PC. :eek:

[Buffy]

From my own slowly dawning appreciation of how popular Excel is with so many users, I’ve learned never to underestimate how well received native Excel can be, and to be accommodating of importing and exporting data to and from it.

 

...I was catching up on some old issues of The Economist the other night...

 

NO INDUSTRY spends more on information technology (IT) than financial services: about $500 billion globally, more than a fifth of the total (see chart). Many of the world’s computers, networking and storage systems live in the huge data centres run by banks. “Banks are essentially technology firms,” says Hugo Banziger, chief risk officer at Deutsche Bank....

 

...Corporate IT systems—collections of computers, applications and databases—always tend to be messy, but those of banks are particularly bad. They were the first to adopt computers: decades-old mainframes are still in use. Lots of product innovation means new systems, as does merger activity, which has proliferated in the industry in recent years...

 

The demands of financial markets make matters worse. Hedging positions, trading derivatives and modelling financial products all require highly sophisticated programs that are only really suited to specific asset classes. The code for new financial products has to be developed quickly. Innovation often takes place on Excel spreadsheets on traders’ desktops. “The big task of management is to manage down the number of spreadsheets,” says one risk chief, whose bank creates 1,000 product variations a year.

 

As a result, many banks have huge problems with data quality.

 

I'll say. I bet Boerseun could jump in on this angle of it too...

 

You're not alone Bill: there are lots of people jury-rigging the silliest things because of the Information Technology Prevention department....

 

The nice thing about Excel of course is that any novice can do it, but sneaking a local dbms on your machine and writing a little code to preprocess it is the best way to avoid trying to sneak those $939 DIMMs into your expense report....

 

If you put tomfoolery into a computer, nothing comes out of it but tomfoolery. But this tomfoolery, having passed through a very expensive machine, is somehow ennobled and no-one dares criticize it, :eek:

Buffy

[alexander]

From my own slowly dawning appreciation of how popular Excel is with so many users, I’ve learned never to underestimate how well received native Excel can be, and to be accommodating of importing and exporting data to and from it. For a few months in 2001, I pushed the idea of using host-served html tables for everything folk were doing in Excel, before finally admitting the reality of Excel’s popularity and making standard comma-separated values (.csv) format input and output devices to our main biz systems.

Who said anything about csv files, that's both old and primitive... I generate excel xlsx files with the ability to insert images, charts, functions, linked multiple work sheets and stuff like that. With that i also don't have to extensively use views that depend on views that depend on other views, and write sql queries to process data which take stupendous time, i get raw data and process it in php, and then serve it to you as a mime type, all you know, you just clicked a link to an excel file, while one doesn't really exist, weeeell, it does, kinda, but i generate it, and its temporary... I can also make templates, and stuff like that, its a part of the reporting and invoicing engine that is a wip, which is not receiving a ton of attention, there are two more important projects on the board at the moment, but it will completely change how we do business in the next year or so, the rough proof of concept i whipped up is already being used in 4 reports for production, every time it saves us time needed to generate the reports by hand, it saves us time in the query world, because we don't have to actually post process the data in sql (My, MS), sometimes selecting the same data multiple times for different data segments in the reports; not there anymore.