Feature released in SummitAI Platform

What is Power BI Desktop?

Power BI Desktop is an application that allows you to connect to, transform, and visualize the data. Using Power BI Desktop, you can connect with the SummitAI data source, visualize the data, and create the required reports.

Benefits

You can leverage the following benefits by using Power BI with the SummitAI database:

Visualize the Reports and Dashboards by creating them using the information available in the SummitAI data.

Integrating SummitAI with Power BI

Follow the below prerequisites:

Download the SummitAI.mez file from the below path:
https://summitbuild.blob.core.windows.net/common/summit-powerbi-dataconnector.zip
Copy and paste the SummitAI.mez in the Documents folder of the local path as follows:
C:\Users\Username\Documents\Power BI Desktop\Custom Connectors
On the Power BI Desktop, click File > Options and Settings > Options > Under GLOBAL sections, click Security > Under Data Extensions, select Allow any extension to load without validation or warning > Click OK.

Connecting with SummitAI

To connect with SummitAI:

On the Power BI Desktop home page, click Get data. The Get Data pop-up page is displayed.
Search and select the Summit connector and then click Connect.
On the From Summit.Contents pop-up page, specify the Summit API URL and Summit API Key to complete the authentication process.
Note:
- Specify the Summit Reporting API URL to get connected to retrieve the data from the SummitAI application. To get the Reporting API URL, contact SummitAI Support.
- Generating API key: Specify the API Key that is generated while creating a User in the SummitAI application. Power BI does not accept an API Key, if it contains the '+' special character. As these API keys are generated automatically, you must regenerate the API key till you fetch the API key without '+' special character.
  Examples:
- Not acceptable API Key and to be regenerated: QiIxxxxxxxxxxx+/V9xxxxxymzG/16tKs
- Acceptable API Key: SiIxxxxxxxxxxxd/t9xxxxztmzG/00tKs
Click OK.
Based on the Summit data collector, the Navigator page displays the list of all DN tables, and two functions named Get Data and Get Schema.
Note:
- Use Get Data function to retrieve the required filtered data by specifying the parameters such as Data Source, Column Names, Where Condition, No of Records, Sort Column, Sort Order, or with the Direct SQL Query.
- Use Get Schema function to retrieve the specific table or column of a table by searching with either a keyword or with the Data Source name.

Select the function Get Data and specify all the required fields as shown in the below screenshot:

Field	Description	Notes
Data Source	Name of the DN table. If you want to specify the RawSQL parameter, DataSource is not mandatory.	For Example: IM_RPT_DN_TicketMaster
Column Name	Specify the column names that are required to build the Reports.	Use comma separation to specify multiple columns. For Example: [Ticket No],[Status],[Registered Time]
Where Condition	Specify the condition to filter the data.	Use comma separation to specify multiple filters inside the condition. For Example: Status IN ('New','Assigned','In-Progress','Pending')
No of Records	Number of records that should be displayed per each page.	For Example: Consider you specified the PageSize value as 100. Based on the PageNumber, the API Response displays 100 records.
Sort Column	Specify the column with which you want to sort the data.
Sort Order	Order in which the column gets sorted.	The value is either ascending or descending.
Direct SQL Query	SQL query to get the data.	For Example: SELECT Status,COUNT([Ticket ID]) as cnt,[Registered Time] as time FROM IM_RPT_DN_TicketMaster WITH (NOLOCK) WHERE Status IN ('New','Assigned','In-Progress','Pending') GROUP BY Status,[Registered Time]

Select the function Get schema and specify all the required fields as shown in the below screenshot:

Field	Description	Notes
Search	Specify keyword to get the filtered data in the Response.	For Example: ticket or master.
Data Source	Specify the name of a specific DN table to get the list of columns that are available in the Data Source.	For Example: IM_RPT_DN_TicketMaster

Select the table to preview the columns of the selected table.
Click Load to load the data.
Select the view from the VISUALIZATIONS and select the required columns from the DN table.

Querying with the Power BI

Instead of Summit data connector, you can also connect with the Power Bi by writing a Query in Power BI Query language.

To Write and Execute Power BI Query:

On the Power BI Desktop home page, click Get data. The Get Data pop-up page is displayed.
Select the Blank Query/Web and then click Connect.

Under the mini toolbar, select Advanced Editor, and then provide the Query to get the data.

Sample Query 1

let
    datasource="SR_RPT_DN_ServiceTicketMaster",
    apikey="XXXXXXXXXXXXXXXXXXX",
  baseurl="https:// <SummitAI Reporting API URL>/API/getdata",
    url=""&baseurl&"?datasource="&datasource&"&apikey="&apikey&"",
    source = Json.Document(Web.Contents(url)),
    results = Table.FromRecords(source[results])
in
   results

Sample Query 2

 let
   url = "https:// <SummitAI Reporting API URL>/API/getdata",
   body = "{
            ""DataSource"": ""IM_RPT_DN_TicketMaster"",
            ""Columns"": ""[ticket id],[Ticket No],[Registered Time],status,[Sup Function Name],[Workgroup Name]"",
            ""Filters"": ""status in ('new','closed')"",
            ""PageIndex"": 1,
            ""PageSize"": ""10000"",
            ""SortColumn"": ""[ticket id]"",
            ""SortOrder"": ""ASC"",
            ""APIKey"":""XXXXXXXXXXXXXXXXXXXXXXXXXXX"",
            ""RawSQL"":""""
           }",
   source  =
   Json.Document(
       Web.Contents(
           url,
           [
               Headers = [#"Content-Type"="application/json"],
               Content=Text.ToBinary(body)
           ]
       )
   ),
    results = Table.FromRecords(source[results])
in
    results

Create the data and then click Apply on the mini toolbar.