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.
ccppmod155
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 PPM
 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.
 
 
2
 
2
 
 
The following image shows the data flow from the data warehouse to an external BI tool using HDP OData:
 The 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 PPM
 credentials, 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:
 
  1. To access the data warehouse using OData, verify that you are using the SaaS edition of CA PPM 15.4 or higher.
  2. Log in to 
    Clarity PPM
    .
  3. Click 
    Administration
    Organization and Access
    Resources
    .
    1. Grant the 
      Data Warehouse OData Service - Navigate 
      access 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.
    2. Grant 
      OData - Access
       to access PPM OData and act on behalf of other users as a trusted connection.
  4. Click 
    Administration
    General Settings
    Data Warehouse OData Service
    .
    The following information appears:
    •  
      Data Warehouse OData v2 EndPoint URL
      Defines the OData URL for the 
      Clarity PPM
       15.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 URL
      Defines the OData URL for the 
      Clarity PPM
       15.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 Authenticator
      Defines the qualifier to use when accessing the OData endpoints with 
      Clarity PPM
       user credentials.
  5. 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 PPM
 credentials, reports users can access the OData service.
 
Follow these steps:
 
  1. As a 
    Clarity PPM
     administrator, grant report users access to the OData endpoints as follows:
    1. For existing users, assign the 
      Data Warehouse OData Service - Navigate
       access right.
    2. For new report users, create the users as resources in 
      Clarity PPM
       and assign the 
      Data Warehouse OData Service - Navigate
       access right.
  2. As a user with the necessary rights, access the OData service using your 
    Clarity PPM
     credentials 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 the 
      Clarity PPM
       Username is 
      PPMODataUser
       and the OData Authenticator is 
      PPMOData
      , then enter the following username: 
      PPMODataUser|PPMOData
      .
    • Enter the 
      Clarity PPM
       password.
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:
  1. Create specific report users in 
    Clarity 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.
  2. Set up a password for these users in 
    Clarity PPM
     so 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 PPM
 Studio. Also, enable the attributes for the Data Warehouse by selecting the 
Include in the Data Warehouse
 checkbox. See 
Enable Custom Objects and Attributes for the Data Warehouse
 in 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 Reference
 for additional details about the jobs.
  1.  
    Load Data Warehouse
    : Run this job immediately after configuring 
    Clarity PPM
     for OData. The job copies the data from the 
    Clarity PPM
     tables and views to the data warehouse schema.
  2.  
    Load Data Warehouse Access Rights
    : The job extracts the access rights for investments and resources from the 
    Clarity PPM
     database 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.
  3.  
    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 configuring 
    Clarity PPM
     for 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.
  1. Open a web browser.
  2. Depending on your OData version (v2 or v4), copy and paste the OData End-Point URL that you noted from 
    Clarity PPM
    .
  3. 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:
 
  1. From the Power BI main menu, select 
    Get Data
    OData Feed
    .
  2. In the 
    OData Feed
     window, enter the Data Warehouse OData End-Point URL (v2 or v4) that you noted from 
    Clarity PPM
    .
  3. Click 
    OK
    .
  4. Select 
    Basic
    .
  5. Enter the username and password that you noted from 
    Clarity PPM
     for accessing the OData service.
  6. Click 
    Connect
    .
    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
You can also perform a simple test of the OData connection 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 
Clarity PPM
 users.
 
Follow these steps:
 
  1. Open an Excel workbook. From the main menu, select 
    Data
    Get External Data
    From Other Sources
    From OData Data Feed
    .
  2. In the 
    Connect to a Data Feed
     window, enter the following information:
    • The Location of the data feed or the Data Warehouse OData End-Point URL that you noted from 
      Clarity PPM
      .
    • The user name for accessing the OData service.
    • The password for accessing the OData service.
  3. Click 
    Next
    .
  4. In the 
    Data Connection Wizard
    , select the data warehouse tables and save the connection.
You can also connect to an OData feed using the Power Pivot add-in. Refer to the Excel documentation for details.
Connect to OData Using Tableau Desktop
When connecting to OData using Tableau, you must specify individual Odata entities (tables).
  1. Under 
    Connect
    To a Server
    , select 
    OData
     as the data source.
  2. In the OData window, in the Server field, enter the Data Warehouse OData End-Point URL (v2 or v4) that you noted from 
    Clarity PPM
    . Use the following format:
    http://<host>:<port>/api/odata/datasource/
    <entity>
    Where 
    <entity>
     specifies the name of the data warehouse table.
  3. Select 
    use a specific username and password
    . Specify the username and password for accessing the OData service.
  4. Click 
    Sign in
    .
  5. 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
 
Clarity PPM
 
. 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 PPM
 users 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 Warehouse
 option is not selected and is read-only for the security attribute.
Configure Row-Level Security in CA PPM
Complete the following steps in 
Clarity PPM
 as an administrator:
 
  1. Configure the resource views to add the BI User ID attribute. See 
    CA PPM Studio Objects and Attributes
     for details.
  2. Populate the BI User ID attribute value for all report users with their BI security login value. For example, map the 
    Clarity PPM
     user (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:
 
  1. 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 in 
      Clarity 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 in 
      Clarity PPM
      .
  2. Create relationships between the tables and views.
  3. 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()
  4. 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)
OData v2 does not support any functions. Even the data warehouse functions are not supported by v2.