Power BI using Service Principal with Synapse SQL Pool with Shareable Cloud Connection

This is a bit overdue and a follow up to a few other posts I have regarding using Service Principal authentication with Power BI reports Power BI using Service Principal with Synapse SQL Pool and Power BI using Service Principal with Synapse Data Explorer (Kusto) Pool.

With the other two posts I did last year I had to use the SQL Server ODBC driver to get that to work and the big downside to that is that you need to use a gateway with that. Well in this case we are going to take a look at the new Shareable Cloud Connections that were announced earlier this year Streamlining cloud connection management for datasets, paginated reports, and other artifacts | Microsoft Power BI Blog | Microsoft Power BI

In the previous articles I already shared how to create the Service Principal account, so really the only thing to show here is the new connection you can create in the Power BI (Microsoft Fabric) service. Here is a link to the documentation that will walk you through this as well Create and share cloud data sources in the Power BI service (Preview) – Power BI | Microsoft Learn.

So in the service you click on the Settings gear icon in the top right and select Manage connections and gateways. From there you will click on the +New button in the top left and then configure your Cloud connection like such and you will see under the authentication method you can choose Service Principal!

Once you upload your report that is using the similar connection all you need to do is modify the dataset (semantic model) settings and adjust the reference from the personal cloud connection to the shareable cloud connection that you created which is using the Service Principal like in the following screenshot.

Once that is done you can go back and view your report and everything is good to go!

And you can go ahead and do your refreshes just like you would normally:)

And no more ODBC driver and no more gateway!!!

MN SQLSaturday #796 Pre-Cons

We are just a few weeks out from #sqlsatmn and over 250 have registered! Don’t miss out on securing your spot, register today! 

http://www.sqlsaturday.com/796/eventhome.aspx

Checkout pre-con events and the earl-bird pricing while it lasts:)  Early-bird pricing on pre-cons ends 9/14, so sign-up today and save:)

We have paid, full-day pre-conference sessions on Friday, October 5th, 2018 (also at Saint Paul College).

These full day training sessions are a great value, current early-bird pricing of only $99 plus fees! (early-bird pricing is good through Friday, September 14 and then the price will increase to $125)

Click the links below to get more details about the pre-con training events:

• Joshuha Owen, Steve Hughes – The Modern Enterprise Data Warehouse in Azure
• Brian Larson – Powering Up with Power BI
• Rick Lowe – Keeping Your DBA Happy. Why Some Practices Drive Admins Crazy.
• Ed Leighton-Dick – Data Security for the Cloud

You must register and pay through Eventbrite for these pre-cons.

#sqlpass

MN SQLSaturday #796 – Oct 6

This year’s MN SQLSaturday will be on October 6 at the same venue as last year, the Saint Paul College. This is a free training event for Microsoft Data Platform professionals and those wanting to learn about SQL Server, Business Intelligence, Database Development and Analytics.

 

The call for speakers ends on Sunday, July 15.

We look forward to seeing you at the event!

http://www.sqlsaturday.com/796/EventHome.aspx

 

 

 

SQL Server 2017 CTP2.1 Install Experience

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

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.

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!).

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

Everything successfully installed that we selected!

Install SQL Server Management Studio 17.0

The new SSMS 17.0 splash screen.

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.

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.

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.

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.

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)

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.

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.

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

Enter the Web Portal URL

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

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

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.

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!

Minneapolis Microsoft Data Amp Event–Listening Party Apr 19

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!

Getting Started with SQL Server 2016 CTP3 in Azure

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)
—————————
OK  
—————————

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:

• SQL Server 2016 Community Technology Preview 3.0 is available
• SQL Server 2016 (CTP3) R Integration
• What’s new for SQL Server 2016 Analysis Services in CTP3
• What’s New in SQL Server 2016, October Update

Cheers!

SQLSaturday 287 Madison Follow Up

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:)

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)

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.

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:)

Final Call for the MN SQL Saturday 238 Speakers

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.

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.

PASSMN July 2013 Meeting

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.

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

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

Live Meeting:

• URL: https://www.livemeeting.com/cc/usergroups/join?id=SRR9DN&role=attend&pw=zd%3F9c.2KJ
• Meeting ID: SRR9DN
• Entry Code: zd?9c.2KJ

Agenda:

• 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

Presentation

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.

SQLFriends Lunch–Dec 14

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:)  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.

I 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.