SyntaxHighlighter

Friday, November 21, 2014

SSAS Warning: Define attribute relationships as 'Rigid' where appropriate

When you're first deploying a dimension with hierarchies in SSDT-BI, you might notice this warning (or more appropriately, this directive): Define attribute relationships as 'Rigid' where appropriate.

A quick interwebs search reveals the answer in dozens of different websites and you'll also probably run across the answer on Microsoft's website. The answer is -- and I quote:
This rule analyzes dimensions to determine whether they contain at least one user-defined hierarchy in which each member attribute uniquely implies the member attribute above it. If the dimension does have such a hierarchy, the rule checks to see whether all the attribute relationships in the dimension have the RelationshipType property set to Flexible.
Thanks, Microsoft documentation! You've given me the answer. Now all I have to do is find the RelationshipType property and we're good to go.

Now where is it? Is it here? Nope. There? Nope. Over yon? Nuh-uh.

Well! Did you just spend 20 minutes of your life searching for the answer and you still haven't found it? Well, it's not completely obvious. Remember, everything in every relationship diagram has properties, even lines and arrows!

  1. In your Multidimensional Analysis Services project in SSDT-BI (or SSDT or BIDS or whatever Microsoft has renamed it this week), open your dimension for editing.
  2. Click on Attribute Relationships
  3. Click on the line-arrow between any two attributes. 
  4. Take a look at the Properties window and you'll see RelationshipType. That's where you can change it to Rigid

Of course, you could read that statement all day and it still might take another twenty minutes to figure out what I'm talking about. In that case -- like me -- you're a visual learner. So in that case, there are two screenshots below to help you find it.

Click on the line-arrow!

Check the properties window. Hint: press the F4 key.
You're welcome, internet.

Update (same day): I just came up with two new helpful tips. Firstly, You can double-click the warning message in the error list and it will take you to the correct location in the project. Secondly, you can also double click on the line-arrow to bring up a dialog bog that will allow you to change the relationship type. See screenshots below.



Tuesday, October 14, 2014

Learning the Microsoft BI Stack for Free through Videos

Microsoft's Business Intelligence offerings are varied and sometimes difficult to connect together. The marketing department sometimes doesn't know what the development departments are doing, and the the development departments sometimes don't interoperate as well as they could.

Despite a bit of disorganization, the Microsoft BI stack -- last I heard it was called BISM -- the Business Intelligence Semantic Model -- is powerful and useful. Learning it can be a long process. Essentially, Microsoft's BI offerings are all categorized under their database offerings, so when you go into the field of database administration, you will also learn reporting, cubes, data cleansing, master data, integration, visualization and more!

Microsoft offers a series of certifications that cover the entire gamut of database and data analysis tools. I talked about them briefly here: Road to Certification: 70-461 Defeated and an Analogy between Microsoft Certifications and the US Educational System. One way to approach these certifications is to simply buy the training kits that Microsoft offers. This is not a bad approach in all honesty for people who learn by reading -- but some people simply learn better in visual environments.

Luckily for us, there are a whole bunch of videos out there to help us learn the basics of these database and data analysis tools. Here is a quick breakdown of my favorite sites:

  1. Microsoft Virtual Academy. First and foremost, Microsoft has produced hundreds if not thousands of categorized videos and they are all available for free. No matter which Microsoft product you'd like to learn or which certification you're studying for, there's some sort of offering. Find out which certification you're interested in, buy the training kit, and start watching videos! They even have quizzes to test your knowledge.
  2. MSBI Academy. This is another great site -- one of my favorites. It goes into depth on a lot of really interesting topics that might be for the more advanced users -- cubes, PowerPivot, etc.
  3. Learn Microsoft BI. This site is not as well organized or as well updated, but it does have some good videos for some slightly older products.
  4. Lynda. You can learn quite a bit about the Microsoft BI stack from this general purpose learning site.
  5. YouTube. YouTube is not exactly set up for Microsoft BI -- you know it's a big, disorganized mess when it comes to finding structured information. But if you know what you're looking for, just type it into the search box and start roaming around until you find something you like!

If you know of other good video tutorial sites cover the Microsoft BI stack, please drop me a line! 




Monday, October 6, 2014

What Do I Really Need to Install Power View in SharePoint?

Introduction

The landscape for Microsoft Business Intelligence products in the enterprise is frankly cluttered. There are several great tools that run primarily in Excel – after all, 90% of the world’s data analysis currently happens in dimly lit cubicles using Excel. But Microsoft has been trying to get into the big leagues by bringing their BI offerings to SharePoint. SharePoint is, in effect, Microsoft’s version of a “wiki” platform. It’s their non-developer-centric web-based collaboration and publishing tool. It makes sense that when data analysts create complex Excel reports that they should be able to share them using a technology more modern than the F: drive – and that’s why SharePoint was built.

Nonetheless, trying to figure out what’s what is a bit mind boggling. This is fairly evident if you’ve ever tried to install or administer these various different products. Microsoft’s documentation in this arena is a scattershot collection of chaos. This is evident in the fact that there are 10 different documents explaining how to install PowerPivot for SharePoint and they all have different nuances and characteristics, not a single one of which “gets it right” – or in more concise terms, not a single one is authoritative.

