Importing Absences from REST API into Jira Cube

Questions & Answers
, , ,
1

Hi,

I recently succeeded (with quite some help from eazybi support, thx for this) to import absences into eazybi jira cube through the rest interface of our absence management tool and wanted to share some insights that might save you some pain in case you try to do anything similar. You can see this as an extension to this help: Import from REST API.

Some general insights for mapping into the jira cube:

  1. most likely you will map from the user name in the rest result to Logged by.Users, you must select “Name column” as advanced option or nothing will be mapped (and no error will be shown, it will just not work, no idea why)
    image
  2. there may be differences in the name from both systems, use “skip missing” to avoid import errors for users that do exist in the rest result but do not exist in eazybi, best case synchronize names between both systems
  3. additional url parameters seem to be very mysterious, one would expect them to be added to the url used for the rest request (and that would be a nice feature) but this does not happen and the effect they have is unclear to me still
  4. if you want to assign a list of things to each user from the rest results (so there are multiple items per one user and the number of items differs per user) then your options are quite limited:
  • import the list as a (e.g. comma separated) string and parse that in your measures (using ExtractString)
  • if the list items are associated with strictly different dates/times then you are in luck and can map the date/time to the time dimension, this is the case for absences (at some days the user is in the office (not absent) at other days they are partially ]0…1[ or fully absent (1).
  1. you can reshape the result from your rest request using js in the Custom JavaScript code panel in the import config
  2. The docu is a bit vague here but it is possible to completely reshape the whole result returned from rest, not just individual array entries, the data is available using “doc.x” where x is the highest level name of the data structure, see example below.
  3. Be aware that some older version of ECMA is used by eazybi, more modern js syntax (which will work in the jsplayground where you typically test your js code) might fail for this reason when used in eazybi, specifically using “{…x}” syntax failed.

Now for my specific example, the rest result I get looks like this:

{
  "outs": [
    {
      "endDate": "01-05-2024",
      "requestId": 0,
      "employeeName": "Mr.X",
      "startDate": "22-04-2024",
    },
    {
      "endDate": "01-08-2024",
      "requestId": 1,
      "employeeName": "Mr.Y",
      "startDate": "01-07-2024",
    },
    {
      "endDate": "01-10-2024",
      "requestId": 2,
      "employeeName": "Mr.X",
      "startDate": "01-09-2024",
    },
	...
	]
};

The outs array can be accessed in the js via doc.outs[i].
Using some js I reshape this to split every request into one request for every day of holidays requested:

function pad(n) {
  return `0${n}`.slice(-2);
}

//-------------------------------------------

const result = [];

for (var k in doc.outs) {
  let start = new Date(doc.outs[k].startDate);
  let end = new Date(doc.outs[k].endDate);
  
  while (start <= end) {
    if (start.getDay() !== 0 && start.getDay() !== 6) { // exclude weekends
      let item = {};
      item['employeeDisplayName']=doc.outs[k].employeeDisplayName;
      item['date']=`${start.getFullYear()}-${pad(start.getMonth() + 1)}-${pad(start.getDate())}`;
      item['day']= 1.0;
      item['requestid']=doc.outs[k].requestId;

      result.push(item);
    }
    start.setDate(start.getDate() + 1);
  }
}

return result;

So the mapping looks like this:

image
image1103×254 9.15 KB

Source ID column is used/needed for incremental import.

The measure AbsenceDay will show up in the user defined measures like this (if import was successful):

image
image929×96 2.53 KB

My current code only supports days where people are completely absent (item[‘day’]= 1.0). For partial absences a value between 0 and 1 would be calculated based on the rest results. Of course, you can put absence hours (e.g. 8h/day) instead of percentage [0…1] or some other unit if you prefer that.

You can also import things as properties (advanced option in the import mapping, these will show up in the “predefined” measures).

If you made a mistake and want to get rid of an imported measure/property then you need to open a report click on “edit” on the measure and then select “delete”. Deleting the data from the cube in Source Data/Import will not delete these measures and as long as the measures exist it seems the corresponding data is not deleted from the DB, even if you select “delete data” in Source Data.

In your reports you can now use the AbesenceDay measure (and restrict the time periods by dragging the time dimension into your report). In addition you can compute the number of days a person is absent in a given time interval:

Custom Measure:

Sum([Time].[Day].DateMembersBetween('now','3 weeks from now'),DefaultContext(([Time].CurrentHierarchyMember,[Logged by].CurrentHierarchyMember,[Measures].[AbsenceDay])))

This is adding all the AbsenceDay values for the days in the given period, it will only work when “[Logged by].CurrentHierarchyMember” is available (e.g. by dragging Loggey.by users into your report on the left).

1 Like