Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only

OData - Requesting Data

By Edward Stuart posted Tue October 17, 2023 09:18 AM

  

The Planning Analytics REST API utilises OData standards, here we review the basic tutorials available on the OData website:

https://www.odata.org/getting-started/basic-tutorial/

BaseUrl:

The baseUrl is dependent upon the version/ location of your instance and will be used throughout this post, a v11 on premise baseUrl would look like:

http(s)//:<ServerName>:<HttpPortNumber>/api/v1

Making Requests for Data:

Requesting Entity Collections:

Making a request to an Entity type (https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=metadata-entity-types)

{BaseUrl}/Cubes for example

will return the following listing of data points for all cubes in the requested model:

  • "@odata.etag": "{Hash}",
  • "Name": Budget,
  • "Rules": null,
  • "DrillthroughRules": null,
  • "LastSchemaUpdate": "2023-10-04T15:36:21.090Z",
  • "LastDataUpdate": "2023-10-04T15:36:21.090Z",
  • "ViewStorageMaxMemory": null,
  • "ViewStorageMinTime": null,
  • "CalculationThresholdForStorage": null,
  • "CellSecurityDefaultValue": null,
  • "CellSecurityMostRestrictive": false,
  • "AllowPersistentHolds": false,
  • "Locked": false,
  • "Attributes":
    • "Caption": "Budget"

Requesting an Individual Entity by ID:

Making a request to an individual entity restricts the query to a targeted selection, in this example I am querying the first cube in my listing "Budget"

{BaseUrl}/Cubes('Budget')

This returns the details above for the single cube "Budget" 

Requesting an Individual Property:

Making a request to an individual property for an individual entity, for example I can call just the Name of the Cube or the Rules or the LastDataUpdate etc..

{BaseUrl}/Cubes('Budget')/Name

This returns the individual property for the single cube requested:

  • "@odata.context": "../$metadata#Cubes('Budget')/Name",
  • "value": "Budget"
If our query returns multiple levels of response we can nest the query to target selected properties, in our example set we have the Attributes item which splits into Caption:
{BaseUrl}/Cubes('Budget')/Attributes/Caption
  • "@odata.context": "../../$metadata#Cubes('Budget')/Attributes/Caption",
  • "value": "Budget"

Requesting an Individual Property Raw Value

We can request raw values by adding $value to the request:
{BaseUrl}/Cubes('Budget')/Name/$value
  • Budget

Filtering:

Filtering requests is enabled by using: $filter

We can apply filter expressions against all endpoints (Cubes, Dimensions, Processes etc..)

  • eq (Equals)
  • gt (Greater Than)
  • lt (Less Than)
  • ge (Greater Than or Equal to)
  • le (Less Than or Equal to)
  • ne (Not Equal)
  • endswith (Ends With)
  • startswith (Starts With)
  • substringof (Returns values with substring)
  • substring (Returns values with substring from defined point)
  • many more are available (indexof, round, floor, ceiling etc..)

{BaseUrl}/Cubes?$filter=Name eq 'Budget'

  • Returns details for the Budget cube (as seen above)

Alternatively, we can filter for all Cube Names that contains the word 'Budget', where Name is the Individual Property of each Cube

{BaseUrl}/Cubes?$filter=contains(Name, 'Budget')

The response returns two cubes in my example model:

  • Budget
  • ElementAttributes_Budget Version

This was a short introduction to filtering via OData, we will investigate orderby, top, skip, count, expand, select and search in future posts

1 comment
21 views

Permalink

https://community.ibm.com/community/user/blogs/edward-stuart/2023/10/15/odata-requests

Comments

George Tonkin
Wed October 18, 2023 05:44 AM

Thanks for sharing this Ed. A good foundation to getting started.