Analysis Services has given many organizations the ability to produce powerful data marts and data warehouses, but delivering the data to the organization is sometimes problematic. SQL Server 2000 Reporting Services gives businesses a free, powerful tool for delivering warehouse data to users. Although Reporting Services won't replace full-blown analytics applications, it provides perhaps the broadest reach for warehouse data in the business intelligence (BI) process. It's useful to learn how to use Reporting Services to access Analysis Services data because it gives users the benefits of data warehousing.
Reporting Services 2005 adds many powerful features for accessing data in Analysis Services 2005 cubes, but many organizations continue to use Analysis Services 2000 and have cubes that will be challenging to upgrade to Analysis Services 2005. In this article, I walk you through an example that shows what Reporting Services can do to deliver data from a warehouse to the whole organization. Reporting Services lets you provide data to business users in static reports or reports that use parameters and have limited drilldown capabilities. In an upcoming article, I'll show you how to modify the report to display measures and dimensions differently, and walk you through the steps required to properly format such a report.
To create a report that pulls data from Analysis Services, the report creator must use MDX. MDX is a query language for cubes, just as SQL is the query language for relational databases. Cubes are multidimensional by nature, so the query language must understand multiple dimensions and have the ability to have more than one measure at the intersection of dimensions (basically by treating measures as another dimension). Reporting Services 2000 doesn't provide a graphical builder for MDX statements, which requires the report creator to code the query by hand. Even if your organization uses Reporting Services 2005, it's important to understand the MDX queries created by the graphical designer so that you can restructure them for added flexibility. If you don't have experience with MDX, you'll be glad to learn about the MDX generator in the Web sidebar "MDX for the MDX Phobic"(http://www.sqlmag.com, InstantDoc ID 50233).)
Building a Basic BI Report
As with any Reporting Services 2000 report, our example report usesVisual Studio's IDE. Start by creating a new project that will consume data from Analysis Services. In Visual Studio, choose Business Intelligence Templates as the project type and use Report Project as the template; don't use the Report Project Wizard.
Next, you need to create a data source. A shared data source usually works well, so right-click the Shared Data Sources folder in the Solution Explorer and choose Add New Data Source to bring up the Data Link Properties dialog box. On the Provider tab, select the Microsoft OLE DB Provider for OLAP Services 9.0, 8.0, and 7.0. Once you've filled in the name of the server and the cube that you want to use, you can create reports. In this article, I use the Foodmart 2000 sample database that comes with Analysis Services 2000 for all examples.
To build a basic report, right-click the Reports folder in the Solution Explorer and choose Add, Add New Item, Report. I don't recommend using The Report Wizard template because it doesn't allow for the full range of options you'll need to design the report. The first report, Basic Report, is in the sample files available for download at http:// www.sqlmag.com, InstantDoc ID 50204.
Prev. page
[1]
2
3
next page 
