Integrating Applications Manager backend Microsoft SQL database with Power BI Desktop

Integrating Applications Manager backend Microsoft SQL database with Power BI Desktop

Overview

This KB provides step-by-step instructions to connect Power BI Desktop to your Applications Manager's Microsoft SQL backend database, enabling you to create custom reports and dashboards using Applications Manager's performance metrics and monitoring data.

Prerequisites

  1. Power BI Desktop installed on a computer with access to the Applications Manager SQL backend database
  2. Applications Manager backend Database connection credentials (SQL Server name,  database name, username, password) with atleast Read access permissions to the Applications Manager database tables

Steps to Connect Applications Manager SQL Backend to Power BI

  1. Launch Power BI Desktop on your computer and select the data source as SQL Server
  2. In the SQL Server database wizard, provide the SQL Server information (Applications Manager SQL database information can be found from: Applications Manager GUI → Settings → Support → Database Backend Details table).
  3. Select DirectQuery if you want to query live data with real-time dashboard updates
  4. Expand Advanced options and set Command timeout to 1 minute. 
  5. In the SQL statement field, provide SQL query to fetch required data (Ex: Query to count the number of Critical alerts - SELECT COUNT(*) AS Critical_Alert FROM Alert WHERE GROUPNAME='AppManager' AND SEVERITY=1 ) and Click OK.
    PowerBI - Applications Manager SQL Server database details
  6. Provide the Username and Password required for connecting to the backend SQL Server and Click Connect.
    SQL Credentials
  7. Once successfully connected, you should be able to view the Query result. Please proceed to Load the data and build Visuals as per requirement.
    PowerBI Dashboard

Need Help?

If you require any help in preparing the SQL queries to fetch your required data, contact us at appmanager-support@manageengine.com