The KPI list in the MOSS Report center main page http://MOSSSERVER/reports is pretty straight forward with the exception of how to integrate Analysis Services KPI data beyond a single KPI measure indicator - Red, Green or Yellow.

Dashboards are primarily Web Part pages that can be used to show XLS Reports, (Excel files that are published to a MOSS library) spreadsheet based pivoted data info that is either comprised of SSAS, SQL or some other OLE DB data via a connection embedded in the worksheet

Some background with Excel will be helpful with more advanced methods of obtaining and reporting on your data without touching SQL Server Client Tools, BI Tools or Visual Studio. Ultimately, it may help you understand some out of the box reporting limitations of MOSS with regards to KPIs. This simple walk-through will demonstrate some of the flexibility and power by which to report KPI measures from a multidimensional cube in a MOSS dashboard.

Starting with Excel 2007, Select the Data tab and then Connections

Click Add > Browse for More

Select "Connect to a New Data Source"

Next the connection wizard will present the obvious option:

Enter the server name (screenshot not show) and it should present you with SSAS and table / cube database (permissions apply)

For this example we have a single cube on the server

Select Database and Table and click Next

Save and Finish

If this is the first system defined data source that you have made in Office from your PC, then after configuration in Windows XP a new folder below My Documents will be created called My Data Sources containing your new Office Data Connections which are important not only for this Excel based view of SSAS data, but also for re-use within the MOSS Portal "Report" Data Connection library. The connection file is Office XML that defines the connection a portion of which is shown below:

<odc:ConnectionString>Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=SQLSERVERNAME;Initial Catalog=ssas_ebr_east_udm</odc:ConnectionString>

Once the connection configuration is complete the current Excel Workbook when saved will always reference the cube.

When publishing this to MOSS, Excel Calculation Services has some caveats as to how this data is refreshed and displayed, so be sure to set the properties on the definition tab to Always use connection file.

(For more information on this, see the MOSS Portal based help files on "Refresh External Data in Excel Services")

In Excel, now select the Data Tab and the ribbon drop down, "Existing Connection".

Select your new SSAS connection and place the display of the data type you want into the worksheet as shown

Excel can show the KPI's as defined within the cube's dimensions and expressions. This can in turn be published to a MOSS Report library. A Dashboard page is then built with a view of this information through the Excel Web Access web part.

Additional, we can re-use these steps taking the ODC file we created via Excel and upload that to the Data Connection Library. This is required to use Analysis Service Data in a KPI list. A KPI list can be a mixed list of rows displaying manually entered information list as well as SSAS data. Getting even more complex, KPI's data can be pulled from dynamic lists.

MOSS has an option of using SSAS Indicators in a KPI list, These can be shown with or without the colored SSAS KPI Status Red, Orange Green. (More info to follow in Part 2)