RSS Feed!

About me

I am a business intelligence developer working at Bizware since August 2015. In this blog I will present code samples, primarilly in tsql but also findings in all other parts of the Microsoft BI-stack.


Disclaimer: All code should be considered as "as-is", i.e. badly tested and not working until extensive testing have been made by anyone reading the code. :)


Happy coding!

Archive for April, 2012

Documentabler – A stand alone t-sql mini light-weight source code storage system

Sunday, April 1st, 2012

Hi!

I’ve never been especially fond of source code systems. There’s no doubt that it is a tricky art of developing a suitable source code storage solution for many different reasons (which I won’t rant about here, you could probably google it easily). I’ve been in touch with three major ones, namely tortoise svn, sourcesafe 2005/2008 and TFS.

Now, being a business intelligence developer in t-sql, there are certain problems regarding development in test environments (test databases that is) when it comes to versioning. If you develop something with other collegues it is very easy to not be in synch with each other. If there are 2 persons developing on the same sql instance, then it’s quite easy to understand what the other one is up to. But add 1 more, and problems start to arise. If you’re not in the same location (e.g. if your collegues are home sick), things get even more messier. When that person comes back and sees that his entire work for the past week has been overwritten and hasn’t been checked in, this could mean that hours dissapears and coding efficency goes down. So what are you supposed to do with half finished code? If you check it in, someone might think the code is fine. If you put it on your laptop, you might forget about it.

I’ve been thinking about this off and on, and this weekend I started coding. My idea is that some great stuff must be stored at the server, right? The ones called INFORMATION_SCHEMA and sys-schema? How about doing a mini source code system for the server with information from these schemas? I started building a prototype, using some interesting tables found in INFORMATION_SCHEMA and sys. Since every database needs a name, I thought of documentabler, document-a-table-abler. A pun. Yes, a bit cheesy, but metadata_dw sounded boring.

After a few hours this weekend, I was done. So lets look at some features. These are tables:


dbo.dt_index
dbo.dt_object
dbo.dt_table
dbo.dt_view

They store metadata about databases. Index stores table indexes, object stores SPs, UDFs and views. Table stores columns in tables and columns in views, dt_view is kind of redundant although good to have if you want to separate tables and views.

These in turn are populated by these SPs:

spUpdate_dt_index
spUpdate_dt_object
spUpdate_dt_table
spUpdate_dt_view

And it is here where the magic happens. When providing these SPs with a parameter with a database name in it, the stored procedure fetches data into the documentabler database. I’v tried to streamline all the tables so they include this information:


[isLatest]
[versionNr]
[latestVersionNr]
isIndexActive/isObjectActive/isViewActive/isColumnActive
[RowContent]
[validFrom]
[validTo]

Creating a data warehouse for other databases! Yes, the solution is a SCD2-only data warehouse, meaning that every change is recorded to this database! What this means is that, if you create a sql server agent job which includes this

use documentabler
go
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

spUpdate_dt_index 'documentabler'
spUpdate_dt_object 'documentabler'
spUpdate_dt_table 'documentabler'
spUpdate_dt_view 'documentabler'

and then run it regularly, you will get everything that is happening on a meta-metadata-level recorded. If you by mistake didn’t save your work, you might find it in there! Through the [validFrom] and [validTo] columns, you could go back in time and see what database changes has been made! So this could be the documentation, if there only was a commenting field…

And there is. the extended property! This is an metadata property that could be set on db level, table level and column level. However, the interface for this is quite tricky in ssms, especially for columns. To solve this, one needs to have a nice way of inserting and updating these fields. Do you have excel? Great! This is for you:


USE [documentabler]
GO

/****** Object: View [dbo].[v_exportExtendedPropertiesToExcel] Script Date: 04/01/2012 17:26:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [dbo].[v_exportExtendedPropertiesToExcel]
as

SELECT TOP 1000000
[dbName]
,[schemaName]
,[tableName]
,[columnName]
,isnull(columnExtendedProperty, '') as OldColumnExtendedProperty
,'' as NewColumnExtendedProperty
,'=CONCATENATE("execute [documentabler].[dbo].[sp_updateExtendedProperty] ''' + [dbName] + ''', ''' + [schemaName] + ''', ''' + [tableName] + ''', ''' + [columnName] + ''',''" & F' +cast(1 + ROW_NUMBER() OVER (ORDER BY dbName, schemaName, tableName, columnName) as nvarchar(10))+ '&"''")' as SQLScript
FROM [documentabler].[dbo].[dt_table]
WHERE isLatest = 1
ORDER BY dbName, schemaName, tableName, columnName
GO

This view produces an excel-ready template, when inserted to excel shows all current extended properties, but also creates a script back to t-sql, making it user friendly to update and insert extended properties. And yes, the extended properties is also SCD2. :)

So this finalizes the first post about documentabler, use the link or surf to projekt->documentabler in the top row. This is free to use, however if you found it awesome, please contribute some money to the ones that needs it the most, to a charity organization of your choice.

Disclaimer: I take no responsibility for any bugs or mishaps you might encounter using this. Everyone should study the code carefully and use it wisely and not use this as a substitute for a real source code system.

Best wishes,
Robin

Some general updates

Sunday, April 1st, 2012

Hello!

Since my last post my employment status has changed. I’ve been having a great time at Avanade but from november last year (2011) I am employed at the online casino/bet company Betsson as a business intelligence developer. Since I’m so lazy with my updates on this blog, I’ve got something really special for you coming up in the next post.

Since I also got some english speaking collegues who might be interested in the stuff I write and do I decided to write in my left-handed english. I hope you’ll find some stuff interesting.

//Robin