Benefits of OLAP Cubes

In Chapter 9, "Using External Data Sources for Your Pivot Table," you learned how to use external data sources to handle datasets larger than 65,536 records. An Access database or SQL server might be able to deliver a few hundred thousand records to an Excel pivot table. What happens if you have a lot more data? Millions, or even billions of records?

Here are two contrasting examples. In a very small bank with just a few branches, you might have only a few thousand customers. You can easily create a dataset that would fit in Excel about those customers' balances and loans. You could then use pivot tables to generate all sorts of interesting aggregations.

However, if you're working for a medium-sized chain of convenience stores and are tracking all the daily sales, that's a set of data that is several orders of magnitudes larger. Imagine that the chain has 250 stores, and each store is averaging 1,000 sales per day. Over the course of a year, you'll see more than 91 million sales occur, each one involving an average of 2.3 products.

You will want to know which items sell fast and which aren't worth the shelf space. You will want to compare this year to prior years, and you will have more than 200 million records per year (one record for each item sold). Can Excel, by itself, handle all this data? No. Isn't there some other way? Yes, if you have an OLAP server.

When your database involves millions of records, most likely you're already using a full-blown relational database management system (RDBMS) such as Microsoft SQL Server 2000 or later. SQL Server brings with it several other excellent tools to build solutions for making sense from all that raw data. Some other RDBMS products also have associated tools for building business intelligence solutions. Your database administrator (DBA) or data warehouse architect can use these tools to build a data warehouse and OLAP cubes and keep them up to date with new data as it comes in.

The biggest conceptual change in using OLAP cubes is that the data is already prepared for you. When you've got hundreds of millions of records, it's too much to query them all on the front end. More than likely you have a full-time DBA tending to them on the back end. The DBA will be experienced in the arcana of data warehousing and will create and maintain the OLAP cube(s), which you will access using pivot tables.

    Pivot Table Data Crunching
    Pivot Table Data Crunching for Microsoft Office Excel 2007
    ISBN: 0789736012
    EAN: 2147483647
    Year: 2003
    Pages: 140 © 2008-2017.
    If you may any questions please contact us: