Connecting to Power BI/Other Data Sources

ASM can connect to Power BI or other data sources to manage your reporting requirements.

For example, the main steps to connect to PowerBI are:

Create an ASM user account that can be used for the connection. You should not use your own account else, your session will be interrupted when Power BI runs.

Please be cautious of making one large data set that looks at everything in the system. It is best practice to make more, smaller data sets for each of the charts rather than one huge table with everything in it.

Create the Data Query in the ASM API Explorer

The first step is to create data query the ASM API explorer. Use this guidance to login.

1. Select the entity (call request, task, etc.)

2. Select the search function. You need to fill out the fields you need in the red area below in comma separated format and it will give you a URL in the green area. Use the list on the right hand side of the screen to get the field names.

If anything returns a number instead of a value, enter .Name to the end to get the value of it rather than its primary key value.

Configure Power BI

PowerBI expertise is needed for this section. Alemba can only give general advice.

1. From the Get Data Menu, Select "Web"

2. Enter the URL you have from the API explorer

3. Enter the account credentials that you created earlier

4. In the PowerQuery editor you can transform the data to a table.

• You may want to edit the query at this point and add &$top=99999 to the end of the query (change the number as appropriate). By default, Power BI will only return 1000 results. Be cautious of the limit and trying to drag too many columns along with a large row limit at once. The more data, the slower the query. Unfortunately there’s no exact science on this, it’s trial and error due to the number of variables involved.

• You want to avoid using long text fields like the description because those fields can be incredibly large.

• You also want to optimise your selection to only request the data that you need so it performs well. I.E. make sure you only select columns you need and add appropriate filters so not too many results are returned.

1. You may need to expand some columns, specifically where the value is a reference to a list or object (e.g. a drop down, person, CI, service, etc.). In the pop out menu, select Name to get the value. Repeat this for each column.

You can also follow a similar process in Excel or other data sources.