Power View for Excel in itself is not simple to install. It doesn’t come pre-activated from Microsoft. It requires a person dig deep inside the bowels of Excel’s labyrinthine options to activate a COM add in. Then, in most cases, the user needs to install Silverlight since it isn’t part of the operating system. Can you imagine the system administrator’s frustration when everyone in the organization suddenly wants Excel to do more? Beyond that, Power View requires you to essentially understand PowerPivot. PowerPivot is a data modeling tool – a very, very powerful one. Both of these pieces of software are easy to use, but getting maximum value out of them is – like any other software – a learning process.

But that’s a minor inconvenience compared to the system administration nightmare that is SharePoint. SharePoint is dauntingly complex all by itself and then trying to add a bunch of services that are supposed to interact with other services and servers and software. Add to that complexity the Microsoft licensing model – you should be aware at this point right now that your deployment isn’t going to be cheap. SharePoint Enterprise edition is not cheap, nor is SQL Server Enterprise edition (or Business Intelligence edition). Let’s be honest – if you were planning on running Microsoft’s BI platform for less than $25,000 in licensing fees, you’re going to be sticker shocked to say the least.

In any case, I’m going to try to break this down into the simplest terms and be thorough at the same time. This post is about all the actual requirements to get Power View for SharePoint working. I will also try to explain the software linkages and basic terminology. If you don't want basic explanations and want to get to the nuts and bolts of this post, jump down a few sections. 

What is Power View?

Power View is an interactive data exploration tool. It allows you to explore data and relationships in order to find insight and trends. It's similar to other software like Tableau. It's really neat because you can filter data in one chart by choosing a data point in another chart. It allows you to see relationships visually. If you've never tried it, give it a whirl. You'll need Microsoft Excel 2013. 

Power View comes in two flavors. It exists in Microsoft Excel and also in SharePoint. In SharePoint, people can look at your Power View worksheets and interact with them and they can also use your data model to create their own Power View worksheets. 

What is PowerPivot?

PowerPivot is the data modeling engine that sits behind Power View. If Power View is the car, then PowerPivot is what's sitting under the hood. This metaphor isn't great, because you still need to know how to be a mechanic to make this motor run. Granted, it's still 1910 and it's just a couple of spark plugs and a distributor (i.e., simple), but you will have to get under the hood to make this motor run. PowerPivot is not a database although it does store a lot of data in a tabular format. Tabular format data stores are beyond the scope of this post, but if you'd like more information, check out xVelocity and Analysis Services

PowerPivot comes in two flavors just like Power View. The Micrsoft Excel version and the SharePoint version. The SharePoint version actually doesn't stand alone -- its actually a "frontman" for Microsoft Analysis Services running in Tabular mode (a component of Microsoft SQL Server). 

What is SharePoint Server?

SharePoint Server is a beast. It's a big piece of server software from Microsoft. Its purpose is to unify the universe. No, seriously! Okay, it's a collaboration engine. It's also gigantic and amorphous. You can do anything with it. The basic premise is this: anything that can be shared or collaborated on or published can be part of SharePoint. Think of it as a gigantic wiki page that allows people to share documents, collaborate on projects, build web pages, publish dashboards, and so on. The problem with describing SharePoint is that it's so big and complex and undefined. It's like a big ball of play dough -- you can quite actually mold it into any shape you want. Want a web page? Okay. Want a project management site? Okay. Want a blog? Okay. Remember: sharing, collaborating, publishing. That's key.

There are four different services in SharePoint that you'll also need. The jumble of services gets tangled so I'm going to try to detangle them below.

Reporting Services

Technically, Reporting Services is part of SQL Server. However, it can be installed in SharePoint mode and it's one of the many pieces of the puzzle necessary to get Power View working.

PowerPivot Services

PowerPivot, as described above, is the tabular data engine inside Excel that stores the data that Power View uses as its data source. In SharePoint, this service must be installed, configured, and enabled to essentially connect to SQL Server Analysis Services running in PowerPivot mode (basically, a special form of Tabular mode). 

Excel Services

Excel Services at their most basic level allow SharePoint users to open and use Excel spreadsheets. On a deeper level, these services are necessary to open PowerPivot models and Power View sheets that have been created in Excel on the desktop. 

PerformancePoint Services

PerformancePoint Services are simply a dashboarding suite. They allow you to connect to various data sources (primarily SSAS cubes) and create meaningful and easy-to-use dashboards. Dashboards are typically one-page collections of informative charts, graphs, or KPIs that give a user a quick overview of what's happening with a system or a process. 

What is SQL Server?

SQL Server is Microsoft's relational database product. The core database engine is fairly easy to understand. It's basically just a very advanced SQL engine. However, there are many, many components tacked on to SQL Server that are necessary to perform business intelligence using Microsoft products. For instance, two key components that must be installed are Reporting Services and Analysis Services. In addition, these services cannot simply be installed in their native modes -- they must be installed in SharePoint mode. 

The Nuts and Bolts

That's a clever title, I reckon. (Insert smiley face)

That's a lot of software and moving parts just to get one data exploration tool up and running. Unfortunately, there's no way around it if you want to share your Power View sheets within your organization. 

You will need this software:
  1. Windows. You will most likely need a server edition but possibly not -- I've always used the server editions for SharePoint, but check the system requirements to see if it'll run on desktop editions. 
  2. Internet Information Services. This web server software is packaged with just about any modern version of Windows you can think of. But no matter which version of Windows you settle on, SharePoint needs it. 
  3. SharePoint Enterprise Edition. Yes, Enterprise. You cannot run the BI tools on Standard.  
  4. SQL Server Enterprise Edition or Business Intelligence Edition. I've never tried using Developer edition, but according to Microsoft, it is exactly the same as Enterprise Edition -- so in theory it should work. 
