Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for August, 2015

How to: Build a SQL Server Dashboard with Power BI Desktop

Posted by denglishbi on August 25, 2015

Just last month Microsoft released the new Power BI which included a new self-service BI application called Power BI Desktop. I introduced this when it was first launched in the following blog posts – Power BI Desktop Install Experience & Publish Feature! and Ready, Set, and Go! PowerBI.com, Power BI Desktop, and Pyramid Analytics Alliance. A few weeks ago I was exploring some new uses for this application and the first one I tried out was to create a SQL Server Dashboard that would provide some information about my local SQL Server instance running on my laptop. Let’s take a look at the final result –

PowerBI_SQLDashboard'

Here we can see that I included the SQL Server logo, the name of my machine, the version and build number of SQL Server I am running, and then some information about the databases – the count, sizes, types, last backup, recovery model, and file information.

Now let’s take a quick look at how this was built. First thing you will need to download and install the Power BI Desktop application. It appears that since the initial release the application has already been updated a few times, latest build version is 2.26.4128.403 and it was released on August 20, 2015. The application is available in both 32 and 64-bit versions.

Once you have the application downloaded, installed, and started up, the first thing we will want to do is get some data. You can either click on the Get Data on the Getting Started screen when you open the application or in the Ribbon you can click on Get Data and then SQL Server.

image

Then enter in your server name and provide the query to get the information. In this case the query will be running against the master database and the query will be the following:

SELECT @@servername ServerName,
@@version SQLVersion,
sdb.NAME DatabaseName,
sdb.create_date CreateDateTime,
sdb.compatibility_level CompatibilityLevel,
sdb.collation_name CollationName,
sdb.recovery_model_desc RecoveryModel,
Max(bus.backup_finish_date) AS LastBackUpTime,
size.sizeinmb DatabaseSize,
CASE
WHEN sdb.database_id < 5 THEN ‘System’
ELSE ‘User’
END DatabaseType
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus
ON bus.database_name = sdb.NAME
INNER JOIN (SELECT [DatabaseName] = Db_name(database_id),
[Type] = CASE
WHEN type_desc = ‘ROWS’ THEN ‘Data File(s)’
WHEN type_desc = ‘LOG’ THEN ‘Log File(s)’
ELSE type_desc
END,
[SizeinMB] = Cast(( ( Sum(size) * 8 ) / 1024.0 ) AS
DECIMAL(18, 2))
FROM sys.master_files
GROUP BY grouping sets ( ( Db_name(database_id), type_desc )
, ( Db_name(database_id) ) )) size
ON sdb.NAME = size.databasename
AND size.type IS NULL
GROUP BY sdb.NAME,
sdb.create_date,
sdb.compatibility_level,
sdb.collation_name,
sdb.recovery_model_desc,
size.sizeinmb,
sdb.database_id

Once the query has been populated to create the data we clean it up a little bit by splitting some columns, changing data types, renaming the columns to be more user friendly, and provide a name for the data query.

image

We can then close this query and add more, in this case we have another query that will provide the information about the files associated with each database. The query we are going to use was actually used in the first query and was from the following post located here:

SELECT [DatabaseName] = Db_name(database_id),

[Type] = CASE

WHEN type_desc = ‘ROWS’ THEN ‘Data File(s)’

WHEN type_desc = ‘LOG’ THEN ‘Log File(s)’

ELSE type_desc

END,

[SizeinMB] = Cast(( ( Sum(size) * 8 ) / 1024.0 ) AS DECIMAL(18, 2))

FROM sys.master_files

GROUP BY grouping sets ( ( Db_name(database_id), type_desc ),

( Db_name(database_id) ) )

Once this data set is added we apply a filter, pivot the data, rename the columns, and then provide a name for the query and close and load the data.

image

Now that we have the two data queries, we can establish a relationship between the two (you could also merge them together if you would prefer).

image

And then you can create reports and add text boxes, images, slicers, and visualizations.

