Third Annual SQL Saturday BI Edition in Charlotte

The third annual SQL Saturday, BI Edition, is back in Charlotte! It's coming up next weekend, on Saturday, October 4th.  

SQL Saturday is a free* training event for Database and Business Intelligence professionals. It's a conference condensed into a single day. The event includes training sessions led by local, regional, and national speakers, and lots of opportunities for networking and meeting up with peers. There's also exhibitor tables with useful products, information, and job opportunities.

The schedule has 40 technical sessions, at varying levels, on:

  • Data Analytics
  • Cloud
  • Big Data
  • Information Management
  • Development
  • Information Delivery
  • Environment Architecture
  • System Administration
  • Professional Development

More information: http://www.sqlsaturday.com/330/eventhome.aspx 

Registration: http://www.sqlsaturday.com/330/register.aspx

Schedule: http://www.sqlsaturday.com/330/schedule.aspx 

I hope to you see you there at SQL Saturday next weekend - be sure to say hi!

*If you would like to join us for lunch, we ask that you pay $10 to help us cover the cost of feeding a few hundred people. However, if you'd rather bring your own, that's ok too - just go through the registration process like normal. When it asks you to pay for lunch at PayPal, go ahead and close that window.

Resolving Errors from Deployment of Database to Azure SQL DB

This week our team at BlueGranite decided that the sample relational database called ContosoRetailDW would be best located for our demo & training purposes in Azure as a SQL Database. Below are the steps taken to get this sample database in a state where it could be deployed as an Azure SQL DB. I will list my process from beginning to end, although I won't bother detailing out all of the early steps.

Download the Sample Database

First, I downloaded the Contoso database backup file, extracted it, and restored the backup to a SQL Server 2014 instance. Then I verified it can be queried in SQL Server Management Studio.

Azure Management Portal

In the Azure management portal, I created a small Azure SQL DB just for testing purposes. Since this was the first Azure SQL DB created in this Azure subscription, I wanted to do a small test to obtain a server name, set the server firewall, and check connectivity.

You can find the server name on the Servers page. To set the server firewall, click the Manage button, and respond Yes to the first prompt.

Azure26.png

Connect to the Azure server from SSMS for a quick verification of connectivity to the test database created as an Azure SQL DB. 

Good. Now that I know I can connect ok to Azure, I'm ready to move onto the task at hand with migrating the Contoso sample database.

First Attempt to Deploy to Azure SQL DB from SSMS

From within SSMS, connect to the instance where the database to be migrated exists. Right-click the database > Tasks > Deploy Database to Windows Azure SQL Database. 

Well, if the story ended here it wouldn't have any drama, right?  With my first attempt I received a list of errors due to unsupported features. I initially tried to rectify them in SSMS but it's just not the best tool for the job so I'll skip that party of the story.  Enter Visual Studio.

Removing Unsupported Features in Visual Studio

Open Visual Studio (SQL Server Data Tools). Connect to your SQL Server instance in SQL Server Object Explorer. We will need a project in order to alter the schema to remove unsupported features.  So, right-click the Contoso database and choose Create New Project.

I named mine as shown below since it's purpose was to convert the original database schema to where it's supported in Azure SQL DB.

Now that we have a project that is based on the Contoso Retail DW database, the first thing to do is change the target platform to Microsoft Azure SQL Database. This is done in the project properties.

Now that the project understands its destination will be an Azure SQL DB, let's do a build and see what happens.

Same list of errors that I got previously in SSMS. However, now we're in a lot better position to address them quickly.

There's an MSDN blog entry which discusses a quicker way to address the issues. I went ahead and handled them individually.  It didn't take very long at all.  

Double-click on each error in the Error List and it'll take you to the DDL where the unsupported syntax exists. 

One of our two main issues is data compression.  That required the "WITH (DATA_COMPRESSION = PAGE) syntax to be removed from some of the tables.

Another one of the issues was extended properties which aren't supported in Azure SQL DB at this time. So, even though they are nice for documenting a database, I went ahead and removed the extended properties from each object.

