Now that I am managing a Business Intelligence group, which when I started wasn’t doing *anything* as far as a method, I had to ask myself if doing Agile would work (I knew it could, but it is different than software dev in many ways, similar in others).

Back in October, my group went Agile. We set up a board, got some index cards, and just started Agile. A big paradigm shift at work for IT, but we needed to do something.

With Business Intelligence, we really don’t have *code* to work on, but more “objects” (Cubes, Dimensions, Reports, etc). As a team we needed to figure out – what is a story? What is a feature/enhancement/task. What is an epic? How are we going to score things, etc.

The first few sprints (2 week sprints – Wednesday’s to Tuesdays) our velocity was lower and/or we just scored things a little weird. But since then we have learned our “zone” of scoring stories and we got into a groove of releasing our BIG cube every 2 weeks, and releasing the smaller changes when completed.

We do the daily *scrum* for 15 minutes, and track burndown on stories, which lets us make some cool burndown charts that we tack up on our board, and we have some other cool bullet charts to track velocity by sprint, to our original, and final goal, and more.

What has Agile brought our group? Confidence, Stability, Ability to Meet Expectations. Agility. Results. and more..

Do we run into issues yet? Of course. Can we adjust and handle them. You bet! Are we continuously learning and changing our process to make it better? Yep. Always room for improvements.

What else does Agile bring us? Visibility to our customers, and to our peers in IT. Eventually the “BI” stuff should just run, over and over, iteratively.

Trek BI Agile Story Board

Agile isn’t a silver bullet though. It isn’t easy. You still need to work to keep things organized and on track. You have to fight that organizational gravity that sucks teams back in and people in as well, and throws that scope creep back onto stories and projects. You also have to fight to get rid of your technical debt, which depending on how long things have been running before you started even thinking about Agile, might take you a while.

This post is more of a high level “Yes We Can” type post about Agile in BI. I haven’t decided yet, but my guess is I might have some more detailed posts on how I like to run an Agile project, and what we are doing as a team to handle situations that come up, and just how we do things.

In the end though, just remember, have fun!

There are many apps out there for windows and mac that let you download your OWN sets, or also download public sets from other users, but I needed to get a guest pass set.

stumbled upon this, FlickrFetchr. Works as promised, nice little app.

With SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS), you set up data sources, connection to databases. In SSAS you usually have a connection which then you build your data source view off of, and in SSIS you have connections from which you push data to and pull data from.

Another thing, in SSAS you can “deploy” right from Visual Studio (BIDS). All these things have a server name. What we have run into is this:

You develop on your local machine, pointing at development server. You deploy to development, your connections are pointing to development, and everything works great. When you deploy to production (usually planned, every 2 weeks, or whatever) you deploy your stuff and what ends up happening?

In SSIS your config files should have a connection string (or however you store it) and it should point to production. But in SSAS, if you deploy from BIDS, your data source will have to change and in the cube project properties you need to change your deployment server.

I have seen countless times, a cube or a connection in SSIS without a config that is running in production, yet pointing at development. We keep our dev data as fresh or very close so sometimes we don’t even notice, but then it happens, something weird is reported and we dig into it, and we find the erroneous connection string.

Here is my solution to the problem:

Developers – go to C:\Windows\System32\Drivers\Etc and open your Hosts file with notepad or text editor. You then add a couple of entries

#production
#xxx.xxx.xxx.xxx datawarehouse

#development
yyy.yyy.yyy.yyy datawarehouse

where xxx is the ip of your production system, and yyy is the ip of your dev system. the # is the rem/comment out symbol. You can see above I have everything commented out but the line for the dev system. But notice each is pointed to “datawarehouse” so if I ping or connect to “datawarehouse” from Management Studio, or whatever, it goes to the IP I have commented out.

Now, go on to each server, but only add the line that corresponds to that server in the hosts file, or better yet just

127.0.0.1 datawarehouse

Now, when you deploy to either server, and your connections, etc are set to connect to “datawarehouse” you ensure it will always connect to the local server. Brilliant!

What were some of the best things tech to come out of the last 10 years? (this list is in no particular order)

iPods..iPhones

YouTube

Facebook (MySpace paved the way, and the others, Friendster)

Netflix

Windows XP

HDTV

3G

Twitter

SQL Server 2005

.NET from Microsoft

Gmail

Blogger.com, Wordpress

Flickr

PlayStation 2, and Xbox 360.. and even the Wii

Wireless.. G, N, Mice, Keyboards

USB 2.0

Craigslist (after it expanded from the Bay Area)

Skype

Pandora (internet radio)

Kindle

GPS (Civilian Based – came out around 2004-2005)

Wikipedia

Music Games (Guitar Hero, Rockband, etc)

And I am sure there is a ton more I could think of.

