In this chapter, we extended a data warehouse and Analysis Services database to cover new areas to support the objectives. We decided to build reports that query the cube rather than the data warehouse so that we benefited from the performance increase from Analysis Services as well as more advanced and flexible reporting features such as key performance indicators. The scorecard reports were published on a Windows SharePoint Services intranet portal, allowing employees to see how the effects of new policies in their areas lead to improvements in the key performance indicators.
We built a single Analysis Services cube containing several different fact tables with different granularities. To help the users navigate the cube, we added several simplified perspectives focused on different subject areas. We identified any common dimensions across business areas and used conformed dimensions to allow cross-functional reporting.
We created a set of reports to display the KPI's using expressions to dynamically pick the relevant image based on the current value and used the Chart component to create graphical views of the information. We built the portal using Windows SharePoint Services to create a team site and used the Report Viewer web part to display reports in the portal. We used linked reports with specific parameter values to allow us to show parameterized reports in the web parts. We also added some dynamic PivotTable views to the site using the OWC controls.
To secure the information, we defined dimension data security in the Analysis Services database to hide some levels of the employee dimension from groups of user, and assigned permissions to specific groups to allow them to access the WSS team site and Reporting Services reports. We deployed the Web portal using Sharepoint Migration Tool (smigrate). Operations staff will now also back up the WSS SQL Server databases in addition to the reporting and data warehouse databases.