Power BI Integration
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. To get a quick understanding of Power BI, browse the following video.
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 1let 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 2let 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.
Confluence Cloud Migration Alert: Please refer to known issues you may encounter in Confluence Cloud: https://eitdocs.atlassian.net/wiki/x/wDGwAQ