Just think about it, 10 years ago we didn’t have anything like the above list in the tech arena. What long way we have come, and those are just some of the highlights.

What are some things you think should be on the list?

The app/system itself is pretty slick. Very basic, but lets you do complex things. Once you get some users set up, and a few models (think: Product, Customer), you can add entities (think: Category1, Category2, etc) you can set up hierarchies, business rules, etc.

I haven’t played much more with it, but it seems like it could get the job done. I would say some things aren’t intuitive enough. Example – they could say “drag this over to this area” but there is nothing as far as what to do, its kind of guess and check.

I’m excited to see where MDS goes.

First issue I ran into, a Web App in development, not being able to load up “Microsoft.AnalysisServices.AdomdClient” because it was looking for version “9.0.242.0″ – the version you get with Office 2007, SQL 2005. But I have 2010 running and SQL 2008 stuff on this new build, so saw that issue. Of course I could install the stuff from SQL 2005, the drivers, but also I can redirect my assembly in the Web.config

          <dependentAssembly>
            <assemblyIdentity name="Microsoft.AnalysisServices.AdomdClient" publicKeyToken="89845dcd8080cc91"/>
            <bindingRedirect oldVersion="9.0.242.0" newVersion="10.0.0.0"/>
          </dependentAssembly>

I was also getting some weird stuff about permission denied to any third party assembly when trying to debug/run it locally. Since the web app is impersonating a user, I figured I need to give that user rights to the temporary internet files for ASP.NET, which worked, but in Win7 (and Vista?) the path is different, it is

%LocalAppData%\Temp\Temporary ASP.NET Files

I gave the user modify and my app finally builds!

Hopefully not too many more roadblocks going forward..

What I wanted to do was capture that, but also keep and automatic running log in SVN (subversion) source control. Here is how I went about it.

