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.

Note:

Ensure that you are using the latest version of the Power BI On-Premise Gateway Connector.

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.

For more information about Power BI benefits and options use Accessibility and Solutions page, refer the Power BI Accessibility Options and Solutions.

Application Pool and API URL Creation

To add a new application in IIS (Internet Information Services) perform the following steps:

Navigate to Start > IIS Manager.
Host > Sites > Default Web Site.
Right click Default Web Site > click Add Application.
Enter Alias name.
Example:
Alias name ReportAPI

Figure: Add Application in IIS
Map the physical path to \inetpub\wwwroot\SummitWeb\API\Report and click Ok.
Example:
Physical path \inetpub\wwwroot\SummitWeb\API\Report

Figure: Physical path
Right click Application > Manage Application > Browse.
Example:
If you are using Alias name as Report API then the URL is as below:
http://localhost/ReportAPI/api
The below page should be displayed on the browser.

Figure: Summit Reporting API on browser

Modify Web Config File

To modify the summitDB connection string for the created application, perform the following steps:

Copy the SummitDB Connection String from \inetpub\wwwroot\SummitWeb\Webfiles\Web config file, which is in encrypted form.
Paste the same connection string to \inetpub\wwwroot\SummitWeb\API\Report\Web config file.
Example:
Tag : <add name="SummitDB" connectionString="____"/>

Figure: SummitDB connection string

Integrating Summit with Power BI

Follow the below prerequisites:

PowerBI Pro license may have some data loading limitation. For more details, check your Microsoft PowerBI Subscription.
Custom Power BI Gateway Connector is not required to be approved by Microsoft / Power BI if the record is less than 30k.
Reports, Dashboards developed with the Power BI PPU (Premium Per User) licence (and not a pro/Premium Capacity/Embedded Capacity license), it can only be shared with other PPU users only and not with Pro/Premium capacity/Embedded Capacity users. Customers to contact Microsoft PowerBI Subscription and type of License they carry to understand the functionality.

For more details, you may refer the subsequent section below: Microsoft Power BI References.
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

If the above path fails to connect, manually copy and paste the SummitAI.mez file in the Documents folder of the user’s OneDrive path as follows:
C:\Users\Username\OneDrive - <Domain Name>\Documents\Power BI Desktop\Custom Connectors
Note:

Manually create Custom Connectors folder under Power BI Desktop folder.
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.

Microsoft Power BI References:

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.