Dan English's BI Blog

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

Archive for the ‘Power BI’ Category

MN PASS Microsoft BI User Group Meeting – Jun 19

Posted by denglishbi on June 12, 2017

The next MN Microsoft BI User Group meeting is on Monday, June 19. There will be two presentations, the first one is Analyzing Healthcare Open Data with Power BI (presented by me) and the other is Time Intelligence in DAX (by Marco Russo!).

Please Register Now (Please register so that your name badge is pre-printed)

MN Microsoft BI User Group June 2017 Meeting

Date: Monday, June 19, 2017

Check In: 3:45 PM Event Time: 4:00 PM – 7:30 PM

Location: Microsoft Technology Center – 3601 76th St W, Suite 600 Edina MN 55435

Please Register Now (Please register so that your name badge is pre-printed)

Agenda:

  • 3:45-4:00 : Check-In
  • 4:00-4:15 : Microsoft BI User Group Updates and Announcements
  • 4:15-5:15 : Analyzing Healthcare Open Data with Power BI (Dan English)
  • 5:15-6:00 : Break, Dinner, Networking
  • 6:00-7:00 : Time Intelligence in DAX (Marco Russo)
  • 7:00-7:30 : Closing / Prizes

Presentations:

Session 1: Analyzing Healthcare Open Data with Power BI

In this session we will explore and evaluate Healthcare data that is freely available to the public. You will see how easily we can consume and integrate the data to analyze and investigate. In the demos you will see some of the latest enhancements and features that have been included with Power BI such as ArcGIS (Esri) maps, forecasting, Top N filtering, grouping / binning, custom R visuals, and maybe a few more along the way. If you are interested in Open Data, Healthcare, or have to perform a lot of data manipulation in Excel or other tools to provide analysis for your job then this is the session for you. Leverage the free capabilities of Power BI, advanced analytics, and help simplify your everyday workload and wow your boss!

Speaker: Dan English, Sr Data Warehouse Architect and Microsoft Data Platform MVP

Dan is the Sr. Data Warehouse Architect at Constellation, Inc., a Microsoft Data Platform MVP, speaker, author, technical editor, and community leader. He has been developing with Microsoft technologies for over 20 years, and over 13 years with Data Warehousing and Business Intelligence. He has been working with SQL Server since version 6.5 and now using 2016 and test driving 2017! Dan has videos on YouTube and Vimeo, is blogger, and tweets. Dan is the group leader for the PASS Business Analytics Virtual Group.

Session 2: Time Intelligence in DAX

Time Intelligence is probably the most interesting feature of any analytical solution. Quick measures in Power BI can help you up to a certain point, because if you have more than one date column in your data model, it is also important creating the right data model, having the right Date table, and using the right DAX function to compute calculations such as Year-To-Date, Month-To-Date, Same-Period-Last-Year, and others.

In this session, you will see how to properly use time intelligence calculations in Power BI, avoiding data modeling and DAX mistakes, and using the right work around for the missing “Mark as Date table” feature that you have in Power Pivot.

Speaker: Marco Russo, Microsoft Data Platform MVP

Marco Russo is a Business Intelligence consultant and mentor. He has worked with Analysis Services since 1999, and written several books about Power Pivot, Power BI, Analysis Services Tabular, and the DAX language. With Alberto Ferrari, he writes the content published on www.sqlbi.com, mentoring companies’ users about the new Microsoft BI technologies. Marco is also a speaker at international conferences such as Microsoft Ignite, PASS Summit, PASS BA Conference, and SQLBits.

Advertisements

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

SQL Server 2017 CTP2.1 Install Experience

Posted by denglishbi on May 18, 2017

In this post I will walk through the install process highlighting the changes of the SQL Server 2017 install. This will be using the CTP2.1 release (May 2017) of the installer and I will also be using the new Power BI Report Server install which is a new light weight installer that is not part of the core server product installation.

I will just include some of the key screenshots from the installation and highlight some of the differences.

Install SQL Server 2017 CTP2.1

image

image

image

We can see here that Reporting Services has now been removed from the core installation similar to SQL Server Management Studio and SQL Server Data Tools. This is now a more light weight installation and it needs to be downloaded separately, just like Power BI Report Server.

image

