Configure OData Access to the Data Warehouse (On-Demand Only)

ccppmop158
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 a 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
Clarity PPM
users.
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
  1. 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.
  2. To populate BI User ID to DWH_INV_SECURITY_V and DWH_RES_SECURITY_V, run the Load Data Warehouse Access Rights job.
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)
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.