Configure OData Access to the Data Warehouse
SaaS customers can access the data warehouse and develop analytics dashboards and reports using REST (OData) endpoints. A Hybrid Data Pipeline (HDP) server in the cappm environment connects to the data warehouse and exposes OData endpoints for data warehouse entities. As a report developer or business user, you can use a business intelligence tool (for example, Microsoft Power BI or Tableau) to access the OData endpoints. Using the OData endpoints as data sources, you can create your own dashboards and reports.
SaaS customers can access the data warehouse and develop analytics dashboards and reports using REST (OData) endpoints. A Hybrid Data Pipeline (HDP) server in the
Clarity PPMenvironment connects to the data warehouse and exposes OData endpoints for data warehouse entities. As a report developer or business user, you can use a business intelligence tool (for example, Microsoft Power BI or Tableau) to access the OData endpoints. Using the OData endpoints as data sources, you can create your own dashboards and reports.
The following image shows the data flow from the data warehouse to an external BI tool using HDP OData:
Provide Access to the Data Warehouse OData Service
As a system administrator, open the OData Service page to view the OData v2 or v4 endpoint URLs. Provide the URL and OData authentication string to your report users. Using their
Clarity PPMcredentials, report users can configure their BI tool to connect to the OData service.
The following tip applies only if you were using the single-user OData service account available in CA PPM 15.3. After an upgrade to 15.4 or higher, for any reports designed earlier, update the connection settings to now use the new CA PPM user credentials. In 15.4 and newer releases, OData Endpoints are no longer accessible using the single user OData service account.
Follow these steps:
- To access the data warehouse using OData, verify that you are using the SaaS edition of CA PPM 15.4 or higher.
- Log in toClarity PPM.
- ClickAdministration,Organization and Access,Resources.
- Grant theData Warehouse OData Service - Navigateaccess right to the administrators responsible for managing access to the OData connection. With this right, administrators can open the Data Warehouse OData Service page in the next step.
- GrantOData - Accessto access PPM OData and act on behalf of other users as a trusted connection.
- ClickAdministration,General Settings,Data Warehouse OData Service.The following information appears:
- Data Warehouse OData v2 EndPoint URLDefines the OData URL for theClarity PPM15.4 or newer instance which exposes the data warehouse to the customer. Use this URL for external authentication with OData v2.Example: http://<host:port>/api/odata/ppm_hdp_datasource_v2
- Data Warehouse OData v4 EndPoint URLDefines the OData URL for theClarity PPM15.4 or newer instance which exposes the data warehouse to the customer. Use this URL for external authentication with OData v4.Example: http://<host:port>/api/odata4/ppm_hdp_datasource_v4
- OData AuthenticatorDefines the qualifier to use when accessing the OData endpoints withClarity PPMuser credentials.
- Share the OData URL (v2 or v4) and the authentication string with the report developers and users so they can access the OData endpoints.
Access OData Endpoints Using CA PPM Credentials
With the necessary access rights and their
Clarity PPMcredentials, reports users can access the OData service.
Follow these steps:
- As aClarity PPMadministrator, grant report users access to the OData endpoints as follows:
- For existing users, assign theData Warehouse OData Service - Navigateaccess right.
- For new report users, create the users as resources inClarity PPMand assign theData Warehouse OData Service - Navigateaccess right.
- As a user with the necessary rights, access the OData service using yourClarity PPMcredentials as follows:
- Enter the user name suffixed with a pipe symbol ( | ) followed by the OData Authenticator as follows:<CA PPM_USER>|<ODATA_AUTHENTICATOR>.For example, if theClarity PPMUsername isPPMODataUserand the OData Authenticator isPPMOData, then enter the following username:PPMODataUser|PPMOData.
- Enter theClarity PPMpassword.
Users cannot access the data warehouse OData endpoints using their CA On Demand Portal login credentials or domain credentials in a federated SSO environment. To allow access to the Data Warehouse OData Endpoint URL in these environments, complete the following steps:
- Create specific report users inClarity PPM.You can re-use existing product users provided they do not have the External Authentication option checked in the resource properties under Administration. If the option is checked, create new report users.
- Set up a password for these users inClarity PPMso that they can use it for accessing the Data Warehouse OData Endpoints.
Enable Custom Content for the Data Warehouse
Any data that you want available in the Data Warehouse, you must first add them in the product. Also, you must specifically enable the data for the Data Warehouse. For example, to view your custom project attributes in reports, add the attributes to the Project object in
Clarity PPMStudio. Also, enable the attributes for the Data Warehouse by selecting the
Include in the Data Warehousecheckbox. See
Enable Custom Objects and Attributes for the Data Warehousein the parent topic above (
Configure the Data Warehouse and Advanced Reporting Domains).
Refresh the OData Endpoints
As new tables and columns are added in the data warehouse, the OData endpoints can get outdated. Run or schedule the following jobs in the listed order to update the OData endpoints with latest changes from the data warehouse. This procedure ensures that your reports reflect the most current data available. See
Jobs Referencefor additional details about the jobs.
- Load Data Warehouse: Run this job immediately after configuringClarity PPMfor OData. The job copies the data from theClarity PPMtables and views to the data warehouse schema.
- Load Data Warehouse Access Rights: The job extracts the access rights for investments and resources from theClarity PPMdatabase and loads them into the data warehouse. Run the job anytime the user access rights are changed. Also run this job after running the Load Data Warehouse job using the full load option.
- Refresh Data Warehouse OData Model: Run this job only when there is a change to the data warehouse schema such as a new object or attributes. The job creates the OData endpoints for both v2 and v4 based on the data warehouse schema. The OData connector can use the endpoints for report building. Run the Refresh job at least once after configuringClarity PPMfor OData.
Read-only user access to the updated data warehouse can vary due to the sequence of required load and refresh jobs. The full sequence of updates can occur in a few minutes or up to two hours. For example, any new custom objects are loaded in Step 1 when you run the Load Data Warehouse job. Completely automated for you, a separate Refresh Grant database job creates and updates permissions and synonyms for these new database objects, now in the data warehouse. The automated Refresh Grant database job is scheduled to run every 2 hours. Your new custom objects are not visible as OData endpoints for reporting tools until the Refresh Grant job has completed and you have run the Refresh Data Warehouse OData Model job again.
Verify the OData Connection
Complete the following steps to verify that you are connected to the OData service.
- Open a web browser.
- Depending on your OData version (v2 or v4), copy and paste the OData End-Point URL that you noted fromClarity PPM.
- Enter the username and password for accessing the OData service and try to connect.
- If the connection is successful, you see an XML formatted OData output.
- If you do not see this output, contact CA Support.
Connect Your BI Tool to the Data Warehouse OData Service
As a report developer, connect your existing BI tool to the OData data source. The OData objects are exposed and you can start building reports for your end users.
The procedures in this section are only recommendations. Refer to the documentation for your specific BI tool for configuration instructions on the following tasks:
- Connect and build reports using OData feed.
- Retrieve only sample data when establishing an OData connection.
- Retrieve limited data when building reports to improve performance.
Work with your BI tool administrator to resolve any connection issues that you may encounter.
CA Support Tip: If we see the following error in the logs, it indicates a customer attempted to retrieve too many records at once:
ForbiddenException: The top request exceeds the maximum entities per page limit of 10000
For example, in this example, a request to retrieve 50,000 rows failed:
"GET /api/odata/test_DataSource/DWH?$top=50000 HTTP/1.1"
The ODATA service limit is 10,000 entities per page. With SSIS, if an implementation doesn't pass the $top parameter, the same error may occur. So, it is best to use pagination to remain within the 10,000-record limit. CA Support observed this issue with SSIS; however, the 10,000-entity pagination limit also applies when attempting to automate with other BI tools.
Connect to OData Using Microsoft Power BI Desktop
If you are currently using Power BI, use the following high-level steps to connect to OData.
Follow these steps:
- From the Power BI main menu, selectGet Data,OData Feed.
- In theOData Feedwindow, enter the Data Warehouse OData End-Point URL (v2 or v4) that you noted fromClarity PPM.
- Enter the username and password that you noted fromClarity PPMfor accessing the OData service.
- ClickConnect.On successful connection, you see a list of data warehouse tables that you can use to build your reports.
Connect to OData Using Microsoft Excel
Clarity PPM exposes OData and you can use third-party tools like Microsoft Excel to consume OData. However, Clarity PPM does not officially support any OData consumption with any third-party tool. You can use the information provided in this knowledge base article to connect to OData using Microsoft Excel.
We do not recommend Excel for reporting. For building reports, we recommend that you use a reporting tool such as Power BI. Power BI renders the data faster using caching and offers better data security and control. Excel provides no convenient way to map the report users to the
Connect to OData Using Tableau Desktop
When connecting to OData using Tableau, you must specify individual Odata entities (tables).
- UnderConnect,To a Server, selectODataas the data source.
- In the OData window, in the Server field, enter the Data Warehouse OData End-Point URL (v2 or v4) that you noted fromClarity PPM. Use the following format:http://<host>:<port>/api/odata/datasource/<entity>Where<entity>specifies the name of the data warehouse table.
- Selectuse a specific username and password. Specify the username and password for accessing the OData service.
- ClickSign in.
- Repeat the steps in this procedure to connect to additional data warehouse tables.
OData v4 is not supported by Tableau; it support only v2. Microsoft Power BI supports both.
Implement Row Level Security for BI Reports
Work with your information security organization to review the data access and storage information. Determine the compatibility of the information with your data security standards. HDP does not leverage the users, groups, or OBS security model available in. Your BI report developer can use the data warehouse security tables to restrict the data visible to the report users.
Before your users generate reports, implement row-level security to secure the data. Enable investment and resource row level security in the BI reports. Only the relevant resources and investments are visible to the end-users based on their access rights.
Example: User A has access to investment 1 and investment 2. User B has access to investment 1 and investment 4. When User A logs in to their reporting environment, the investment reports show only the rows for investments 1 and 2. User B sees only rows for investments 1 and 4.
To enable row level security for reports, use the BI User ID attribute to map the
Clarity PPMusers to the report users in your BI tool. The BI User ID attribute is included in the Resource object and installed by the PMO Accelerator. See Install the PMO Accelerator add-in. The attribute is not configured to appear in the resource views by default. Also, the attribute is enabled for the data warehouse by default. However, the
Include in Data Warehouseoption is not selected and is read-only for the security attribute.
Configure Row-Level Security in CA PPM
Complete the following steps in
Clarity PPMas an administrator:
- Configure the resource views to add the BI User ID attribute. SeeCA PPM Studio Objects and Attributesfor details.
- Populate the BI User ID attribute value for all report users with their BI security login value. For example, map theClarity PPMuser (User A) to the BI User ID value, [email protected]<company_name>.org.
Configure Row-Level Security in Your BI Tool
Report developers using any BI tool can use the Data Warehouse security to implement row-level security as described by their vendors. The following procedure provides high-level generic steps using Power BI as an example. Refer to your BI tool documentation for instructions about implementing row-level security in your BI tool. For example, see the Microsoft Power BI Documentation for instructions about implementing row-level security in Power BI to restrict access to reporting data.
Follow these steps:
- Load the following tables in the Power BI Desktop to populate the report data:
- DWH_INV_INVESTMENT: If you are reporting on investments, the table populates the investments data. This table is commonly used with the investment security. Depending on the use case, you can also use other tables containing the investment key.
- DWH_RES_RESOURCE: If you are reporting on resources, the table populates the resource data. This table is commonly used with the resource security. Depending on the use case, you can also use other tables containing the resource key.
- DWH_INV_SECURITY_V: The view populates the access rights information for investments. The view includes the BI_User_ID column which is the user ID of your BI tool and it reflects the value as populated inClarity PPM.
- DWH_RES_SECURITY_V: The view populates the access rights information for resources. The view includes the BI_User_ID column which is the user ID of your BI tool and it reflects the value as populated inClarity PPM.
- Create relationships between the tables and views.
- Define a where condition to restrict the data to a specific user.For example, in Power BI, navigate to Modeling, Manage Roles, Create Role, and create a DAX expression on the BI_User ID attribute as shown below:[BI_USER_ID] = USERNAME()
- Save and publish the report.
Supported PPM Data Warehouse Functions
The following data warehouse functions are supported in Odata v4:
DWH_CAL_CURRENT_DATE_FCT DWH_CAL_DATE_ADD_FCT DWH_CAL_DATEDIFF_FCT DWH_CAL_DATEFORMAT_FCT DWH_CAL_DATELABEL_FCT DWH_CAL_DIFF_IN_SECONDS_FCT DWH_CAL_END_OF_MONTH_FCT DWH_CAL_PERIOD_START_FCT DWH_CAL_STARTDATE_FCT DWH_CAL_TRUNC_DATE_FCT DWH_CHART_COLOR_MAP_FCT DWH_CONVERT_CURRENCY_FCT DWH_DAYS_LATE_PCT_FCT DWH_DISPLAY_MAPPING_FCT DWH_GET_RETAIN_PRECISION_FCT DWH_INV_REMAINING_ALLOC_FCT DWH_NULL_DATE_FCT DWH_NULL_NUMBER_FCT DWH_NULL_VARCHAR_FCT DWH_START_DAYS_LATE_FCT DWH_START_DAYS_LATE_PCT_FCT DWH_TO_CHAR_FCT
OData v4 functions are supported; however, the following exceptions are not supported:
- function imports
- functions that return primitive types
- functions that return complex types or entities
- functions that are bound to entities
- functions with OUT or INOUT parameters
- functions with parameter aliases
- functions invoked by the Odata $filter, $select, or $orderby options
- functions invoked by parameter values including URL query parameters
- built-in functions
- overloaded functions
- unbound functions (static operations)
RU views such as DWH_INV_TEAM_RU_PER_FACTS_M_V are not exposed as OData.
OData v2 does not support any functions. Even the data warehouse functions are not supported by v2.