Dan English's BI Blog

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

Posts Tagged ‘sql server’

PASS Business Analtyics VC July Meeting – Incorporating R with SQL Server 2016

Posted by denglishbi on July 7, 2016

The next PASS Business Analytics Virtual Chapter meeting is coming up this week on Thursday, July 14.  This month we welcome Microsoft MVP Ginger Grant (b|t) who will talk to us about using R with SQL Server 2016.

–> REGISTER NOW <–PASS_BAVC_Logo_New_201502

July 14 – Incorporating R with SQL Server 2016


Interest in the R language is has grown along with the amount of data being accumulated. As Microsoft is now incorporating the R language into SQL Server 2016, the language is going to be a required skill for all data professionals in the near future. This webinar will provide an explanation the different versions of R, demonstrate writing R code in Visual Studio, and run some R code on SQL Server. To ensure everyone who wants to can run this code on their PCs, all of the setup information needed to make R work on SQL Server and the UI will a be covered. After watching this you will know everything you need to start incorporating SQL Server and R.Ginger Grant

Bio –

Having worked with the Microsoft BI stack for many years, Ginger Grant is exploring new challenges by applying her data expertise to the expanding field of data science and data visualization. Using R and Machine Learning she has been able to use data and algorithms to answer many different business questions for users. When not working, Ginger is studying for the pilot degree program Microsoft created for Data Science, blogging at DesertIsleSQL.com and speaking at numerous technical events, including SQL Saturdays, GDI and Women Who Code. An active member of the Microsoft data community, she was recently awarded a Microsoft MVP in Data Platform.


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

Save $150 right now using discount code VC15BKR3

If you are interested in speaking at an upcoming PASS BA Virtual Chapter meeting we are always looking for speakers, please leave a comment or send us an email at PASSBAVC@SQLPASS.ORG

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

MN PASS Microsoft BI User Group Meeting – Feb 22

Posted by denglishbi on February 9, 2016

The next MN Microsoft BI User Group meeting is coming up in a couple of weeks on Monday, February 22.  We will have two topics, one on the upcoming release of SQL Server 2016 (which CTP 3.3 was just released) and the other on Power BI (all of the new content since I last spoke on it last year – which is a lot of content).

Please Register Now (Please register so that we can have your name badge pre-printed)

MN Microsoft BI User Group Feb 2016 Meeting

Sponsored by:


Date: Monday, February 22, 2016

Check In: 12:45 PM Event Time: 1:00 PM – 5:00 PM

Location: Microsoft Technology Center – 3601 76th St W, Suite 600 Edina MN 55435 (in LOW 1, 2, and 3)

Please Register Now (Please register so that we can have your name badge pre-printed)


  • 12:45-1:00 : Check-In
  • 1:00-1:15 : Microsoft BI User Group Updates and Announcements
  • 1:15-2:15 : SQL Server 2016
  • 2:15-2:30 : Break
  • 2:30-3:30 : Getting the new year started with Microsoft Power BI!
  • 3:30-5:00 : Networking and Pizza


Session 1: SQL Server 2016

Microsoft is continuing to invest heavily in BI and Analytics with SQL Server 2016. Reporting Services and Analysis Services are receiving major enhancements and will deliver modern and mobile reporting as well as powerful modeling capabilities. SQL 2016 also brings new advanced analytic capabilities with R Services and enables hybrid BI scenarios to benefit from existing on-premises investments. In this sessions we’ll go over the new BI features of SQL 2016 and talk about the future of BI in SQL Server.

Speaker: Will Weber

Will Weber is a Data Platform Solution Architect with Microsoft. He’s been an active participant in the SQL community in the Twin Cities for a number of years and looks forward to continuing that work in the future. And if the PowerBall gets back up to $1.5 billion he’ll definitely consider buying a ticket.

Session 2: Getting the new year started with Microsoft Power BI!

