Dan English's BI Blog

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

Archive for the ‘SQL Server’ Category

Minneapolis Microsoft Data Amp Event–Listening Party Apr 19

Posted by denglishbi on March 30, 2017

Please join me and Paul Timmerman next month Wednesday, April 19 in Edina, MN for the Microsoft Data Amp Live Streaming Event.


I have reserved a room from 9:30AM to 12PM. The event will start at 10AM.

Seating is limited, so register soon before it fills up.

Register now –> http://bit.ly/mplsdataamp

There should hopefully be some swag available as well to those that attend!

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

Getting Started with SQL Server 2016 CTP3 in Azure

Posted by denglishbi on November 11, 2015

If you have an Azure account (possibly through your MSDN subscription) here is the easiest way to get up and running with SQL Server 2016.

First go to the Azure Portal – http://portal.azure.com

Search and find the SQL Server 2016 CTP3 in the Data and Analytics Marketplace in Azure.


Create the Virtual Machine.


Start the Virtual Machine once it is created and then once it is running Connect with RDP.

Once it is started up you can search and add items to Start as well as Taskbar such as SQL Server Management Studio, SQL Server Configuration Manager, SQL Server Reporting Services Configuration Manager, etc.


Turn off the IE Enhanced Security for Administrators.


Download and install the latest version of SSDT for SQL Server – http://msdn.microsoft.com/en-us/library/mt204009.aspx


SSDT for Visual Studio 2015 for SQL Server 2016 now includes the database projects as well as the BI project templates, so you no longer need to download and install two separate installs.  If you already have Visual Studio 2015 installed then a smaller download (install) will be used to add in the additional template projects.


The installer will download and then install the components. This will take approximately 10 to 15 minutes on the Virtual Machine.

Then add SQL Server Data Tools 2015 to the Start and/or Taskbar for easy access.


Review the SQL Server Components installed.


Based on this as well as reviewing the summary setup log located here – C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\ – we can see that the Database Engine was installed along with Data Quality Services, Analysis Services (multidimensional), Reporting Services (Native), Integration Services, Management Tools, and Master Data Services.

We still need to install Analysis Services (Tabular), Advanced Analytics Extensions, and also complete the configuration of Reporting Services.

So to complete the install components we will access the setup in the following location – C:\SQLServer_13.0_Full\.  First we will install the Advanced Analytics Extensions. This is part of the process so that we can use SQL Server R Services.



Additional steps are required to complete and start using the Advanced Analytics Extensions in SQL Server.


Use the link provided in the Warning to access the complete steps to getting R services completed on the server so that you can start utilizing R code with SQL Server.

Rule Check Result
Rule "Post-installation steps are required for Advanced Analytics Extensions" generated a warning.

The feature Advanced Analytics Extensions requires some post-installation steps after completing SQL Server setup. Please follow the steps outlined in the link http://go.microsoft.com/fwlink/?LinkId=626645 (Ctrl-C to copy)

This installation for the extensions will also install the SQL Server Launchpad.

Next we will run the installer again and get an Analysis Services Tabular instance installed. For this we will need to install a named instance since multidimensional SSAS was used for the default.





Once that is done then run the Reporting Services Configuration Manager to complete the SSRS Native setup.


Once connected go to the Web Services URL and click Apply button. Then go to the Database page and use the Change Database option to create a new report server database. Next go to the Report Manager URL and click Apply.

Other items that you might want to do are setup Execution Account, backup Encryption Keys, setup Subscription Settings for file share access, and lastly Power BI Integration!


We will setup the Power BI integration so that we can use the new Pin to Power BI capabilities when we create our Reporting Services reports so that we can pin and share content in our Power BI dashboards!

The last item that we have to do is go to Report Manager and we can install the new Report Builder.


You will see that Report Builder has a new theme as well as the new Chart Types – Treemap and Sunburst which are great for displaying hierarchical data.


There you go, up and running and ready to explore some of the new SQL Server 2016 CTP3 capabilities.

More resources on new features in SQL Server 2016 CTP3:


Posted in SQL Server | Tagged: | 3 Comments »

SQLSaturday 287 Madison Follow Up

Posted by denglishbi on April 30, 2014

Last month I had the pleasure of attending my first SQLSaturday event outside of Minnesota, I went to Madison, Wisconsin.  Not only did I get to attend and present at the main SQLSaturday event, I was also fortunate to be able to put on an all-day pre-conference event the Friday before – Self-Service BI to the Max.

So Thursday afternoon I headed out on my four hour road trip into the neighboring state to visit Madison (for my first time).


