Last Update Dates

It's been a while... We are well into deployment and finalization of this version of our ETL and reporting system. Things are going well.

Recently I ran into an interesting ETL problem while using a source system "last update" field. Let me give you some background.

We have an ETL process that reads from a source system that was developed in-house. The queries were all based on the last update field in all of the tables.

While in UAT, several reports were reported as missing rows. After investigating, it appeared that the rows had never made it to the data mart. Needless to say, this was very worrisome.

I researched and tried to find out why these rows were excluded. There seemed to be no pattern, just random rows.

While looking at my morning logs, I noticed something strange. The ETL Last Update table showed times from about 5 hours after the ETL had run. It should have been when the ETL had run.

I looked in the source system, and there were 3 rows that had update dates in the future! 5 hours to be exact!

It turns out that under certain circumstances, the source system was using the wrong date/time to update the last update field. And this date was GMT, so it was 5 hours in the "future" when it was applied to the last update field.

The result of this was missed records on the ETL. It would miss 5 hours worth of updates anytime this occured in the source system.

So my recommendation, which I am now kicking myself for not implementing to begin with, is this: Always use a date range, not just a "Greater than" for last update fields. For example:

Where LastUpdate Between '3/5/09 11:00:00' and GetDate()

And the other rule... Never trust the source system to be accurate 100% of the time. Anticipate issues like this.

Anyway, that's all for now.

peace

Report Layout Basics

I thought I would go over some of the basics of report layouts since I have seen a few forum posts about basic layouts. This is by no means a comprehensive overview, but a starting place.

The first thing to realize about the report layout is that the only sections you have to work with are the page header, page footer and the report body. There is no concept of a report header/footer, etc. But also, unlike Crystal Reports, the report sections are NOT tied to data.

Instead, the report body holds the controls that can act as report sections. Here are some ideas:

Report Header

If you need a report header, you can place any control, like a text box, on the body of the report, and populate it with data, static text, etc. If you place these controls inside a rectangle, you can set the PageBreakAtEnd property to true, and then those text boxes will appear on a page by themselves. If you dont use a rectangle in this manner, your report header will be on the same page as the start of your data area.

Report Footer

As with the report header, if you place controls at the bottom of the body section, in a rectangle, and set the PageBreakAtStart to true, you will have these on the last page of the report.

Multiple Body Sections

You can have multiple body sections just by adding multiple controls to the body section of the report. Say that you have 2 datasets in your report and you want to show them in separate sections of the report. Add a table for the first dataset. Set the PageBreakAtEnd property to true.
Then add another table below that for the second dataset. When you render the report, you will have the first table displayed, a page break, and then the second table. Add the report header and footer from above, and you now have a 4 section report!

Using the List Control

The list control is used to repeat a set of controls within it. You can place a list control on your report, set the dataset and the grouping on the list. Then place some text boxes in the list control and populate them with the fields from the dataset. The list will group the data as you set up, and populate the text boxes, repeating for every group.

You can also put multiple tables in the list control. I will do this often when there is a lot of detail data to display, and I do not want to have to worry about the column layouts.

Well, that was some basic layout ideas. I will add more soon

peace

BobP

Rendering SSRS 2005 Reports with the Web Service

Even though Report Manager is a decent way to deliver reports to your users, you may have the requirement to embed the report in an existing .NET application. This is very simple using the web services provided with SSRS 2005.

Here is a code sample that I use to show developers at my clients how to pull a report.

string historyID = null;
string deviceInfo = null;
string format = "Excel"; //Can be XML, NULL, CSV, IMAGE, PDF, HTML4.0, HTML3.2, MHTML, EXCEL, and HTMLOWC
Byte[] results;
string encoding = String.Empty;
string mimeType = String.Empty;
string extension = String.Empty;
ReportingExec.Warning[] warnings = null;
string[] streamIDs = null;


ReportExecutionService re = new ReportExecutionService();
re.Credentials = System.Net.CredentialCache.DefaultCredentials;

