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.
ccppmod153
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.
Configure the Data Warehouse OData Service
As the system administrator, open the OData Service page to view the OData endpoint URL. Also, update the default assigned password that is created for the read-only report user. With the URL and credentials, a report user can configure their BI tool to connect to the OData service.
: The OData single user name and password login credentials are shared. To minimize security problems, apply caution when sharing the OData URL and login credentials with your report developers and users.
Follow these steps:
  1. To request access to the Data Warehouse using OData, log in to your CA Support account. Open a new case after your upgrade to
    Clarity PPM
    15.3 is complete.
  2. After OData access is granted, log in to
    Clarity PPM
    .
  3. Select
    Administration
    ,
    Organization and Access
    ,
    Resources
    .
  4. Grant the following access right to the administrators responsible for managing the password for the OData connection:
    Data Warehouse OData Service - Navigate
     allows access to the OData Service page.
  5. Select
    Administration
    ,
    General Settings
    ,
    Data warehouse OData Service
    .
    The following information appears:
    • Data Warehouse OData End-Point URL
      The read-only field defines the OData URL for the
      Clarity PPM
      instance which exposes the data warehouse to the customer. Share the URL with the read-only report user so they can access the OData endpoints and can develop reports.
    • User Name
      The read-only field defines the authentication login for accessing the OData service.
    • Password
      Set the authentication password for accessing the OData service.
    • Confirm Password
      Verify that the passwords match. 
  6. If you changed the password, click
    Save
    .
  7. Share the OData URL, user name, and password with the report developers so they can access the OData endpoints.
Work with your information security organization to review the following data access and storage information. Determine the compatibility of the information with your data security standards:
  • HDP accesses your data using a single shared account
    : Only HDP administrators and report developers need access to the account. You configure the account in your BI tool and it has access to all the data in the Data Warehouse. As a shared account, any data that is accessed using HDP is not easily traced back to a single named account through 
    Clarity PPM
    SaaS or HDP access logs.
  • 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.
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 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 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. With a web browser open, copy and paste the OData End-Point URL that you noted from
    Clarity PPM
    .
  2. Enter the username and password for accessing the OData service and try to connect. See Configure the Data Warehouse OData Service.
    • 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 for 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.
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 that you noted from 
    Clarity PPM
    . See Configure the Data Warehouse OData Service.
  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, and click Next:
    • 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.
      See Configure the Data Warehouse OData Service for details.
  3. In the Data Connection Wizard, select the data warehouse tables and save the connection.
You can also connect to OData feed using the Power Pivot add-in. Refer to the Excel documentation for details.
Connect 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 souce.
  2. In the OData window, in the Server field, enter the Data Warehouse OData End-Point URL 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 that you are connecting to.
  3. Select "use a specific username and password". Specify the username and password for accessing the OData service. See Configure the Data Warehouse OData Service for details.
  4. Click Sign in.
  5. Repeat the steps in this procedure to connect to additional data warehouse tables.
Implement Row Level Security for BI Reports
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 
Clarity 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.