It started out raining (only one accident being reported by TomTom) and eventually turned to snow as I got into Wisconsin.  The roads were not too bad, but the two lane 94 driving in was a tad fun with all of the semi drivers;)  The drive was a tad long and driving solo makes it a bit boring.  I tried to check out a few of the water parks along the road and eventually made it into Madison and found the hotel, which was right next to the pre-conference event (walking distance).

I did some final preparation for the pre-con and drove around town a bit (roads are crazy, need a GPS for sure) and got confused with the medians that divided the left turn lanes in the streets (found that a odd and confusing).  The next day was the all-day pre-con and I got spiffed up in the brand new Power BI shirt:)

WP_20140419 1

The event went great, the location was nice and the 25 people in the room were great.  We went over the Power BI content (my pre-con was Self-Service BI to the Max), went over some demos, answered questions, and did some hands-on labs.  A fairly long day, but it was a great time meeting a new crowd and talking about Microsoft BI! (photo taken by Jes Borland – @grrl_geek)

Embedded image permalink

After the long day I headed over to the main SQLSaturday event location to check it out as well as stuff a few event bags (350!).  I also met a bunch of new people as well as SQL family that I knew by name, just have never met in-person.  One of the new people I met was Pat Wright (@SQLAsylum, a.k.a. PASS photographer) who took a bunch of awesome photos and a few down below that I am including and linking back to with his permission, thanks!


Once we were done with the bags is was off to find something a cool to drink (thanks to Ted Krueger, @onpnt, he tracked down a Mt. Dew to tide me over for a bit).  We headed over to Erin’s Snug Irish Pub for a little ale.

WP_20140419 4WP_20140419 2

Then it was over to the speaker’s dinner, non-stop action.

That was about all I could do on Friday, I was wiped out.  Time to get some shut eye and get ready for Saturday.  On Saturday morning I headed out with Dustin Mueller (@sqlcheesecake), Mike Donnelly (@sqlmd), and Tracy McKibben (@RealSQLGuy) for a little SQL run (just 4 miles).  It was a tad chilly on the first lap (remember, this was in March), but things were fine on the second one.  Unfortunately Jes Borland (@grrl_geek) was unable to join us because she was still recovering from an injury (she was with us in spirit and talk though).

Once I got back to the room after the run I got cleaned up and got a text from Ted inquiring if I could give a second BI presentation because they had a cancellation.  There were only a handful of BI sessions for the event, so they wanted to have another BI one to fill in.  I agreed and arranged another slide deck and demos for the second presentation (good thing I did an all-day pre-con the day before).

For the SQLSaturday event I did two presentations, fie the first one the room was a tad warm and it was standing room only, jam packed (awesome)!  I spoke on Delivering Self-Service BI with Power (new Excel 2013 features, Power Pivot, and Power View).  Then for my second presentation after lunch I spoke on “Getting Down with Power Query and Power Map”.

I was very pleased and glad to receive all of the evaluations and feedback.  On a scale of 1 to 5 I received good scores and the feedback was very helpful, lots of content to cover.


All-in-all I had a wonderful time, enjoyed my first out-of-state SQLSaturday event, looking forward to a few more.  I want to thank Ted and Jes for reaching out to me and Gina Meronek (@equerystrian) and the gang for putting on an awesome event, very well organized and an extremely helpful group over in WI.  Thanks for having me out and looking forward to returning next year:)
Read the rest of this entry »

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

Final Call for the MN SQL Saturday 238 Speakers

Posted by denglishbi on August 10, 2013

This is the final call to submit your sessions for the Minnesota SQL Saturday event coming up in October. The speaker abstracts can be submitted through Tuesday, September 13 and then the fun begins to start selecting the sessions and setting up the official schedule.image

So far on 47 sessions have been submitted by 27 different speakers and 12 of those submitted more than one session.

If we do a breakdown of the current list that has been submitted it looks like the following so far:


Just a word of warning, if you have only submitted 1 session you might want to review the list that has already been submitted.  If your topic is a duplicate the chances of you making the final cut might be tough, particularly if the duplicate submission speaker only submitted 1 as well:)

So if you were holding out to submit a session to speak your time is up, time to submit.  If you already submitted, time to review the full list and re-evaluate your submission(s) to secure your spot.

Call for Speakers

Submitted Sessions

Good luck to all the speakers that submitted and can’t wait for the big event.

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

PASSMN July 2013 Meeting

Posted by denglishbi on July 2, 2013

The next Minnesota SQL Server User Group meeting is on Tuesday, July 23. This month SQL Server and Pragmatic Works founder Brian Knight is in town and will present. 

