It's late Friday afternoon, so you are wrapping up your day and getting ready for the weekend when you get a frantic call from your boss. He just received a call from your division's executive who urgently needs data on a specific group of projects in your portfolio. In months past, this report would have taken you the entire weekend to generate. Now, by using IBM® Rational® Portfolio Manager Version 7.1, you can generate this report automatically with just a few mouse clicks.
More options and more flexible reporting methods
Reporting on what has happened and where things currently stand is an important aspect of any project manager's job. In IBM Rational Portfolio Manager, you previously had two options for producing reports:
- Use the Rational Portfolio Manager proprietary reporting tools
- Export a copy of the online analytical processing (OLAP) pivot reports, and then use third-party tools, such as Microsoft® Excel®
With the release of version 7.1 of Rational Portfolio Manager, there is a third option: access to an external relational database that is provided with Rational Portfolio Manager to view and manipulate a copy of the data. Before going into the new method of reporting, letâs take a look at where and how the reporting was done in earlier versions of Rational Portfolio Manager.
Up through version 7.0.x.x of Rational Portfolio Manager, you could use the reporting tools within Rational Portfolio Manager to do Investment maps, OLAP pivots, or reports. All of these are run against the live operational database. The stored procedures to support these reporting methods, first of all, consumed a huge amount of CPU cycles, as well as returning the complete set of data associated (potentially millions of data points, depending on the size of the database) with the underlying query. This was all run against the proprietary database which is non-relational, has no referential integrity constraints, and contains table and column names that are used for multiple purposes. In addition, there is also no native or external query building ability against the database. This does not mean that custom reports couldnât be developed, they could, but it required specialized knowledge of the report writing tool that is delivered within Rational Portfolio Manager. What does all this mean? It meant that reports could be slow to generate, that they were difficult to change and that more data was returned than what was âasked for,â and that during a report generation the CPU resources being consumed could drastically reduce the amount of CPU available for daily operations within the database.
Starting with Rational Portfolio Manager V7.1, you can access an external relational database to view and manipulate a copy of your data. This method enables you to use tools such as IBM® Lotus® 1-2-3, IBM® ALPHABLOX®, Crystal Reports, Cognos, Microsoft Excel, or BIRT (Business Intelligence Reporting Tools, in Eclipse). In addition to giving you the flexibility to use existing reporting tools, business intelligence tools, or Open Database Connectivity (ODBC) data-sourced tools, this alternative has several additional benefits:
- Improved performance
- Technology based on open architectural standards
- Secure access to Rational Portfolio Manager data
- Fully documented staging tables
- Fully documented data warehouse stars that cover current Rational Portfolio Manager pivots (except Assets)
- No middleware necessary to access the data
- Easier, faster, and more adaptable operations:
- Use existing tools and replication technologies
- Separate CPU use for report generation from daily, operational database CPU use
- Automate the transfer of updates to the relational database, either for the delta data only (changes since the last update) or for all data
- Store your data on a separate server
These benefits mean that you can create reports ad hoc and get the resulting data faster and easier than ever before.
There are two basic components of this more flexible reporting method:
- A data warehouse, which IBM says can mean either "a central repository for all or significant parts of the data that an organization's business systems collect" or "a subject-oriented collection of data that is used to support strategic decision making," depending on the context. Ralph Kimball, one of the foremost authorities on the dimensional approach to data warehousing, defines a data warehouse as "a copy of transaction data specifically structured for query and analysis." Any or all of those definitions accurately describe the Rational Portfolio Manager data warehouse.
- Star schemas (also known as "stars"), which are the framework used to store the data in the warehouse. Michael W. Cain describes the star schema as "a centralized table, known as the fact table, [that] is surrounded by highly normalized tables known as dimension tables. The name 'star schema' reflects the fact that the data model appears as a star (the dimension tables appear as points of a star surrounding the fact table)."
Figure 1 shows a stylized example of what a star looks like, and Figure 2 is an example of an actual star.
Figure 1. A diagram of the star schema
Figure 2. Example of an actual star schema
How the data transfer process works
In its simplest form, the Rational Portfolio Manager reporting method now uses a two-stage process to get data that the various reporting tools retrieve into the data warehouse. The first stage is called the Internal Staging area, and the second stage is the data warehouse itself. Figure 3 shows how these are related. Extract-Transform-Load (ETL) is the process that moves the data from one location to the next.
Figure 3. Data transfer structure
The Internal Staging area provides a temporary location for cleaning the data, as well as for creating a "normalized" view of the data. The process of creating the normalized view converts the Rational Portfolio Manager database from a proprietary format into a relational database format. As part of this process, data that cannot be converted is not transferred. In that case, the project manager receives notification that there are problems with the data. Until the issue with the non-transferable data is resolved, the data is not transferred to the data warehouse.
The second stage in the data transfer process is the data warehouse itself. This is where you run the reports from after the data transfer. It contains views that allow secure access to the Rational Portfolio Manager data and these two tables:
- Dimension table: This table contains data types that are likely to remain the same or vary only slowly, over time. These tables are what a report would elaborate on, either by association with facts or by enumeration. These tables contain data, such as "Project name."
- Facts table: This table contains data for an event that occurs at a given date and time, that has measurable quantities associated with it, and that is associated with one or more dimensional values (dimension tables, for example). This table contains data such as project "actuals." The Facts table is the center of the star, and the dimension table is associated with the Facts table. The data warehouse in the standard configuration is part of the Rational Portfolio Manager database instance, which means that it is either an IBM DB2 or an Oracle database. Understanding the structure of the data warehouse helps when you are writing ad hoc reports.
Scheduling automatic data updates
One of the problems with reporting on data that is not in the operational database is how to keep it up to date. Rational Portfolio Manager uses the database tool set's scheduler, which automatically triggers the extraction of the data into the Internal Staging area. This Extract-Transform-Load (ETL) process is triggered according to the scheduled defined by the administrator.
This scheduled update can be set up on a daily (or more often), weekly, or other preset schedule. When this ETL process is triggered, it does two things:
- Clears the staging area of the previous data collection
- Then transfers a copy of the data from Rational Portfolio Manager to the Internal Staging area
If this is the first time that you are transferring data, it will transfer all of the data. Thereafter, it transfers only the delta data (changed project or new projects) since the previous collection. After the data has been transferred to the Internal Staging area, the process of cleaning and normalizing the data starts. During this process, the software also checks for corrupted data. When this is finished, the ETL process transfers the data to the data warehouse.
Two options for ensuring security of the data
After the data is in the warehouse, another common problem that needs to be resolved is how to keep this data secure. Rational Portfolio Manager can use either of two methods of authentication: Lightweight Directory Access Protocol (LDAP) or database authentication.
The first step in setting up the security parameters requires the database administrator (DBA) to create a database user ID for each user who will have access to the data warehouse. This must be done regardless of whether you use LDAP or database authentication. As part of the security setup, a cross-reference table validates data access, based on a user's Rational Portfolio Manager project assignments. Each database user is automatically added to this table.
The next step is to determine what security rights each user has, because there are two different models:
- Model 1: Users can view data only if they have been granted access privileges, based on their project assignments in Rational Portfolio Manager.
- This is the most desirable model, because security is controlled through the views
- Users can build SQL statements against the database views.
- Users must exist in Rational Portfolio Manager and must be listed in the user cross-reference table.
- Only row-level security is available, not column-level security.
- The DBA needs to set these security rights:
- Deny read-only rights to all tables.
- Grant read-only rights to all views.
- Model 2: Users can view all data in the warehouse.
- Users can build SQL statements directly against the Facts and Dimension tables.
- Users can build views that limit the columns returned (you can then use these views in Model 1).
- No security is applied to the tables.
- This model requires a database user ID, but it does not require the user to also be a Rational Portfolio Manager user.
- This kind of access should be granted only to very privileged database users.
- The DBA needs to grant these security rights:
- Read-only rights to all tables
- Read-only rights to all views
There are additional considerations for using LDAP authentication:
- You must use LDAP authentication when the Rational Portfolio Manager middleware is also using LDAP authentication.
- If the LDAP user name is not a valid database user name (for example, if it contains non-alphanumeric characters, such as periods, ampersands, @ signs, and so forth), then the alternate ID attributes (name and password) must be set. This set of attributes is located in the individual resource record in Rational Portfolio Manager (see Figure 4).
Figure 4. Identification portlet showing the attributes for the data warehouse
Two ways to access the data in the data warehouse
After you have created the data warehouse, populated it, and determined access privileges, you next decision is what tool or tools to use to retrieve the data from the warehouse.
Consistent with the wide variety of options available with this data warehouse, there are two methods for accessing the data, and each has its advantages:
- Use third-party or in-house reporting tools
- You can connect to the data warehouse by using your preferred reporting tool.
- You can create your own reports.
- You can view the reports from an internal Web site or from a URL portal on the My Portal page in Rational Portfolio Manager.
- Use the Common Reporting tool included with Rational Portfolio Manager
- You must deploy the Web application for the Common Reporting tool and BIRT.
- You can run reports on all of your projects from the My Portal view.
- You can run reports on individual projects by using the Portfolio Viewer in the Work Management view.
Using third-party reporting tools
Let's look at the first option. The data warehouse supports three different tool sets, each of which has its own advantages (this is a representative example, not a complete listing):
- Reporting tools: BIRT (Eclipse), Crystal Reports, Actuate, and others
- Business intelligence tools: ALPHABLOX, Cognos, and so on
- Tools that use ODBC data sources: IBM Lotus 1-2-3, Microsoft Excel, Microsoft Word, and so forth
Using the built-in Common Reporting tool
For this second option, Rational Portfolio Manager is delivered with the open source report runtime program that is part of the Eclipse project, which is called Business Intelligence Reporting Tools, otherwise known as BIRT. This is an excellent option if you do not already have a reporting tool or if you want to take advantage of using Rational Portfolio Manager to determine what data is visible. The Common Reporting tool includes these three elements:
- A repository to store reports
- A generic interface to multiple third-party reporting tools (using URLs)
- A report administration interface
If you use this method and want to develop your own reports, you will need to download the designer from the Eclipse site (see the Download section in Resources).
The first thing that you will notice in this tool is an icon on the Portfolio Dashboard (Figure 5) that was introduced in Rational Portfolio Manager V7.1.
Figure 5. Data warehouse link on the Portfolio Dashboard
This is where you access all of the reports that have been registered in the Common Reporting repository. If you've registered any third-party tools and reports in this repository, they will show up here also (Figure 6).
Figure 6. Listing of reports for the data warehouse
The list of reports reflects the security role, as well as the report security that has been applied. If you are running the Portfolio Dashboard from the My Portal view, the generated report will reflect the projects and proposals that are in your portfolio, based on whatever filters have been applied (see Figure 7).
Figure 7. Filtering on the portfolio
You can also run these reports from the Work Management view. This works the same way that it does with the OLAP pivots or the proprietary reporting method, meaning that what is selected (the folder or subset of projects or proposals) defines the set of "projects" that is evaluated for the report. The sample reports included are formatted to resemble the reports from the previous proprietary reporting method (see Figure 8).
Figure 8. Generated report
With the BIRT Report Viewer, you can enable the Table of Contents (TOC) for easier access to the data. Just click the TOC icon located in the upper-left corner (Figure 9), which is hyperlinked to the data in the report.
Figure 9. Generated report with the TOC enabled
Storing your reports
Now that you've seen how to generate reports from the data warehouse by using Rational Portfolio Manager, it's time to think about getting your reports into the repository. The Common Reporting tool provides a Web interface that is separate from Rational Portfolio Manager (see Figure 10).
Figure 10. Common Reporting tool Web interface
This interface is where you manage your reports. There are three hyperlinks in the Administration section for using the functions of this interface, each of which is described here:
- Reporting system management
- Folder management
- Report management
Reporting system management
Let's start with the Reporting System Management hyperlink. Clicking on the link displays a new page (Figure 11) that shows all of the reporting systems that have been added to the Common Reporting tool. The BIRT V2.1.2 Engine and Viewer are predefined for you. You can register any reporting system that works through a Web URL. You can move to other screens by clicking on the links at the bottom of this page.
Figure 11. Reporting System Management screen
Figure 12 is an example of a third-party reporting system definition. The Unique Name and Description show up in the Reporting System Management screen (Figure 11). The Reporting Server URL is the location used to store the third-party reporting system's URL.
Figure 12. Reporting system definition
After defining the reporting systems, you will need to set up the folder structure. To access this page, select Folder Management. This allows you to categorize the reports into sections for ease of use. This structure is reflected in the data warehouse drop-down menu (see Figure 13) in Rational Portfolio Manager.
Figure 13. Folder structure for reports
You can put these folders at the root level or nest them within other folders (Figure 14). You can also apply security settings at any level. With the Common Reporting tool, 31 additional security levels have been defined in addition to the pivot security levels that existed previously. These new security levels are defined at the role level in Rational Portfolio Manager.
Figure 14. Folder Definition screen
You can select the final settings in the Report Management screen (Figure 15), where you can do any of these tasks:
- Add a report
- Modify an existing report
- Delete an existing report
- Launch (run) the report
Figure 15. Report Management screen
Your new scenario
With the V7.1 release of Rational Portfolio Manager, report writing and generation have become significantly easier, partly because you can now use it with other powerful reporting tools, such as Crystal Reports or ALPHABLOX. Through these reporting tools, you can join disparate data to expand your data mining process. This enables you to discover trends directly from the data without having to resort to a manual process. Because this solution resides on a separate database, the process of generating ad hoc reports is not affected by the performance of the operational database, which improves efficiency.
Your weekend is no longer at risk. You've sent the report to your boss with the exact information that he requested from the specific projects in your portfolio. This information included financial estimates, as well as resource allocations and demands. You did not have to manually gather and evaluate this data before creating a customized report. Given that all of the data is already in your data warehouse, you can use the reporting tool or tools that you're most comfortable with to create your ad hoc report. However, now that you have it, you might want to ask your database administrator to include it in the Common Reporting interface so that your boss can run it himself the next time he needs it in a hurry.
- Read The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling, Second Edition (2002), by Ralph Kimball and Margy Ross. New York, NY: John Wiley and Sons, Inc.
- Visit the Rational Portfolio Manager area on developerWorks for project manager content and resources.
- Visit the Rational software area on developerWorks for technical resources and best practices for Rational Software Delivery Platform products.
- Browse the technology bookstore for books on these and other technical topics.
Get products and technologies
- Download the Business Intelligence Reporting Tool (BIRT) from the Eclipse site.
- Download trial versions of IBM Rational software.
- Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Tivoli®, and WebSphere®.
- Participate in the Rational Portfolio Manager forum on developerWorks to post your questions and comments and to share your thoughts, ideas, and solutions with others who use this software.
- Check out developerWorks blogs and get involved in the developerWorks community.