In this session we will take a look at the changes that Microsoft has released for the Power BI Service, Power BI Desktop, and Power BI Mobile Apps. Microsoft continues to release updates to Power BI on a weekly and monthly basis. Some of the changes since we last met include items such as Custom Visualizations, Full Screen Mode, Pop-Out for Visualizations, Printing, Export Data, QR Codes, Excel Add-In, Widgets, R Visuals Integration, Improved Image Support, and much, much more. Come and check out all of the latest capabilities of Power BI and be amazed!

Speaker: Dan English

Dan is the Senior Data Warehouse Architect at Constellation, Inc. He is also a Microsoft Data Platform MVP, author, technical editor, and community leader. He has been developing with Microsoft technologies for over 15 years, and over 10 years with Data Warehousing and Business Intelligence. He has been working with SQO Server since version 6.5 through 2014 and is exploring 2016. Dan has videos on Vimeo, is an avid blogger, and tweets. Dan is the chapter leader for the PASS Business Analytics VC and on the executive leadership team for the MN Microsoft BI User Group.

Planning on attending the Data Insights Summit or the PASS BA Conference?  If so, make sure to associate yourself with our user group.

Data Insights Summit – Our 3-digit Power BI User Group (PUG) code is 102.

PASS BA Conference – Use the following code to save $100 off registration USMO03

Posted in Training | 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 »

Tabular Models, Compatibility Level, and Power View

Posted by denglishbi on October 4, 2012

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

A couple of weeks ago I did a review of the CTP4 of SP1 that came out for SQL Server 2012 – Installing SQL Server 2012 SP1 CT4 Experience. Part of my test involved upgrading an Excel 2013 PowerPivot file to a Tabular Analysis Service project.  After I did that I setup a BI Semantic Model connection file in SharePoint and explored the Power View designer updates.  It was business as usual and I was seeing all of the new features and functionality that I had previously mention in my posts on Excel 2013 and Power View – Power View meet Microsoft Excel 2013 and Power View meet Microsoft Excel 2013 Part 2.

Well this past week I was prepping for my pre-con for the SQLSaturday #149 event and I noticed something odd.  When I was connecting to my tabular models with Power View I was not seeing my hierarchies or key performance indicators (KPIs).  I was a bit confused.  I upgraded my environment to SP1, so what was going on.  I thought the upgrade provided support for these model features now, right?  I know I had seen them already, maybe I was just imagining things though.  When I was doing my tabular project deployments I was also seeing a new dialog box that I was just clicking ‘Yes’ on without really understanding what it was talking about…


I completed out the pre-con still confused and then last weekend I did a bit more digging into this issue.  I went back to my Excel 2013 SQLSaturday model that I upgraded and I reviewed the property settings on the database.


The item that I noticed that seemed like it might be important was the Compatibility Level setting.  This database was showing SQL Server 2012 SP1 (1103).  I then took a look at the database I was using for some of my demos.


Ahhh, now the light bulb was starting to get bright:) The database that I was deploying was still in the SQL Server 2012 (1100) setting even though I had upgraded my environment to SP1.  Interesting.  Apparently you have the ability to control this setting now if you want to run in different levels or do some testing in the same environment before upgrading all of your databases.


I didn’t realize that I now had to make a change on my model property settings before I deployed my database.

Once this setting is changed you will be prompted with the following:


Once again I click ‘Yes’ and redeploy to upgrade the database on the server.  Now when I connect to my model with Power View I see my hierarchies and KPIs, perfect!


So just something to be aware of now when working with tabular model projects now.  There is a property setting that we have to take a look at, Compatibility Level, and it is a pretty important one for the users of our data models:)

Here are a couple of additional reference links to take a look at (some of this documentation and instructions might differ from what you see with CTP4 of SP1, but they should be available in the RTM of SP1):


Posted in Analysis Services | Tagged: , , | 8 Comments »

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 »

Upgrade PowerPivot SharePoint from SQL 2012 RC0 to RTM

Posted by denglishbi on May 21, 2012

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