So we see that now for Machine Learning there is also Python support and that SSIS has new scale out capabilities. In addition to this Reporting Services – Native installation has been removed (and no more SharePoint integration!).

image

New Consent to Install Python, similar to the experience we have seen with Microsoft R Open.

image

image

Everything successfully installed that we selected!

Install SQL Server Management Studio 17.0

image

image

image

The new SSMS 17.0 splash screen.

image

Build Version for SQL 2017 CTP2.1 – 14.0.600.250

Installing Power BI Report Server

This is available for customers that purchase Power BI Premium or have SQL Server Enterprise Edition with Software Assurance.

image

image

image

image

image

image

Once you have completed the install then you will need to use the Report Service Configuration Manager to connect and complete the setup tasks – creating database, establishing the web service and web portal URL, etc.

image

What we see here is that it has created a named instance for the Power BI Report Server called ‘PBIRS’.

In reviewing the new tables that are part of this installation I noticed three new tables: CleanupLock, Comments, and ProductInfoHistory. The first two tables are blank for now, but the ProductInfoHistory did have one record in it stating the build number for the current SKU installed.

image

I just noticed one other item, it appears in the configuration table you can control if users have the ability to export Power BI report data and this can also be accessed if you connect with SQL Server Management Studio and review the Advanced settings. The ability to control the Custom Visuals is also an option.

image

image

image

Successful configuration and you can see we have a similar color scheme like the Power BI Service for the default and using the new Power BI logo, very nice.

So the new Power BI Report Server is a superset of Report Server where it contain mobile reports, KPIs, paginated reports, and now Power BI reports. Plus you can upload Excel workbooks and we expect them to be viewable in the browser in the near future like Power BI reports.

Now to make Power BI reports that are supported in Power BI Report Server you need to use the Power BI Desktop (Report Server) version of the application, which is a separate install as well and can be installed side-by-side with an existing version of the Power BI Desktop applications.

The May 2017 release of Power BI Report Server will now support Power BI Custom Visuals, so that is really cool!

Install Power BI Desktop (Report Server)

image

image

image

image

image

image

image

From the start screen here you will see a new option to ‘Open from report server’ and some Reporting Services links to the forums, blog, and tutorials.

image

image

I can open up an existing report and then deploy this to the Power BI Report Server. Just remember that currently only Live SSAS connections are supported as a data source, no other data sources are supported for the time being, hopefully later this year we will see additional support so that we can use embedded models.

image

To publish to the Power BI Report Server simply select File—>Save as—>Power BI Report Server

image

Enter the Web Portal URL

image

Pick the location if you want to place it in an existing folder possibly and then click OK.

image

Then you can click on the ‘Take me there’ link and you will be able to view your report.

image

From here you can interact with the report, print it, different options for viewing the report (fit to width, fit to page, and actual size), open it back up in Power BI Desktop, explore the data, refresh the report, filter the data, add comments to the report (comments also support ability to add attachments as well).

Now if you are not able to install Power BI Report Server (and remember, if you can use this, you will still need additional Power BI Pro licenses for the report authors and users that want to export the reports to say PowerPoint or CSV, sorry no Excel option yet…) you can install the regular Reporting Services.

Install Reporting Services

Nothing too different from the Power BI Report Server installation.

image

image

image

image

image

image

image

This time the named instance that tool creates is called ‘SSRS’.

After the install you can go into the Reporting Services Configuration Manager and complete the configuration, just like in versions past.

So that is it for now, hope you enjoyed the quick walkthrough experience of the new SQL Server 2017 installation!

Posted in Power BI, Reporting Services, SQL Server | Tagged: , , | 1 Comment »

On-Premises Power BI VOL. 2

Posted by denglishbi on May 5, 2017

Here we go again, round 2 (or should I say Vol. 2). I am back to discuss how I feel (and I am sure quite a few others do as well) about the new information we received this week on Microsoft Power BI on-premises. You can check out my first blog post regarding this here – On-Premises Power BI–A Microsoft Story.

All of the opinions and information that I state in this post here is simply my personal experiences as well as a few thoughts that I just wanted to post. Oh and by the way, I am not a graphic artist by any means, so please don’t slam me for my mocked up image below. I love movies, so just wanted to put this quick little graphic together, I hope you like it.

image

