Dan English's BI Blog

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

Posts Tagged ‘msbi’

Microsoft Data Insights Summit 2017 Sessions Available On-Demand

Posted by denglishbi on June 14, 2017

If you missed out on attending or watching the live streaming event the last two days you can now watch the content on the MS Power BI YouTube channel.


And if you missed out on the 2016 event, those videos are also available.


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

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)


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 »

Voting is now live! Please vote for Power BI Contest

Posted by denglishbi on January 20, 2014

Voting is now open for the Power BI Contest, please go out and vote today!  You can vote from today, Jan 20, through next Thursday, Jan 30 (once per day).

If you go to my entry you can click below the video (big blue section that says ‘VOTE FOR THIS ENTRY’ between Previous and Next).

click here to vote


Don’t forget to vote once per day and appreciate any help you can do in sharing this with family, co-workers, and friends!

Thanks again for your support!

Posted in News and politics | Tagged: , , | Leave a Comment »

IMDb Analysis with Microsoft Power BI

Posted by denglishbi on January 15, 2014

The Microsoft Power BI Contest entry submission ended today and this morning I worked on my video and got my entry posted.  For my entry I ended up going with the Top 250 rated movies on IMDb.  Last year when I was testing out “Data Explorer” (now called Power Query) I did a blog posting that highlighted this set of data along with some Power View reports here – Installing Data Explorer Preview Demo with IMDb Data

Since that post, which was back in March 2013, the data on the web page has changed (ratings of course, but the data available).  Like now the vote counts were not included in the table on the web page.  Also, I was using an external site to get some XML data to reference for additional information about the movies, well that changed to, so I did get to freshen up the demo a bit.  I also had to prepare some additional items for the Office 365, Power BI, Q&A, and I did setup a Windows 8 Power BI demo.

For our entries we were limited to only 10 minutes, so by no means are these going to be an end-to-end instruction guide.  I didn’t go into a deep analysis of the data, what I was trying to demonstrate was the ability to go from a basic web page of data to a fully decked out analysis tool.  I used Excel 2013, Power Query, Power Pivot, Pivot Charts and Tables, Power View, Office 365, Power BI, and the latest Q&A, so I was trying to show off as much as I could and stay within the timeframe provided.  I also had a demo to show off the Windows 8 Power BI app, but I wasn’t sure the Camtasia video capture software would capture the app demo since it was running on my desktop (will need to test that).

Here is the entry on Facebook (view in full screen to get better resolution – also turn the volume up) – IMDb Analysis with Microsoft Power BI.

PowerBI Facebook Demo

Action Item (please help and vote – Jan 20 thru 30)

Starting next week on Monday, January 20 the entries will be open for public voting.  I would appreciate all of the support you could provide (please vote, and pass it on to co-workers, family, relatives, family, etc.).  The voting will run through Thursday, January 30.  Then 10 out of the 61 entries will move on the semi-finals!  So I need all of the support you can provide.  Thanks so much!

Posted in Data Explorer, Power BI, Power Query | Tagged: , , | 2 Comments »

Inside the Power View RDLX File

Posted by denglishbi on June 12, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

So if you have downloaded and started playing with the new SQL Server 2012, one of the new features you can use with SharePoint 2010 and Reporting Services is Power View.  Power View is a new self-service reporting tool developed by the Reporting Services team and it currently works against Tabular BI Semantic Models (so either PowerPivot files in SharePoint or Tabular SSAS databases).power view

Now when you create a new Power View report you might realize that it doesn’t create just an RDL file anymore, they are now RDLX files.  Theses files are really zip files, so just like PowerPivot XLSX files you can simply rename these files with a ZIP extension and explore the contents.

So what is inside?  Let’s take a quick peek:


Here I have renamed an RDLX file called Hotel Performance Dashboard that I downloaded from SharePoint with the ZIP extension and extracted the contents.  Inside nothing too interesting yet, but I see a folder called ‘Reports’.  Let’s take a look in there…


Now this looks a little more interesting and what do you know, an RDL file…what is in there?  I won’t paste the whole contents here, but I will give you a little screenshot of what the contents include:


Now this is what we are used to seeing, XML with DataSources, DataSets, etc.  We can even see the DAX that is generated and used to retrieve our data for the report!  Really nice.

Some other things that we will see in here are ReportSections:


More on these in another post that will be coming up soon.

So let’s go back to that ZIP folder and see what else might have been in there tucked away…I think I saw a folder called Metadata…


Cool, look at that, the snapshot images of our views in our Power View report file.

Okay, so that is a quick tour of the RDLX file, up next…Power View URL commands…coming really soon!

And by the way, if you haven’t heard, the new Power View book, Visualizing Data with Microsoft Power View has been released, so check it out and let me know what you think.  Not only does it include information about Power View, but also how to build Tabular BI Semantic Models, use DAX, how to build a BI Virtual Environment, and it is loaded with ‘Learn By Doing’ exercises and videos as well!


