By Sander Bots
Recently CA extended their SaaS offering of CA PPM with an OData connection. This enables users to connect a CA PPM Datawarehouse to your Business Intelligence tool of choice. You are no longer restricted to using the BI tool Jaspersoft. I will briefly explain how to setup and use the OData connection and how you can setup a simple report. For more Business Intelligence advice around PPM solutions you can contact me directly.
My tool of choice for the examples below is Microsoft PowerBI. Keep in mind that any BI Tool that is enabled to read an OData feed can be used.
Let’s start with the basics. What is needed to make use of the OData feed?
- to be on CA PPM 15.3
- to open a support ticket to request OData access
- to setup your data warehouse
- to grant access to the report developer to the make use of the OData service
I won’t dive into the details of these requirements. If you are interested in more details, please drop me a note. Keep in mind that only once they are met you can start using OData.
Setting up the connection
- After the initial setup, you can open the Data Warehouse OData Service in the General Settings (Admin) of CA PPM. From there you can see the OData Endpoint URL and OData Authenticator, which are needed to setup the connection in Power BI Desktop.
Recommendation: use the v4 (JSON) Endpoint URL which is faster than the v2 version (XML).
- Next step is to open Power BI Desktop and choose OData in the Get Data options. In the pop-up which automatically appears you should paste the Endpoint URL.
- Enter your login details to finalize the connection. Select Basic and provide your CA PPM User name followed by | and the OData Authenticator retrieved earlier (e.g. username|odataauthenticator).
- Finish with your CA PPM password and connect.
Gather data and select tables
Once you have full access to the CA PPM Datawarehouse, you can start gathering the data you need in your reports and build the data model.
Recommendation: Only select tables from the database you need to report on. This will significantly increase the speed of running a report and you don't have to insert steps to filter out unused data.
In this example I will create a report showing a monthly resource information aggregated by primary role. Therefore, I use the following tables:
When using Power BI Desktop, the tables are loaded in to Power Query Editor. From here you can do whatever is necessary to transform your data into relevant information.
Start with selecting the required columns. Use the Choose Columns command. Make sure the column headers are understandable and that data types are setup correctly. Example: make sure that the type duration has the type duration assigned to it, otherwise PowerBI will not recognize it as such. It will be 'just a number’. Obviously, you can do lots more. If you are interested in these options, please drop me a note.
To make sure the tables are linked to each other, I need to setup the relationships. This is simply done by dragging and dropping attributes from one table to the other in the Manage Relationships screen.
Build a report
Microsoft Power BI comes with a lot of out-of-the-box visualizations which can be used to build reports. Select the visualization of choice and drag it to the report area. In this report the Line and stacked column chart will be used to visualize the availability (line) vs. the allocated hours by primary role. Setup the visual by linking it to the data. This is simply done by dragging and dropping the available fields.
To make sure the relevant data is shown add some Slicers (filters). In this case a slice based on the Primary Role and Period Start data have been added. These will automatically be linked to the visualization(s) used.
Finalize and share your report with others by publishing it. Other users can now access it in the online version of Power BI which is part of Office 365. From here you can also setup a Schedule Refresh function, which allows you to automatically refresh the data and Share your report with others.
The ability to access data from the CA PPM Data Warehouse via an OData connection significantly improves the reporting capabilities for SaaS users. Some key advantages:
- Use your CA PPM data in your BI Tool of choice.
- Use Microsoft Excel to import OData feeds and do extensive data analysis.
- Create trend analysis reports by using the recently added trend data (CA PPM 15.4).
Having an OData feed available is an easy way to access your CA PPM Data. Here are some things you need to be aware of.
- The data retrieved comes from the Data Warehouse. Therefore, it’s not real-time data.
- Retrieving data might be time-consuming. Especially larger datasets will be slower.
- Configuring the Data Warehouse in CA PPM requires specialized knowledge about the data model.
- OData doesn’t take the access rights into account. Be aware of which data is shared to whom.
Drop me a note if you need some advice about these topics.
The addition of OData to CA PPM for SaaS customers is very powerful. It takes away a lot of the limitations of Jaspersoft and its options are next to limitless. If you need the latest data make sure to get yourself a cup of coffee once you hit the refresh button.