When I get my cell phone bill from Nextel every month, it lists all the calls I’ve made, all incoming calls, text messages, AIM time, Direct Connect minutes, etc, etc. I am curious to know what kind of database cell phone companies use to keep track of calls, time of day, length of call, rates. Are they using SQL or something even more powerful?
Most telecommunications companies use something called a “Data Warehouse”. The difference to a normal RDBMS (Relational Database Management System) like Oracle, or DB2, or SQL Server is that the data is not stored in tables with multiple columns, but in rows. Each row contains one data entry that uses something called “bitwise indexing”. This means the index is in the data itself. The advantages are that queries run much, much faster than in a traditional RDBMS, and the data is stored much more efficiently (less space = lower costs). This is important because a decent sized telecom company will have Terabytes of data in its data warehouse!
These data warehouses are great for getting results of queries (in other words reporting data). They are really slow when it comes to manipulating only certain parts of the data (like changing account information for one customer). That’s what you use a traditional RDBMS for.
The data is literally pumped into the data warehouse from the various RDBMS where it is originally stored. Then after the data is all there, the reports are run. One example of a data warehouse is a product called Sybase Adaptive Server IQ. You can get some simple info on this here.
I’ve worked at one major telecoms company in the UK. They receive CDRs (“Call Data Records”) from the actual service providers, match against customer and tariff records, re-rate the calls at retail rates (i.e. price the calls according to your tariff), and apply bundling (i.e. net off free minutes, discounts etc) using a custom system that actually doesn’t use an RDBMS, but relies on vast numbers of sequential text files.
Reporting (not billing) is performed via a data warehouse as described above, in my experience often using an Oracle database and data mining and reporting tools such as Viper or Business Objects.
Um… no.
I have no clue what kind of database Nextel or any other telecom company uses, but your definition of data warehousing is completely incorrect. Maybe you’re getting it mixed up with another term?
For what it’s worth, “data warehouse” generally refers to the practice of consolodating data from a variety of diverse sources into one database for the purpose of analyzation. Typically, a data warehouse supports management level decision making.
Data that consists of lots of discrete transactions (like cell phone records from millions of cell phone users) is exactly what a data warehouse is NOT for. A telecommunications company might consolodate individual cell phone data into monthly, regional, or other high-level segments and use THAT data in a data warehouse to study marketing trends, but the data warehouse itself would not be used to store each person’s individual call data.
Sorry, just couldn’t let that one go…
I work for one of the top cell phone service providers in the US and in our CDR archives we track not only calls made and recieved but also each time your phone switches towers a record is made whether you are using it or not. If it is turned on and you are driving you are most likely generating records in the archives. You can imagine the size it takes to hold all that information. We also have one of the top 10 supercomputers in the world running the archival system .
Ok, so thats just hearsay from the people that set it up, I don’t have anything to back that one up.
I had heard everything you said above on some radio show last week. I thought it might be true. Cell Phones are one way for big brother to track everything you do and everywhere you go.
Is it also true that the phone doesn’t even have to be turned on for them to know where you are?
Oh yeah I remember where I heard this, it was on coast to coast.
Um … no.
Data that consists of lots of discrete transactions (like cell phone records from millions of cell phone users) is exactly what a data warehouse IS FOR!!! My mistake was mixing up the terms “data warehouse” and “data mining”. A “data warehouse” is simply the collected data from various databases and applications. This means many Terabytes of data for a typical telecom company.
The “Adaptive Server IQ” I mentioned is actually used for “data mining”. Since it would take much too long to run queries on these huge amounts of data, the important data is loaded into an IQ database, and then reporting queries are run on this metadata. This way the telecom company can recognize important trends like: “At what time of the day does the number of calls peak?”, “In what region do most calls originate?”, “To what region are most calls made?” and things like that.
Here is some more information on how various telecom companies use “data warehouses” and “data mining”:
http://www.sybase.com/detail/1,6904,1012492,00.html
http://www.sybase.com/detail/1,6904,1012495,00.html
http://www.sybase.com/detail/1,6904,1009829,00.html
So, I guess we were both kind of wrong and right. Next time I should make sure I don’t get my terminology all mixed up.
There are just so MANY ways that a government could track you these days its pointless to worry about it. Quite simply, the government doesn’t care about you unless you get big enough for them to notice. The potential benificial aspects of having a truely location based tracking system are fairly outweight the minor decrease in privacy.
And I don’t see how anybody could track you with a turned off phone. It’s just a piece of inert plastic. If your really worried, take the battery out.
While the answer technically is yes, cell phones can be one way for big brother to track you, just as Shalmanese said unless you are big enough for them to notice they dont’ really care. So unless you are a terrorist or on the FBI top wanted list you can relax, you are not being tracked for surveillance reasons. There are actually federal mandates requiring cell phone companies to be able to track phones like this for the purpose of 911 calls. They usually will triangulate your signal from 3 towers and try to determine your location from signal strength. I believe that is how it works, I work on the hardware that keeps everything running, but not the software end that makes it all work so I am not familiar with how it all works precisely.
On a side note, these records are not automatically fed to the feds so they can track you, it takes warrants and all that before they can access these records.
Newer, “Smart” cell towers can use multiple antennas on the same tower running out of phase to track you down more precisely that current antennas. The advantage is the 911 thing as DiLLiGaf mentioned but also the ability to simulataneously have more users and greater signal strength per tower since the beams are directed at you rather than spread over a wide area.
Not completely true data rows are stored with columns contiguously in Oracle whenever possible. This maximizes performance by allowing a row to be read in a single data block once it’s located in the index. This isn’t absolutely enforced and a condition that forces a data row to be spread over multiple data blocks on disk is a sign of poor management and is a severe performance hit. In any event data is not stored as individual columns save for long colmns which have an undefined and for the most part unlimited size.
I’ve been an Oracle DBA for about eight years and am just making the transition to data warehousing on NCR TeraData so I have a little street cred I think.
You are correct about indexes being integrated into the table in a DSS oriented system where in a relational DB the index is a separate entity. In Oracle you find the row in an index which points to the disk block that contains the row. In Teradata that’s one step as long as it’s the primary index.