– Visualizing Data with Microsoft Power View (book) – http://tinyurl.com/powerviewbook

– Microsoft Business Intelligence http://tinyurl.com/msftbi

– Power View Overview http://tinyurl.com/mspowerview
– Power View Samples – 6 samples to test out http://tinyurl.com/msbitryit

Posted in Reporting Services, SharePoint | Tagged: , , | 5 Comments »

Now Available: Visualizing Data with Microsoft Power View

Posted by denglishbi on June 5, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

I am very excited and proud to announce that there is a brand new book available on the market and now in print – Visualizing Data with Microsoft Power View.  When I got home after work yesterday there was a box waiting for me and the books were inside.  It is hard to believe that just over a year and a half ago that I received the email from Brian Larson asking – “Ever want to be an author?”

Just got my #powerview book http://www.mhprofessional.com/product.php?isbn=0071780823 #msbi #SharePoint #sqlserver

Here is the outline for the book:

Part I – Power View

  • Chapter 1 – Getting Started
  • Chapter 2 – The Table Visualization
  • Chapter 3 – Additional Visualizations
  • Chapter 4 – Charting
  • Chapter 5 – Bringing Your Data to Life
  • Chapter 6 – Sharing Reports

Part II – Creating a BI Semantic Model (BISM)

  • Chapter 7 – BISM: Getting Started
  • Chapter 8 – Basic BI Semantic Model Design
  • Chapter 9 – BI Semantic Model: Additional Model Features
  • Chapter 10 – Using DAX Expressions
  • Chapter 11 – Deploying Tabular BI Semantic Models

Part III – Appendixes

  • Appendix A – Installing and Configuring the Sample Data
  • Appendix B – Creating a Virtual Learning Environment

The book is loaded with Learn By Doing exercises throughout the book and included with the book is a DVD that includes the sample data, project files, Power View reports, and videos of all of the exercises.

There will be eBook versions made available as well and there is even talks of an enhanced one that would be available in the iTunes Apple Store that would have the videos embedded throughout the chapters for viewing.

So what are you waiting for, check it out now and let us know what you think – Visualizing Data with Microsoft Power View.

Here is an example of one of the videos for a Learn By Doing exercise:

Posted in Business Intelligence, SQL Server | Tagged: , , , , | 18 Comments »

Microsoft BI VHD XII – SQL 2012

Posted by denglishbi on May 23, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

Update 5/25/2012: Just had someone reach out to me today in regards to a file size issue with the extract.  They were stating that it required 6PB instead of 60GB of space.  When doing the extract they were using the built-in extracting within Windows 7.  I had used WinRAR without any issues.  They downloaded that tool and successfully extracted the files.  Just wanted to make mention of this in case anyone else runs into an issue like this.

After I heard that the new version of the Microsoft Business Intelligence VHD was available I downloaded it to check it out.  The major change was that it now had the RTM release of SQL Server 2012 all setup on it.  You can check out the new demo solution in the catalog here http://mssalesdemos.com and you can download it from here http://www.microsoft.com/betaexperience/pd/BIVHD/enus/.

You can download the VHD Hyper-V image, the zip file is 21.6GB and when you extract out the contents you will need approximately 58GB of space (and you will also want some free space as well because the image is setup to grow out to 250GB if needed).  The recommended memory to run this is 8GB, but unfortunately I don’t have that available, so I tested it out with only around 4.5GB of RAM and devoted 2 processors to try and run it with VirtualBox.

The password for the image is the typical pass@word1 for the contoso\Administrator user.  The name of the server is SQLIMAGE2012.

Now since I am using VirtualBox I disabled all of the Hyper-V services along with some additional ones that are not needed to save on some RAM like Remote Desktop services and Windows Defender.  I then took a look at the SQL Services setup and … grrrr … noticed that there are just a few, like three instances.  By this I mean there are three instances of the DB engine (which are definitely not needed), none of the SQL Agent services are running, all three of the SSAS services are running which is good, and they also have two instances of SSRS running (no idea why, similar to the DB engine setup).


So here is the breakdown on the three DB engine instances, the default is for all of the sample databases for the demos along with the MDS database:


The multidimensional instance is where the DQS databases reside:


And the PowerPivot instance is where the SharePoint databases reside:


Wow, let’s see how they are doing on memory with all three of them running:


If you are not going to be doing any DQS you can safely shutdown the Multidimensional instance until you are ready to do so (free up a little more memory).  In looking at the database names I don’t see the SSISDB catalog, so I guess there are probably no demos of any Integration Services.

So along with this I also have to watch out for the other SQL services like Analysis Services (SSAS) that needs a bit of memory:


The top one is the Multidimensional instance, the second one is the PowerPivot, and the third instance (the default) is Tabular SSAS.  So the majority of the data and memory consumption is happening in the PowerPivot and Tabular databases which makes since with the xVelocity in-memory database technology at work here.

