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:

Search for	Example	Results
A single word	`cat`	Topics that contain the word "cat". You will also find its grammatical variations, such as "cats".
A phrase. You can specify that the search results contain a specific phrase.	`"cat food"` (quotation marks)	Topics that contain the literal phrase "cat food" and all its grammatical variations. Without the quotation marks, the query is equivalent to specifying an OR operator, which finds topics with one of the individual words instead of the phrase.

Search for	Operator	Example
Two or more words in the same topic	`AND` `and` `+` (plus symbol) `&` (ampersand)	`cat AND dog` `"cat food"+milk` `"cat food"&"dog food"`
Either word in a topic	`OR` `or` `\|` (pipe)	`cat OR dog` `cat \| dog`
Topics that do not contain a specific word or phrase	`NOT` `not` `!` (exclamation point)	`NOT cat` `! dog`
Topics that contain one string and do not contain another	`^` (caret)	`cat ^ mouse`
A combination of search types	`( )` parentheses	`cat + (dog \| mouse)` `cat \| dog + (! mouse)`

Person - ID, Name, Group members

Group - ID, Name

You need to take the following steps:

Add the required record types and fields

In BI integration:

Select the following record types:
1. Group
2. Person
For the record type Group, add the following fields:
1. ID
2. Name
For the record type Person, add the following fields:
1. ID
2. Name
3. Group members (from the Person many to many relationships section)
Save your changes.

Execute the REST call

While logged in to Service Management Automation:

Open a new tab in the browser
Launch the Postman Chrome extension.
Execute the REST call to run the BI sync job.
Using the SyncId from the call, execute the REST call to retrieve the status and file names created by the BI sync job.
There will be several files. Record the names of all the files.
Open another tab in the browser.
For each file, enter the following URL:
```
<your Service Management Automation domain>/rest/<TenantID>/frs/file-list/<FileId>
```
Continuing the example from the images above:

A file download starts. The file name format is <record_type>.zip.
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:

Open each of the files and save them as Excel files.
Open the file PersonToGroup. This file stores the relationships between people and groups.
After the SecondEntity_PersonGroup column, add these new columns:
1. Group Name
2. Person Name
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.
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:
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.