Archive for June, 2012


Reporting on your reports with BIxPress

June 30, 2012

SQL Server Reporting Services (SSRS) is the latest portion of the BI stack to get some attention in the most recent version of BIxPress.  Two new features are included in BIxPress 3.5, Reporting Performance Monitor, and Report Deployment Tool

I’m going to take some time now to walk through how easy it is to get the  Reporting Performance Monitor configured and running.  From the BIxPress start up screen you can see the new reporting icon.


Clicking on it will launch the reporting console.  From here the first step is to configure the connection to the report server database. To do this click on the server icon.


The connection screen will appear and here you will configure the server, the instance and the database for your report server database.  On my local machine everything is set to the default.  You can also choose windows authentication or supply a Username and Password instead.


Once that is done you are ready to start monitoring your reports.  There are a few things that I want to point out, though they might seem a little obvious to most of you.  The Reporting Performance Monitor will only give you details about reports that are deployed to the report server, not reports that are run in BIDS or Report Builder.  With that being said lets take a look at some of the details you can get you hands on.


This is the view when you first run the Report Monitor after connecting to the report database.  There are four report:

  1. Report Averages
  2. Longest Running Reports on Average
  3. Frequent Reports
  4. Active Users.

If we click on each report we get a more detailed view. There is a icon on the top right corner of each report that will expand the view.


The Report averages chock full of great information. It includes the Last report status, meaning did the report succeed or fail during its last execution, the number of times the report has been run, and its average run time.  Then it breaks down the average runtime into Averages on data Retrieval Time, Processing Time, and Rendering time, so you can quickly see what is the most time expensive portion of your report.


The other reports are not quite as dense as the first report but still offer valuable insight into your reporting environment.




There is a ton of useful information here and you can be sure that the brains behind these reports will be adding more to this console as new versions come out.  If you’d like to have your voice and suggestions heard regarding any of the Pragmatic Works software make sure to visit our Feature request page. In the meantime please take a minute and download a trial of BIxPress and test out this cool new feature and the many other features that span the SQL Server BI Stack.


SQL Server Indexed Views Barney Style

June 26, 2012

During my first few weeks at a new client I was asked to do some DBA tasks that I don’t normally do.  They were in the process of developing a new application and as a result a legacy DB was being carried over inside a new DB as a series of views instead of physical tables.  While the views rendered the data correctly they query performance went from a matter of less than 10 seconds to more than 10 minutes, and this was just on development data, not a full production cycle.  So with the performance blown out of the water they asked me to do a some research and see what was causing the slow down.

The first thing I did was looked at the tables in the legacy DB to see if there were indexes on the corresponding tables, and as I’m sure you’ve guessed, there were.  As I’m sure you have also deduced the indexes weren’t created in the new DB on the views.

At this point the only thing I really knew about creating indexes on views was that you could.  So I did a little digging and found out what was required to create and indexed view.  Of course I didn’t discover all of this at once, but instead after every hurdle I cleared I was presented another one.  So for your benefit and mine, I’m going to share with you what I’ve learned about indexed views.  As you can tell by the title, this is not an all inclusive dissertation on SQL Server indexed views.  This will be very much an introduction so I hope you get what you need from it.

So the first thing that I tried that didn’t work was scripting out the index from the old table and try adding it to the view.  This brought up the first requirement I found.  I got an error saying the view didn’t have Schema Binding on it.  I had no idea what this meant but I found the MSDN article that listed what Schema Binding was:


Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

So I did what was suggested and dropped and created the view with schema binding.  Great the view was created so I was ready to create the index again.  That was of course when the next error popped up.  The underlying tables where in different schemas which isn’t a big deal except, on top of that they had different owners for each schema, and that my friends is not allowed.

At this point my brain actually started to work properly, and in a stroke of, well not genius but somewhere above a drunken stupor and genius, I decided to search out how to create an indexed view instead of figuring out each error as it popped up.  That lead me to this MSDN article.

A view must meet the following requirements before you can create a clustered index on it:

  • The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.

  • The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.

  • The view must not reference any other views, only base tables.

  • All base tables referenced by the view must be in the same database as the view and have the same owner as the view.

  • The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.

  • User-defined functions referenced in the view must have been created with the SCHEMABINDING option.

  • Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.

  • All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports whether a user-defined function is deterministic….

