Thursday, July 30, 2009

My first Posterous Post

This is my first posterous post.  I think Posterous will become pretty popular.

-- Sent from my Palm Pre

Posted via email from nateskousen's posterous

Sunday, July 1, 2007

Internship

My blog has turned into a journal of technical accomplishments. Some of my posts probably don't apply to many people. But I have written them because they were things that I wished people had blogged about so that I wouldn't have to spend so much time figuring it out. So, ultimately, I hope they can help someone wanting to do similar things.

As the title of this post indicates, I have started my internship with a Big 4 Accounting Firm. In fact, I have survived 2 weeks of training and firm orientation. This is my first experience with a truly global company/firm. The resources that this firm has is incredible. There were over 120 interns in our intern training group representing the pacific southwest area. We stayed in a very nice hotel in downtown Los Angeles and had catered breakfast and lunch everyday. We even had some nice dinners and room service. They gave each intern a brand new laptop and corporate charge card. They also spent 2 weeks training us on their audit methodology and the importance of the work that they and the other firms perform. We heard from many partners, managers, and staff throughout the 2 weeks. What I'm trying to say is that they spend a ton of resources (money) on interns. It was quite impressive.

Money isn't everything of course. I'm more excited to see how the 'work' will be. I'm anxious to see what I'll be doing and how I can contribute to the team and to the firm. My goal of doing this internship is to see if this is where I would like to launch my career.

Tomorrow I begin the first day of work and I'm excited to see how it will go. There are many things that I like about working for this firm and there are things that I'm skeptical of. But as with anything, it takes careful thought, experience, and advise if I decide (and if they give me a full time offer) to work for them after graduation. Ultimately, I'm excited to learn more and to get some more experience.

Friday, April 27, 2007

How to Create Dynamic Annotations with Dundas Charts

Let me just tell you that I love my job. I work at The Generations Network in the Business Intelligence Team. We manage the data warehouse for the company and I develop reports in ProClarity Analytics and in SQL Server Reporting Services (SSRS). We recently purchased Dundas Charts which adds some nice functionality to SSRS charting. One of the features is the ability to add annotations. There are some days when we experience a huge spike in data because of processing issues. Before, people would ask us "what happened on the 1st? Did we have a great day or what???" Now we can add annotations that explain dips or spikes.

Creating the annotations manually is pretty simple. But I wanted to create them dynamically. We have or will have hundreds of reports and charts that look at this data. Manually creating annotations for each report or chart would be ridiculous. By creating them dynamically, every chart we create can include these annotations. So how did I do it? Well it took a lot of help from Dundas Support who was excellent. I don't know VB.net very well but I knew how it could work. They really stepped up and made it easy. I would highly recommend Dundas Charts! Anyways, here is the solution:

1. I first created a normal SQL table with 2 fields... Annotation_Name and Annotation_Text. In order for this to work you need to name the annotation like a date (01/01/2007). The code will then anchor the annotation to the data point based on the name of the annotation. In the text column, you can add the text of the annotation.

2. After the table is created I needed a way to query this table, loop through the results, and create a new annotation for each record in the results set. This is where I had to do a lot of research, get a lot of help from Dundas Support, and some help from one of my professors. It is really simple if you know VB.net. Here is how you do it:

- You will need System.Data.dll. Find the assembly in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

- Copy the assembly System.Data.dll to the following folder: C:\program files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies. When the report is deployed, you will need to copy this to the ReportServer\bin folder on the server. By default, this is located at C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer\bin (where "x" is some number.)

- In the Chart's code editor > External Assemblies tab, add a new external assembly reference to System.data.dll

- In the source code paste in the code below. Modify the connection string and query string.

' Define Variables
Dim connectionString As String = "Data Source=localhost;Integrated Security=SSPI; Initial Catalog=DB"
Dim queryString As String = "Select Annotation_Name, Annotation_Text From Rpt_Annotations"
Dim connection As New System.Data.SqlClient.SqlConnection(connectionString)
' Open connection
connection.Open()
Dim command As New System.Data.SqlClient.SqlCommand(queryString, connection)
Dim reader As System.Data.SqlClient.SqlDataReader = command.ExecuteReader()
Dim newAnnotation As CalloutAnnotation

' Add annotations from the query results
While(reader.Read())newAnnotation = New CalloutAnnotation