clip_image012

Now we could stop here at this point, but what fun would that be. What if I wanted to be able to monitor and report on my databases while I am out on the road or having a conversation with a colleague? Hmmm, there must be a solution for that…and ‘Yes’ there is!

Microsoft provides the online PowerBI.com service that allows you to upload (or Publish) your Power BI Desktop creations. I already have an account, so I can click on the Publish button in the Ribbon and then I will be prompted to save the file and if I have already published this before an option to Replace the existing dataset (or Cancel).

clip_image014

The solution will then be published and you will be provided a link to access the item directly.

clip_image016

Once published I can create a dashboard in the online service and pin the items I want to my dashboard from the reports I included (see the initial image in the blog post for the final result). You can also enhance the reports and edit them if needed, just be careful though, if you publish from the desktop you could override changes in the online version of the reports.

Now, are we done yet? No way, what about refreshing the data? But how can I do that, the data is running on my laptop… No worries, download and install the Power BI Personal Gateway. Once this is downloaded, installed, and configured (Power BI Personal Gateway online knowledge base article) you will have the ability to refresh the dataset that is loaded in the online PowerBI.com service.

clip_image018

How cool is that! And you can see in the below screenshot that is exactly what I did, if you compare this to the initial dashboard at the top of the post you will see that there are two additional databases (flashback goodies – Northwind and pubs return!).

PowerBI_SQLDashboard_refresh

And I can view this on the mobile application on my phone as well:)

clip_image022

There are lots of possibilities for expanding on this set of reports – analyzing database and table statistics, usage, queries, and more.

I would really love to hear what you think of the “new” Power BI and the ideas and creations you are building, leave a comment and let me know, thanks!

Advertisements

Posted in Power BI | Tagged: | 3 Comments »

PASS Business Analytics VC August Meeting–Power BI, Mission Possible!

Posted by denglishbi on August 24, 2015

The next PASS Business Analytics Virtual Chapter meeting is coming up on Wednesday, August 26.  This month we welcome Microsoft SQL Server MVP, author, and trainer Reza Rad (b|t).PASS_BAVC_Logo_New_201502

–> REGISTER NOW <–

August 26 – Power BI, Mission Possible!

Abstract

Power Query, Power View, and Power Pivot has been in the BI market for a while. Microsoft combined these three products into a single product and service with a new designer called Power BI Desktop (Power BI Designer). Power BI gathered features of these three products all in one, so building dashboards and effective visualization items with this tool is much easier nowadays.

In this session you will learn all new and great features of Power BI. You will learn how to work with different data source, and apply Power Query transformation on them. You will learn tricks and tips of modelling with Power BI Desktop, and then how to visualize effectively with this tool. You will also learn how to build dashboards on Power BI website.

At the end of this session you will be able to use Power BI for your everyday data analysis as well as organizational data mash up and visualization. So expect lots of demos in this session.

Bio – Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in SQL Server for five continues years (from 2011 till now) for his dedication in Microsoft BI. His articles on different aspects of technologies, especially on MS BI, can be found on his blog: http://www.radacad.com/blog (previously was http://www.rad.pasfu.com) . He wrote some books on MS SQL BI and also is writing some others, He is also an active member on online technical forums such as MSDN and Experts-Exchange, and is a moderator of MSDN SQL Server forums, and is a MCP, MCSE and MCITP of BI. He is the leader of New Zealand Business Intelligence users group. He is a International Speaker in SQL Saturday and SQL users groups and Conferences like TechEd and PASS Summit. He is a Microsoft Certified Trainer.

–> REGISTER NOW <–

If you are planning on attending the PASS Summit 2015 Conference use the following discount code and save $150

Save $150 right now using discount code VC15BKR3

If you are interested in speaking at an upcoming PASS BA Virtual Chapter meeting we are always looking for speakers, please leave a comment or send me an email at denglishbi@gmail.com

Posted in Training | Tagged: , , , | Leave a Comment »