What we have learned this week is that Microsoft will provide customers two options to get the on-premises Power BI functionality that will be part of what has now been called Power BI Report Server (this is actually a superset of Report Server – so includes all Report Server functionality plus Power BI). Option 1 is for organizations to go with the new Power BI Premium option. So basically along with the Power BI Service cloud functionality, Microsoft is providing customers the on-premises Power BI Report Server which will include a SQL Server Technology License that you can use to install Power BI Report Server, database engine, and Analysis Services (SSAS) on a single server. The SSAS will provide users the ability to create the data models for the live SSAS connections that Power BI on-premises will initially support. Basically this option will cost an organization roughly $5k/month at minimum based on the calculator (so we are talking at least $60k/year).

Option 2 then is for customers that purchase SQL Server Enterprise Edition (EE) along with Software Assurance (SA). If you have this then you will be entitled to Power BI Report Server, but there is a catch, read all of the fine print in the post (particularly the bold text).

Power BI Report Server is an on-premises server that enables Power BI Pro users to publish Power BI reports and distribute them broadly across the enterprise, without requiring report consumers to be licensed individually per user.

So what this means is that even though you just invested $XXX,XXX six figures or possibly more for your SQL Server EE + SA you will also need to pay a $10/month subscription fee for each Power BI Desktop report author that wants to publish reports to the Power BI Report Server. But wait, there is more! If consumers of the report want to export reports to say PowerPoint or Excel (and come on, how many users don’t want to export reports…), they will also need Power BI Pro licenses.  Granted the text might not call that out specifically, but that is what is also meant by “distribute”.  What most users will see is just the following in the post:

without requiring report consumers to be licensed individually per user

So that statement is not necessarily true from what I have been told, if a user wants to export a report, that will cost each user $10/month subscription because they will require a Power BI Pro license.

Now I don’t believe technically Microsoft will be able to enforce and track the Power BI Pro license for on-premises to enable / disable features such as publishing and export, but if you were ever audited then that could come back to bite you if you didn’t purchase them and are using this functionality.

How do I feel about this…. I am not happy by any means. I don’t understand this at all to try and nickel and dime customers of SQL EE + SA that want to leverage the Power BI on-premises. First Power BI wasn’t made available to SQL 2016 customers and has been pushed (or at least we thought) into SQL 2017 (formerly known as vNext). Now we are finding out that the Technical Previews of Reporting Services we have been using with Power BI functionality is not going to be made available to SQL 2017 customers unless you purchase EE + SA. But even if you do purchase that you also have to buy Power BI Pro subscriptions!!!

Hmmm…. I am trying to draw a comparison here of how I see this being played out.

image

Do you see where I am going with this? Can you draw any similarities here? T-Mobile vs Sprint?

So for my company we already have on-premises Power BI and we have been using this now for almost a year. This is because we own BI Office (Pyramid Analytics). For BI Office on-premises Power BI is provided and supported and there are no hidden fees, taxes, charges, costs, etc. associated with the product to get this functionality. All we need to do is pay our yearly maintenance agreement (and that is like 60% less than what we would need to pay for Power BI Premium). And in addition to Power BI on-premises we also get all of the BI Office features like dashboards, R integration, and much more! I can actually incorporate BI Office reports, SSRS reports, Power BI reports, and more all on a single dashboard page and they can all interact with each other!!! How cool is that? Kind of reminds me of PerformancePoint, but this actually works and is much easier to configure!

I am truly disappointed in the decision that has been made here for Microsoft customers. We have been working on our new BI roadmap for the Power BI the past two years, had a delay in the Power BI on-premises release, and now we need to possibly payout even more money. That is confusing and frustrating for customers (and champions / advocates of Microsoft BI like myself) and is definitely going to bite Microsoft. Enough with the games Microsoft, have we not been patient enough? I am sorry to say that now we are most likely not going down this path and I am extremely upset about this because I have been wanting and pushing for this capability. So for us we are going back to the BI roadmap and looking at Plan B and focusing our attention on the un-carrier.

Posted in Power BI, Reporting Services | Tagged: , , | 21 Comments »

Power BI Wide World Importers Demo Walk-Through

Posted by denglishbi on March 28, 2017

Last August at the MN Microsoft Power BI User Group I presented a walk-through of Power BI based on the new SQL Server 2016 sample database Wide World Importers.  I thought I had done a follow up post to share the content, but I believe I just posted this on the LinkedIn Group and possibly on Twitter and it might have been posted on the old user group site.

