BI integration use case

Your organization wants a report in Excel format that lists all the users and all of the groups they belong to.

The data you need to include in the BI sync, to report on groups and group memberships, is:

• Person - ID, Name, Group members

• Group - ID, Name

You need to take the following steps:

If not already done, enable BI sync on the tenant

From the Main menu, select Administration > Utilities > Integration, and then select BI Integration.

Add the required record types and fields

In BI integration:

1. Select the following record types:

   1. Group

   2. Person

2. For the record type Group, add the following fields:

   1. ID

   2. Name

3. For the record type Person, add the following fields:

   1. ID

   2. Name

   3. Group members (from the Person many to many relationships section)

4. Save your changes.

Execute the REST call

While logged in to Service Management Automation:

1. Open a new tab in the browser

2. Launch the Postman Chrome extension.

3. Execute the REST call to run the BI sync job.

   

4. Using the SyncId from the call, execute the REST call to retrieve the status and file names created by the BI sync job.

   

5. There will be several files. Record the names of all the files.

6. Open another tab in the browser.

7. For each file, enter the following URL:

   <your Service Management Automation domain>/rest/<TenantID>/frs/file-list/<FileId>

   A file download starts. The file name format is <record_type>.zip.

8. Repeat for each of the FileId values obtained in the status REST call.

Load the data into Excel

Navigate to where the downloaded files are, and unzip them. On completion of the preceding steps, there are the following files:

1. Open each of the files and save them as Excel files.

2. Open the file PersonToGroup. This file stores the relationships between people and groups.

3. After the SecondEntity_PersonGroup column, add these new columns:

   1. Group Name

   2. Person Name

4. In the Group Name field paste the following formula:

    =VLOOKUP(D2,PersonGroup_0!C$2:D$800,2,FALSE) 

   When you save the formula a file selector opens. Select the Person_group_0.xlsx file created above.

5. In the Person Name field paste the following formula:

    =VLOOKUP(C2, Person_0!C$2:D$800,2,FALSE)

   When the file selector window opens, select the Person_0.xlsx file created above.

   Note You should replace the 800 in the formulas above with the total number of rows in the referenced spreadsheet.

   In excel it should look like the following:

   

6. Copy the formula for columns E and F for all entries, and add a filter to the top row.

   It is now possible to filter on group or person and see all of the associated values.