Installing #1 - #3 above is outside the scope of this article. But let's say you get that far and you're going to install SQL Server. There are several things you need to keep in mind:
  1. When you install SQL Server (usually on a machine separate from SharePoint), make sure you do not install any Reporting Services of any kind. 
  2. Feel free to install  Multi-Dimensional Analysis Services on your default instance. 
  3. After all that's done, you will go back to your SharePoint server and install Reporting Services for SharePoint and Reporting Services for SharePoint plugin. 
  4. Finally, you will install PowerPivot for SharePoint. This is one of the options when installing SQL Server. If you don't see the option, you might not be installing from Enterprise or Business Intelligence Edition disks. It will automatically install a new instance of Analysis Services called [ServerName]\POWERPIVOT. This is a special version of the Tabular model of Analysis Services. It's fairly well locked down to only service SharePoint so you won't be able to use this to do backups and restores of other, non-PowerPivot related databases. 
    • What's really important here is that your Excel Services configuration must point to the POWERPIVOT instance of analysis services. If it does not, the chances of getting it to work are very very low.
Getting Power View to work in SharePoint is not easy or simple or straight foward. There are a number of guides on the web and you might have to go through more than one of them. I can pretty much guarantee this: there are a number of important details that Microsoft doesn't emphasize well. It's these small details that make the difference between a successful configuration and one that's not. 

Good luck!





Friday, June 6, 2014

SQL Server Monitoring Tools (Dashboards)

Now see here! Dashboards are awesome. I'm a person easily stimulated by visual input and a good dashboard is, in my opinion, the quickest way to get information into my brain.

Zion Control from Matrix Reloaded
What I really really want is an interface like Zion Control from the Matrix movies. I know, I know. We've wanted this kind of stuff for years -- all of us.

Honestly, though, we're getting pretty deep into the 21st century, aren't we?

So why do SQL Server monitoring interfaces suck so badly? Why do dashboards in general suck so badly? We are data scientists and the data visualization experts. All I want is an easily installable, easily customizable interface that I can hook up  to a data source and instantly have metrics and graphs and charts. It's not that much of a request.

Well, here's the reason, I think. Companies that do this kind of stuff make big big dollars charging for it. Companies like QlikView and what not. Microsoft is trying to get into that space with Power Query and Power Map and Power View, and those are amazing tools that I use on a daily basis. Unfortunately, we have to tie them to SharePoint and the administration and configuration of that is just a nightmare. It'll be at least another year before we have those bad boys installed here.

So in the meantime, I'm evaluating SQL Server Monitoring software. Ugh. It's bad. It's ugly. It's hard to customize. Yes, I know, I'm the DBA and I need to know all this stuff. Honestly, though. It all needs work. Why can't we have something clean and simple and easy like Dashing?

Anyway, in my opinion, the best visual monitors are Spotlight on SQL Server and Idera's Diagnostic Manager. I'm still running through the evaluations. I probably won't post a full write up since there have been so many written before. Here's a thorough write up of various SQL Server Monitoring Tools. Enjoy!

Update 2014.06.06: Here is a comprehensive list of SQL Server Monitoring Tools. It doesn't dive into the details of each product.

Update 2014.06.06: Here is another review of some SQL Server Monitoring Products. The article itself is not very interesting, rather the comments are what I found fascinating.

Friday, May 30, 2014

SQL Server: Huge Log Files and Recovery Pending Status

It's been an interesting morning so far.

We all got an email from some phantom IT department member last night (who knows who it was because it was a system account) letting us know that there was going to be some reconfiguration going on at the data center (which is hosted). It basically said, "There's nothing to see here. Move along."

So we moved along.

Last night around 8:33, I got a notification from my main production SQL Server (2008 R2 Enterprise) that the server had been restarted and moved to a different cluster node. I logged in remotely, checked it out, all looked good. I figured it was a failover or a failover test. (Incidentally, for some strange reason, our IT department sees fit not to allow the database administrator -- me -- any access to the clusters). Then at 10:45PM I got another reboot message -- same thing, but this time it was flipping back to the other node. Interesting, but I was able to get to all the data and nothing seemed amiss in the logs.

This morning, there is basically nobody from IT in the building except for myself and one single system admin. This SA has been working with a contractor on a System Center database and noticed that he couldn't get any data from System Center. He logged into SSMS and noticed that the databases were all in a state that said "Recovery Pending" and he couldn't get any information about the databases whatsoever -- no properties, no file information, nothing. So he restarted the server. Still, "Recovery Pending."

He came to me and asked me to help troubleshoot. Right away I knew that there were only a couple of paths that could cause this error message. Basically the log files for these database was missing or corrupt. However, the system databases were all online.

Step 1. Check for properties on the database. Not available. Curious.
Step 2. Check the logs. Opened up an archived log. Or tried. SSMS started choking. Okay, the log must be too big?
Step 3. Check the physical log on the disk. It was 1.2GB. Yes! You read that correctly! It was 1.2GB! No wonder SSMS was choking.
Step 4. Check the data files and the log files.

CRASH! My PC (formerly my computer) was showing a single drive -- the system drive. There used to be 3 LUNS there mapped to three different drive letters (E:, F:, G:).

Step 5. Open up the disk management tool. The LUNs were still there, they just offline. We flipped the switch and they all came online.
Step 6. Restart SQL Server.
Step 7. Verify in SSMS that the databases were online. They were.

