Using Veeam As a DataSet for PowerBI

Veeam with Microsoft PowerBI

Creating a backup Statistics & analytics Dashboard with PowerBI

With some time at home during lock down I decided to play round with PowerBi & learn.

The easiest start for me was to plug this into a SQL database, looking around for a Data Set I thought what not easier than use the Veeam Config Database in my lab as its already holding rich data that I could transform into a Dashboard. I also have an deep knowledge into backup and can understand how & why to transform the Data Set.

This was my first Time using PowerBi so bear with me ..

First, I downloaded and installed PowerBI Desktop to my Veeam Backup Server (seemed easiest as data set was local to the Tool)

This is a free download for Microsoft Power BI Desktop.

https://powerbi.microsoft.com/en-us/downloads/

I’m not going to show installation as its simple & straight forward.

On first Launch of Power BI Desktop , I was needed to select a Data Source.

There are a few options here to choose from but for this post I use Microsoft SQL Database.

At This point we need to add the SQL connection and Instance Information to be used as Data Set.

If Veeam was installed with default configuration then local windows account was used, same one that was used to install the Veeam software.

To view this information, we need to open Veeam DB config Utility

%PROGRAMFILES%\Common Files\Veeam\Backup and Replication\DBConfig

At the next Point you can Choose which Tables are going to feed you Dashboard data.

I have Chosen the following across Tables:

· BackupRepositories
· Backup Model BackupTaskSessions
· Backup Model Storages
· EntityLocationView

Once all selected you can simply Click Load Data.

You can keep most of the Table data standard the only additional formatting would be to convert a few figures in columns to correct decimal place or measurement.

Example:

Firstly, Navigate to the Table View on the left Navigation

On the Right Select the Table You would like to Format or Add data too.

I then added a few additional columns

Data Reduction Total ( calculated diff between Data_Size & Backup_size for total Reduction)
Backup Size GB ( I divided Source Values by /1024 a few times to get to desired storage format )
Data Size GB ( I divided Source Values by /1024 a few times to get to desired storage format )

The Formula Bar can be used to apply formula or calculation to a Column.

For example, my Data reduction column calculation:

data reduction total = (100-(('Backup Model Storages'[backup_size]/'Backup Model Storages'[data_size] )*100))/100

Next thing is to Jump to the report View and start creating Graphical representation of the data set for the dashboard.

On the Right Navigation pane, you will have Options of the Type of Graph to use.

For the first report I used Clustered Column Chart:

Using Table data: Backup Model BackupTaskSessions And Columns:

· Object_Name
· Processed_size
· Processed_used_size
· Stored_Size

I did another 2 that I will just add screenshots below as the process is similar to follow as the first.

Free Space Report On repositories

Total Backup Size Vs Total Data Size

During my testing I came across a cool little feature in PowerBI Called Q & A Explorer.

With Q & A Explorer you can ask questions about any of the data contained in any of your datasets.

For Example I used This to display total data reduction percentage % of stored backup data just by asking the Question “ average data reduction total “

Or another Example could be : getting Average speed across backups .

Finally I also wanted to include a Map, PowerBI has Geographic Maps available to show Location data.

The only Location Data in Veeam is Location tagging for Data Sovereignty use cases:

Veeam will alert if you are attempting to restore or backup across geolocations for Data Compliance reasons.

You can Understand more on the feature here: https://helpcenter.veeam.com/docs/backup/vsphere/locations_create.html?ver=100

In Veeam I created 4 Locations

1. Cape Town (South Africa)
2. Johannesburg (South Africa)
3. New York (USA)
4. Prague (Czech)

In Veeam I tagged 4 Backup repositories with Separate locations:

This will then reflect Under Table : EntityLocationsView

What we do next is add a Map overlay , I initially used filled map but this didn’t show the Geographic locations bold enough for it to be used as a Dashboard , it did however zoom into each geolocation which was cool “ might use it for something different “.

So first we need to download a Heat Map.

We should navigate too > More Visuals > From AppSource > from the Tool Bar

This will open the MarketPlace , Search in the search bar for HeatMap , I used the following one :

At this Point I then applied the New Heatmap as the visual and selected the Table : EntityLocationsView as the Dataset , You can do further Table customization and cross link relationships between tables so that you can display amount of backups in each location , you could also increase the intensity of the Heat dot to display more populated geolocated repositories over others, etc , Guess you just need to have fun with it.

Ensure you select the Heat-maps under Visualizations , Under Fields make sure that Table : EntityLocationsView is selected and that Columns Entity_name & Location _Name are selected.

You must ensure that Location ( ID) is Mapped to Location_name column.

PowerBI will Call Bing Maps API to find Geolocation of data in Column

Location _Name.

You also need to ensure when Tagging locations in Veeam that your Tags are named after a Real location that can be found on a MAP , Names like Datacentre 1 or 2 will not resolve on a bings MAP API.

I didn’t spend too much time customizing the data in tables , there is a lot more that can be done here to make the dashboard more presentable , but I hope this gives you a start or template to work off if you want to look deeper into you backup data or backup infrastructure.

Thank you for taking the time to read the post , please share comment or like.

MR IT Surgeon

Search This Blog