First, you need SVN set up somewhere. We use unfuddle (http://unfuddle.com/) – which we also use for Agile BI stuff, but that is another post :) and unfuddle also let’s us do SVN in the cloud, but you could do it locally or internally or whatever you’d like. I had to create a user for this, to do automatic commits, and give it rights to commit to the repo.

Second, you probably already want your DDL triggers set up to capture events on your databases, and write to a table. Writing to central DB on a server is probably a good idea. If you have that, or something similar, I then created a trigger on that table, to capture the INSERTED action and take that record that was just inserted and parse what I need, and automatically commit to SVN.

But first, you want to script out all your objects for a database to a folder, say C:\DBSchemas\

I created a folder structure to help me

DBSchemas\ServerName\DatabaseName\ObjectType

and then scripted out all the objects to each folder for ObjectType (Tables, StoredProcedures, Functions, and Views)

Once that was done, I did an initial import into SVN, and make the folder I was working with a working copy. Then the funs starts.

I created a couple of procs (which I found online, links to the blogs are below) to CREATE and DELETE files from T-SQL using OLE automation

CREATE/APPEND (http://sqlsolace.blogspot.com/2009/01/ole-automation-write-text-file-from.html)

CREATE PROCEDURE [Utils].[usp_OLEWriteFile] (@FileName varchar(1000), @TextData NVARCHAR(MAX),@FileAction VARCHAR(12)) AS
 
BEGIN
DECLARE @OLEfilesytemobject INT
DECLARE @OLEResult INT
DECLARE @FileID INT
 
EXECUTE @OLEResult = 
 sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT
IF @OLEResult <> 0 
  PRINT 'Error: Scripting.FileSystemObject'
 
-- check if file exists
EXEC sp_OAMethod @OLEfilesytemobject, 'FileExists', @OLEresult OUT, @FileName 
-- if file esists
IF (@OLEresult=1 AND @FileAction = 'APPEND') OR (@OLEresult=0) 
BEGIN   
 
IF (@FileAction = 'CREATENEW')
 PRINT 'New file specified, creating...'
IF (@OLEresult=1 AND @FileAction = 'APPEND') 
 PRINT 'File exists, appending...'
IF (@OLEresult=0 AND @FileAction = 'APPEND') 
 PRINT 'File doesnt exist, creating...' 
 
 -- open file
 EXECUTE @OLEResult = sp_OAMethod @OLEfilesytemobject, 'OpenTextFile', @FileID OUT,
 @FileName, 8, 1
 IF @OLEResult <>0 PRINT 'Error: OpenTextFile'
 
 -- write Text1 to the file
 EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TextData
 IF @OLEResult <> 0 
  PRINT 'Error : WriteLine'
 ELSE
  PRINT 'Success' 
END
IF (@OLEresult=1 AND @FileAction = 'CREATENEW')
 PRINT 'File Exists, specify APPEND if this is the desired action'
 
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @OLEfilesytemobject
 
END
GO

DELETE (http://www.kodyaz.com/articles/delete-file-from-sql-server-xp-cmdshell-ole-automation-procedures.aspx)

DECLARE @Result int
DECLARE @FSO_Token int
 
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT
EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, 'C:\delete-me-file.txt'
EXEC @Result = sp_OADestroy @FSO_Token

You need to make sure OLE Automation is on. You need to make sure that the account you are running SQL as has modify rights to your DBSchemas folder.

But the crux of the solution is the trigger that gets the DDL info, and writes/deletes the files and SVN Add/Del/Commit’s the file. Now this is some ugly 1 hour SQL script craziness, tons of IF statements, etc. It could be improved, but it works, and it is a start, it can be modified and tweaked to do whatever you want. Note, if your SVN repo isn’t authenticated you don’t need the username/password for the SVN commands.

You can see, it gets the DDL, checks the events (and I have it limited to one database), and it checks what type of object and what operation, and for and add, it adds and commits, for a updated, deletes file, recreates it, and commits, and for a delete it does and svn delete and commit. Pretty easy :)

 
 
 
CREATE TRIGGER DDLRevisionHistory
	ON dbo.DDLEventLog
	AFTER INSERT
AS
 
BEGIN
SET NOCOUNT ON;
 
DECLARE @EventType VARCHAR(50)
DECLARE @DatabaseName VARCHAR(50)
DECLARE @ServerName VARCHAR(50)
DECLARE @ObjectName VARCHAR(100)
DECLARE @SchemaName VARCHAR(10)
DECLARE @CommandText VARCHAR(MAX)
DECLARE @LoginName VARCHAR(50)
 
SELECT 
	@EventType = EventInstance.value('(//EventType)[1]', 'varchar(50)'),
	@DatabaseName = EventInstance.value('(//DatabaseName)[1]', 'varchar(50)'),
	@ServerName = EventInstance.value('(//ServerName)[1]', 'varchar(50)'),
	@ObjectName = EventInstance.value('(//ObjectName)[1]', 'varchar(50)'),
	@SchemaName =EventInstance.value('(//SchemaName)[1]', 'varchar(50)'),
	@CommandText = EventInstance.value('(//TSQLCommand//CommandText)[1]', 'varchar(max)'),
	@LoginName = EventInstance.value('(//LoginName)[1]', 'varchar(50)')
	FROM inserted
 
DECLARE @filepath VARCHAR(8000)
 
SET @filepath = 'C:\DBSchemas\' + @ServerName + '\' + @DatabaseName + '\' 
 
	IF (
		@EventType = 'CREATE_VIEW' OR @EventType = 'ALTER_VIEW' OR @EventType = 'DROP_VIEW'
		OR @EventType = 'CREATE_TABLE' OR @EventType = 'ALTER_TABLE' OR @EventType = 'DROP_TABLE'
		OR @EventType = 'CREATE_PROCEDURE' OR @EventType = 'ALTER_PROCEDURE' OR @EventType = 'DROP_PROCEDURE'
		OR @EventType = 'CREATE_FUNCTION' OR @EventType = 'ALTER_FUNCTION' OR @EventType = 'DROP_FUNCTION'
		) 
 
		AND @DatabaseName = 'YourDatabase' BEGIN
 
 
		-- write out new file to correct folder
		IF CHARINDEX('VIEW',@EventType) > 0 BEGIN 
			SET @filepath = @filepath + 'Views\' + @SchemaName + '.' + @ObjectName + '.VIEW.sql'
		END
 
		IF CHARINDEX('TABLE',@EventType) > 0 BEGIN 
			SET @filepath = @filepath + 'TABLES\' + @SchemaName + '.' + @ObjectName + '.TABLE.sql'
		END
 
		IF CHARINDEX('PROCEDURE',@EventType) > 0 BEGIN 
			SET @filepath = @filepath + 'StoredProcedures\' + @SchemaName + '.' + @ObjectName + '.StoredProcedure.sql'
		END
 
		IF CHARINDEX('FUNCTION',@EventType) > 0 BEGIN 
			SET @filepath = @filepath + 'Views\' + @SchemaName + '.' + @ObjectName + '.UserDefinedFunction.sql'
		END
 
		IF CHARINDEX('CREATE',@EventType) > 0 BEGIN
 
			-- create file
			EXEC dbo.usp_OLEWriteFile @filepath,@CommandText,'CREATENEW'
 
			-- svn add
			DECLARE @instrAdd VARCHAR(4000)
			SET @instrAdd='svn ADD ' + @filepath + ' --username dbschema --password yourpassword'
			EXEC xp_cmdshell @instrAdd
 
			-- svn commit
			DECLARE @instrCommitAdd VARCHAR(4000)
			SET @instrCommitAdd='svn commit ' + @filepath + ' --message "added by '+ @LoginName +'" --username dbschema --password yourpassword'
			EXEC xp_cmdshell @instrCommitAdd
 
		END
 
		IF CHARINDEX('ALTER',@EventType) > 0 BEGIN
 
			--delete and readd file
			EXEC dbo.usp_OLEDeleteFile @filepath
			EXEC dbo.usp_OLEWriteFile @filepath,@CommandText,'CREATENEW'
 
			-- svn commit
			DECLARE @instrCommitChange VARCHAR(4000)
			SET @instrCommitChange='svn commit ' + @filepath + ' --message "changed by '+ @LoginName + '" --username dbschema --password yourpassword'
			--PRINT @instrCommitChange
			EXEC xp_cmdshell @instrCommitChange
		END
 
		IF CHARINDEX('DROP',@EventType) > 0 BEGIN
			-- svn delete
			DECLARE @instrDel VARCHAR(4000)
			SET @instrDel='svn delete ' + @filepath + ' --username dbschema --password yourpassword'
			EXEC xp_cmdshell @instrDel
 
			-- svn commit
			DECLARE @instrCommitDel VARCHAR(4000)
			SET @instrCommitDel='svn commit ' + @filepath + ' --message "deleted by '+ @LoginName +'" --username dbschema --password yourpassword'
			EXEC xp_cmdshell @instrCommitDel
		END
 
	END
 
END

as you can see you can create a homegrown revision history of your DDL objects in SQL . I have tested this on the basic operations, no renames, etc using the GUI, but if you do use it, you might want to wrap it all in exception handling just to be on the safe side.

Happy DBA’ing :)

I asked on twitter what the DBA’s think, @SQLRockstar thinks using OpsMgr is the way, which probably would be great, if I had it. I need a quick and dirty solution.

What I did was query the sysjobs, sysjobactivity, and sysjobhistory tables to get some kind of “look” into the running jobs. That way, if a job that is normally 45 minutes has been running for 5 hours, I should be able to catch it early on, just trying to be more proactive instead of reactive.

SELECT 
	j.job_id AS 'JobId',
	name AS 'JobName',
	start_execution_date AS 'StartTime',
	stop_execution_date AS 'StopTime',
	avgruntimeonsucceed,
	DATEDIFF(s,start_execution_date,GETDATE()) AS 'CurrentRunTime',
	CASE WHEN stop_execution_date IS NULL THEN 
			DATEDIFF(ss,start_execution_date,stop_execution_date) ELSE 0 END 'ActualRunTime',
	CASE 
		WHEN stop_execution_date IS NULL THEN 'JobRunning'
		WHEN DATEDIFF(ss,start_execution_date,stop_execution_date) 
			> (AvgRunTimeOnSucceed + AvgRunTimeOnSucceed * .05) THEN 'LongRunning-History'
		ELSE 'NormalRunning-History'
	END 'JobRun',
	CASE 
		WHEN stop_execution_date IS NULL THEN
			CASE WHEN DATEDIFF(ss,start_execution_date,GETDATE())
						> (AvgRunTimeOnSucceed + AvgRunTimeOnSucceed * .05) THEN 'LongRunning-NOW'
			ELSE 'NormalRunning-NOW'
		END
		ELSE 'JobAlreadyDone'
	END AS 'JobRunning'
 FROM msdb.dbo.sysjobactivity ja
	INNER JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
INNER JOIN (
	SELECT job_id,
	AVG
	((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100)
	+
	STDEV
	((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100) AS 'AvgRuntimeOnSucceed'
	 FROM msdb.dbo.sysjobhistory
	WHERE step_id = 0 AND run_status = 1
	GROUP BY job_id) art 
	ON j.job_id = art.job_id
WHERE 
(stop_execution_date IS NULL) OR 
	(DATEDIFF(ss,start_execution_date,stop_execution_date) > 60
	AND 
	CAST(LEFT(start_execution_date,11) AS DATETIME) = CAST(LEFT(GETDATE(),11) AS DATETIME))
ORDER BY start_execution_date DESC

The inner query looks at the history to get the average runtime, and I added the standard deviation to that to make it more realistic, in case you have a one off that throws the average off. Of course this all depends on you having more than 3 entries in your job history as well.

I then just compare the average to the current runtime (if the job is executing) and to the actual (if it is complete).

You could use this query in a report, to view throughout the day to catch long running jobs, you could also use it to just give you the one that are currently running and over the time threshold, and alert you, so you can catch them right away.

I tested this on a few servers running SQL 2005, but I am guessing it will work on SQL 2008 as well. Find and kill those long running jobs!!

Try to delete, rename, resync, redeploy, anything, and I would get the error..

Errors in the metadata manager. The dimension with ID of..

Couldn’t do anything. The solution? I had to stop the SSAS service, go to the OLAP data directory under my SQL install and remove the folder for that cube. I then started the SSAS Service, and the SSAS Database was gone (finally!). I redeployed and reprocessed and everything is working again. Whew :)