Microsoft Power BI

Data Acquisition

To start work with Power BI you need to load data according to which it will be possible to create reports.

For this purpose find the section "Data" in the toolbar.

Press on "Obtain data" and choose "Empty request".

In the opened window press "Advanced Editor".

Enter the request:

For the creation of request the Web.Contents function is used ()

It is necessary to specify by the first argument of this function the resource from which data will be loaded. The template looks so:

"http://<server_address>:<port_number>/api/graphql"

On the screenshot, the example of input of the address is shown above.

The following argument which we give to functions is in braces "[]" after the comma.

In the beginning, it is necessary to prescribe Headers of HTTP of requests:
#”Method” – this field needs to appropriate POST value
# "Content-Type" – this field needs to appropriate application/json value.
# "Authorization" – for authorization is used by Bearer authorization, the token can be received

it is possible in the system. For this purpose come into Settings-> the General and copy API Token, then it needs to be entered.

Further, it is necessary to create a request. The problem of the request is to choose the necessary data for work. The request is written in the GraphQl language. The template of the request looks so:

Content=Text.ToBinary (" {
""query"": ""query { <type of request> {
<pole1>/n
<pole2>/n
…….
}\\n""
}")

Parameters:

Types of requests are given below:

type Query {

traffic (start: Date, end: Date, vaId: Int = 0): [Traffic]

alpr (start: Date, end: Date, streamId: Int = 0): [PlateStat]

alprAlerts (start: Date, end: Date, streamId: Int = 0): [AlertStat]

person (start: Date, end: Date, streamId: Int = 0, alerted: Boolean = false, limit: Int = 100, offset:
Int = 0): [Person]

personNotification (start: Date, end: Date, limit: Int = 100, offset: Int = 0): [PersonNotification]

}

In Square brackets after each type of request types which this request addresses are shown. In each type the set of fields that can be used. To choose the necessary fields it is necessary to specify them by the principle that is stated above.

Available fields by types of request are given below:

scalar Date
enum TrafficType {
people
bike
car
motorcycle
bus
truck
}

enum Gender {
male
female
}

type Traffic {
va_id: Int
date: Date
line: Int
count: Int
type: TrafficType
direction: Int
directionOne: Int
directionTwo: Int
}

type PlateStat {
stream_id: Int
date: Date
total: Int
countNumbers: Int
countMakeModel: Int
countBoth: Int
}

type AlertStat {
key: String
total: Int
createdCount: Int
foundUnique: Int
foundCount: Int
closedCount: Int
}

type Person {
alerted: Boolean
streamId: Int
date: Date
age: String
gender: Gender
faceImage: String
frameImage: String
box: String
}

type PersonNotification {
name: String
age: String
gender: Int
distance: Float
date: Date
status: Int
box: String
streamId: Int
detected: String
detectedFrame: String
detectedObject: String
}

In the following part, it is necessary to create the received JSON in the table with which Power will be ready to work. The code is written in the Power Query M language. The Power Query M language User Guide you can find following the link: https://docs.microsoft.com/ruru/powerquery-m/

Principle of writing:

#”<name” – the name of the field is written in such format. The name can be any, but it is desirable that it somehow described content. Everything written in such a format influences the final table.
The value after the sign "=" is appropriated to Paul. For values it is possible to use ready functions of the Power Query M language.

#”JSON” = Json.Document(Source),
Data = (JSONdata)
traffic = data(traffic)
In the code above, the converted to the JSON document is described.

Everything that goes below is forming of the table with which Power bi can work. After input of request press the button "Ready".

In the editor, the table with data will appear.

In the right part, you can change the parameters of the request.

In Properties, you can change the name.

To add the description of the request press "All properties".

In "The applied steps" you can see all changes that happened to the table.

For this purpose that you will see the condition of the table on the certain step press it.

To keep the obtained data, press "Close and Apply" in the toolbar:

Creation of Visual Elements

After closing the editor you will pass to the page of creation of the report. After the successful request fields will be displayed in the right part of the area.

For the creation of the chart, it is necessary to choose in the section visualization the visual element (the chart, the schedule, the histogram) and to note fields that shall be displayed in the visual element. Undressed filters create the condition for the choice of elements – it allows to reduce the number of elements for display.

After the choice of the visual element, it will appear in the working area. After the selection of fields and filtration, the visual element will display data in the necessary format.

For data table viewing, execute the transition to the page of data having pressed the badge in the left part of the screen:

Having pressed icons opening of the list in the name of fields you can choose necessary data and sort them.

To prescribe communication between tables, pass to the page of model, having pressed the icon in the left part:

To open the window creation of communication:

1) Press the badge at the top of the table or in the partition of the field to right-click on the name of the table.

2) Press "Manage relationships".

3) In the opened window press "Create communication". In this window, all existing communications will be shown.

4) Choose necessary tables, specify fields and frequency rate. Press "Ok".

If everything is made correctly, then communication between tables will appear.