Create Local Cubes with Microsoft SQL Server 2000 and Microsoft Office
Using the Local Cube
The PivotTable facility of Excel is a very flexible tool that can be used to both browse as well as generate reports. We can drag the necessary dimensions and measures to the axis and the data area to view only the fields that interest us.
Any OLAP client software that can be connected to Microsoft OLAP Services can also be used to access a local cube file. The MDX Sample Application found on the OLAP Manager’s program group is one such example. Running the MDX Sample Application will prompt you to enter the name of the server in the opening connection window. You can select the name of the local cube you want to access in the cube combo box. Follow it up with the query you have by typing it in the MDX query that appears at the top of the MDX Sample Application edit box.
Another option is to develop your own application by using ADO MD with the help of Visual Basic in order to access local cube features available in Excel.
The Various Uses of MOLAP, ROLAP and Hybrid OLAP Cubes
There are three kinds of OLAP Cubes — the Multidimensional OLAP (MOLAP), Relational OLAP (ROLAP) and the Hybrid OLAP (HOLAP). A MOLAP is based on the nonrelational data model, while ROLAP is created out of RDBMS or relational databases. Hybrid is a combination of the two that uses relational tables to hold base data and multidimensional tables to hold the speculative aggregations.
MOLAP Cubes are used more commonly as they have the ability to extract data from multiple data sources. They can handle small-sized data very well, however creating them takes up enormous space and time. Their biggest advantage lies in their ability to answer queries very fast.
ROLAP cubes are more scalable and take up less time and space but they are slow in query performance. They serve as an excellent tool for designing new models or proving new concepts. An ROLAP cube can be used to hold and test a representative set of data in order to find the viability of any contemplated design. Users can simulate real like problems with ROLAP cubes and identify flaws.
A ROLAP local cube can serve as a prototype to test the success of a model in terms of the data structure, its interface ability, usability and the solution it eventually provides. You can experiment with various structures and processing tests to figure out what changes to make in order to arrive at a solution that best fulfills the needs of the model you are designing. They provide a complete laboratory free of any influence for experimenting and establishing any empirical model.
HOLAP combines the best and the worst of both MOLAP and ROLAP in all areas, but it is good for quick preprocessing and can be scaled.
Add Value to Your Business
For a business to succeed, any support that can enhance its business intelligence capabilities assumes great significance.
Local cubes made by integrating SQL Server 2000 and Office can serve as an excellent tool for enhancing analytical thinking and enabling good decision-making. Its ability to support intuitive interaction with users and to provide rich visualization can help corporate houses identify both the opportunities and the anomalies in a business and make decisions accordingly.
Local cubes can integrate data from various applications and give it a more consistent look, scan data to establish relationships between them and use data to make forecasts and predictive analysis. They facilitate the generation of reports using multiple formats and techniques. In other words, local cubes created with the help of SQL Server 2005 Analysis Services provide specialized tools to help enhance your business intelligence.