ExecutionInfo ei = re.LoadReport("/directory/ReportName”, historyID);

ReportingExec.ParameterValue[] rptParameters = new ReportingExec.ParameterValue[1];

rptParameters[0] = new ReportingExec.ParameterValue();
rptParameters[0].Name = "DateFormatID";
rptParameters[0].Value = "fr-FR";

re.SetExecutionParameters(rptParameters, "en-us");


results = re.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

FileStream stream = File.Create("C:\\report.xls", results.Length);
stream.Write(results, 0, results.Length);
stream.Close();

You will need to set up the reference in your project. The URL for this is: http://servername/reportserver/ReportService2005.asmx

This code, which I give to the developers in a web app, demonstrates the basic use of the web services. It loads a report, adds a parameter value, and then renders the report and saves it to disk. If you wanted to render it back to the user on a web page, you would add:

Response.AddHeader("content-disposition", "attachment; filename=File.xls");
Response.OutputStream.Write(results, 0, results.Length);

Response.End();

By taking this code a little further, you could easily send out an email with the report attached. Or another commonly requested feature for SSRS: emailing multiple reports on 1 email.

You could loop thru a database table, reading the reports to send a user, render that report, save the file, attach it to a system.mail email, and then move to the next report. When the looping for that user is complete, send the email, delete the files, and move on to the next user in the table.

I hope this helps clear up web services in SSRS 2005. Most developers that I work with are not familiar with this web service, and this always gets them going in the right direction. This is by far a VERY basic demonstration. There is a lot more to the web services than just this.

For more reading on the web services, visit:
http://msdn2.microsoft.com/en-us/library/ms155076.aspx


peace

BobP

Is the business view of IT changing?

Today, I am going to stray from SSRS again and discuss business.

In my normal batch of morning email, I came across a link to an article
(http://www.networkworld.com/news/2007/101607-noncertified-it-pros-better-paid.html) with the headline being: Non-certified IT pros earn more than certified counterparts: survey.

Being a non-certified professional, I was really happy to see that. But after reading it, I realized that the headline was not the only point to the article. It was just the one that would catch people’s eye. For me, the interesting message is that companies are tired of “techies.” They really want business people that can do a technical job.

I have been working with business units for most of my career. My first true technology job offer was: “I can’t get any information out of IT; I want you to work for me, the CFO, and provide me with the reports that I need and can understand.” I understand from the business side how frustrating it is when you ask for a report, and a developer responds with technical jargon about primary keys and cross server queries. The average business person glazes over when they hear the first technical term.

Don’t misunderstand me, I am a technical person. I am a geek. I love the inner workings of almost anything, not just software or gadgets. But first and foremost, I am a business person. I started my first business at age 7, selling lemonade. I made almost $10 in one day! (And I did it without a PC, without a large un-wieldy database application that took 2 years to implement and never worked right, without a tech-head telling me that I shouldn’t even be in business because my network platform was not on the cutting edge.) I have started and run several successful businesses outside of the technology realm, and a couple within. I have been in management, and in the ranks. I have seen a lot from the business side.

So it was refreshing for me to read that more business people want this IT re-alignment. But, I have been hearing this for almost 20 years. Are things really starting to change?

One can hope.


Peace

3 Lessons on Deadlines and Business Requirements

Well, here I am again, back after a "Got to have it now, pull out all the stops" project has been completed.

Let me give you a quick overview:

I had a client come to me on Sept 27th with an urgent request:

"We have a new system going into beta with several very important clients and we need a quick data mart and 2 reports. And we need them by next Tuesday!"

So with a fair amount of skepticism, I accepted the task. After all, this was a client that I have been dealing with for a while, and it was only 2 reports.

So, using SSIS and SQL 2k5, I put together the data mart (I had already been working on some data marts for this client, so I was familiar with their business and data model) and started looking in depth at the reports.

OOOOOPS!! Guess I should have done that first. By looking at the reports I could tell that the users had asked for some reports, the deadline aware project manager talked to the developers, who told the client that they only had time for 2 reports, not the 33 the users originally needed.

This was not 2 reports. It was "let's take all 33 reports, combine all of the parameters and fields and put them on 1 report, and then add 1 because we are allowed to."

So, I naturally did what any decent consultant would do. I balked. Not in a bad way, not rude or condescending, just factual:

While this 1 master report will work, is it really fulfilling the users needs? Do they need 28 parameters in the parameter bar in the SSRS report manager? It takes up 80% of the maximized browser window. How do they view the report? Also, the report is very slow, because you are joining not only fact tables and dimensions, but also different atomic level fact tables. Let's talk to the users to see what they really need.

So we did. And in doing so, we found out that they had indeed been told that they could only have 2 reports. So:

• Lesson #1: If your users tell you they need X functionality, and you restrict them to only Y number of items, they WILL find a way to get X into the Y.

In talking with the user, we also found out that next Tuesday was NOT a hard fast deadline.

• Lesson #2: Given the choice between crap in 2 days and gold in 2 weeks, most users will take the gold.

By giving the users my recommendations, they came to appreciate that someone was looking out for their best interests. They felt comfortable telling me their goals, their needs, as it pertains to these reports. I built up a relationship with these people. In turn, I made sure to try and understand what they needed, and provide it for them.

• Lesson #3: Users are just people trying to get their job done. Respect that.

And they will get it done with or without your software, reports, data, etc. It's your job to make theirs easier, and to contribute to the well being of the company as a whole.

These seem to be pretty basic lessons, right? However, as a consultant, I see these same mistakes being made everywhere. And yet it surprises me every time. We, as an Information Technology industry, should never let these lessons slip from our minds. They are fundamental to our jobs, and to our industry as a whole.

In summary, the users defined 4 reports that would meet their immediate needs, and I was able to produce those reports for the users in a couple of days, and everyone is happy. Sounds like a cheesy movie, huh?

Peace