Brian Knight is in town doing his two-day SSIS Master training course, something else to check out if you are available – http://pragmaticworks.com/LearningCenter/Workshops/DataIntegration/7-23-2013_MinneapolisMasterSSISWorkshop.

Be sure to register so that your name badge will be available for you at the Microsoft Technology Center when you arrive.Minnesota SQL Server Users Group

The sponsor for this month’s meeting is Pragmatic Works.

Pragmatic Works

Location: 3601 West 76th Street, Suite 600 Edina, MN 55437

Live Meeting:


  • 5:00-5:30 : Announcements / Registration / hospitality / networking
  • 5:30-6:45 : Data Visualization Moves, by Brian Knight
  • 6:45-7:00 : Prize giveaways

Please click here for meeting details and to RSVP for the event


Ninja Data Visualization Moves by Brian Knight

Abstract: PowerPivot and Power View brings with it some amazing business intelligence and data visualization enhancements. Watch how you can improve data cleansing with Excel 2013 and some of the new features integrated into Excel and the other Office tools to visualize data better than ever before. See how the new PowerPivot and Power View features in Excel 2013 take your data to the next level of visualization. We’ll also show how you can visualize map data with GeoFlow and tie disparate data together with Data Explorer.

About Brian: Brian Knight, SQL Server MVP, MCITP, MCSE, MCDBA, is the owner and founder of Pragmatic Works. He is also the co-founder of SQLServerCentral.com, BIDN.com and SQLShare.com. He ran the local SQL Server user group in Jacksonville (JSSUG). Brian is a contributing columnist at several technical magazines and does regular webcasts at PragmaticWorks.com. He is the author of more than a dozen SQL Server books. Brian has spoken at conferences like PASS, SQL Connections and TechEd and many Code Camps. His blog can be found at http://www.bidn.com.

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

SQLFriends Lunch–Dec 14

Posted by denglishbi on October 3, 2012

UPDATE (10/19/2012): The date for this event has changed from Oct 19 to Dec 14. The website should be updated soon to reflect this as well.

The next SQLFriends lunch event is coming up and it will be right here in Edina, MN.  This month I will be the guest as well:)image  This will take place at the Edina Grill:

From Fish & Chips to our award winning Turkey Burger, Banana Waffle and Hash Browns, you’re bound to find something you’ll love at the Edina Grill!

At the Edina Grill we serve fresh, often organic, always made from scratch fare – offering breakfast, lunch and dinner seven days a week, morning, noon and night.

imageI have not had a chance to attend one of these lunch events yet, but I can only guess it will be a fun time hanging out with the SQL family.  I will be available during the lunch time to talk about topics such as consulting, Microsoft MVP program, blogging, tweeting, running a user group, organizing events, Microsoft business intelligence, SQL Server, the future of business intelligence, authoring a book, and any other off topics that you might want to discuss such as family life, coaching, water skiing, etc. 

Here are the details of the event:

Date: Friday, December 14, 2012

Time: 11:30 am – 1:30 pm

Location: Edina Grill, 5028 France Ave S, Edina MN, 55424

Guest: Dan English

If you are available to attend it would be great to see you and hangout, head on over and register now – SQLFriends Lunch 5 – Edina Grill with Dan English.

Special thanks to Mike Donnelly and Aaron Lowe for stepping up and organizing these great events.

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

MN SQL Saturday #149 Follow Up

Posted by denglishbi on October 2, 2012

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

This past weekend was the big SQL Saturday event in Minnesota and for the first time not only was the event on a Saturday, but we also had a couple of pre-con events.  I was fortunate to be able to conduct one of the pre-cons corey - data herowith Brian Larson.  We did an all day event on “Unlocking Insight – Be a Data Hero.”  I was a little nervous going into the pre-con because I had never done one before and wasn’t quite sure what people’s expectations would be and wanted to make sure that we had enough content.  Based on the classroom interactions I feel that the event went very well.  Brian and I covered the Microsoft BI tools, BI semantic models, PowerPivot, SharePoint, PerformancePoint, Tabular Analysis Services, and Power View.  We also provided each of the attendees a copy of our book Visualizing Data with Microsoft Power View.  With all of the content, demos, and questions sqsat149that were asked I think we had a very successful day (next time handing out evaluations would be a good idea).

Then came the speaker dinner at Don Pablo’s and that was a really great setup.  We had a nice patio area all to ourselves and about about 40 people must have shown up.  It was nice to catch of with some familiar faces along with people in the SQL Family that I have never met as well as some of their spouses (even some kids).  All-in-all it was a nice relaxing time and it was fun to not only talking about the work we were all doing along with discussing how our jobs were going, but also to talk about our family lives.  Such a caring and awesome community, I couldn’t ask to be around a better group of people.

