T-SQL Tuesday #22 – Data Presentation

Tags: ,
Comments: No Comments
Published on: September 13, 2011

Tuesday is upon us.  It also happens to be the second Tuesday of the month and you know what that means.  It is time for TSQL Tuesday.  This month, Robert Pearl, a friend, is hosting.  Read his invitation here.

As the invitation suggests, this topic is wide open.  There are many ways to present data.  Some of those methods may be better than others.  There are a few things of note with Data Presentation that will impact how much better your Data Presentation will be.

The items I will discuss that will help your data be better presented are: Performance, Accuracy, Display, and Business Requirements.  I will use a few scripts to progress through each of these topics.  Let’s start with Performance.

Performance

Why is performance included in this discussion?  How does performance relate to data presentation?  Well, have you ever had an end-user complain that a report was utterly useless because it was too slow?  If the report is too slow, it won’t matter if the data is accurate in the eyes of some.  They needed to have the data yesterday and it simply took too long.

Here is an example of a query that could be optimized a bit more (admittedly this query does not perform soooo slowly that a user would give up on it – by the end you will see that it could perform better).

[codesyntax lang="tsql"]

[/codesyntax]

The requirements for this script are simple.  Provide the number of days in a month in a given date range.  If the starting date provided is not the first of the month, then we must only provide the number of days from that date to the end of the month.

As you can see, this script utilizes a looping mechanism to provide this information.  The loop inserts into a table variable one record at a time.  When examining the execution plan and the execution time on this query, one would see that the insert is the most expensive part of the query.  One would also find that this query does take a fair amount of time to run – despite its’ simplicity.  On my machine, it takes about 1.3 seconds to execute.

Certainly, if this were a more complicated query, one would see that this type of query could cause some delays in data rendering and subsequently cause grief for the end-user and you.

There is one more issue with the provided query in that it doesn’t meet all requirements.  I neglected to mention that the date format needs to accept date/month/year format.  Yes it is nitpicking, but it was a requirement and an invalid date is far too easy to submit with this query.

Accuracy

There should be no question on how accuracy affects data presentation.  No matter how pretty the rest of the data/report may look, wrong data will render the report useless in the eyes of many business users.

Again, this example is not extreme – but it does create sufficient concern that the query should not be used – unless fixed.

[codesyntax lang="tsql"]

[/codesyntax]

Though this query works faster than the first query, it is not entirely accurate.  This query only supports a max of 12 months.  Also, this query is overly complex.  There is value in keeping this particular query more simple.

Display

This topic could be deemed to be largely a matter of preference.  That said, there is great value in how you display the data to the end user.  Displaying the data involves such things as useful column headings, and meaningful data.  For instance, naming a column “Month” but displaying a year value in it – is not very useful.  This could also overlap with the prior topic of accurate data.

Personally, when I am displaying the month, I like to see month names.  In my experience, displaying the name of the month is more easily recognizable than the month number for many users.

[codesyntax lang="tsql"]

[/codesyntax]

In this case, the Column Heading matches the data and works.  However, a little change could make this query and output more useful to an end user who is quickly scanning over the results.

[codesyntax lang="tsql"]

[/codesyntax]

To this point, each query has progressively increased in performance.  This last query (both versions) also matches the need to accurately display the data.

The display of data is closely coupled with the next topic – Business Requirements.

Business Requirements

When looking at the results of the last two queries, which one is more accurate?  Well, that entirely depends on what the business has specified as acceptable output for this query.  In this case, both could be correct or both could be wrong.

It is essential to have the requirements written down and understood by all parties so as to avoid any misconception or misunderstanding of what the query should display.  Matching your output to the specified business requirements will help to provide an overall impression of accuracy and usefulness.  It will also help to achieve faster sign-off by the business.

Conclusion

Data Presentation is not just about the look of a report.  Data Presentation involves performance, accuracy, display and the business requirements.  By giving proper attention to each of these facets, the Data presented will be both remarkable and acceptable to the end-user.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">






Calendar
September 2011
M T W T F S S
« Aug   Oct »
 1234
567891011
12131415161718
19202122232425
2627282930  
Content
SQLHelp

SQLHelp


Welcome , today is Wednesday, July 30, 2014