Jeff Pries

Business Intelligence, SQL Server, and other assorted IT miscellany

Month: August 2013

T-SQL Date Dimensions with ConnectWise

T-SQL Date Dimensions with ConnectWise PSA

ConnectWise PSA is a Microsoft SQL Server OLTP-based application commonly used by IT Managed Services Providers for managing their businesses.  ConnectWise provides a mechanism for entering and storing service tickets, time worked by technicians on service tickets, sales items, client billing, and other finance information.  For any Managed Services Provider, it tends to be the heart of the business.

A Date Dimension is a concept typically used in a Data Warehouse for implementing Time Intelligence which allows for querying on say “year to date” or for the quarter.  A Date Dimension is created through the creation of a Date Table.  A Date Table is a table which contains every date for a time period (with no gaps within) and numerous properties for each date.  Some of the most common properties are:

  • The Date, as a DATE or DATETIME
  • The DateKey, which is an INT that shows the date in YYYYMMDD form
  • Whether or not the date is a work day or not
  • Numerous date parts of the date, such as the month number, month name, day of the week, week number, quarter, year, and many more.
While the exact fields which make up the date table will vary based on the needs of the business, the above items tend to be some of the most common.

Continue reading

SSRS Usage Reporting — with SSRS!

SSRS Usage Reporting — with SSRS!

A task I’ve recently needed to perform was to migrate our company SSRS portal from a SQL Server 2008 R2 Native Mode portal to a shiny new SQL Server 2012 Sharepoint Integrated Mode portal.

Additionally, during this process, I had a desire to clean up the organization of the reports and potentially retire some of the older reports which were no longer used.  The issue that I immediately encountered, however, was a lack of readily accessible information about which reports were frequently (or infrequently) used, by whom, and how often.

With a little bit of research on the topic, I found that SSRS does indeed track report usage metrics within the ReportServer database, however, there aren’t any built-in mechanisms to easily review that data.

My research first took me to a Microsoft Technet posting with the following T-SQL to collect some basic usage statistics from the ReportServer database:


SELECT 
Cat.[Path], 
Cat.Name, 
Ex.[Format], 
Ex.Parameters, 
Ex.UserName, 
Ex.TimeStart, 
CONVERT(nvarchar(10), 
Ex.TimeStart, 101) AS rundate,  
1 AS [Counter] 
FROM ExecutionLog AS Ex INNER JOIN [Catalog] AS Cat ON (Ex.ReportID = Cat.ItemID)
ORDER BY Ex.TimeStart DESC


This was a great indicator of the possibilities for reporting on SSRS usage.  A little further research turned me onto the following Wrox article by Jim Minatel with some additional sample queries and some great ideas for what I’d like to include in the SSRS Usage Report I was creating.

Within the ReportServer database (both for SSRS Native Mode and SSRS SharePoint Integrated Mode), the most interesting tables when it comes to SSRS usage statistics are:

  • ExecutionLog – The most useful table, this is a log of all SSRS component executions as well as the status of the execution.
  • Catalog – Equally useful, this is a full listing of all SSRS assets (report RDL files, data sources, datasets, and even Power View reports) which are needed to identify in a friendly manner the names of the items in the ExecutionLog.
  • Users – A listing of all users with their unique IDs and authentication types.  This table proved to be the least useful, as usernames are stored in a readable form in the above tables.
Performing T-SQL queries to join at least two of these tables allowed for me to create a base detail query which would provide the foundation for data shown in my SSRS Usage Report:

Continue reading

The Office Hijinks Series – Part 2: The My Little Pony Prank

The Office Hijinks Series – Part 2: The My Little Pony Prank

Welcome to the first in the series of office hijinks and pranks featured on this blog.  Our first prank comes to you from the summer of 2009.

For our first prank, we weren’t really sure what we wanted to do.  We had an idea of whose office we wanted to prank (as he was on vacation for a week), but we weren’t sure what exactly we wanted to do.

Ultimately, we decided the name “Eric” was similar enough to the name “Erica” that we were going to turn his office into a little girl’s room, featuring My Little Pony.

At the time, My Little Pony had not yet come back into mainstream popularity (wow, that’s weird to say), so we didn’t have a lot of resources at our disposal.  We got creative though, fired up Google Image Search and the color printer, and got to work!

[ Go back to Part 1 | Go forward to Part 3 ]

 

The Office Hijinks Series – Part 1: Introduction

The Office Hijinks Series – Part 1:  Introduction

It’s pretty easy to get caught up in the technical hustle and bustle of all work all the time.  While hard work and professionalism are of course very important (especially for a technical blog visible to the entire Internet) it’s also important to have a little fun and express some good-natured creativity.

A number of years ago, I pioneered a series of good-natured office pranks as a means of introducing some additional fun into the office.  While it may seem at first glance to be silly, there were actually some very good benefits from integrating some office pranks into our workplace:

  • Increased morale and team building — when you say to your co-workers “hey, let’s fill Eric’s office with balloons while he’s out for the week” and the entire team is participating and laughing, you know you’re doing a good thing.
  • Increased employee retention — having a fun environment in which you never know what the next big prank will be, it’s hard to give that up in favor of something else.
  • Marketing – inevitably, when we take pictures of an office prank and post them on Facebook, we get tons of comments to the tune of “wow, cool, I wish I worked there!!”
  • A great outlet for creativity — when you spend the majority of the day repairing issues and fixing problems, its nice to be able to exercise some creativity and build something unique.
So, there you have it, a business case for office hijinks!
Whenever I put together an office prank, I usually have a few guidelines I follow to make sure it stays fun for everyone:
  • No permanent destruction of the victim’s or office property — let’s face it, if you intentionally were to break something the victim cares about or would have difficulty replacing, all the fun is sucked out of the room quickly.
  • Nothing that would be too difficult to clean up or prevent the victim from being able to work in a reasonable amount of time — having hours and hours of cleanup or inability to work is no fun for anyone.
  • If anybody wants to opt out, they’re out — if anybody doesn’t want to participate for any reason, either as a participant or victim, that’s totally fine.
  • It shouldn’t be too disrupting during work hours — its normal to build some of the office prank during business hours, but it shouldn’t be too disruptive.  Some work outside of business hours is almost always required.
  • Spend some money, but don’t go overboard — at best, the prank is going to stay around a week or so, so there’s no harm in making things out of cardboard.  Odds are it’ll be thrown out at the end anyway!
Stay tuned for next week when we cover our first office prank — Part 1, the “My Little Pony Prank

© 2017 Jeff Pries

Theme by Anders NorenUp ↑