image

image

I apologize for not posting this on my blog as well for reference.  All of the files for the walk-through are available out on my OneDrive and this includes the presentation slides, Power BI sample files, instructions, logo, and DAX calculations. You can download the files individually or simply download the single zip file.

image

In this example I walk through the process of getting the data, building out the data model, and then performing the report development with the visualizations.

Here is a screenshot of what the final output looked like:

image

Sorry for the delay on posting this content and I hope you find it useful. If you like the example let me know and I can include some additional updates to this material and build it out a bit more.

Thanks and enjoy!

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

Analyzing Healthcare Open Data with Power BI

Posted by denglishbi on March 24, 2017

Back in December I presented the topic Analyzing Healthcare Open Data with Power BI at the PASS BA Marathon. I want to apologize for not posting this follow up post sooner, December was just a busy month with the holidays, vacations, and after that I just kept putting this off. Well no more, here is the follow up finally. In addition to this post I have also posted the Power BI example I prepared into the Data Stories Gallery.

Here is a screenshot of the demo that I presented which was based on the World Health Organization world wide expenditure data

image

The presentation material and a recording of the presentation is available on the PASS web site here – Analyzing Healthcare Open Data.

Just a heads up that during the presentation I lost my Internet wifi signal but it was only for a couple of minutes and I was able to get reconnected and resume the content from where I left off.

Here is a list of some of the Healthcare Open Data sites that I shared:

I would have liked to have spent more time on reviewing some of the sites listed above to showcase some of the data that is freely available, but with the technical difficulty I had to skip over that to allow time for the Power BI demo that I had planned and to allow time for Q&A. In the demo I showed some of the new capabilities in Power BI that were released such as the dropdown slicer option, forecasting, clustering, R visuals, Top N filtering, and a few more.

I will plan on revising the content and doing an updated version that hopefully I can present for the PASS Business Analytics Virtual Group later this year. There were a bunch of great questions asked and I am appreciative for the feedback that was provided from all of the attendees. I am glad people were able to walk away with a few nuggets of information regarding Healthcare Open Data and Power BI capabilities.

Posted in Power BI | Tagged: , , , , | 1 Comment »

On-Premises Power BI–A Microsoft Story

Posted by denglishbi on November 18, 2016

Just a few warnings, backgrounds, and heads up before a do a quick status update here on the current Power BI on-premises situation.  If you do not know me or haven’t met me before, I am a huge Microsoft fan and always have been and will continue to be. I have run every version of the desktop OS since Windows 3.1 as well as Office since version 2.0 and SQL Server since 6.5.  I have owned the three versions of the Zune with the original brick, nano, and touch. I was an early adopter of the Windows phone with the Samsung Blackjack and not until very recently have I made the switch to Android (but I still have a Windows Phone with Win Phone 10 running to test), but that is because of Microsoft’s new vision and direction where their software can run on any device (even on iOS, crazy).  I will always be a fan of Microsoft, nothing will ever change this.

All of the opinions and information that I state in this post here is simply my personal experiences as well as a few thoughts that I just wanted to post. Oh and by the way, I am not a graphic artist by any means, so please don’t slam me for my mocked up image below. I love movies, so just wanted to put this quick little graphic together, I hope you like it.

OnPremisesPowerBI_MicrosoftStory

So let’s begin the story.

Last year, back when Power BI went GA in July 2015 there was a pretty significant announcement made. Microsoft and Pyramid Analytics had formed a strategic alliance and this is what helped Microsoft deliver the Power BI Desktop application so quickly. If you are not familiar with the Pyramid Analytics product offering, BI Office, but you might be familiar with a former product that Microsoft acquired, ProClarity, they are for the most part the same thing. An outstanding tool for doing reports, dashboards, and self-service models with Analysis Services.

Along with the announcement and the release there was information that at some point Power BI Desktop users that owned BI Office would be able to publish their reports to a Pyramid Analytics Server. I was so excited about this announcement that I immediately downloaded, installed, called my Pyramid Analytics sales person, and did a blog post – Power BI Desktop Install Experience & Publish Feature!