Let's try another build (a rebuild actually, to make sure everything gets evaluated). And now that all of the errors are rectified we are happy and ready to move on.

Schema Comparison in Visual Studio

Now it's time to get the real database sync'd up with the changes made to the project schema. So let's create a schema comparison under Tools > SQL Server > New Schema Comparison.

For the left side I used the project (i.e., the "fixed" one) and for the right side I used the database (i.e., the "wrong" one that needs to be "fixed"). 

Looking down the list, I see some deletes and some changes. They all look reasonable. Click on the Script button on the SqlSchemaCompare toolbar.

Now I have a script that I can run against the database to get its DDL updated.  Scrolling down through the script, everything looks reasonable.  Next step is to Connect to the database server using the icon on the toolbar.

Once connected, we can execute the script.

Mine took 18 minutes to run on a laptop.

Deploy from SSMS to Azure SQL Database

Now that the unsupported features have been addressed, let's try the deployment again. In SSMS, right-click the database > Tasks > Deploy Database to Windows Azure SQL Database.

We are presented with a wizard.

Connect to the server in Azure.  The server name from Azure for a SQL DB is appended with ".database.windows.net".  Be sure to use SQL Authentication. 

I changed the maximum database size from the default of 1 GB to 5 GB, since my version of the Contoso Retail DW is about 3.9 GB. 

You're presented with a summary page before choosing Finish. Double check your Source Database is less than the Maximum Database Size selected for the target with some room to spare. If the source is larger, it'll still deploy what it can until it runs out of space.

As it's working you'll see status messages.

Mine took a couple of hours to deploy the ~4GB database. Now our sample Azure SQL DB is ready to be used.

Verify Connectivity to Azure SQL DB

In SSMS, connect to the instance & do a quick test to verify connectivity is working.

Using Power Query to Get Data From an Excel File in OneDrive Via URL

In this blog entry I'm going to discuss accessing an Excel file that's stored in OneDrive. What I do *not* want to do is associate the files to my local file path such as c:\users\melissa\onedrive... because another person who downloads the workbook and wants to edit or look at the Power Query settings will get an error. So I want to use the URL for OneDrive to make it easier to pass this workbook among other people. This took a bit of trial and error to find the right URL that would work with Power Query.

Storing the Source File in OneDrive

The first thing we want to do is get a file in OneDrive and make sure it's shared. For this purpose I'm just using a little simple file called PowerQueryTest, directly in my Public OneDrive folder. However, this same logic can be applied to a file shared with a set of coworkers.

Getting a Link for the Source File

If you click the Excel file to view or edit it in the browser, the URL looks like this:

Power Query doesn't like that URL. So, the next thing you might think to try is to generating a link with the Share feature. For my file, that URL looks like:

The 2nd URL is a little different from the first one, but the resid that identifies the report is the same so that's good. But, the above format still won't work in Power Query.

What I've found that will work with Power Query is this kind of URL structure:  

 

Basically, it's https://onedrive.live.com/download?&resid= followed by the unique report # which you can obtain from the URL as you view or edit a report in the browser.

Using the URL in Power Query

Even though we're going after contents of an Excel File, we want to use the From Web  menu item on the Power Query ribbon (instead of From File). Paste in the URL in the correct format mentioned just above.

In the Navigator menu on the right pane, select the sheet and then Edit.

 

Now you can carry on with whatever steps you need for your file. For my teensy little set of sample data here, I told it to "Use First Row as Headers" and then gave it a name and description in the query properties.

Credentials to Access to One Drive from Power Query

Now, the next thing you need to know is what will happen when the next person opens this workbook and wants to edit or even just look at how the Power Query steps. So, for this last series of screen shots, we are seeing it from the perspective of person #2 who just opened the file.

The first thing person #2 sees is the normal security warning. It's ok to click Enable Content since it's been shared by our trusty coworker. 

When we hover over the query name in the Workbook Queries pane, we see a warning in the popup box.  Permission Error: Credentials are required to connect to the Web source.  That makes sense, even though this was a file I had in the public share - Power Query just doesn't know it yet.  