So last week I was doing a presentation on Self-Service BI with SQL Server 2012 at the TechFuse MN technical conference and I realized that my demo virtual machine was still running SQL Server 2012 RC0.  I was thinking that I shouldn’t do my presentation and demos using RC0, so why not upgrade.  Probably not the best idea to do this just a few days out from a presentation, but I had my fingers cross and just went for it.

I found some guidance from a thread in the forums by Jen Underwood here Upgrade in place RC0 to RTM and decided to go for it.  I did the in-place upgrade and my SharePoint virtual machine with SQL 2012 was still functioning after I completed all of the steps.  Then I moved on to testing my integrated SSRS setup and realized that the Power View reports were not working and neither were my data sources.  The quick fix for that was to go into Central Administration and go into the properties of the Reporting Services application and click OK.  That completed the upgrade process for my shared service and my Power View reports functioned properly after that.

Next was to verify my PowerPivot was still working.  I opened up my PowerPivot files and uploaded them to SharePoint.  The snapshots appeared in the PowerPivot Gallery just fine, but I was unable to work with the files (the slicers did not work, I received error messages as shown below).  I also went into Central Administration and tried to checkout the PowerPivot management dashboard.  In doing this I received the same error message below:


An error occurred during an attempt to establish a connection to the external data source. The following connection failed to refresh: PowerPivot Data

I wasn’t quite sure how to proceed, but after the fix for the SSRS shared service I was thinking that maybe I needed to do the same for the PowerPivot application.  SO I decided that I would try to re-run the PowerPivot configuration tool which is new with the SQL Server 2012 installation.PowerPivot Configuration Tool

The following are the screenshots of running the PowerPivot Configuration Tool with the SQL Server 2012 RTM installation software against my virtual machine that was RC0:








Once that was done I went back into Central Administration and checked out the PowerPivot Management Dashboard again:


Everything was working as expected again and no more error messages! Victory!

That was quite the relief and I was able to do my presentation with my newly upgraded RTM release of SQL Server 2012, not bad for doing an in-place upgrade just a few days prior to the big event.  This included three instances of SSAS (multidimensional, tabular, and PowerPivot), the SQL database engine, SSRS, SSRS SharePoint integrated, SSIS, and DQS as well.

Here is some additional information if you are looking to do the upgrade for DQS – Upgrade DQS: SQL Server 2012 RC0 to SQL Server 2012 RTM.

Posted in PowerPivot, SharePoint | Tagged: , , | Leave a Comment »

SQL Server 2012 RTM is here!

Posted by denglishbi on March 7, 2012

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

Yesterday I was at the Minneapolis Microsoft Technology Center and I received the news short after 11 a.m. that SQL Server 2012 went RTM and the evaluation version is available to download.sql2012

The SQL Server 2012 Virtual Launch is going on today – http://sqlserverlaunch.com, so don’t miss out on that. I heard that over 20,000 people are registered, so this is going to be one of the largest online launch events.

You can grab the downloads here – http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx.

On this site you will find the links for:

To find out what has changed in the new version of SQL Server 2012 checkout the online material available here – What’s new in Microsoft SQL Server 2012.

Also, don’t forget that the SQL Server 2012 Special Ops Tour is going on as well.  The one for Minneapolis still has seats available.  Make sure you register for that today as well – http://specialopstour.com.


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

SQL Server 2012 Upcoming Launch Events

Posted by denglishbi on January 30, 2012

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

Just wanted to do a quick post to make sure everyone was aware that there are a few SQL Server 2012 Launch events coming up in the near future.  sql server 2012

SQL Server 2012 Virtual Launch Event

The first one is on Wednesday, March 7.  This is a free event and it is also an online virtual event as well.  There will be a couple of keynote presentations and then three tracks and it will even include a virtual ‘Expo Hall’ where you can talk with vendors and partners as well.  This will be a great event to get exposed to what is coming in SQL Server 2012 so you will be aware of the new features and capabilities.  Register now over at http://sqlserverlaunch.com/.