Well needless to say it took some time for the Publish feature to be actually turned on and the new version of BI Office to be available, so flash forward to basically one year later from the GA announcement and Pyramid Analytics Launches On-Premises BI for Power BI Desktop. Once again I downloaded the new version of Power BI Desktop and I then had to wait for the new version / update for the BI Office.  Once I had everything installed I was able to quickly test out and use Power BI reports on-premises, very exciting!

Example of a Power BI Desktop report in BI Office:

image

A few things to note about the Power BI and BI Office functionality:

– the reports have to be created with live connections to an Analysis Services database

– custom visualizations are not supported

– Power BI report items and reports can easily be integrated into BI Office dashboards (storyboards)

– the reports can be modified and viewed in the browser, but they cannot be saved, would need to download and publish to update

– Power BI published reports can be secured through BI Office

– Power BI reports can easily have the data sources changed once published in BI Office if migrating content between environments

 

So this is all good.  Now in the meantime, pretty much everything we are hearing from Microsoft with regards to the roadmap for Reporting Services is that all reports will be able to run in the new SQL Server 2016 Web Portal. This means mobile, paginated, Power BI, as well as even Excel workbooks. This has been talked about and discussed since October 2015. We were a little uncertain about Excel, but with the recent announcements with regards to SharePoint-Integrated Mode going away, we now know that Excel workbooks will be viewable in the Web Portal as well.

The excitement and anticipation for Power BI coming to on-premises in Reporting Services has been building up all year and finally it was demoed at Microsoft Ignite (click link to watch video) and PASS Summit as well as a Technical Preview in Azure being released to test drive. Everyone including me is just biting at the chops to get our hands on this and now it is available. Everything looks great and now the waiting game to see how we can get this new functionality.

Well I was not able to physically attend the PASS Summit this year (I watched some of the keynote and sessions that were streamed live), but apparently it was stated at the What’s New and What’s Next session that Power BI on-premises was not coming to SQL Server 2016 in an update or service pack. This new functionality was in development and would be made available in SQL Server v.Next only.

What?!? Are you serious???? I know, my exact thoughts and I just found this information out YESTERDAY!?!  I was completely baffled, slightly upset, and extremely confused. I followed up on this since I wasn’t able to attend or see the session yet from PASS Summit and it has been confirmed by internal Microsoft resources. The information is apparently public knowledge, no secret, and it has never truly been communicated that Power BI would be coming to SQL Server 2016.  The only thing communicated, but rather slyly, was that Power BI functionality would be coming in the future and was being worked on. But everything we have been seeing has led us to believe that it would be made available in SQL Server 2016, but now sadly that is not the case. I sure hope that customers that purchased SQL Server 2016 in anticipation of this also purchased the product with Software Assurance. Otherwise they could have been misguided by some of the Microsoft sales and messaging.

 

As of right now, the functionality that we are expecting to see in the Power BI / SSRS on-premises story is very similar to what we (or I should at least say I) have with BI Office.

– the reports have to be created with live connections to an Analysis Services database

– custom visuals are not supported

The main exception though, at least currently, is that there will be no dashboard functionality. That is kind of the secret sauce with BI Office, very slick.

 

You can continue to stay up-to-date with some of the status with Power BI being made available for on-premises by checking out the Power BI Ideas forums. In the near future we should expect to see a blog posting by the SSRS team to provide us the true communications about the Power BI and SQL Server story, so stay tuned and subscribe to their blog feed.

Well I will wrap this post up. I am not by any means upset with the SQL team, it is complete opposite. I love all of the work they do and amazed at what is being worked on. Just seems like we need better and more accurate communication so that we can keep our expectations in check. This will help the community, MVPs, and customers all plan accordingly and make sure everyone is on the same page.

Posted in Power BI | Tagged: , , | 7 Comments »

Power BI–Marvel vs DC Movie Analysis

Posted by denglishbi on February 24, 2016

Back in January Microsoft held it’s second community Power BI contest for Best Report Contest. After a few weeks of brainstorming and sifting through data I finally came to a conclusion that I wanted to do some analysis on a couple of items that I like, movies and super heroes, so why not take a look at how Marvel and DC movies have faired over the years:)

This is an interactive report comparing the results of Marvel versus DC movie information with regards to the number of movies, adjusted worldwide gross box office earnings, and includes IMDb ratings. You can get a feel for the shift from the 1960’s through the 1990’s as DC dominated the market and then Marvel stepped in and has dominated the box office since.