I am not a SAN administrator not am I a dedicated Windows Server Admin, but I have not to this date seen a SAN LUN completely go offline. I'll leave that work to the the Sysadmins ...

What's the lesson here? If your databases are in "Recovery Pending" mode, one of the first things to check is whether the files actually exist -- in fact, if the drives they are on actually exist.

UPDATE: I think the log was so large because the LUN volumes went offline while SQL Server was still running. I haven't had a chance to look at the log file yet, but that's my guess.

Thursday, May 15, 2014

TSQL: Check to see if a partition function already exists OR check to see if a partition scheme already exists

When working with TSQL scripts and creating objects, it's typical to drop the object before creating it using a conditional check on it's object identifier. For example:

IF( OBJECT_ID( 'myTable', 'U' ) IS NOT NULL )
BEGIN
 DROP TABLE myTable;
END

The biggest reason for this is to avoid an error. What if the table didn't exist? If you tried to drop it without the conditional expression, you'd get an error and your script would likely halt.

But what if you're trying to drop/create an entity that isn't necessarily an object (i.e., it doesn't have an object identifier and therefore the OBJECT_ID function won't work)? You will definitely need to change your condition up a bit by querying a catalog view.

For partition functions and partitions schemes, you will query sys.partition_functions and sys.partition_schemes.

-- create a paritition function to test this out
CREATE PARTITION FUNCTION PfTest( tinyint )
AS RANGE LEFT FOR VALUES( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 );

-- visually inspect the partition functions table to see that it's there
SELECT
 *
FROM
 sys.partition_functions

-- drop the partition function if it exists
IF( EXISTS( SELECT * FROM sys.partition_functions WHERE name = 'PfTest' ) )
BEGIN
 DROP PARTITION FUNCTION PfTest;
END

-- visually inspect the table to see that it's gone
SELECT
 *
FROM
 sys.partition_functions

The EXISTS function basically runs the query to see if there are 0 rows (hence, nothing exists) or 1+ rows (hence, something exists). If the sub-query returns a single row that matches the name of PfTest, then the condition is true and the partition function will be dropped. If it doesn't exist, then it will not be dropped.

Another option -- if you prefer cleaner and more readable TSQL -- is to create some custom functions, something like PARTITION_FUNCTION_ID or PARTITION_SCHEME_ID.

Monday, May 12, 2014

Road to Certification: 70-461 Defeated and an Analogy between Microsoft Certifications and the US Educational System

If I recall my previous post correctly, I was perfectly accepting of failure of the 70-461 exam. As I reported, last Saturday I sat through the exam knowing that failure was an option because I had the ever-helpful Second Shot voucher in play. It turns out I didn't need it. I passed the exam on the first try! That's the second pass in a row and I couldn't be happier about it. Two more exams and I'll be an MCSA (Microsoft Certified Solutions Associate).

On a related but slightly tangential note, I have been having some thoughts on the cursus honorum (Latin for course of offices, or in more common terms, the political ladder) through the certification process. Analogies are helpful and I think a comparison to our US education system will be appropriate to understand how the process works. Please note that this information is relevant for SQL Server 2012.

Level 1 - High School Diploma

MCT - Microsoft Certified Technician. This is the most basic level that Microsoft offers. You have to know quite a bit about databases to pass this test but if you've been working in the database world for a few years, it shouldn't be that hard. This won't really help you get a job just like a high school diploma won't, but it demonstrates effort and basic competency. Incidentally, elementary and middle school would be the equivalent of learning how to use operating systems and productivity applications. For an MCT in databases you have to pass one test. 

Level 2 - Bachelor's Degree

MCSA - Microsoft Certified Solutions Associate. After the MCT is the MCSA. Not even a seasoned database administrator would be able to pass these without specific training in the Microsoft SQL Server stack. Sure, knowing advanced queries would help a lot but without actually picking up some training material, you'd be very lucky to guess what SET TRANSACTION ISOLATION LEVEL SNAPSHOT actually means. This level includes a lot of theoretical material that will get you going on the basics of a lot of different aspects of querying, data warehousing, and administration. This will definitely help you get a job if you back it up with a few years of experience -- but unless you have lots and lots of experience, don't expect to be coming into the Senior level. For an MCSA in databases you need to pass three tests. 

Level 3 - Master's Degree

MCSE - Microsoft Certified Solutions Expert. This is where it gets interesting because, for the database track at least, the program actually splits off into two separate tracks -- Data Platform and Business Intelligence. Each one requires two tests. In my opinion, this coupled with a good amount of experience will definitely qualify you for a job at the Senior level in terms of database administrators or business intelligence analysts. Although many less experienced IT managers might not see the value in certifications, the ones who have been around for a long time will no doubt understand that certifications provide the theoretical underpinnings of a highly experienced professional. Sure, you may have been working on SQL Server technologies for 20 years by now, but if you don't know what you don't know then you're truly missing out on the bigger picture -- and by extension, so is the company you're working for. 

Level 4 - Doctorate

MCM - Microsoft Certified Master. Finally there's the grand-daddy of all database certifications. I've only read about this one and it sounds fairly hellacious. There was, of course, an exam or two to pass for this level but the most interesting part of it was that you had the option of spending several weeks on Microsoft's campus and learning from top-tier Microsoft product specialists and perhaps even product developers. Unfortunately, this level is gone. Microsoft Learning was not getting enough interest in this -- apparently it was a $20,000 investment and so they retired it late in 2013. Sadly, they have yet to announce a replacement. However, I'm sure that a new MCM program is in the works -- and hopefully a bit cheaper. If you ever achieve this level, you definitely deserve to be at the senior or director level in your local IT department simply because you somehow managed to talk someone in your company into shelling out the big bucks for the training. 