Then don’t forget about the two Native instances of SSRS running:


Probably don’t need either of these running and by looking at the database names I only see the databases setup for the new SharePoint Integrated mode setup as the Shared Service Application.  So you could safely shut both of these down for the time being as well.

Well how about we take a look at the SharePoint setup and some of the demos.  In attempting to go to the SQL Image 2012 home page (http://intranet.contoso.com/SitePages/Home.aspx) I got that the page could not be displayed.  So I went to the localhost or server name page instead and it worked just fine (http://sqlimage2012/SitePages/Home.aspx).  Without troubleshooting this further I went with the server name setup and had to modify my connection and URL references for the Power View reports.


We can see a few minor typos with the Power View name not including the space.  The scripts for each of the demos is provided in the Shared Documents library:


All of the images that will be used for the demos have been setup on a separate website instead of being stored in SharePoint:


Here are the examples of the Power View reports:






And there is a nice home page setup for the Contoso Telco company as well (this uses the Multidimensional instance of SSAS, so if you shut that down you will need to start it back up).  You might need to refresh it a few times to get the site and data warmed up a bit so that everything gets displayed:


Well that is the quick overview of the new XII image.  There is plenty more and I will keep exploring and leave some for you to check out as well, enjoy!

Posted in Business Intelligence, SQL Server | Tagged: , | 25 Comments »

Using Styles in Microsoft Power View

Posted by denglishbi on April 19, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

The other night I was working on some final chapter reviews of our upcoming book Visualizing Data with Microsoft Power View and I stumbled across something new in the RTM release of Microsoft Power View.  A new feature called Styles that provides theming capabilities with the views in the report files. Let’s take a quick look at where this new feature is located and what this can do to your views.


Now when you launch Power View in your browser you will see a new tab in the ribbon called Styles next to the Home tab.  When you click on this you will see that you are provided 8 different accent color themes to choose from as shown above.

So what do these look like?

Accent 1


Accent 2


Accent 3

(same as Accent 2 with exception of 1st color)


Accent 4


Accent 5


Accent 6

(same as Accent 5 with exception of 1st color)


Accent 7

(same as Accent 1 with slightly different blue color)


Accent 8


Now the one thing to note is that the Styles will change the colors for the entire view, so this includes all of the items (tables, slicers, etc.), not just one particular visualization.


The other thing to note here is that if you have multiple views in your report file that each view can have its own accent color setup.


So when designing your reports you have some options for colors to select from.  Nothing custom like you have available in Reporting Services reports, but at least you have something unlike say PerformancePoint.

So that is a quick intro and overview of the new Styles option in Power View, hope you enjoyed the quick tour.

If you are looking for more information and guidance don’t forget to check out our book that will be coming out very soon – Visualizing Data with Microsoft Power View.

Posted in Reporting Services, SharePoint | Tagged: , , | 4 Comments »

New book: Visualizing Data with Microsoft Power View

Posted by denglishbi on April 17, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

There is a brand new book coming to a bookstore near you – Visualizing Data with Microsoft Power View

I am very proud to announce that I am one of the co-authors along with Brian Larson, Mark Davis, and Paul Purington.  We are all from Superior Consulting Services🙂


This book is dedicated to the new self-service report authoring tool Power View that is provided in SharePoint 2010 as part of the new SQL Server Reporting Services 2012 integration. 

The book is split into two parts, the first focusing on Power View and the second on Tabular BI Semantic Model development, DAX, deployment, and management.

Included with the book is a DVD that will provide our sample data, supporting files, Power View report samples, Tabular model project files, and over 50 video demonstrations!

The book will be available in June in time for North America TechEd.  We are currently on the home stretch of the book doing our final reviews and we are very excited to see all of our hard work come together.

I want to thank Brian Larson and McGraw-Hill Publishing for getting this put together, including me, and making this possible.

Posted in Business Intelligence, SQL Server | Tagged: , , | Leave a Comment »

Microsoft Business Intelligence (BI) Resources

Posted by denglishbi on October 13, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

UPDATE (10/20/2011): Added an updated link for the PowerPivot TechCenter – http://technet.microsoft.com/en-us/bi/ff604673.aspx.

Attended a session today on Power View and received a USB drive with some good resources on it. I thought I would share one that contained a bunch of links:


Microsoft BI Resources

PowerPivot Hands on Demo and Download: http://powerpivot.com
PowerPivot (Self-Service BI):
SSRS (Operational & Managed Reporting):
SSRS Report Builder (Ad Hoc Reporting):
PerformancePoint Services (Dashboards, Scorecards, Advanced Analytic Charts & Grids):
Excel (Personal Productivity):
SSAS (Data Mining):
Forums to use for researching / posting questions

I also have a list of the Microsoft BI portals, but I think most if not all are possibly listed above here already – Microsoft Business Intelligence Portal Page References.

Posted in Business Intelligence | Tagged: , , | 6 Comments »