Long ago, it was code-named "Plato." In the SQL Server 7 release, it was called "OLAP services." Now, in the SQL Server 2000 release, it's called "analysis services." I am, of course, referring to Microsoft Corporation's engine for online analytic processing (OLAP), which is bundled into every license of the SQL Server database at no extra charge. Analysis services shows significant advancements over OLAP Services.

  • Unbalanced dimensions. OLAP Services perform well with symmetric, balanced multidimensional data structures, but analysis services has improved handling of unbalanced dimensions.
  • Changing dimensions. Analysis services supports "type 2 changing dimensions" (in the Ralph Kimball sense), recreating aggregates in the background with little or no hit on performance, so you don't need to rebuild and reload the entire OLAP database.
  • Data scalability. According to Microsoft representatives, users of OLAP services included as many as four million members per dimension. (Industries such as telco or retail banking typically have millions of customers, and you may want to include these as members in the customer dimension so you can analyze them.) OLAP engines from other vendors typically top out at one million members. Some users of Analysis Services are predicting members in the tens of millions.
  • Internet-based access. Client applications can now connect to an analysis services cube over the Internet, simply by finding it through a URL and communicating through standard HTTP. This enables the strong trend that many e-businesses are now living of supplying business intelligence over the Internet. You can even link one analysis services cube to another over the Internet to create one large virtual cube. Imagine several companies, each with its own cube of supply chain data, integrating their cubes to gain better supply chain intelligence.
  • Security. OLAP Services have had cell-based security since Service Pack 1 for SQL Server 7, where users cannot see the values of cells for which they have no clearance. (For example, the system could hide granular data like other employees' salaries.) Analysis services now adds dimension-level security so users without security clearance cannot even see that dimensions they are disallowed exist.
  • Miscellaneous advancements. These include automatically generated object groups for lowest levels of granular data (for instance, creating ranges of customers that have names starting with A-E); write back where current results are shown in the cube immediately; custom roll-ups for account dimensions; conditional actions (such as formatting in green all numbers greater than a certain value); and many improvements to the Cube Editor and wizards.

THE HURWITZ TAKE: Although OLAP services was a proof-of-concept release and has been in the field for only about 18 months, it has gained strong adoption as a platform for data marts as well as some adoption for data warehouses. Data mart developers say they are drawn to SQL Server's OLAP services because of its ease-of-use and price-per-performance. It is exactly these two attributes – which are key requirements for business intelligence in mid-market organizations – that have made SQL Server's OLAP services the preferred platform for data marts in the middle market as well as a commonly encountered platform in high-market IT organizations.

Besides IT organizations, many consultants and independent data warehouse practices have adopted SQL Server's OLAP services as a requirement, which has further spurred its proliferation, as has the growing number of analytic applications and analysis tools from independent software vendors that require SQL Server's OLAP services. Furthermore, SQL Server runs on NT (or Windows 2000) on commodity- priced, Intel-based hardware, and it integrates easily with any database or warehouse platform supporting ODBC or OLE DB, so price and integration issues have not been barriers to adoption.

Because of its high adoption rate, Hurwitz Group recognizes that OLAP services has more than proved its concept. Analysis services offers many advancements over OLAP services that bring it close to parity (or even beyond) with many older OLAP engines (which don't have its ease-of-use or favorable price-per-performance). Therefore, Hurwitz Group predicts that SQL Server's analysis services will soon become the most prevalent platform, whether for data marts or data warehouses, whether in middle or high markets. Data warehouse and mart developers who demand ease-of- use, price-per-performance, Internet-based cubes, millions of dimensional members, and granular security should put Microsoft's SQL Server and analysis services at the top of their evaluation list.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access