Level X - Honorary Degree 

MVP - Most Valuable Player. Were you the popular kid in high school? The cheerleader or the quarterback or the class president? If so, you know how these kinds of popularity contests work. It's a lot of hand shaking and baby kissing. Basically, hook or crook your way to the top and become everybody's friend and they will vote for you. I'm joking, of course, but remember that every joke has a kernel of truth. If you're at this level, you are probably working the lecture and training circuit already and you are -- in some sense -- your own boss. 
So -- you're probably thinking to yourself -- "Oh, this guy is barely in Level 2. What does he know?" Well, I know I have almost exactly 15 years of experience with the SQL Server product line dating back to the days of SQL Server 6.5. It's been a long road, of course, to get where I am. Now that I have a massive amount of experience in all three areas -- production, development, and business intelligence -- I want to fill in the gaps and get those shiny pieces of paper to prove that I know what I know. Plus, I already have an actual master's degree and I was a teacher for 4 years. So, long story short, I've been through the wringer a couple of times. Despite the fact that I'm at Level 2 in terms of certifications, I'm sitting somewhere within Level 3 in terms of actual job competency. And to be honest, I wouldn't mind being Level 4 or even Level X someday.

Saturday, May 10, 2014

Road to Certification: Pre-exam Jitters for 70-461

About a month ago I took the MCT exam for database fundamentals (98-364). I signed up a day in advance and blew through the test in less than 30 minutes and got a high passing score. For me (as for any database administrator), the exam was simple. For anyone who doesn't have a lot of experience with databases and querying, the exam would have been difficult to pass. But that's the point of these certification exams, right? To basically make sure you're at the level you say you are.

Today, in about 2 hours in fact, I'm going to take the first test in the MCSA exam series, the 70-461. I'm a bit nervous, I admit it. However, I'm not that nervous for two reasons. First, I managed to score a retake voucher -- if I fail, I get to take the exam again before May 31st. Therefore, I fully expect to fail and it's okay because I get a redo. Second, I studied -- a lot. I read the 70-461 prep book and took the practice exams. I also watched all the videos and took the practice exams on Microsoft Virtual Academy. Finally, I read through this synopsis of an actual exam taker's experiences with the exam.

