Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 20, 2004

Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I

By William Pearson

About the Series ...

This is the nineteenth article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation, manipulation and use of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("MSAS"), with each installment progressively adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSAS.

Along with MSSQL Server 2000 and MSAS, which are employed in all articles of the series, additional application considerations apply for this tutorial, because it introduces a third-party reporting solution, ProClarity Professional ("ProClarity"). For those joining the series at this point because of a desire to work with MSAS and its components from a ProClarity perspective, it is assumed that MSAS is accessible to / installed on the PC, with the appropriate access rights to the sample cubes (which are provided in a typical installation of MSAS).

We will use ProClarity (Platform 5 or higher), and various components that come along with its typical / default installation, to demonstrate the setup of, and some approaches for, using this business intelligence tool for performing analysis and creating components of reports, based upon an MSAS cube as a data source. ProClarity provides the capability for establishing connections to our cubes, among other functions that we will explore. Because we are targeting existing or evaluation users of ProClarity, we will assume installation of the application involved, and focus upon the specific setup required to enable reporting from MSAS cubes. If this is the first time ProClarity is being accessed on your machine, or if ProClarity is not already installed / configured, please consult the ProClarity documentation for installation instructions.


While the majority of the articles of our series to date have focused upon the design, creation and manipulation of cubes within MSAS, I began in Article Ten to discuss analysis and reporting options for cubes. I undertook this because, as most of us are aware, until the release of MSSQL 2000 Reporting Services (stay tuned for a series of articles on this amazing new paradigm in enterprise reporting!), Microsoft does not provide a graphical tool within MSAS to analyze or report upon the data in an OLAP cube. In addition, I offered tutorials on using a couple of external options in response to the expressed need of several readers for alternatives in this regard - alternatives beyond the mere browse capabilities within Analysis Services.

We therefore explored some of the options offered by the Excel PivotTable Report (Article 10), the Microsoft Office PivotTable List (Article 11), and Cognos PowerPlay (Article 12) for report building with MSAS cubes. Within each of these articles we presented an introduction to using the respective tool to retrieve and display information from our cubes, first discussing the steps needed to define our data source, and to establish a connection to the cube. Next, for each option, we exposed the functionality involved, the physical layout and general navigation. Moreover, we then explored the use of each tool in sample browsing / reporting scenarios with MSAS cube data. Finally, we discussed various presentation and formatting considerations for the respective reporting options, when it was useful.

Reporting Options for MSAS Cubes: ProClarity

In this two-part article, we will return briefly to our exploration of what has continued to be a popular topic (and which, as I mentioned in Article Ten, results in several e-mails each week in my inbox), Reporting Options for Analysis Services Cubes. As I stated before, our focus is to explore options beyond the Analysis Manager / Sample Application interfaces for obtaining reports from, or browsing MSAS cubes within a range of similar business intelligence capabilities. My intent continues to be, therefore, to review the process of establishing connectivity and enabling reporting capabilities for each of the options, coupled with a high-level overview of the functionality that is available. It is beyond the scope of the articles to compare the product features themselves, to any significant extent. As I stated from the outset of the Reporting Options articles, I will consider providing a similar examination of other analysis and reporting applications, as time and circumstances permit, based upon useful suggestions I receive from readers. (This article is itself the result of such a suggestion.)

After a brief introduction to the application, together with an overview of the process of connecting it to an MSAS cube, we will examine some of the options offered by ProClarity for report building. As we did with the MS Office and Cognos reporting options in the previous Reporting Options articles, we will present an introduction to using ProClarity to retrieve and display information from our cubes, after exposing the steps required for establishment of a connection to the cube. Next, we will briefly examine the layout and navigation of the reporting interface that ProClarity offers, and examine illustrations of browsing and reporting our cube data from within the application.

The topics within this article will include:

  • The establishment of connectivity and other preparatory steps to enable analysis and reporting via the ProClarity application;
  • An introduction to several features and options that are available within ProClarity for browsing MSAS cubes, and creating components of robust and flexible reports;
  • The basics of use and navigation of the ProClarity interface in analyzing and reporting from MSAS cubes.

Introduction to ProClarity

In exploring the use of ProClarity with MSAS, we will primarily examine using the application to perform analysis from the perspective of ProClarity Professional, a part of the ProClarity Analytics Suite, which contains both Windows desktop and thin browser client components. The examples and images I present are taken from an installation in a simple, standalone server environment. We will be looking at the Windows desktop component, and focusing more on analysis than reporting, per se, as the same principles apply in general; Analysis typically involves more interactive scenarios, and the flexibility that ProClarity affords in publishing allows the practitioner to generate and deploy reports in many common formats and environments.

ProClarity is typically used by advanced analysts and other power users to perform analysis of cube data, and to create, manage and publish reports, KPIs, queries, sets and logic to numerous end destinations that are anticipated in the suite. Included are ad-hoc analytic capabilities, powerful visualization and flexible presentation functionality, and other robust query and calculation options. One of several strengths of the application, as we shall see, is that it removes unnecessary complication from the analysis process, making decision support more intuitive and faster (the aim of many of the solutions in the Enterprise Business Intelligence applications class), and available in a "self-serve" environment. The end desire is a unified, integrated analysis and reporting capability that is reliable, flexible, easy to use and rapidly delivered.

ProClarity comprises a powerful enterprise analysis and reporting option that promises substantial savings over the cost of any of the large, proprietary solutions such as Cognos, Business Objects and others whose five- to six-figure price tags include both relational and OLAP elements, as well as cube-building apparati. While these proprietary solutions are compatible with most popular RDBMS', it has only been in recent versions that the "Big Sisters" began to make their once dedicated cube reporting components work with other OLAP servers, including MSAS. The motivation is obvious, as the ascendancy of the RDBMS-generated cube is, at this late stage, both inevitable and irresistible.

A tool like ProClarity allows us to perform in-depth analysis and build robust, powerful OLAP reports and business intelligence applications, based directly on MSAS cubes. This eliminates the peripheral components that add so much to the cost of the predecessor enterprise reporting applications - and allows the flexibility to select relational and other analysis, reporting and consolidation components singly and independently, thereby affording the construction of a custom solution tailored ideally to the industry and reporting environment.

Article Scope

In exploring the use of ProClarity with MSAS cubes, we soon become aware that there are multiple approaches to reaching our ends. In this article, we will examine using the ProClarity desktop incarnation, to report from select sample cubes that accompany a typical installation of MSAS.

We will expose the steps of setting up connectivity without many of the complications of real world implementation, where the integration of security and a raft of other components, all of which are beyond the scope of this article, would require careful consideration. Our intent here is simply to offer another option for reporting from MSAS Cubes, as we have stated in each article of our Reporting Options for Analysis Services Cubes sub-series.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM