Luckily there are a lot of great resources out there to help. From live webcasts like SQLLunch and 24 hours of PASS, to local SQL Saturday events, from recorded TechEd sessions to hundreds of books to blogs posts on sites like this....the list goes on.

But it's still a long road, and building that initial solid foundation can be a hard step.

The Inquisitive Coworker

Over the years I've never had a good answer to the "where can I go to learn more about that" question from coworkers. There's a ton of beginner books out there, but who has the time to weed through them and figure out which ones are more dangerous than they are helpful?

So the thought runs through my head, "How _did_ I learn that CROSS JOIN trick I just used?" and of course I don't remember. Except I'm willing to bet that I learned it while trying to solve a problem. It might have been from a book, from a video, from trying someone else's answer to one of Denis's SQL puzzles. The driver was the fact that I had a problem to solve and that new tool I picked up was what helped me solve it. And I later remembered it, used it again, and learned more about it because it helped me solve a problem.

Learn by Doing

I've always liked the idea behind the TryRuby site, an interactive experience that walks you through the basics of Ruby as you type them into an interactive console. Short of finding a mentor for every person that wants to give Ruby a try, this seems like an excellent way to provide a guided experience and help you build that initial foundation.

Why not do this with SQL?

And why not take it even further? After all, if you have an interactive site that helps build some basic skills with SQL, why can't it also offer a section for more advanced techniques? Sure, you can download a free version of SQL Server Express or one of the other brands of SQL and try to learn these things on your own, but a guided set of exercises on a topic might be the perfect foundation before trying to branch out and try to invent your own problems to solve.

And thus was born the idea of SQLisHard.com.

It's Still Early