' Assign the annotation name and text
newAnnotation.Name = reader.GetString(0)
newAnnotation.Text = reader.GetString(1)

newAnnotation.SmartLabels.Enabled = True
chartObj.Annotations.Add(newAnnotation)

End While


' Match up each annotation to its data point

For Each annotation As Annotation In chartObj.Annotations

Dim point As DataPoint = ChartObj.Series(0).Points.FindValue((DateTime.Parse(annotation.Name)).ToOADate(), "X")

If Not point Is Nothing Then
annotation.AnchorDataPoint = point

End If
Next



- This can be tricky since it is all in the code editor. If you have problems, go to the Chart Designer > Advanced tab and set the Chart's SuppressExceptions property to false to see what errors might be occurring.


And that's it. It works wonderfully and now I can insert a new record in the annotations table and have it show up on many reports and charts! Beautiful.

Thursday, April 5, 2007

More Sample Headers for ChoiceSkills.com

Here are some more sample headers with some different color schemes. Tell me which one you like. Leave some comments.




Monday, April 2, 2007

ChoiceSkills.com Header Mockup

As part of my web analytics course we are re-designing the website www.ChoiceSkills.com and making improvements aimed at increasing traffic, increasing conversion, and increasing revenue.

Here is a mockup header that could be implemented on the new site:

Friday, March 30, 2007

How to create a Report Execution Snapshot with SSIS

This last week I have been searching the internet for a solution on how to create a Reporting Services Report Execution Snapshot with SQL Server Integration Services. SQL Server Reporting Services mainly runs on XML and SOAP methods. So calling a RS function or method is relatively simple. SSIS even has a web service task that theoretically would work perfect for this situation. I tried referencing RS .wsdl file and SSIS wouldn't accept the format. After some more research I realized the web service task is pretty much useless when connecting to Reporting Services.

Then I looked at the scripting task in SSIS and that seemed too complicated. To get it to work I would have to create a RS proxy server and write a good amount of code. It seemed too difficult for what seemed to be an easy task.

I then found the rs utility. You can write reporting services script files (.rss) using VB.net to call Reporting Services web service methods and run these files from the command line. By putting the command line command in a batch file I am able to create an Execute Process task in SSIS and call the batch file. It was that easy.

So here is my solution:

reportsnapshot.rss
Public Sub Main()
'Run this in the command line: rs -i reportsnapshot.rss -s http://server/reportserver -v reportPathParameter=%1

Dim reportPath as string = reportPathParameter
Dim schedule AS NoSchedule
Dim EnableManualSnapshotCreation as Boolean = true
Dim KeepExecutionSnapshots as Boolean = false

'Sets the credentials
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

'Sets the Report History options
rs.SetReportHistoryOptions(reportPath, EnableManualSnapshotCreation, KeepExecutionSnapshots, schedule)

'Updates the Report Snapshot
rs.UpdateReportExecutionSnapshot(reportPath)


End Sub


reportsnapshot.bat
rem change directory
cd to where ever you have the .rss file saved

rem This batch file calls Reporting Services Script 'reportsnapshot.rss' and passes the report path which is the %1 variable
rs -i reportsnapshot.rss -s http://server/reportserver -v reportPathParameter=%1

rem This will indicate whether there is an error. Error = 1, Non Error =0. This is used in the Process Task to indicate whether the task fails or succeeds.
EXIT [ERRORLEVEL]


Execute Process Task
Executable: Path to where you have the .bat file saved
Arguments: The path to the report (ex. "/folder/report") Note: You need the first "/". This will be passed into the batch file as the %1 parameter.
FailTaskIfReturnCodeIsNotSuccessValue: True
SuccessValue: 0 (The EXIT [ERRORLEVEL] will return a 0 or 1. 1 is fail. This will make it so the task will fail if the batch file encounters an error.)


If you are in need of clarification or if you have questions about this you can e-mail me!

Disapointing March Madness

So I'm a little disappointed with this year's NCAA tournament. There just weren't enough upsets and I'm sick of seeing Florida win everything. I hope UCLA creams Florida. Besides, Ohio State should have lost to Xavier in the 2nd round. Had only Xavier made that last free throw, Ohio State would not be in the final four. Needless to say, my bracket is full of red font and slashes. Maybe next year!