Power BI Reporting
Power BI is a powerful Microsoft tool for visualising and analysing data. As Patriot stores its data in a standard SQL Server database, it is possible to use Power BI to directly query Patriot data.
Power BI Setup
The Power BI Desktop application can use a lot of CPU / Memory resources while loading + displaying visualisations. It's not recommended to run this on the server itself.
After installing + launching Power BI, select 'Get Data' and choose SQL Server Database as the source.
Enter the connection details for your Patriot SQL database.
You will then need to choose a connectivity mode for accessing the Patriot data. In Import mode, Power BI will copy the data out of SQL Server and store it internally. This allows Power BI to visualise the data and run calculations directly on its own cached data, without affecting the Patriot SQL instance. However, this requires importing all relevant data out of SQL Server. In Direct Query mode, data is not copied out of SQL Server. Instead, visualisations and calculations are done by executing SQL queries directly against the Patriot database.
Which mode to use depends on several factors including the size of your database, which data you plan to query, how up-to-date you require data to be, and the specific visualisations that you plan to create.
The Microsoft documentation on Direct Query has additional information on the differences between these modes and recommendations for which mode may be best for your scenario.
In both Import + Direct Query mode, Power BI can query large amounts of data from the Patriot database. It is important to consider the additional load that these reports can have on the system. If practical, using Import mode, importing only the data you need, and scheduling the data refresh for a less busy operating time is likely to have the least impact on the system.
Alternatively, using a separate instance of SQL Server with a recent Patriot backup restored onto it will allow queries to be run without impacting the live system.
Creating Visualisations
Usage of Power BI to create visualisations is generally outside the scope of Patriot documentation. Consult the Microsoft Power BI documentation or tutorials for examples on how to best visualise your data.
The Patriot Database Schema document has additional information on the Patriot database layout which will help find the relevant data for your visuals. As the database schema evolves over time, you may need to make changes to your reports to match the new schema after applying Patriot upgrades.
Patriot support staff are also able to assist with the building of custom reports. This work is not included in software assurance and will be charged at our standard report customisation rates.
Example Reports
Example reports can be downloaded as a starting point:
- Power BI Example Alarm Response. Shows some common visualisations of recent alarm data statistics. Additional computed columns have been added as well as database columns renamed to make the report easier to use.