It's still in the very early stages with only a single set of exercises, and I've only implemented about half of that set. There's a longer wishlist of features on a Trello board, but the initial focus here was making the core experience work. It's not as whimsical as the TryRuby site (Damnit Jim, I'm a software engineer not a web designer). Most of the fanciest features are focused on helping me choose the right improvements to make; easy deployment, anonymous usage statistics, error capturing and reporting, and so on.

My list of "features I haven't gotten to yet" starts with "finish the first exercise set". After that I have more exercise sets, both foundational and "where can I go to learn that" advanced sets, an ability to save your progress (register/login) while continuing to let people without pre-registering (or without registering at all, up front registration annoys me), more permanent pieces for the few that I took shortcuts on, alternative exercise goals so you can try several queries while learning a single step, performance improvements and retry logic

But most importantly, I'm looking for feedback. Does this seem like something you would point a beginner to? When your coworker asks where they could learn to use windowing functions, could you imagine a method like this being useful?

Check it out: SQLisHard.com

The technologies behind both the site and the deployment process were interesting, I'll have follow-up posts on those soon as well.

Here is the table and the data

This is the expected output

Col1		UpperCase	LowerCase	Neither
ABCD01234Z	5		0		5
AAAAAAAAAA	10		0		0
aaaaaaaaaZ	1		9		0
a&a&a&a&aA	1		5		4
1234Tt7890	1		1		8

For bonus points, you can add 3 more columns to the output, one column will hold only uppercase characters, one column will hold only lowercase characters, one column will hold only characters that are neither uppercase or lowercase

Valentino will be talking about the tricks: little bits of wisdom on how to get things done in Reporting Services. We did the dry-run of the session yesterday and I definitely learned something from his part, so be sure to check it out!

The Community Day is on the 20^th of June in the city of Mechelen. It combines the various user groups of Belgium (and some of the Netherlands) and gives you an astonishing 5 track agenda with over 30 sessions! So get yourself registered, come on over and get your learning cap on!

In the following dialog, configure the name, URL, permissions and navigation. Select the Business Intelligence Center template, listed under Enterprise. Click Create to finish the configuration.

Hold on! An error? It seems we need to activate the SharePoint Publishing Infrastructure feature at the collection level before we can create the site.

So let’s activate this feature. Go to Site Settings > Site Collection Features and activate the SharePoint Server Publishing Infrastructure feature.

Next go to Site Settings > Site Features and activate SharePoint Server Publishing if it isn't already active.

Go back to create a BI subsite, fill in the details and hit Create.

What? Another error! The Performance Point features must also be activated before you can create a BI site. (I hope you are reading through this blog first before you actually try it out). Although I will probably not use Performance Point – like many other – I still need to enable it in order to create my site. So let’s do this.

Go to Central Administration > System Settings > Manage services on server and activate the Performance Point Service.

Next go to Application Management > Manage service applications. In the ribbon, click New and select PerformancePoint Service Application.

Configure the name for the service application and the database server.

Create a new application pool and specify the security account. Click Create to finish.

When the creation was successful, you’ll get a list of possible follow-up actions. Click OK.

Go to Site Settings > Site Collection Features and enable the PerformancePoint Services Site Collection Features.

Next go to Application Management > Manage service applications. Click on the PerformancePoint Service Application and go to its settings.

Configure the unattended service account to SPFarm and click OK at the bottom.

Finally go to Application Management. Click on Configure service application assocations.

Verify if PerformancePoint is correctly associated with the web application.

Now PerformancePoint is enabled, you can finally create your BI site! Repeat the steps from the beginning of this blog post to create your site.

All that’s left is to adjust this site to your preferences. This is for example how my Power View library looks like at the time of writing.

If you want to use a SharePoint list as an OData source for your demos, you don’t need to install ADO.NET data services like in SharePoint 2010. This functionality is already present.

This concludes the series on how to configure your own SharePoint 2013 BI demo environment! Have fun with creating some great demos!

Go back to overview.
Previous part: Installing Reporting Services.

Attach the SQL Server image to the virtual machine and launch the SQL Server setup. Go through the usual steps of the setup wizard until you need to choose the installation type. Choose Add features to an existing instance of SQL Server 2012 and make sure you select the correct instance from the dropdown if you have multiple instances on your machine. Click Next.

In the Feature Selection step, choose Reporting Services – SharePoint and Reporting Services Add-in for SharePoint Products. The add-in will for example include the Power View feature.

Go over the installation rules, check the disk space requirements and go to the Reporting Services Configuration. Here you simply have the option Install Only. Click Next.

Skip the Error Reporting, check the installation configuration rules and take a look at the installation overview. If everything seems OK, hit Install.

After all of this hard work, you deserve a coffee…

After a while the installation will be finished. Click Close to finish the wizard.

Once Reporting Services is installed, we need to register and start the Reporting Services SharePoint Service. This can be done with the PowerShell commands Install-SPRSService and Install-SPRSServiceProxy in the SharePoint 2013 Management Shell. However, the first command immediately fails with the following error:

What’s the problem? We forgot to update the Reporting Services installation with service pack 1! So launch the sp1 setup and go through the wizard.

You also might want to run the KB again we discussed in part 4. It’s possible a reboot is needed after these updates.

Run the two PowerShell command again, who will run without a problem this time.

When we check the services on the SharePoint server (Central Administration > System Settings > Manage services on server), we can now see the SQL Server Reporting Services Service, which is in the stopped state. Click on Start to start the service.

The next step is to create a SQL Server Reporting Services Service Application, or SSRSSA as we call it. Go to Manage service applications under Application Management. In the ribbon, click New and select SQL Server Reporting Services Service Application.

Configure the database server, the database name and the stored credentials. For the Web Application Assocation, choose http://sp2013bi. This is the only one we created in this walkthrough.

Click OK to create the service application.

The final step is to active the Power View Site Collection Feature. Browse to your SharePoint site and go to the Site Settings. Click on Site collection features.

Locate the Power View Integration Feature and activate it if it isn’t already.

The installation and configuration of Reporting Services and Power View is behind us, so now you can create some dazzling reports!

Go back to overview.
Previous part: Verifying PowerPivot Integration.
Next part: Creating a BI site.

Click on the PowerPivot Gallery link in the navigation menu. You probably get the Silverlight icon, telling you to install it. Click on the icon to start the installation process. You might have to refresh the browser after the setup to see take it effect. This step is necessary, the PowerPivot libraries and Power View make use of this technology.

When you upload some PowerPivot workbooks to the PowerPivot gallery, you should get some snapshots on the workbook preview. However, sometimes they do not work.

This can be caused by a multitude of reasons, of which a bunch are listed in this KB. The example I used probably suffered from the problem described in point 7: “The workbook might contain more than one data source. If so, a thumbnail will not be generated.” I uploaded another PowerPivot workbook with a simple pivot on AdventureWorks, and this one worked immediately:

Let’s proceed with the verification. In the navigation menu, click on Site Contents. Next click on add an app.

If you can see the Data Feed Library and PowerPivot Gallery in the list, the PowerPivot feature is integrated correctly.

Next up is the Central Administration integration. Navigate to the Central Admin site on the following URL: http://sp2013bi:2000. Now you can see why you had to choose an easy-to-remember port in the SharePoint configuration.

Click on Manage Farm Features in the System Settings section. The PowerPivot Integration Feature should be active.

Back in the Central Admin, click on Manage Services on Server. The SQL Server PowerPivot System Service should be started. The documentation also mentions also a SQL Server Analysis Services service, however this service cannot be found in the list. I don’t have a SharePoint 2010 installation to verify if it is there in that edition, but you don’t seem to need it in SharePoint 2013. We do know for sure the PowerPivot SSAS service, installed in part 6, is running.

The next step is Manage Service Applications in the Application Management section. The Excel Services application should be started, but more important, so should also be the Default PowerPivot Service Appplication.

Clicking on the application brings you to the PowerPivot Management Dashboard, a key instrument for the IT department to guide self-service BI. However, it is empty.

This might be caused by the fact you haven’t done much PowerPivot related activity on your server, but also because the PowerPivot Management Dashboard Processing Timer Job (quite a mouthful) hasn’t run yet and thus hasn’t collected any data yet. So click on Review timer job definitions to take a look at the job schedules. You’ll see the Dashboard job has a daily schedule.

Change this to an hourly schedule by clicking on the jobs name to go to the edit screen.

Click on Run Now to launch the job immediately. You’ll have to wait for a few minutes until the job has finished and the dashboard has been updated. However, in my case, the dashboard stayed empty. So I investigated the issue! In the job definitions menu, you can click on the Job History link at the left of the screen.

Here I could see the job had failed!

Time to troubleshoot! I used the free download ULS Viewer on Codeplex. After filtering on Excel, I saw the following exception:

The data connection uses Windows Authentication and user credentials could not be deletegated.

Or in other words: you did not configure a domain controller stupid! This is the one place where it has an actual downside of not having a domain controller configured. This issue could have other possible causes as well. This blog post does a nice job of listing most of them: The data connection uses Windows Authentication and user credentials could not be delegated.

In order to solve this issue, we need to use an unattended account to run the PowerPivot Management Dashboard. We can do this in the Excel Services application. Go to Global Settings and set the Target Application ID to PowerPivotUnattendedAccount (which we configured in part 7) in the External Data section.

Unfortunately, this only solves a part of the problem. If you run the timer job again, you can now see the Activity Chart is populated with one nice little bubble, which represents the dashboard itself. But the Server Health is still very empty. This is caused by the fact that this chart uses some Excel workbooks behind the scenes. These workbooks need to be configured themselves to use an unattended account as well. This procedure is described in the following blog post: Taking your PowerPivot server off the network. It is written for people using PowerPivot when disconnected – for example on an airplane – and thus have no connection to a domain controller. But it also works for people stubborn enough not to install a domain controller. Such as me.

The process is simple. When in the PowerPivot Management Dashboard, click on Site Contents. There go to the PowerPivot Management library.

Click on the GUID.

You’ll see the PowerPivot Management Data workbook and a folder called 1033. In the folder you will find a connection and two workbooks, Server Health and Workbook Activity.

Click on one of the workbooks to open it in Excel Services. There, click on Open in Excel, which will open the workbook locally on your computer.

Go to the Data tab and click on Connections.

There will be only one connection, called Data. Go to its properties. In the connection properties window, go to the Definition tab and click on Authentication Settings for Excel Services.

Set the account to use to None. This will force Excel Services to use the unattended account.

Click OK and repeat the same process for the other workbook. You might get an error about the name for the connection being a reserved name. If this is the case, change it to something else and save. Excel will change the name back automatically. When we now run the timer job again and go to the PowerPivot Management Dashboard, we have a fully populated dashboard!

This concludes the PowerPivot integration verification. In the next part of this series, we will play with even more awesome features: Power View!

Go back to overview.
Previous part: Configuring SharePoint.
Next part: Installing Reporting Services.

If you get the following message when launching the wizard, it means you forgot to apply SQL Server 2012 service pack 1 to the PowerPivot instance (like I first did if you’re wondering how I made the screenshot). So hurry back to part 6 of the series and apply that service pack!

If the wizard does launch successfully, you have the choice to configure or repair the PowerPivot for SharePoint installation, or to remove or upgrade features, services, applications and solutions. Since it is the first time we are running the wizard, you can only configure. Click OK.

The wizard consists of a list of sections. In some sections you need to supply parameters, such as passwords or service accounts, in other sections you are not required to take action (indicated with green flags). If you have already setup a farm and configured some services, the wizard will detect this and will automatically fill in the necessary values. In our case we are dealing with a blank server. In each section you can also inspect the PowerShell script that eventually will do the actual configuration. It might come in handy if you want to do a scripted deployment in the future. Since the wizard executes the PowerShell statements behind the scenes, it is possible to configure SharePoint without the use of a domain controller. I leave it up to you to decide if you want to include a domain controller or not.

I’ll go over each section where input is needed, but a lot of parameters might have already default values specified.

The first one is Configure New Farm. Here we configure the database server, the farm account (see part 5) and the passphrase. As you can see, I used a local account for the farm account, not a domain account. In a simple demo environment like this it helps if you specify the same password/passphrase for everything. It makes your demos go a lot smoother! (The usual disclaimer: do not do this in production). Finally specify an easy to remember port for the SharePoint Central Administration site.

Next up is Create PowerPivot Service Application. You need to specify a name for the service application, the database server and the name for the database. Some sections, like this one, are optional. If you do not want those configured, you can deselect the checkbox next to Include this action in the task list.

Create Default Web Application follows in the list. Specify a name for the web application, an URL, the database server and a name for the database. As with most service accounts, I specified the farm account.

Check if the values specified at Deploy Web Application Solution satisfy your requirements.

You can create a site collection in Create Site Collection (they tend to keep things logical). Specify an URL, an administrator – the email address can be fake – and the title for the site.

If you want to add the PowerPivot features to the site, such as the carrousel in the PowerPivot library, select the checkbox in Activate PowerPivot Feature in a Site Collection.

Next we are going to configure the Secure Store Service, which is important if you want to work with unattended accounts. Specify a name for the Secure Store Service service application (what an alliteration) and the database server in Create Secure Store Service Application.

Also configure the Secure Store Service proxy in Create Secure Store Service Application Proxy (the defaults are fine).

And specify a passphrase in Update Secure Store Service Master Key.

Finally specify an unattended account for the data refresh in Create Unattended Account for Data Refresh. Configure the target application ID, a friendly name if you must, an account and the site URL.

Excel Services is next in the list. Specify a name for the service application in Create Excel Service Application.

Add the SSAS PowerPivot instance we installed in part 6 to the Registered PowerPivot Servers in Configure PowerPivot Servers.

Verify the Service Application Name in Register PowerPivot Addin as Excel Services Usage Tracker.

You get an overview of the most important information at the top, in Configure or Repair PowerPivot for SharePoint.

Click Validate at the bottom of the wizard to validate all your specifications. If everything is OK, all items get a green flag.

Click on Run to start the configuration of the SharePoint environment.

Once the configuration is done, you can go to the Central Administration site or your very own PowerPivot site to check if everything works. At first the sites might load a bit slowly, but once everything is in the cache it should go quicker.

In the next walkthrough, we’ll go deeper into verifying if everything behaves as expected.

Go back to overview.
Previous part: Installing PowerPivot for SharePoint.
Next part: Verifying PowerPivot Integration.

The first step is to create a service account for this instance. I named it SP2013BI\SPPowerPivot.

The next step is attaching the SQL Server image to our virtual machine and launching the SQL Server setup. Go through the usual steps of the setup wizard until you need to choose the installation type. Choose the type Perform a new installation of SQL Server 2012 and click Next.

Enter the product key and accept the license terms.

Choose the SQL Server PowerPivot for SharePoint setup role. We already have a SQL Server instance installed, so you can deselect the checkbox for adding another instance. Click Next.

Everything is already selected in the Feature Selection page, so just click Next to go to the next step.

A set of operation rules will run. It will for example check if the PowerPivot instance name has not already been taken. If everything is validated, click Next.

In the Instance Configuration page, leave everything as default and click Next.

Review the disk space requirements and go to the Server Configuration page. Configure the SPPowerPivot account as the service account for our Analysis Services instance. Leave the collation to the default. Click Next.

Add yourself and the SharePoint farm account as administrators. If you have multiple drives, you can optionally move the data and log files to another disk. Click Next twice.

The Installation Rules will run. If every rule is validated, click Next.

Review the installation and click Install if you’re satisfied.

Go grab a beverage of your choice while the SSAS instance is being installed…

Once the installation is finished, click Close to finish the setup.

There is now a SSAS instance called PowerPivot running on the server. When your SharePoint environment is finished and you have PowerPivot workbooks in SharePoint, you get the following list of databases when you log into the instance:

Each PowerPivot workbook is a Tabular Model – with a bunch of gibberish appended to the workbook name - stored in the instance. The interaction is limited though. For example you cannot process the tables.

The last piece is running SQL Server 2012 service pack 1 again. Run the sp1 setup and make sure the PowerPivot instance is selected.

After the installation, finish the setup.

We are now ready to launch the PowerPivot Configuration Wizard, which will be discussed in the following part of the series.

Go back to overview.
Previous part: Installing SharePoint.
Next part: Configuring SharePoint.

The question is the following: How can you add a new column to existing table (already populated) and make it a sequence column?

Let's take a look. First create this very simple table

What you want to do is have the sequence start at 6, here is how to do that.

Use START WITH to indicate that the sequence should start at 6

The next step is to add a default to the column, this default would use the sequence

Now if we do an insert, followed by a select

Here are the results

id
1
2
3
4
5
6

As you can see the value 6 was inserted

There you have it, a simple way to change a column to a sequence column

If you run this code

you get these results

Col1
Z
A
B
C
M

What I want to see is the following

Col1
A
B
C
M
Z

Without using ORDER BY, how would you make the SELECT query return Col1 in ascending order? You can't make changes to the table, all that you are allowed to modify is this part

Warning
Do not use these suggestion in production code, the only real way to guarantee order of a result set is by using ORDER BY