Go ahead and click Edit.

Then choose Edit Credentials.

Lastly, select Anonymous access.  For me, the first radio button worked perfectly.

You might want to let your coworkers know to go ahead and choose anonymous credentials if they see the warning and want to edit, view, or refresh Power Query. (Of course, if it's not anonymous then let them know which organizational account to sign in with instead.)

Power Query Saved Data Source Settings

On the Power Query ribbon, there's a Data Source Settings item in the Machine Settings section. After person #2 set their credentials in the previous step, their machine now stores this setting for OneDrive.

That's it! Found a different way? Leave a note in the comments.

As always, Power BI is constantly evolving and changing. This information is correct as of Aug. 2014.

You Might Also Like...

Power Query Data - Should It Be Loaded to the Worksheet?

Where to Find Q&A Optimizations in Power BI

Just a quick tip about where to locate the options in Power BI for doing Q&A optimizations. In the Power BI site, these are not where you might first think to look.

What is Q&A?

First, just a quick bit on what Q&A is and why you should care.  Q&A is also referred to as natural language querying, and essentially amounts to using a Bing-like search box in order to return a report.  This is interesting and important for your report consumers because it means the report author doesn't have to publish every single permeation of a report. 

This behavior of constructing reports on the fly relies upon certain things in the underlying data model (Power Pivot) for it to return results properly.  So the first thing you can do for Q&A is to have a proper data model and use Synonyms in the Power Pivot model. (See the link below for Part 2 for more info.)

In addition to proper modeling techniques, there are some optimizations that are now available in Power BI - i.e., available in the browser in the Power BI site. When done in the browser, these optimizations are written back to the original workbook. However, where to locate Q&A optimizations isn't quite as obvious as you might think.

Finding Q&A Optimizations in the Power BI Site

1. Go to the main page of Power BI.

2. Select the Q&A page within Power BI Site Settings.

Q&A_SiteSettings2.png

3. Locate the workbook you wish to optimize, click the ellipses, and choose "Optimize for Q&A."

Q&A_SiteSettings3.png

At this point, you have 4 options: the Overview, Synonyms, Phrasings, and Usage.

 

Synonyms can be done in either the Power Pivot workbook (assuming you have the correct version of Excel, that is...I discuss that more in this blog entry:  Getting New Power Pivot Features), or in the Power BI site. If you start doing Q&A optimizations in the Power BI site, make sure you re-download a new copy of the workbook before making edits and re-uploading it! (If this becomes an issue, you might think about turning on versioning in the document library to keep 1, or maybe 2, versions in change history.)

Phrasings, however, can only be done currently in the Power BI site. I would say that the Q&A functionality available in the cloud only, along with the new licensing model for Power Map, is one of Microsoft's ways of encouraging customers to purchase Office 365 and Power BI licenses.

Who Has Access to Q&A Optimization?

From what I understand, you will see the Q&A optimization page if (a) you are a Power BI Administrator, or (b) you are the one who uploaded the file. Now that doesn't cover a situation where Suzie uploads the file but John is taking over management of it. I figure in that case John can download it, delete it, and re-upload it. However, I'm hoping the permissions for allowing optimization become a bit more granular.  In my mind, the person who developed the model is the best person for optimizing it, but that of course depends on the optimizations.

Cloud Modeling?

In the documentation for Q&A from Office.com, they refer to Q&A optimizations also as cloud modeling. I personally have a very different definition of modeling. However, it's helpful to know what the lingo is if you are searching for help. Usage of the term cloud modeling makes me wonder if that means something more along those lines is coming at some point?  Hmmm.

Finding More Information

Introduction to Power BI Q&A (and Cloud Modeling)

Demystifying Power BI Q&A - Part 1  < An introduction

Demystifying Power BI Q&A - Part 2  < Discusses good modeling techniques & synonyms

Demystifying Power BI Q&A - Part 3   < Discusses phrasings