Will DC regain its focus and dominance that it once had? Batman v Superman could be a start and that is where its roots are from and then later this year with Suicide Squad it can gain some more momentum. Maybe next year with Justice League and Wonder Woman it will find new life and something to compete with Avengers, Guardians of the Galaxy, and X-Men. You will see in the insights that keeping the movie rating at a PG-13 level is key, these movies will average 4x more than an R rated movie will gross.

Sorry Marvel, Deadpool is not looking good for earnings, but you will make up for it with Captain America Civil War which should make 5x as much! (this comment was made on Jan 29, prior to Deadpool opening up at the box office)

image

This report showcases and highlights a bunch of features in Power BI, integrating multiple data sources, calling a web service, cards, charts, drill down, custom visuals, slicers, color definition, back ground images, and more!

Here were some of the insights I discovered while exploring this data:

– From 1960 to 1990 DC dominated making 86% of movies and grossed $4.54B more than Marvel movies

– From 2000+ Marvel has made 80% of the movies and grossed $15B more than DC

– R rated movies average $446M less than PG-13 movies

– Marvel movies average $60 to $90M more than DC movies

– Marvel/DC: stick to Avengers and Justice League characters! They make more money;)

Another insight that I was curious about was why Iron Man 3 was so popular and grossed so much money.  I mentioned this to my wife and she quickly asked, “Well did it come out after the Avengers movie?”  I switched back to my Power BI report…well “Yes it did, that is brilliant!” So follow up the movies with more spinoffs, kind of like DC will do next year with Wonder Woman.

Here is my video showcasing my Power BI Best Report Contest Entry, unfortunately I did not make it into the Top 10 for the voting process.

If you want to check out the Top 10 finalists they are all posted here and I believe at some point they will be adding all of the entries into the public showcase area as well.

And I guess Deadpool is defying the odds and proving everyone wrong, after two weeks in the box office it has already beat out the best X-Men movie here in the US and has grossed nearly $500M globally. Not bad for a movie they spent $58M on to make, plus it was banned in China.  So I guess maybe making the next Wolverine move Rated R might work out okay.

After Less Than Two Weeks, Deadpool Is Already the Biggest X-Men Movie Ever

After Less Than Two Weeks, Deadpool Is Already the Biggest X-Men Movie Ever

As of February 22, Deadpool has grossed almost $236 million in the United States, according to Box Office Mojo. During its entire run, the previous #1 X-film, X-Men: The Last Stand, grossed just over $234 million.

I hope you enjoyed this, now I have to figure out what else to check out with Power BI!

Posted in Power BI | Tagged: , , | 4 Comments »

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!

Posted in Power BI | Tagged: | 3 Comments »

Power BI Desktop Install Experience & Publish Feature!

Posted by denglishbi on July 23, 2015

Tomorrow is the official Power BI GA release for the “new” 2.0 version of Power BI.  With the new release there will be a new version of Power BI Desktop application (formerly known as Power BI Designer).  This application is already available to download and here is the quick walkthrough of the install as well as some sample pics of a file that gets published to the PowerBI.com site!

Install Experience

image

image

image

image

image

image

Are you ready?

image

Awesome! Game on:)

image

Version: 2.25.4095.554

Publish

With the new release of the application you can publish to PowerBI.com in the cloud and soon to on-premise Pyramid Analytics Server (not available yet, but hopefully very soon, maybe next month).  Just announced today was the strategic allegiance and collaboration of Pyramid Analytics along with Microsoft.

image

Power BI Desktop file created from IMDb data.

image

File published to PowerBI.com

image

Very cool.  There are a ton of new features and functionality in the new application.  Way too many to cover here, so check out the Power BI team blog.

Here are some good articles and resources to check out:

Announcing Power BI general availability coming July 24th

Power BI team blog (great content, examples, articles, announcements)

Power BI Community site

Microsoft Power BI Support

Training Resources (provided by Jen Underwood)

Enjoy and I would love to hear your feedback!

Posted in Power BI | Tagged: | 4 Comments »

Power BI Preview–The Designer

Posted by denglishbi on April 9, 2015