Then it was time for a few hours of sleep and the big event down on the U of M campus.  This was possibly my second time on campus and it was a very nice location.  The rooms were great (at least the one I was in, didn’t get a chance to check them all out – lots of tracks and sessions, plus I had to leave early to coach a soccer game).  I had a good crowd based on all of the great sessions going on at the same time and for it being the first one in the morning.  Unfortunately I misjudged my time and the material I wanted to cover.  I should have skipped the PowerPivot and Tabular SSAS project demos and just dived into the Power View ones.  I don’t feel like I did justice to the Power View demo, so need to do a little rearranging of this presentation next time.  I did cover some of the new items coming out with SQL Server 2012 SP1 and Excel 2013 though.  Hopefully everyone got something out of the content and demos and walked away with some new information.  You can download my slides from the SQL Saturday schedule site here – Power View – Bringing your Data to Life!

I really enjoyed the nice speaker blanket, shirt, and the Quest Software swag bags as well:)

sqlsat149 blanket sqlsat149 speaker shirt quest swag bag

I want to thank everyone that was involved in organizing the event (and the volunteers), special thanks to Paul Timmerman who was leading the charge this year.  I know from past experience the time, energy, and sleepless hours that goes into doing these events.  This year I was pretty much hands off with the exception of reviewing and picking the BI sessions.  I hope that everyone had a great time and I can’t wait to do it all over again next year:)

For those that attended our pre-con or my session I will have a couple of blog postings coming up this week to cover a couple of specific items that we talked about or that I did some further research after our event.

Thanks again for those that attended and hope to catch you at one of the PASSMN user group meetings, MN Microsoft BI user group meetings (next meeting Tue, Dec 4), or one of the big events going on nationally. Until next time:)

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

Installing SQL Server 2012 SP1 CTP4 Experience

Posted by denglishbi on September 22, 2012

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

A standalone version of the SQL Server 2012 SP1 was released this week.  It is not the final release, just a community technology preview (CTP4), but a release that we can test out none the less.  The exciting part of this release to me are the enhancements that have been added to Power View.  Here you will see the steps I went through to upgrade my evaluation virtual machine I have setup that is based on the Appendix B that comes with our book – Visualizing Data with Microsoft Power View.

First you will need to download the SQL Server 2012 SP1 CTP and you can do that from here – SQL Server 2012 SP1 CTP (there is even a full SQL 2012 slipstream version you can download as well that includes the service pack).  You also might want to download some of the additional features that are provided with the feature pack and you can get that from here – Microsoft® SQL Server® 2012 SP1 Community Technology Preview Feature Pack.

Once you have the service pack downloaded you can run this on your machine and follow through the screenshots below.






I had one item that I needed to shutdown so that I could avoid a restart after I installed the service pack and that was related to IIS services (these are running because of SharePoint 2010).


So I had to stop the IIS Admin Service and the World Wide Wed Publishing Service.


I did a refresh check and I was good to go.


Running RTM Evaluation Edition – 11.0.2100.60


Updating the default instance of SQL 2012


Updating 2nd instance of SQL 2012 (POWERPIVOT)


Instance MSSQLSERVER overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Start time:                    2012-09-22 08:15:42
  End time:                      2012-09-22 08:33:33
  Requested action:              Patch

Instance POWERPIVOT overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Start time:                    2012-09-22 08:33:57
  End time:                      2012-09-22 08:35:24
  Requested action:              Patch

Patch Level: 11.1.2845.0


Verify I can connect to my instances of the database engine and Analysis Services along with reviewing the build numbers.

Microsoft SQL Server 2012 – 11.0.2845.0 (X64)
    Sep  1 2012 02:36:04
    Copyright (c) Microsoft Corporation
    Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Now to go start into SharePoint and check out Power View!


New Ribbon layouts, icons, and features (images, hyperlinks, field list control)


More style, text, and background options


New visualizations – Pie and Map charts along with font sizes


Pie Charts, but no data labels or sorting options.


Bing geo-encoded maps


Pre-defined drill up/down capabilities


Support to restore Excel 2013 PowerPivot files in Tabular SSAS


SQL Saturday #149 is coming up next:)


SharePoint 2010 will not support the Power View that is in Excel 2013 files.


I noticed that my PowerPivot was not working yet, so need to run the PowerPivot Configuration Tool.



New version detected, time to upgrade.


Run the upgrade process.


After this is done we go back in and test out PowerPivot again


Now it works just fine.  The one thing I noticed is that it doesn’t appear to be working with the Excel 2013 PowerPivot files though.


