Productivity | Bearded Analytics

Reading Time: 2 minutes

We have already covered how to add a data dictionary to a sql server table by using extended properties (see here). We can go one step further and making a simple stored procedure that will give us quick access to the code book. After building the stored procedure below we can now double click a table and use a keyboard shortcut. I have mine shortcut as Ctrl+5.

Create Procedure [dbo].[GetDataDictionary]

@TableName varchar(200)

as
Begin
SELECT 
	[major_id], 
	[minor_id], 
	t.name AS [Table Name], 
	c.name AS [Column Name], 
	[value] AS [Extended Property],
	infos.[Data_Type],
	infos.[is_nullable],
	infos.[Numeric_Precision],
	infos.[Numeric_Scale]
FROM 
	sys.extended_properties AS ep
	inner join
	sys.tables AS t 
	ON ep.major_id = t.object_id 
	inner join
	sys.columns AS c 
	ON ep.major_id = c.object_id 
	  and ep.minor_id = c.column_id
	inner join 
	INFORMATION_SCHEMA.COLUMNS infos
	on infos.table_name = t.name 
	  and infos.column_name = c.name
Where 
	class = 1
	and t.name =@TableName /* This is our Table name */

End

You can see what my shortcuts look like in the picture below.

If we follow the example in the article for building a code book, we can create a table with the iris data set and add a quick code book. Then when we highlight the table [iris] and use our keyboard shortcut of Ctrl+5, you should see this:

Now we can quickly check the definition of a column without having to leave our SSMS window. If you are interested in the other helper stored procedures besides GetDataDictionary, then take a look at this post.

Reading Time: 3 minutes

I recently came to the conclusion that I need a more meaningful way to track my productivity than the spreadsheet I am currently using, so my next few posts are going to be about building a system in R to track this. If you're building your own productivity tracking system then by all means take this as inspiration, but don't expect it to suit your needs. I'm making it to suit my needs using terminology that is common in my workplace and you'll have to figure out what will work for your needs in your workplace.

As with all such endeavors, the thing that is really going to make or break this tracking is the data model, so let's define that first.

At the very top level I have projects. Each client will have one or more projects. I'm not interested in tracking work for particular clients (at least for now) so I'm skipping that level, but it is necessary to note that each client has a 4 digit number. Each project also has a 4 digit number, so the combination of the client digits and the project digits form a partial billing code. The addition of the task-level 4 digit number makes a complete billing code that can be entered into my timesheet, but we're not there yet. At the project level, the first two quartets is all that is necessary. Additionally, we're going to have a name for the project, the date the project gets added, and the date the project gets removed. Projects can often be multi-year endeavors, so understanding just how long you've been working on various tasks for a project can be useful. For referencing across different datasets in this data model a project ID will also be defined.

Below the project level, as mentioned, are tasks. Each task is a concrete goal that has been assigned for me to work on for that project. Sometimes I only have one task for an entire project, other times I might have several tasks simultaneously. Some tasks may also depend on the completion of other tasks. So we're going to want the following things: task ID, task name, project ID, complete 12 digit billing code, if the task depends on the completion of another task, add date, complete date, budgeted hours, total used hours (will be cumulative), impact, effort, and notes. I'm using the impact and effort fields to automatically assign priorities. They will each be given a value from 1 to 10, with 10 being the highest. I'm not going to get into how impact and effort will be used to create the priority since I will go into more detail about that in a future post, but see this article for my inspiration.

Finally, I want to track the actual hours in the day that I do the work. So for this dataset I just want the task ID, the date/time in, and the date/time out.

Since I want all of this to appear as a single object I'm going to use a list containing three data frames. Below is a function that will actually generate this object. I expect I'll only ever have to use it once, but it's still useful to me to think in this way. My next post will get into adding projects and tasks.

createStructure <- function() {
  require(lubridate)
  Projects <- data.frame(ProjectID = character(),
                         ProjectName = character(),
                         BillingCode = character(), #(possibly partial)
                         AddDate = ymd(),
                         RemoveDate = ymd(),
                         stringsAsFactors=FALSE)
  Tasks <- data.frame(TaskID = character(),
                      TaskName = character(),
                      ProjectName = character(),
                      BillingCode = character(), #(should be complete)(multiple codes spill into Notes field)
                      AddDate = ymd(),
                      CompleteDate = ymd(),
                      BudgetHours = numeric(),
                      TotalUsedHours = numeric(),
                      Impact = integer(),
                      Effort = integer(),
                      Notes = character(),
                      stringsAsFactors=FALSE)
  Hours <- data.frame(TaskID = character(),
                      TimeIn = ymd_hms(),
                      TimeOut = ymd_hms(),
                      stringsAsFactors=FALSE)
  return(list(Projects, Tasks, Hours))
}

Category Archives: Productivity

Making a code book in sql server - Part 2

Building a productivity system in R, Part 1

Recent Posts

Archives

Categories

Links

Posts by Author