Better Data Insight Reporting With Microsoft Power BI

Compliance, Data Management, and Governance projects often begin with our customers' need for more data visibility. Providing data visibility, exposing potential data liabilities, and predicting data problems before they happen is a key strength of Veritas Data Insight. Many of our customers are aware of our strong integration between Data Insight and Microsoft 365, enabling automated and simplified content classification and superior data security through quick ‘true’ ownership identification. However, not everybody is aware that Data Insight can integrate with Microsoft Power BI to offer visually stunning and dynamic reporting capabilities.

This is the first in a series of four articles discussing in-depth how to integrate custom Data Insight reporting with Microsoft Power BI. 

How to create DQL Templates for Custom Reporting

Customers can use Data Insight with Microsoft Power BI to create powerful, interactive, graphical reports for storage optimization, pinpointing security risks, and classifying data for compliance. These reports can then be shared with other groups within your organization.  Data Insight includes several standard reports which you can run from the Reports tab.

Many customers may be unaware that Data Insight comes bundled with dozens of additional reports. To view and run these reports, expand Custom Reports, right-click DQL Report and select Create Report.

Then select the Query tab and check the Use Template box. If you select the pull-down arrow beside Template: you will see dozens of additional Data Insight reports that you can run.

What is DQL?

DQL (Data Insight Query Language) is a read-only query language for retrieving information stored in the Data Insight indexes. DQL cannot modify the Data Insight indexes and is used to create custom reports. Perhaps there’s a report you wish to create, and the standard reports bundled with Data Insight do not quite produce the results you are looking for? This is where DQL comes in handy. A detailed description of DQL is beyond the scope of this article. But if you wish to better understand how to write DQL queries (which are similar to writing SQL Queries) refer to the Veritas Data Insight Programmer’s Reference Guide. Another excellent way of learning DQL is to review the DQL Templates in the illustration above, bundled with Data Insight and look at the output returned when you execute the scripts associated with these DQL templates.

What are DQL Templates?

DQL Templates are sample DQL queries that you can select directly from the Data Insight Administration console. In this article, we show you how to create your own custom, DQL Templates. Later, we will create reports based on these DQL Templates and show you how to integrate the report output into Microsoft Power BI to create powerful, interactive reports. Listed below is a sample, DQL Template for listing files containing PST extensions.

In order to use this template file, you would copy it onto your Data Insight Management Server into the following subdirectory: \DataInsight\data\templates\dql. The template file has to end with a .template file extension. You may have a number of Data Insight Servers in your environment performing different roles. Be sure to copy the templates to a Data Insight Management Server.  Let’s review the various sections within this DQL template and some of the DQL script.

name: identifies the name of the template, in this case, PSTs

desc: provides a description of what this DQL query template is used for. In this case, the description says: This DQL creates a detailed report of pst files in your environment.

Category: identifies the template category, which is StorageOptimization. You can create your own template categories or use existing ones, by simply naming them in a template file.

You can see the name and category of the template appear, when selecting this template inside the Data Insight Administration Console, once you have copied it into the \DataInsight\data\templates\dql folder.

version: describes which version of Data Insight this template supports. In this case, 6.1

query: is the section that includes the actual DQL script. The following brackets mark the beginning and the end of the query { }

In this query, we are querying a DQL object (or table) called path and it is returning to us data from the following table columns:

device.name the name of a file server or filer, for example.

msu.name the name of a file share.

absname the full path name, down to the name of the file.

extension is the file extension associated with the file.

permitted_users_count are the number of users that are permitted to access this file

size AS size_bytes would be the file size, represented in bytes

formatdate allows us to format the file’s creation, last accessed and last modified dates

WHERE type = “FILE” means we are searching for files, not directories (DIR).

Extension = “pst” means we are looking for a pst extension at the end of the file.

At the end of the script, we have the ability to limit our output of pst files to a specifc filer or share. The two dashes preceeding the final lines indicate those lines will not be processed, unless we remove the dashes.

-- AND path.device.name='<Filer name>'

--AND path.msu.name IN ('<ShareName>')

Validating the Query and Running the Report

Veritas Data Insight allows you to validate your DQL Query and the syntax used, prior to executing the script by selecting Validate Query.

You can also set the report to run on a regularly scheduled basis.

Now that we know the query is valid, we will click Save and Run.

Listed below is the report produced by this PST DQL Template File.

Stay tuned for our second blog in this series on VOX, discussing how to use Data Insight’s advanced options with custom reporting. In the meantime, learn more about Data Insight’s functionality on our webpage.