We will continue exploring the ‘new’ Power BI Preview and in this post we will look at the Power BI Designer.  The Power BI Designer is the new download tool that you can get (for FREE) that provides the ability to get data, transform it, add calculated columns, merge, etc. (Power Query) and create reports (like Power View – but the Silverlight is gone! but features not completely identical yet, few more visualizations added though).

NOTE: The Power BI capabilities that have been added to Excel over the years with the add-ins and some are becoming native, are now transitioning into the designer to allow for more rapid releases and one tool versus the different add-in components.  And did I mention…it is FREE?!?

To get started you will first need to download and install the designer.  You can check out one of my previous posts on the walkthrough of installing and highlighting the features – Installing Power BI Designer Preview Experience.  You can download the designer from the downloads location on the Power BI site.  In this example I have the latest build that was made available at end of March 2015 – version 2.21.3975.261.

image

Once you have this downloaded and installed you will then launch the tool to get started.

image

On first launch you have the ability to access forums, the team blog, tutorials, and videos.  The team has done an outstanding job on creating lots of great content right out of the gate!

If you are familiar with Power Query then you will be very comfortable with the get data portion and creating the queries that can then be used to load the model to be used for report creation.

image

In this screenshot you will see that there has been a recent update and a new source is available for Google Analytics!  This was recently announced in the blog here – 7 New Updates to the Power BI Designer Preview (March 2015).

In this example I will go back to a previous one I did when I first covered Power Query (previously known as Data Explorer) – Installing Data Explorer Preview & Demo with IMDB Data.  I will try and recreate this solution with the new designer and you can see the comparison.  At the end I will note a few of the differences that I have come across so far.

First I will get the data, this time around I am going to get the core data from the IMDB website like I did previously, but then I will use a function to do a call and retrieve JSON from http://omdbapi.com.

Original site view on IMDB (click image to go to the actual site)

image

Initial query results getting data from the web

image

Function created that will then provide additional information about the movie such as genre, actors, awards, runtime, rating, plot, votes, poster url, etc.  In my initial example I did I used VBA code and parsed XML data, this time around I am doing this all with Power Query functionality.

image

Now combining this function call into a calculated column in the first query we end up with our complete data set.

image

We can then create our reports.

image

image

If you compare this to my original post from the Data Explorer you will notice that they are a tad different.

image

image

So some of the items that I came across in the new designer are the following (as of 4/9/2015):

– No image support in data model, not able to access the model yet to complete this setup, plus not able to include or embed images

– No ability to control the formatting of the numeric values as far as number of decimals, to include commas, currency

– No data card title support, this goes back to not being able to access the model and complete the setup

– Not able to resize columns and easily resize items such as data cards

– Not able to add data labels to visuals

– Not able to remove a sort on a table once it has been triggered (I accidently did this a couple of times, clicked on a column)

– No textbox at all which is possibly the #1 item used today in Power View

– No play axis support in scatter charts

– No color, styles, or theme support options to control color options

– Limited filter options (need to drag-and-drop to filters), no pinned filters, and just multi select checkboxes (so hope you don’t have a long list, numeric values do have some extra options)

Some of the things that you will see that have been added are the following:

– easily change visuals when item selected

– new visuals that were added such as – treemap, filled map, radial gauge, funnel, and combo charts (require same axis if you combine them)

– new relationship options to provide support out-of-the-box for many-to-many

Now once you have your data and reports created the next step is to save your file (pbix extension) and then if you go back to the Power BI site in the Get Data section you can upload your file to a dashboard (make sure you are in the dashboard you want this added or create a new one first).

image

Once you have the file uploaded you will see your reports and dataset as well. In the next post we will take a look at adding items to the dashboard and exploring the Q&A capabilities.

For a recap of previous posts check out the following:

Getting Started with the Power BI Preview

Power BI Preview Layout Overview

Power BI Preview–Getting the Data

If you liked the example and want to check out the file you can download it from here http://1drv.ms/1JqmwaF

NOTE: I have set the File Settings in this file to ignore Privacy levels, otherwise I was being prompted for each call to the web to get the images for the move posters that are being pulled into the model (ready for when the support for images and accessing the data model is available – thanks to Kasper for his help on this – Loading an image into the Model using Power Query).

Hopefully in a couple of weeks we will be able to expand on this example once we can access the data model, can’t wait!

Posted in Power BI | Tagged: | 3 Comments »