The list goes on from there, but you get the point.  There is a lot of prep work to do before creating an indexed view.  You might find it hard to go back and create an index on a view if you hadn’t planned it out well in advance.  with that being said here is the basic syntax for creating an indexed view, assuming all other conditions are met.


IF OBJECT_ID ('dbo.vTest', 'view') IS NOT NULL
DROP VIEW dbo.vTest ;
SELECT SUM(ColA*ColB) AS Revenue,
ColC, ColD
FROM dbo.TableA AS a, dbo.TableB AS b
WHERE a.ColE = b.ColE

ON dbo.vTest (ColC, ColD);

I hoped this helped get you on your way to creating indexed view.  For more information please check out these MDSN Articles 1, 2  and 3


No Complaining, it works.

June 24, 2012

In my job as a consultant I get exposed to a wide variety of working environments, some collaborative, some social, some conventional. Often times there are politics that I am unaware of, and quite frankly I try to stay out of, because I’m not there to take anyone’s side, I’m there to help the meet that goal.   This can be quite a stressful existence at times.  On top of all of these work environments I am introduced to, I also have the work environment back at my company office, which admittedly is better than most places I’ve been, but that doesn’t mean that from time to time there are not any complaints. 

When work at a client site is ratcheting up my stress level, either because of approaching deadlines, or on nights like tonight when I’m away from my family and my wife has to take the brunt of the parenting, or when I’m commiserating with co-workers to are facing the same challenges I am, it all seems a bit too much.  I start resenting the fact that I have to travel, and that I’m under pressure at work for a client because they have deadlines to meet.  Sometimes I just let all of these frustrations snowball until I want to call in sick for a month.  In the end regardless of the reason, I’m just not happy from time to time. After a particularly bad string of weeks I spent traveling I just felt out of control, and not the “I’m pissed and I’m not going to take it anymore”, but more of the “something’s got to give” sense. 

It was at this time, where I was looking for a way to improve my situation that I came across a book we had at the office, titled The No Complaining Rule by Jon Gordon.  This isn’t your typical self help book (not that I’m able to judge that since this is the first one I’ve read cover to cover).  It tries to teach you through the use of a fictional story.  The main character of the book, Hope, is facing problems at work that could get her fired, problems at home with her children, and health problems as well.  Add all of this up and if any one had a right to complain it was Hope.  Early in the book its easy to see the authors point is that complaining is a easy habit to get into, to feel justified in doing and can be infectious to others.

It was at this point that the authors words started to make sense to me.  I’ve definitely been in the situation where I didn’t think something was a big deal or reason to get upset, but after talking with someone else, who thought it was a huge, end of the world type deal, it became a much bigger deal to me.  Looking back it seems silly to me that I let somebody’s feelings on a topic make me miserable, but I’m sure it happens more often then we are aware of. Anyway, back to the book.

As the story moves on Hope meets someone who introduces her to the no complaining rule.  As it turns out it isn’t just one rule, but a number of rules, that revolve around complaining.  As you would expect the Hope resists, then applies the rules to her life, and after 150 pages everything is great and wonderful in here life.  While the overall “cheese” factor of the book might be a turn off I did feel like a learned some valuable takeaways from The No Complaining Rule

The most important one of which was that complaining is ok, as long as its constructive complaining.  This is more than just pointing out problems, because that’s pretty easy, and we can create more problems than actually exist once we get in the habit of complaining. This is pointing out a problem and in the next breath offering some solutions to those problems.

One of the other takeaways from the book was that complaining and negativity are more of a choice than we might believe.  This has helped me a lot over the last few months.  I’ve just made it a rule to stay out of negative conversations with people, and to try and cutoff conversations that are just people venting their frustrations and complaints at me as quickly as possible, but not getting drawn in.  Every time somebody complains I just look for a way to spin the conversation into a different direction.  These might seem like obvious coping mechanisms to some of you, but when your bogged down in the negativity it can be hard to see a way out.

This book helped me just take a look at myself and ask why I was unhappy.  I realized that I was letting things get to me too easily, I was listening to people who were unhappy and letting their state of mind rub off on me.  Once I was able to stop my own complaining and limit my interactions with other complainers I saw a dramatic change in my job satisfaction, and my general outlook as a whole.  This isn’t to say there haven’t been or never will be any complaining again, but I now at least have some ways of combating them when they do creep up.