Clarity PPM OData

With the release of CA PPM 15.3, SaaS customers can access the out-of-the-box Data Warehouse via OData. The following will take through the process of connecting Tableau to the PPM Data Warehouse. Along the way, we’ll build a sample resource allocation dashboard, look at best practices, and several limitations of the OData solution.

What is OData

OData or Open Data Protocol, is an open REST-based data access protocol initiated by Microsoft in 2007, and most recently standardized at OASIS asVersion 4. OData, as implemented by CA, exposes information stored within the CA PPM’s data warehouse to reporting and analytic platforms such as Tableau, PowerBI, Microstrategy, and other similar tools.

Getting Started

OData is not turned on by default within your SaaS environment, a request to CA Technical Support is necessary to get the ball rolling. Upon submission of a request to turn on OData, CA will respond with its own request to acknowledge the following:

• ‍OData Connector does not utilize, by default, the stock PPM user/group/OBS based security model. To implement security your BI report developer will be able to restrict the data, seen by report consumer, by utilizing the DWH security tables. More information on leveraging the PPM security model for reports utilizing OData Connector as a data source will be provided.
• Note that it will require a minimum of one business week lead time to deploy OData Connector in your environment. During the scheduled date and time, the OData Integration operation will require approximately 4 hours downtime for the target PPM environment.
• ‍OData Connector can only be turned on in batches, for non-production environments, every Thursday, and for production environment on every Sunday.

Initial Set-Up

Upon acknowledgment of OData’s security limitations, the request was submitted and fulfilled as advertised. Setting up the OData service is a breeze if you already have the data warehouse jobs running and you have full administrative access rights.

1. Ensure thatyourPPM user hasData Warehouse OData Service - Navigateglobal access right.
2. Log off and back in to PPM after adding the global access right to be able to view the menu item.
3. Navigate to Administration > General Settings > Data Warehouse OData Service.
4. Changeyouruser password in the Administration section of PPM, you’ll need this password in the next section.

Refer to the CA Documentation Site if you require help setting up the data warehouse.

Getting Started

When you navigate toAdministration > General Settings > Data Warehouse OData Serviceyou’ll notice two end-point listed, v2 and v4. There are many practical differences between these two protocol versions, the notable one is the underlying file format, v2 uses XML, while v4 uses JSON. For that reason alone, I recommend you use v4; JSON is typically much faster due to its smaller file size.

Testing the Connection

1. Open a browser of your choice, although I suggest the use of Chrome
2. ‍Navigate to the Data Warehouse OData v4 Endpoint URL as specified in your environment
3. ‍You will see a Username and Password prompt box
4. ‍Username: yourppmusername|ODataAuthenticatorID
5. Password:enter the same password referenced in Step 4 of the previous section
6. ‍Click the Log-Inbutton

If successful, you will see a list of CA PPM data warehouse tables represented as JSON. If you’re having a hard time reading the outputted JSON, you may pretty format the content by installing theJSON Formatter Chrome Extension, it makes reading JSON much, much easier.

If unsuccessful, you may see an error which looks like this:

222206007
Invalid user ID or password.

Verify your username/password and access rights. If you’re unable to resolve the error, reach out to CA Technical Support.

Example Tableau Dashboard

Now that we’ve successfully set-up OData in our PPM environment, we’ll use Tableau to build a simple dashboard to show Role Allocation Cost by Investment Goals.

1. Open Tableau Desktop, on the left hand side selectODataunder“Connect > To a Server”heading.
2. You’ll see an OData connection prompt window
3. In the Server input box, type in the URL of the OData v4 endpoints followed by “/DWHINVINVESTMENT”
4. Use the same Username and Password you used while testing the connection.
5. Click on the Sign-In button
6. Tableau will present a list of available fields based on provided OData endpoint
7. Click on Sheet 1
8. Tableau is now using the OData protocol to communicate with the CA PPM data warehouse. Depending on the amount of data stored in your PPM instance, this may take a few seconds, minutes or hours

Try theTableau OData connection guideif you run into issues.

Within Tableau, you should now see a list of investment Dimensions and Measures on the left-hand side of Sheet 1. Unfortunately, this isn’t all the data we need, we also need to pull in team facts and resource information. To do so, click on theDatamenu link found in the main top-navigation bar, and selectNew Data Sourceand add the following end-points: DWHINVTEAMSUMMARYFACTS and DWHRESRESOURCE.

Joining Data

Creating relationships, or blending data in Tableau is a relatively easy, though a tedious task. From theDatamenu link found in the main top-navigation bar, selectEdit Relationships…Join the Resources end-point to Teams via the Resource Key and join Teams and Investments via the Investment Key.

Once you bring in the Hard Allocation Cost measure, and Role dimension, your visualization should look something like this. Of course, you may need to add filters to get rid of any noise you may have in your data.

OData Limitations

Lack of SQL Support

OData is NOT a separate database or data warehouse. You cannot useSQLto directly query, join, and filter information from the OData stream. Each OData end-point is a reference to a single data warehouse table. To build meaningful reports, multiple connections are needed to join data. Blending data into tools like Tableau is possible (with limitations), although not as easy as using SQL joins.

Latency or the Lack of Fresh Data

CA decided to implement OData on top of the existing data warehouse, therefore, it is impossible to query “real-time” information from your PPM instance. The freshness of data presented to your stakeholders will be throttled by two processes:

1. The time needed to successfully run the existing data warehouse extraction jobs within PPM. In my experience, these jobs take between 30 minutes to 1 hour.
2. The time needed for Tableau to run a full extract of all the tables (OData endpoints). This process is totally dependent on the number of OData end-points within a report. In the example showcased in this article, Tableau took about 10 minutes to extract information from the Investments, Resources, and Teams end-points. This duration will be drastically longer if we were to pull in “sliced” time-period values.

Final Thoughts

For CA PPM customers who have struggled with reporting due to previous Jaspersoft limitation, OData is a great addition to the PPM ecosystem. For customers who crave real-time access to their data, this implementation of OData falls short of the mark.