Feb 16

Using PowerBI & MAP Toolkit & to review your SQL Server Environments

TSQLTues Logo  This is a posting for T-SQL Tuesday #75

As the SQL Server & BI Consulting Practice lead in a Tier-1 managed Microsoft Partner consulting company www.Northdoor.co.uk , my role often involves me discussing our customer’s SQL Server environment, perhaps they are considering an upcoming version migration or need to perform some consolidations, maybe they are undertaking a SQL Server licensing compliance review or embarking on a major modernisation project.

Regardless of what the reason is for their SQL Server project, it’s important that we quickly gain an understanding of the complexities of the environment(s), frequently it’s the very first time we are working with the client and so lack any prior knowledge of their environment, to overcome this and to begin to answer some key questions with confidence in the accuracy of the data, we have them run a Microsoft (free tool) known as the MAP Toolkit (Microsoft Assessment & Planning), then we can start to tackle questions such as these

  • How many of X Versions and Y Editions are there?
  • What different versions of OS are the instances running on?
  • What is the breakdown of Virtual/Physical, Clustered/Standalone; 32/64-bit
  • Details on CPU models & number of Cores, How much Memory  
  • Number of Databases; Database Sizes , Compatibility levels

The MAP toolkit does have some basic reporting capabilities and provides us with a few high-level overviews, but does lacks the capability to get truly interactive with the data it has captured and we don’t really get the answers we need answering, well fortunately we can resolve that by creating our own PowerBI reports based on the MAP data tha it collected, these PowerBI reports can provide us with the interactive report capabilities which we lacked.

MAP Toolkit #1          latest version is 9.3 http://www.microsoft.com/en-in/download/details.aspx?id=7826

Following the MAP toolkit instructions and running an inventory, we have something like this report.MAP Toolkit #2

What’s next

Now we going to use the 2 excel (MAP output files) as the data for this PowerBI Report we are building, we can easily create them from the MAP Toolkit (Top Right) Options > Click the Generate Report links and save them to a local folder, they are probably named SQLServerAssesment.xlsx & SQLServerDatabaseDetails.xlsx , we’ll also want to join these 2 datasets inside PowerBI we create 3 Queries in from these 2 files, the following screenshots below depict this.

Ok, so let’s take a look here is the first PowerBI report, it consist of a Multi-page PowerBI report developed with the PowerBI Desktop app and this is just using the 2 excel files as the source.PowerBI #1 PowerBI #2

As you can see these reports have a few slicers (Filters) which can be used to filter the underlying information and the visuals will dynamically change based on the filtering that is applied.PowerBI #3

Here we are switching to the Data Pane, by adding a new column [InstanceName] into each of the 3 queries by adding a simple formula as follows, as we need  the ServerName\InstanceNamePowerBI #4

DatabaseInstances {SQLServerAssement)

InstanceName = DatabaseInstances[Computer Name] & “\” & DatabaseInstances[SQL Server Instance Name]

Databases {SQLServerDatabaseDetails)

InstanceName = Databases[Server Name] & “\” & Databases[SQL Server Database Engine Instance Names]

DatabaseDetails {SQLServerDatabaseDetails)

InstanceName = DatabaseDetails[Server Name] & “\” & DatabaseDetails[SQL Server Database Engine Instance Name]

Below we are showing the linked queries based on the joining on the [InstanceName] column.PowerBI #5

We can now use the report, share it with others and even now publish this to the web

With our finished report, we probably don’t want to have to recreate the report from scratch each time, thankfully we can simply make a copy of an existing report and then update the data held within that PowerBI report, replacing the files in the source folder and then doing a data refresh being the simplest way to load in the new dataset for an inventory run.

Alternatively we can navigate to each of the queries and selecting the first applied step which is named Source [Click the gear icon to the right] and then update the path to the excel file, repeat this step for all 3 queriesPowerBI #6

Also we could click Query > Advanced Editor and then update the relevant File-Path to the file in Advanced Editor window, then click [Done] and followed by the Refresh button.PowerBI #7

So none of that is that time consuming, just make sure the files are editable and all should work just fine, now I’m more inclined to use a SQL Server Database than Excel, so wanted to access the data directly from the MAP database and skip the Excel file step {Where Excel files gets its data anyway}

So another PowerBI Report was born, this time I’m using a T-SQL Query directly against the MAP Database, note the location of the database is generally found at (localdb)\MAPTOOLKIT.[MAP]PowerBI #8

Here we have 1 query, populated from the query and this provides all the information needed for the PowerBI Report, it’s very simple now to switch between MAP databases by simply changing the details of the source database (Server & database)PowerBI #9

In the SQL Statement (optional) pane, we have pasted in the T-SQL query (links at the end for script) we are using and make the updates the Server & Database fields as needed.PowerBI #10

That’s as far as I can take this post today, hopefully I’ve made this a quick overview of both the MAP toolkit and how we can explore the data it collects with some sample PowerBI Reports

If you want I‘ve included the example PowerBI reports with sample data loaded in them to help you get started, but why not edit these reports to suit your own layouts, and run MAP toolkit and then view the data from your own environments.

Sample Files are on available at the following for download


Oct 22

Northdoor PLC Welcomes their SQL Server Technology Evangelist

I have the enormous pleasure of announcing that I’ve now joined Northdoor PLC www.northdoor.co.uk as their SQL Server Technology Evangelist, our office is centrally located in London


It is exciting to be working with their existing team, and to bring along my own experiences and SQL Server knowledge to add to the mix, I will be primarily responsible for leading and developing our existing SQL Server practice, so I look forward to being able to add some new offerings and enhance the experience we have working with our existing and new clients over the coming years

logo  mslogo20130312_sml

Northdoor is an IT consultancy and solutions provider. We help fast-growing enterprises achieve their business objectives through IT enablement. With over 20 years of experience, we have been a trusted adviser to many organisations.
Northdoor combines specialist skills across IT consulting, enterprise infrastructure and IT support and managed services into one integrated solution for our clients

So please follow our twitter account @SQLNorthdoor for daily news and information from the world of SQL along with information & events and also what our Team-SQL are doing out in the community

We will have vacancies {for permanent staff} to fill as we expand our SQL Server Practice, already we are looking to find another to join our team, so if you know SQL Server and want to become part of this dynamic team, you can email me for details, I’ll let you know what vacancies we have available

Upcoming Events

  • 28th Oct 14 – Leeds – SQLRelay
    • Presenting my DQS/MDS Session
  • 30th Oct 14 – London
    • Just as a SQL Relay volunteer, as SQL Relay is one of favourite events
  • 1st Nov – Portland, USA – SQLSaturday #337 Oregon
  • 7th Nov 14 – Seattle, USA – PASS Summit 2014
  • 3rd Dec 14 – PASS Chapter London evening event (6-9PM)

There is more still to come in 2014 but I’m still working on the arrangements for them right now.. so watch this space for further announcements