This is the fourth in a series of VOX blogs discussing how to integrate custom Data Insight Reporting with Microsoft Power BI to create stunning visual reports or dashboards. The earlier three blogs covered how to create and use custom reports in Data Insight and how to integrate them with Microsoft Power BI.
Veritas Data Insight can be used with Microsoft Power BI to create powerful graphical reports to share with other users and groups within your organization. We will focus on using pre-created DQL Report Templates that are easily copied onto your Data Insight Management Server, executed from the Reports tab, and then viewed within Power BI Desktop. Before getting started, install Microsoft Power BI Desktop onto your local workstation.
Installing Microsoft Power BI Desktop
- From your local workstation, download Microsoft Power BI Desktop here.
- Select Language and click Download.
- Select PBIDesktopSetup_x64.exe and click Next.
- Once the download completes select PBIDesktopSetup_x64.exe to initiate the install.
- Follow the installation steps to complete the install.
- Create a subdirectory off of your C:\ drive called DIReports, C:\DIReports.
- Download the PowerBI and DQL Template files, dda_templates.zip below.
- Copy the Power BI Desktop .pbix file into your C:\DIReports folder on your local workstation.
Data Insight DQL Templates
Unzip the dda_templates.zip file. There are 11 DQL templates.
Step 1: Copy the template files onto your Data Insight Management Server into the following subdirectory: \DataInsight\data\templates\dql
You may have several different Data Insight Servers in your environment, performing different roles. Be sure to copy the templates to the Data Insight Management Server in your environment.
Step 2: Create a subdirectory off the C:\ drive of the Data Insight Management Server called DIReports. C:\DIReports.
If you do not create a C:\DIReports folder on the Data Insight Management Server, you will receive an error when you run the reports. This step is mandatory!
Creating Reports
- Log into the Data Insight Administration Console.
- Select Reports/Custom Reports/DQL Report/Create Report
3. Select Query/DQL Query, checking the Use Template box, and then select the down arrow beside Category.
4. Select DDA.
Note: If the DDA category does not appear, logout of the Data Insight Administration Console. Restart the DataInsightConfig Service and relog onto the Data Insight Administration Console.
Select the down arrow beside Template. The 11 DQL Query Templates should appear.
5. Select the ClassificationSummaryReport.
6. Scroll down in the report and highlight the code between:
/* Advanced SQL Queries
*/
7. Click Ctrl/C to copy.
8. Select the Advanced Options tab
9. Check the box beside Run SQL commands on generated DQL output database
10. Click Ctrl/V to copy the portion of the script into the Advanced Options window.
11. Select the Report Information tab at the top of the screen.
12. Enter a name for the report in the Name field.
13. Click Save and Run.
14. You should receive a message that says, Report created Successfully.
15. Click OK.
16. Repeat steps 5-16 for each remaining template in the DDA Category.
If you chose to select Validate Query before clicking Save or Save and Run, you may receive an error. If this happens, click Save and then select Save and Run again.
17. Once done, confirm that all jobs are completed successfully.
18. Go to the C:\DIReports folder on the Data Insight Management Server.
19. Running the eleven reports should create the following, 15 CSV Files.
All 15 CSV files must be within C:\DIReports. If not, then refreshing the Power BI template, which is the next step, will fail. If not all 15 CSV files are present, go back and rerun the reports that will produce the missing files.
Working with the Power BI Template
- Copy the CSV files from the Data Insight Management Server to the C:\DIReports on your local workstation.
- Double-click the PowerBI Template file, DDA_Reports.pbix that you had previously copied into the C:\DIReports folder.
- Click the Refresh button on the PowerBI toolbar.
- This will Refresh the charts, graphs, and tables in the Power BI Template file with the data from the .CSV files you copied into the C:\DIReports folder on your local workstation.
The Power BI Template is composed of two sections. The first section includes an analysis of your actual data, based on the Data Insight reports we ran and copied into the C:\DIReports folder. The Appendix section shows sample screenshots of additional reports that can be created, to illustrate the power of Data Insight’s Reporting capabilities. These are some of the additional types of reports that would be generated during a paid, Veritas Data Insight Advanced Reporting Assessment.
Become familiar with the reports and how to dynamically filter the report data within some of the reports. Hovering over the Information icon found in the bottom, left-hand corner of each report provides a brief explanation of each report.
Important: Except for the Appendix, confirm that the data in each report has refreshed appropriately, and is now displaying your data. Never click the Publish button within Power BI to publish data from Power BI Desktop into Power BI, without your organization’s consent.
Troubleshooting Power BI Desktop Refresh
Sometimes when selecting the Refresh button within Power BI, it will throw an error, preventing the Refresh to occur. In the illustration below, all of the CSVs are producing the same error: Loading blocked by failures with other queries. These CSV files do not have an issue. They are being prohibited from refreshing due to another error. In this case, the psts.csv file was not found within the C:\DIReports folder. The solution would be to execute the PST DQL report, producing the psts.CSV file, placing the file into C:\DIReports, and selecting Refresh again.
Please note that these DQL and Power BI Templates are provided to customers as examples only. Veritas does not offer additional support when working with these reports or templates.
I hope this series of blogs will help you to create stunning Data Insight reports even quicker. If you require additional reporting capabilities for your Veritas Data Insight environment fast but don’t have the time to focus on it, contact your Veritas Sales team. We offer through our Services team a Data Insight Advanced Reporting Service, helping you to solve all your reporting challenges, and don’t forget to check out the latest Veritas Data Insight capabilities on our website.
