Data Warehousing and
OLAP Cubes

The Quill Consultancy recently held a presentation in conjunction with Microsoft on Data Warehousing and OLAP (online analytical processing) cubes using SQL Server 2000. This very informative session was conducted by Des Jacobsen (Senior Technology specialist) from Microsoft’s Melbourne office and was attended by over 25 local IT professionals. We wish to thank Microsoft for providing the resources to support this presentation.

Des was able to provide an overview of Microsoft Data Warehousing technologies. Many organisations have a number of operational databases that could be combined into a Data Warehouse from which OLAP data cubes can be created that can then be queried using various software clients such as Microsoft Excel (using PivotTables), Cognos’ PowerPlay and Business Objects for example, or via your browser as data can be presented in either HTML or XML format using third party tools like Aladdin from Adelaide based company SDM - strategic data management (who presented a business intelligence seminar here late last year).

The following diagram summarizes this process:

The main business objective of Data Warehousing is for the data to be easily manipulated to deliver business intelligence information to non-technical users within an organisation.

Data Warehousing technology has been around for sometime but in general terms has only been available on very large systems that required a team of people to extract and manipulate the data in a useable form. The technologies available in SQL Server 2000 enable the creation and manipulation of data much easier. It needs to be stressed, however, that this technology is only as good as the data that makes up the data warehouse and many organisations have significant issues in relation to the state of the data they currently store.

Data warehouses are designed to overcome problems encountered when an organization attempts to perform strategic analysis using the same database that is used for online transaction processing (OLTP).

Whereas data warehouses and data marts are the data stores for data to be potentially analysed, online analytical processing (OLAP) is the technology that enables client applications to efficiently access this data.

For more information on OLAP and SQL Server you may wish to visit this site: click here.

Editorial comment:

While this technology is exciting and extremely powerful, many businesses in both the private and government sectors currently lack the IT infrastructure to take advantage of these tools. We estimate that only approximately 20% of organisations have successfully upgraded or migrated to Windows 2000 Server and Active Directory which provide a better platform for much of the newer OLAP technology.

A continued under resourcing of IT in many areas of private industry and government continues to prevent technology from being fully utilised. This appears to have been compounded over the last 12 months or so with the perception that expenditure for the Y2K "bug" and updates to software systems as a result of the introduction of the GST have seen "enough money going into IT" and therefore a reluctance by organisations to commit funding for the continual upgrading of core infrastructure.

The key to efficient operation of today’s businesses and government agencies is the ability to quickly and efficiently access information that assists the business decision process. Unless an on-going commitment is made to sustaining up-to-date IT infrastructure businesses will lose their competitive edge through an inability to extract key operational information in a useful and timely fashion.