SQL Server 2012 Special Ops Tour

Another event that will be starting up in March is the SQL Server Special Ops Tour being put on by the PASS organization and Microsoft.  This will be in the U.S. only and going to 12 cities – Atlanta, Irvine, Denver, Columbus, Jacksonville, Chicago, Dallas, Philadelphia, New York, Boston, Minneapolis, and San Francisco.  Now this is not a virtual event, so seating will be limited.  Registration is open and it is a free event, so make sure you secure your spot today – http://specialops.sqlpass.org/#fbid=PH48KYb0Gig.

24 Hours of PASS – Spring 2012

Another 24 back-to-back hours of free SQL Server training is also coming up brought to by the PASS organization and the SQL community.  Call for speakers is currently open through tomorrow and the event will take place on March 21, focusing on SQL Server 2012 of course.  For more details check out the following link – http://www.sqlpass.org/24hours/spring2012/.

SQL Server Connections – Spring 2012

If you are up for a conference and want to head to Las Vegas, NV, then you are in luck.  The Spring 2012 DevConnections conference is coming up and there will be a SQL Server 2012 Launch Conference and Expo included provided by Microsoft and SQL Server Connections.  This event takes place March 26-29.  For more information check out the details here – http://www.devconnections.com/shows/sp2012/default.aspx?s=185.

As you can probably tell the release date for SQL Server 2012 is coming up.  Lots of excitement and information is just pouring out and looking forward to the official release of the product.

Posted in SQL Server, Training | Tagged: , , | 1 Comment »

MN SQL Saturday #99 Event Update

Posted by denglishbi on November 8, 2011

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

The big day is almost here, just three more days and then it is time for the Minnesota SQL Saturday #99 event at Grace church in Eden Prairie (maps and directions).  If you are currently signed up but are unable to attend, please go to the registration site to update your status.  There is a waiting list going right now and we are actively reviewing that throughout the week.

Now for some of the details:sql sat 99 logo

  • There have been some schedule changes and a few updates, so please review the online schedule.  The room numbers have been posted for each track as well on the site in the track names.  You can also build your schedule list online – http://sqlsaturday.com/99/schedulebuilder.aspx (login to the site is required).
    • Some things to note:
      • We have two lunch sessions thanks to our You Rock sponsors Quest Software and HP-Microsoft Alliance; Baselining for SQL Server and Converged SQL Server Solutions from HP & Microsoft
      • WIT Panel Discussion has been set and Nicole Roepke, Amanda Bates, and Barb Rokke will be leading the discussion. Tim Plas will be moderating this session.
      • In the DBA2 Track The Powershell and Perfmon and Database Restores sessions have traded time slots
      • In the BI1 Track Intro to Data Mining and Inside PerformancePoint sessions have traded time slots
      • In the Prof Develop Track the Entity Strategies: Structuring your Consultancy has move to the 2 p.m. slot, two new sessions have been added as well – SQL Community & Social Networking (MVP Ted Krueger will be moderating this discussion as well) and Geographic Visualizations using Maps in SSRS
      • In BI2 Track Becoming DAX and Securing an SSAS, SSRS, and SharePoint BI Solution sessions have traded time slots
      • A new session was added to DBA2 track – PowerShell for the Reluctant DBA
  • The doors will open up at around 7:30 so you can start to get registered.  When you get to Grace church come in on the east side of the building (turn on Spring Rd and enter parking lot) and the door you will enter in through is labeled #4.  Everything will take place upstairs, so once you are checked in proceed down the hallway and take the stairs to the terrace level.
  • There is a Guidebook App available that you can put on your mobile device or check out online as well – http://m.guidebookapp.com/697/
  • There will be wifi access available at Grace church

That is all for now, might do one more update Thursday, otherwise I will see you there if you are planning on attending!

Posted in SQL Server, Training | Tagged: , , , | 1 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 »


Get every new post delivered to your Inbox.

Join 2,323 other followers