Maybe these will not work in SharePoint 2010, not quite sure yet, will need to look into this more.

For more information on the new features available in the updated version of Power View check out some of these references:

Here are some more links for information on the SP1 updates:

This service pack also includes performance improvements with slicers and filters with PowerPivot and Tabular SSAS as well as I demoed here in this post and video – Excel PowerPivot SQL 2012 vs. Excel 2013 Performance Demo.

Now we just need to wait for the final release and hopefully that will be coming out in a couple of months now, should be very soon. Enjoy!

Posted in SQL Server | Tagged: , | 23 Comments »

MN SQL Saturday 149 Pre-Cons & Main Event Next Week Sept 28 & 29

Posted by denglishbi on September 21, 2012

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

Just a quick reminder that next week the MN SQL Saturday 149 takes place.  On Friday, September 28 there are two all day pre-con events.  There is a Business Intelligence pre-con where Brian Larson and I will be going over Microsoft BI including items such as PowerPivot, SharePoint, Tabular SSAS, and Power View.  I also know that there is an added bonus to signing up for our session besides just getting your lunch paid for on Saturday, you will get a copy of our Visualizing Data with Microsoft Power View book!  Down below I have included the tentative agenda for the day as well.  In addition to our pre-con SQL Server MVP Ted Krueger is doing one on T-SQL performance tuning.

9:00 to 9:30 – Welcome and introductions
9:30 to 10:45 – Talk about Microsoft BI
10:45 to 11:00 – Break
11:00 to 12:00 – PowerPivot
12:00 to 1:00 – Lunch
1:00 to 2:00 – Team/Corporate BI: SharePoint and Tabular SSAS
2:00 to 3:00 –  Building the reporting model
3:00 to 3:15 – Break
3:15 to 4:30 – Power View
4:30 to 5:00 – Questions

In addition to doing the pre-con I will also be doing a session first thing the following morning.

  • Power View – Bringing your Data to Life!

The world of self-service BI just keeps getting better. From self-service analysis with PowerPivot to self-service reporting with Microsoft Power View. This session will discuss the new BI Semantic Model concept, show you how to build models with PowerPivot and Analysis Services, and then go over using the new browser based reporting tool Power View to provide highly interactive reporting solutions. There is fun to be had by all and who knew that analyzing data could be so fun, come and find out more and see how it all works and what to expect with the upcoming release of SQL Server 2012 SP1 and even Excel 2013!

So if you are in the area and are available next week, take this opportunity now to review the schedule – http://sqlsaturday.com/149/schedule.aspx

Hopefully you can make it, there is a great lineup and a great opportunity to network with the SQL Family:)

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

Excel PowerPivot SQL 2012 vs. Excel 2013 Performance Demo

Posted by denglishbi on July 30, 2012

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

I just wanted to do a blog post to demonstrate a performance situation I ran into last week.  I had created a PowerPivot model in Excel 2013 and it performed very well (quite the understatement, phenomenal) when creating PivotTables and Power View reports.  I then went ahead and recreated the model in Excel 2010 with the SQL Server 2012 PowerPivot add-in.  The model creation went fine, but when it came to trying to replicate the report I ran into a slight snag.  Well I actually couldn’t even complete the report, I ran out of memory on a box with 8GB of memory.image

We decided to upgrade the box and gave it an additional 4GB of memory, but that didn’t change anything, the query still consumed all of the memory and wouldn’t complete.  We decided to test this out and migrate the model to a Tabular SSAS instance on a server with 64GB of RAM.  Well the query for the PivotTable just kept running and we watched the Analysis Services process climb to over 18GB and it was still climbing with no results coming back.  Needless to say I was confused and a bit embarrassed.  I decided to give it a go in Excel 2013 again and once again no issues and the results were very fast (might even say blazing fast).

To demonstrate this I have gone ahead and created a demo of the exact scenario with a very comparative sample so you can get a feel for what I was seeing, just take a look at this video:

Excel PowerPivot Performance Demo from Dan English on Vimeo.

In reaching out to the product team I discovered that in Office 15 there have been some performance improvements when it comes to dealing with filters.  Well we can definitely see that based on my results:)  It turns out that these same performance improvements will be coming eventually to the current version as well, we just need to wait for the release of SP1 for SQL Server 2012.  Some of the improvements seen will also be available to querying the data in Analysis Services as well, both Tabular and Multidimensional!  So just hold on until SP1 is released.  If you want to checkout the performance improvements today have a go with PowerPivot for Excel 2013 (you will not be disappointed).

Posted in PowerPivot, SQL Server | Tagged: , | 4 Comments »