What scares me a little bit is that I've found a couple of exam questions and exam review topics that point to information that I have never even heard of before today. But, as I said earlier, I'm not that worried. I get a redo. After the last exam, I got a scoring sheet that told me what areas I need to work on. So, if I fail the exam the first time (and I hear it's a fairly common occurrence), then I'll bone up on my weak areas and take it again in two weeks.

One other thing that kinda bothers me -- it's supposedly a 3 hour exam! Yikes. I'm glad I didn't schedule my post-exam, stress-relieving massage until 4 hours afterwards!

Thursday, April 10, 2014

Microsoft SSRS Error: The report parameter is read-only and cannot be modified

So one of my scheduled SSRS (SQL Server Reporting Services) reports that was called with GET query parameters in the URL started failing today. It started giving this message:
Unknown error generating/adding reports
I checked the SSRS logs (in my case in C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles) and found a message I have never seen before and was not anticipating:
The report parameter 'PARAM' is read-only and cannot be modified. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ReadOnlyReportParameterException: The report parameter 'PARAM' is read-only and cannot be modified.
Note that PARAM was substituted for the actual parameter name to protect the innocent. Bizarre, right? This happened on a column that was previously hidden from the user in the SSRS Report Manager. It would run just fine in SSDT (SQL Server Data Tools).

One thing I had noticed is that when I logged in, someone had applied updates to the SSRS server (or perhaps it was an automatic update). Aside from that communication slip-up between admins, I wonder if it's related? Microsoft released a bulletin a few months ago regarding this.

Here is the basic solution:

  1. Go into the SSRS Report Manager web interface.
  2. Find the report.
  3. Right click on the report and click Manage.
  4. Go to the Parameters tab.
  5. Uncheck the Hide and Prompt User checkboxes on the affected parameters.
  6. Apply the changes.
  7. Recheck the Hide parameter.
  8. Apply the changes.
  9. Test your report using it's parameterized URL to see if the problem is solved. 

Simple!

Wednesday, March 19, 2014

TFS Plugin for SSMS Stopped Working Today

Today, the TFS plugin that I use (MSSCCI) that I use for version control stopped working. When I try to load a solution, SSMS simply ignores my request. No error message, no event logs, nothing. I can connect to the TFS server but when I open the solution from version control, again, SSMS ignores my request. I can see my solutions in VS2012 and in the Team Explorer window/Source Control Explorer.

What happened!? Argh. I think the IT department pushed a bunch of updates. Unfortunately, I don't have any control over that. I don't know which one it might have been.

So I uninstalled SQL Server 2012 and the MSSCCI Provider, reinstalled the client tools with updates and service packs and all that, reinstalled the plugin, and no go!

Now I'm installing 2008R2 and 2014 CTP2 client tools to see if the older and newer versions of SSMS are also horked.

UPDATE: I got desperate and I didn't want to reinstall the OS, so I uninstalled all SQL Server and Visual Studio products, uninstalled all the SSMS plugins, and reinstalled everything. Drastic, I know, but it fixed the problem.

UPDATE2: Have you ever had a "duh" moment? Yeah, me too.

Turn on the Solution Explorer with CTRL+ALT+L. See below.


Thursday, March 13, 2014

Set Theory Mathematics: The Problem with Sums of Sums and Averages of Averages

Recently, it has come to my attention that the reports that have been written prior to my coming have an itsy bitsy flaw. Let me explain.

In one particular report, we count groups of people carrying out actions. We then add and subtract the counts of those actions to get a total number of people doing aggregate actions.

I'm going to make up a hypothetical example. Let's say I'm counting the actions of grocery shoppers who bought certain items.

  • 32 people bought paper towels
  • 17 people bought napkins
  • 12 people bought chicken 
  • 5 people bought crackers
Now, let's say I want to know how many people bought paper products. The answer should be easy. 

32 + 17 = 49

Right? That would be true if the sets were mutually exclusive, like the Venn diagram to the left. That means the sets don't overlap ever.

Unfortunately, that is most likely incorrect! Why? A person can very easily buy both napkins and paper towerls.

Let's say I failed to mention this fact:
  • 7 of the people who bought napkins also bought both napkins and paper towels
So the more accurate answer is:

32 + 17 - 7 = 42

Why does this happen? Because of the count of counts and the average of averages and the sum of sums (or any combination thereof) are not the same! It's a mathematical fact of life, and we need to take it into account whenever we're dealing with sets. 

Sets, like scalar values, can be "added together" and "subtracted" in a manner of speaking. There are lot of special terms and symbols used in set mathematics that I won't get into here today. What is relevant now is how this all applies to database sets, otherwise known as tables.

If you want to get the proper count of two intersecting (or exceptional) tables, you will need to use the set operators, UNION, INTERSECT, and EXCEPT. 

If you want to (distinctly) add two tables together, use UNION. If you want to get the rows where the tables intersect, use INTERSECT. Finally, if you want to subtract two tables, use EXCEPT.

Once you've completed your set operation, then you can summarize it. But if you want accurate numbers, never summarize before the set operation.

Here's some pseudo-sql as an example:

SELECT
 COUNT(customer_id) AS 
FROM
(
  SELECT
   customer_id
  FROM
   products
  WHERE
   product = 'paper towels' 

 UNION

  SELECT
   customer_id
  FROM
   products
  WHERE
   product = 'napkins'

) unioned_products


The reason this works is because duplicate customer_id values are thrown away. UNION works by sorting both tables and then merging them together, throwing away any duplicates that it finds. In the second Venn diagram above, people who bought both napkins and paper towels were doubly counted when in fact they should have been singly counted.

Rest assured, this is an imagined scenario that came about because of a real situation. Our reports at work were most definitely reporting the wrong numbers and now we have to go back and rewrite a lot of them with this specific problem in mind.

What will you do the next time your CEO comes to you and asks you how -3 people bought your products this month? Embarassing. Just tell 'em "I inherited the report from the last report writer." Then fix it as fast as you can! 

Friday, February 28, 2014

Installing a trial of SQL Server 2014 and Windows Server 2012 in a VirtualBox VM

It's sometimes really useful to get a trial copy of SQL Server to play with. There are a number of reasons for this: certification testing, validation, upgrade testing, skills training, experimentation, recovery trials, etc.

There are a lot of caveats, however. You don't want to install it on your work machine because you don't want to mess up your current configuration. The next best option is using a virtual machine. But you don't want to spend hours or days installing and configuring Windows and you don't even have Hyper-V running on your operating system (for instance, Windows 7).

Luckily, all these problems have easy workarounds. Here's a preview:

  1. Download the VHD file for the Windows Server 2012 trial
  2. Downlload and install VirtualBox
  3. Windows is already installed! 
  4. Download the ISO for SQL Server 2014 
  5. Mount the ISO virtually in your virtual machine
  6. Start the virtual machine
  7. Install SQL Server as you please
  8. You're done! 
  9. However, if you're really interested, you don't even need to sign in to your new VM to access SQL Server. Just make it accessible to the network and make sure your VM host and guest can talk to eachother. Then just install your Client Tools (such as SSMS) on your host and you can remotely manage the guest. AMAZEBALLS!
This process looks longer than it actually is. The most tedious part about this will be waiting for the downloads, honestly. 

Let's get started!

Download the VHD file for Windows Server 2012 Datacenter (Trial)

This trial version of Windows comes in a pre-packaged VHD (virtual hard drive) file directly from Microsoft here: Microsoft Windows 2012 Trial

Download it and put it somewhere safe on your local machine. From here forward, your local machine will be known as the host machine.

Download and install VirtualBox

Download from here: Download VirtualBox

You'll want the 64-bit version for Windows. Put it in a safe place on the host machine and then install it. You shouldn't have to worry about install options.

Create a new virtual machine using the VHD file

This is easy. Just start up VirtualBox and click [New]. When you're setting up your virtual machine, make sure to choose Microsoft Windows for the type and Windows 2012 (64-bit) for the version. See the screenshot. 

Next, you won't want to create a new hard drive. Instead, use the one you downloaded from Microsoft. 


You will most likely receive an error that the hard drive already exists See the screenshot. The error is approximately "Cannot register the hard disk file because a hard disk with UUID already exists." If that's the case, you need to cancel the process and go to the main VirtualBox window. From there, click the File menu, then Virtual Media Manager. Delete the old hard drive from the list. It will ask you if you want to delete the file -- don't do that (unless you'd like to redownload the file). See here for more details on the error "Cannot register the hard disk file because a hard disk with UUID already exists".

Next, you can play with the settings of your Virtual Machine. Give it as much or as little memory as you feel appropriate. Give it more or fewer CPUs. Do what you feel is appropriate for your situation. The defaults should run adequately for demonstration purposes. 

Here's some extra help: Microsoft VHD images on VirtualBox (although not everything in the article is accurate or applicable to this situation

Download the ISO for SQL Server 2014 

Download the Microsoft SQL Server 2014 trial. Make sure to grab the ISO.

Install the ISO as a Virtual DVD in your VM

Go into the settings of your VM, click on Storage, then click the little icon of a CD next to the CD/DVD Drive. Then choose "Choose a virtual CD/DVD disk file..." and browse to your SQL Server ISO file. Save the settings.

Start your Virtual Machine

In the Oracle VM VirtualBox Manager window, highlight your server and then click start. It will take a while to boot and it will look a lot like a Remote Desktop session. Jump through all the standard hoops. 

Next, you're wondering, how do I send CTRL+ALT+DEL to the Virtual Machine to log in? Click on the machine menu and then click on "Insert Ctrl-Alt-Del."

Install SQL Server

Once you're in Windows, go to This PC and double click the installation DVD for SQL Server. Then click the setup icon to start the installation. 

It is beyond the scope of this article to help you install SQL Server. If you're a SQL Server admin looking to become certified, this is the part when you should grab your certification manual and follow along so that you understand all your installation options.

Special note: You will need to install .NET 3.5 ... this is not automatically included in Windows Server 2012, but you can add it as a feature. Check out To install .NET Framework 3.5 by using the Add Roles and Features Wizard (scroll down about 3/4 of the way).

Install Guest Additions

VirtualBox guest additions are some utilities and features to help VirtualBox talk to your virtual machine and speed it up a bit. To install them, while your machine is running, click on the Devices menu then click on "Insert Guest Additions CD Image." Next, inside the virtual machine, go to This PC then open up the VirtualBox Guest Additions CD Drive and install VBoxWindowsAdditions. The installation is straightforward. Make sure to allow the installation of all drivers and to reboot afterwards.

Remotely Access Your Server

Your virtual machine is also known as the guest machine. You can install just the SQL Server client tools in your host machine and remotely administer the server running your guest machine. That configuration is also beyond the scope of this article, but most likely I will post a second article describing that. In the meantime, fire up your guest machine and start SQL Server Management Studio (under the Start menu or the All Apps menu). 

Good hunting!



Monday, February 10, 2014

SQL Server Agent Restart Notification / SQL Server Database Cluster Failover Notification

I work in an environment running a SQL Server Cluster (2-node, Active/Passive) on the production server. I want to know when the SQL Server Cluster fails over or when it's tested. It seems to have happened a few times. Since our IT department is somewhat regimental, I don't have access to a lot of system administrator functions. However, there is still a simple way to get that notification.

When I was researching this problem, I came across this:
SQL SERVER: How Can I Get Notification Alert When Fail Over Happened? This got me started down the right path but it turns out to be simpler.

First, a bit of background on the fail over. When fail over occurs, the clustering services on the remaining nodes detect that failure. They attempt to shut down services on the failing machine. As soon as that completes or when the machine is deemed inaccessible, the clustering service starts SQL Server services on the new active node. We can take advantage of that stop service/start service process because SQL Server Agent has a hook in its scheduler that can start a job when SQL Server Agent starts (see the screenshot to the right).

Here are the steps:
  1. Create the new job.
  2. Name it something like "Agent Restart Notification".
  3. Add at least one step. 
    • It can be an empty step with some do-nothing code in it, but it has to be there (otherwise the scheduler will not enable the job).
  4. Add a schedule, but choose Start automatically when SQL Server Agent starts.
  5. On the notifications page, send an email when the job completes.
    1. Please note that you must have Database Mail enabled and properly configured.
    2. You must also have an Operator properly configured.
  6. Save the job.
That's it. Next time the SQL Server Agent restarts, you will receive a notification. There's one problem here, of course. What if the SQL Server Agent restarts because of some other event, like someone manually restarting it? Unfortunately more complex code is necessary for such a specific event. To get started with that, see the above referenced blog post. As for me, this serves my purposes well enough. 

Thursday, February 6, 2014

T-SQL Error: Each GROUP BY expression must contain at least one column that is not an outer reference

I've written thousands (perhaps tens of thousands) of queries in at least half a dozen database engines. I consider myself pretty good at this query business. Unfortunately, I run into a quizzical error message every once in a while that makes me scratch my head.
Msg 164, Level 15, State 1, Line 163:Each GROUP BY expression must contain at least one column that is not an outer reference.
Gnarly.

In general, I feel that Microsoft's error messages leave something to be desired. Oh, for instance, meaning, context, and substance.

Here's the story. I had a regular old query and I wanted to sum the financials for each period.

SELECT 
 X, 
 Y, 
 @CURRENTYEAR as CurrentYear, 
 SUM( Z ) AS Z
FROM
 FINANCIALSUMMARY

No problem, right? Just add the non-aggregate columns into a GROUP BY statement and VOILA! But no. Didn't work after all. Why? First, let's parse the error message to see if we can actually get any meaning out of it.
  • expression
    • Any value made of constants or variables combined using operators
      • examples:
        • 10
        • @x
        • 10 + @x
  • must contain at least one column
    • Columns are by definition part of a table or table expression! Therefore any expression that doesn't have a column must be excluded. 
  • that is not an outer reference
    • This is a very abstract concept in terms of the SQL query language. I can't really do it justice here in one sentence, so I recommend reading up on something like Aggregates with an Outer Reference
Because there are variables that do not make reference to columns in some of the expressions, those expressions cannot be part of the GROUP BY clause. In other words, I had to remove the variable  @CURRENTYEAR from the GROUP BY list.

T-SQL Error: The multi-part identifier could not be bound

Sometimes when I work with different databases I forget the rules that are exclusive to each one. For example, today I wrote a trigger in T-SQL (SQL Server) that would have probably worked in PL/PGSQL (PostgreSQL). When it bombed with a syntax error, I had to scratch my head. I read most of the manual page from Technet but had no real luck finding the answer. I've been trying to use my own mental knowledge-base to figure out the answers to a lot of questions lately, but this time I was under an approaching deadline so I just Googled it.

Here's the problem:
 
CREATE TRIGGER 
 dbo.tr_FOO
ON  
 dbo.FOO 
AFTER INSERT, UPDATE AS 
BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for trigger here
 IF( UPDATE( REF ) AND ISNUMERIC( inserted.REF ) = 1 )
 BEGIN

  UPDATE
   dbo.FOO
  SET
   REF = 'Y'
  WHERE
   ID = inserted.ID;  

 END

END
GO

Why didn't this work in T-SQL? Because inserted is a table and I'm trying to reference a column. The error message I received was The multi-part identifier "inserted.REF" could not be bound.

So it turns out that there's no way to directly reference a table in T-SQL without using a SELECT statement. Unfortunately, I write triggers so rarely that I forget this because it seems somewhat illogical and annoying to me. In PL/PGSQL that's not the case. In that language, the inserted and updated sets is a record variable that can be referenced directly, so referencing something like inserted.REF is perfectly legitimate. That's one of the really annoying "missing features" of T-SQL. So the solution is simple but long-winded.

CREATE TRIGGER 
 dbo.tr_FOO
ON  
 dbo.FOO
AFTER INSERT, UPDATE AS 
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 -- In T-SQL, 
 DECLARE @ID char(10);
 DECLARE @REF char(6);

 SELECT
  @ID = NID,
  @REF = REF,
 FROM
  inserted;

        -- Insert statements for trigger here
 IF( UPDATE( REF ) AND ISNUMERIC( @REF ) = 1 )
 BEGIN

  UPDATE
   dbo.FOO
  SET
   REF = 'Y'
  WHERE
   ID = @ID;

 END

END
GO


On another note, SQL Server has an AFTER and INSTEAD OF triggers but not the extremely useful BEFORE triggers that are in PostgreSQL. Dear Microsoft: Fix these two annoyances and I will love you more!

Thursday, January 16, 2014

How to Find All Ecrypted Columns in a SQL Server Database

I needed to nullify the data in all encrypted columns in a development server (according to our business rules). I knew most of the columns because we used them regularly, but I wanted to be thorough and also I wanted to be able to prove that I've found them all.

I asked the question Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data on Stack Exchange and got a couple of great responses that pointed to "no" but someone came through with the answer and it worked. Basically you have to use the KEY_NAME() function and loop through the system tables to get what you need. 

I won't go into great detail here since it's all in the web page linked above. 

Monday, January 6, 2014

Questions to Ask the Interviewer during a Job Interview

I recently switched jobs because I was unhappy with the business and technical directions at my previous employer. When I decided to switch, I remembered two things. First, during every interview, they always ask the questions, "Now, do you have any questions?" If the answer is "no" then it's either a unicorn job or else you need to have a moment with yourself to come up with some. Second, I didn't ask enough questions (or at least didn't pay close enough attention to the answers) during the interview process at my last employer. I could have kicked myself for falling into such an obvious trap -- the signs of a sinking ship -- water pouring in through holes in the windows -- were everywhere.

More recently when I went on the job hunt, I was very careful to come up with a comprehensive list of questions ahead of time -- most especially questions relating to the problematic nature of my previous employer. I think this strategy really paid off. My new and potential employers were much more engaged by the level of awareness I had going into the process. I feel like I've found a good career-home and I owe it directly to a strong interview process (and a good recruiter who agreed with my philosophy).

This morning I was forwarded a list of interview questions by Julia Evans -- not for your potentially new employer to ask you but for you to ask. There are a ton of questions and many of them are developer specific, but a lot of them are very good for general situations and for a starting point to get you going.

My advice: spend an hour before the interview and get a good 20-40 questions ready. You won't use them all during any given interview, but when you glance at the list, you should have a good idea of which ones are appropriate. Also, highlight your high priority questions. Those are the ones for which you absolutely need to know the answers.

Some examples from Julia Evans' blog:
  • Do you do code review? Does all code get reviewed?
  • Do you have an issue tracker?
  • Do people work on the weekend?
  • Do people check in when they’re on vacation? How often?
  • Do your employees speak at conferences about your work?
  • Is it easy to move to other divisions or offices?
  • How does internal communication work? This one is super important and I need to remember to ask it more.
  • How much are you planning to hire in the next year?
  • What do you wish you had known when you joined this company?