Un-SQL Friday: Bad Interviews

I haven’t been participating in many of these blog memes because I have been so busy lately. A lot of my time was occupied with job hunting and since I just finished a series of interviews with different companies, this meme is perfectly timed. My good friend Jen McCown (Blog/Twitter), the fairer half of the Midnight DBAs, tagged the SQL Community asking for our bad interview stories. I’m going to give two bad experiences and one surprisingly good experience.

First, I’ve been putting this announcement off, but I have changed employers. I am now a Senior Business Intelligence Consultant for a specialized consulting firm in Irving Texas. I want to thank the entire SQL Community for their help, training, and friendship on my way to this goal. To my Made2Manage friends, let me just say that this isn’t the end. I’ve started doing formal M2M consulting as part of this new position as well and still intend to release M-Data Analytics. More information on that will be coming soon, so stay tuned.

So, on with the stories.

Two Men Enter, One Man Leaves

I answered a post from a local company that was looking for a Business Intelligence Developer with Data Warehousing experience. I had a first interview (telephone) that went very well and was called back for a second. They were very accommodating in that they scheduled the interviews at the end of the day. It’s easier to duck out at the end of the day for a “Doctor’s appointment” rather than losing 4 hours in the middle of the day. If an employee has a string of “appointments,” the employer starts to wonder.

Anyway, I arrived a few minutes early but there was a guy in a business suit already getting out of the car next to mine. He looked nervous and obviously there for an interview as well. He signed in at the front desk right before me and was seeing the same person. I immediately got a weird feeling.

We rode upstairs and sat in a small reception area for 15 or 20 minutes (though we were both on time) which was uncomfortable. I broke the ice and joked with him about whether he or I had made a scheduling mistake or perhaps we were going to have a cage match to see who got the position. We then discussed our work experience and I convinced him that he should be going to our local SSUG meetings. By the way, the receptionist wasn’t very good at hiding the fact that she was listening to everything we said.

The BI Director finally came to get us and I asked him in a humorous way if there had been a scheduling mistake or if we were about to fight to the death like Mad Max Beyond Thunderdome. The director indicated that having both candidates interact was intentional and meant to raise stress levels. I said that it hadn’t raised my stress level, as I wasn’t there to compete with anyone else and had in fact recruited another PASS community member to boot.

Liar Liar

My second experience involves a recruiter. In the IT world, recruiters are a fact of life and Brent Ozar (Blog/Twitter) has written a couple of wonderful articles about dealing with them.

I spoke with a recruiter about a Data Warehouse position that they were trying to fill, but it would involve relocation. Relocation is problematic because of the costs involved, but even more so if the new location is in an economically depressed area. The job market in Dallas is very strong, particularly for business intelligence people, and I was reluctant to relocate somewhere with limited employment alternatives. I aced the technical telephone interview, largely thanks to all of the help and training I receive from other PASS members. However, when we discussed the terms of the job, I was informed that it would be a contract to hire and that relocation costs were not part of the package.

This was unacceptable to me as I wasn’t going to pay several thousands of dollars moving everything I own to an economic wasteland for a contracted hourly rate. There is no commitment on the employer’s part, and if things didn’t work out, I’d have to pay to move again to find a good position. Umm… no.

The recruiter called their client to discuss this and said that since the client was so impressed with my resume, they’d make an exception, hire me as an employee, and compensate me for relocation. I arranged an in person interview with the client, and paid the costs to travel to it. This wasn’t a big deal because I was traveling to this area anyway.

The interview went well and I found the hiring manager to be a straight forward and nice guy. He then offered me the job and asked when I could start. I was rather taken aback because we hadn’t discussed the actual salary, benefits, and relocation package. When I asked him about those, he was suprised and said, “What do you mean? This is a contract to hire job and there is no relocation package. We don’t have to provide that because the job market here is so poor.” Ugh.

I then explained to him exactly what the recruiter told me and offered to send him a copy of the e-mail to corroborate my “story.” He asked me to wait a moment and as I sat there, he called the recruiter. The conversation went like this.

“Ms. Smith (name withheld to protect the guilty), what did you tell Mr. Stein about my job terms?” Pause…. and then more forcefully, “No, what EXACTLY did you tell Mr. Stein?” Longer Pause… “Precisely what did you hope to gain by wasting his time and mine this way?” Pause…. “Well Ms. Smith, have Mr. Jones, the head of your company, call me later so I can explain to him why we will never use your company again.” Pause… “No, you simply cannot treat people this way as it reflects poorly on me and my company. Good Day.”

After he hung up we shook hands and he apologized for wasting my time. I told him that I enjoyed speaking with him anyway and eventually added him to my LinkedIn network. It may not have resulted in a job, but it was a hilarious experience anyway.

Diamond in the Rough

A few weeks ago, I went to a Qlikview demonstration at the request of my previous employer. To set the scene, I brought my laptop and such because I was going to a North Texas SQL Server User Group meeting immediately afterward, but was dressed very casually in shorts and a video gaming t-shirt for the same reason. I wasn’t trying to impress anyone and certainly didn’t expect to find a potential employer there.

Perfect Interview Attire

Anyway, I was surrounded by a group of business people, typically dressed in suits or other business dress and obviously stood out. I noticed a a guy with brown hair, goatee, and glasses who entered with a group of people and I did a double take. I thought he was Andy Leonard (Blog/Twitter). By chance they sat down near me so I struck up a conversation with the guy asking him what he did. He’s a Microsoft BI DBA, so I asked him if he had heard of Andy, and he gave me a weird look, because this whole situation was fairly unusual. I opened up my laptop and found a picture of Andy on the internet and the guy agreed that they were likely twins separated at birth. Anyway, the gentleman next to him, who was obviously the doppleganger’s Director, was listening to our conversation and asked me about my background. I told him what I did, how I knew Andy, my work with the North Texas SQL Server User Group, etc. I didn’t realize it, but I was being interviewed. The company was looking for a talented, outgoing, business intelligence person and the Director saw something in me that he liked.

The Director sent me an email asking me to lunch and I had my second interview at Chili’s while wearing business casual dress. In fact, I was wearing my Chicago SQL Saturday Presenter’s shirt. The interview had a technical portion, but mostly it was about my personality, the current team, and how to mesh the two together. We were just two guys talking about our mutual love of all things Data.

The rest is history. I now work for the new firm, and am getting the experience and being challenged every day.

So, what about you? Any of you have any bad or good interview stories?

Stop Writing Visual FoxPro Reports... Today

Last week I saw a discussion on the LinkedIn Made2Manage Group about the Pros and Cons of using SSRS vs. VFP Reporting, and it amazes me that people are still arguing about it. So, I thought I’d reaffirm a stance that I’ve had for many years, which is VFP Reporting Must Die.

I’ve lamented Visual FoxPro’s limitations in the past and I don’t want bore you by hashing them all out again. Let’s just hit the highlights.

  1. VFP reports are difficult to write and maintain.
  2. VFP lacks chart and graph capability.
  3. M2M report permissions management is tedious.

However, in addition to that list, there are many other reasons you should curtail your use of Visual FoxPro.

Technological Advantages

There many technical advantages to using SSRS (or even Crystal Reports for that matter) over Visual FoxPro. In addition to graphing capabilities, SSRS also facilitates more interaction and customization. For example, you can easily create reports with drill down, and you can drill across to different reports as well. Let’s say for example, that your Operations Manager is looking at a Sales Order report, and wants to drill across to Purchase Order information tied to that Sales Order. Think you can do that with VFP?

Develop Marketable Skills

As I mentioned last week, VFP is a dying language and will never make you a more desirable employee in the market. The same applies if M2M adopts some other niche product as their new reporting strategy. Nobody is going to pay six figures for experience in some product nobody uses. Spend time focusing on T-SQL Programming and SSRS instead.

Remember, that it’s in Consona’s best interest to use a proprietary niche product as that generates additional consulting revenue for them.

Ease of Use

SSRS is so much easier to use than Visual FoxPro. It’s easier to learn and the tools are more polished.

Re-usable Code

All of these methods essentially boil down to one common denominator, T-SQL. The base T-SQL code is more easily extracted from SSRS, Crystal, etc. than from Visual FoxPro. Also, once you have the T-SQL, you can quickly re-create reports in any of the other methods including Qlickview, Tableau, and Excel.

Also, don’t forget to use Database Views to make your code more manageable and easier to upgrade.

SSRS is Less Expensive Than VFP

SSRS is free with SQL Server 2005 and above. Did you actually license all of those copies of Visual FoxPro that you most likely have on your server and several workstations? If not, you’re breaking the law as those licenses cost approximately $500 a pop.

SSRS Consulting is Less Expensive

M2M charges $225 an hour for report customization work. One of the reasons they can is because there are relatively few people who are skilled in Visual FoxPro Reporting. SSRS Reporting skills are much more common and the competition drives prices down. Competent SSRS report writers can be hired for anywhere from $100 – $150 per hour on contract.

The Made2Manage people who recommend VFP Report Writing have three basic reasons that they cite for doing so.

Using Anything Else is Not Supported

Yes, this is true. Consona does not support the use of SSRS, Crystal, or even Excel for that matter. However, they don’t support their own VFP report customizations either. Consona’s contracts generally state that the contracted customization is only guaranteed to work on the version for which it was written. Be prepared, no matter which reporting method you use, to test and possibly edit your custom reports when migrating to a new version of M2M. If you’re using M2M Customized VFP reports, that may well mean more billing hours at $255 per.

M2M Version 7 is Imminent

Really? M2M has just begun to ask for Beta Testing volunteers. That process, especially when migrating to a new major release, will take a long time. And really, even if Version 7 was available today, how soon will most of you migrate anyway? I personally wouldn’t move until 7.01 (or after several service packs were issued) as new major releases are always problematic. Just ask the poor souls who were early adopters of version 6.0.

Yes, in a future version, M2M will get away from using Visual FoxPro. However, to the best of my knowledge they still haven’t announced what their new method will be. Even if it is some proprietary product, does it really matter anyway? They certainly aren’t going to migrate the entire product to Oracle or another database platform. Your SSRS reports will still work, and the time and effort invested in them is not wasted.

Opening Reports in Another Program is Inconvenient

You don’t need to open SSRS to use the reports. Those of you who regularly read this blog know that I’ve already shared how you can launch SSRS reports quickly and easily directly from M2M. I know that people have done the same thing with Crystal Reports as well.

One Exception….

The only time I edit a M2M VFP Report is when the requested customization is very small and easy to deliver. If a user wants another field from a table in the query, or some other small task, I’ll edit the current VFP report instead. I only do that when it’s more efficient than creating an entire report in SSRS or another medium.

What do you think? Anyone disagree with what I’ve said? What are you using as your reporting method and why?

What is Your Focus?

When was the last time you booked a trip through a Travel Agent? Rented a Video from an actual person? Have you ever seen a Milkman Telephone Operator, or Elevator Operator except in classic movies?

Why do I ask? Well, technology is eliminating the first two jobs, and the last three are practically extinct. Which way is your skill set headed?

Several years ago, my career was going in that direction. I was a Made2Manage expert and used Visual FoxPro to create reports, customize the system, etc. I had skills, but they were very difficult to leverage in the job market. I saw the writing on the wall, made changes, and I urge you to do the same.

Focus on Skills With Staying Power

Invest your time developing skills in technologies that are not going to be obsolete or completely change every year. In my case, my skills were focused on obsolete technologies like Visual FoxPro Programming and Reporting, which was a dead end.

I initially switched my focus to T-SQL and Visual Basic programming, then eventually to Data Warehousing, and Microsoft Business Intelligence (SSIS, SSRS, SSAS). As others have mentioned, T-SQL is relatively static, and since Data Warehouse design concepts are technology agnostic they’re also unlikely to change radically. Microsoft has invested a fortune in it’s Business Intelligence products and has an immense customer base.

Keep in mind that these were my choices and aren’t necessarily for you. Just make sure that your choices have staying power as well.

Focus on Skills That Fascinate You

Gotta Love Your Job

While presenting at M2M’s premier conference last year, an audience member asked me how I felt about being a M2M Administrator. I answered with a joke,


It’s a lot like working at a sewage treatment plant. You’ll always have a job that pays relatively well, but you always go home smelling like crap.

Like most jokes, it’s funny because there’s an element of truth. It’s not enough to focus on a specific skill because it has staying power, you need to figure out what you love to do. Practicing a skill you find fascinating increases your learning rate and leads to a more enjoyable life as well.

Narrow Your Focus

In a past life, I taught high school biology and one of the concepts was Specialization vs. Generalization. In terms of Biology, organisms that specialize are more efficient (like humans) whereas organisms which are more generalized (like Bacteria) have greater survivability in times of calamity. The same is true for IT Skills. You can’t be excellent at everything, there is simply too much to know. Andy Leonard (Blog/Twitter) is probably not an expert in Disaster Recovery and Performance Tuning and Paul Randal (Blog/Twitter) can’t create world class SSIS packages.

However, a narrow focus fosters greater skill and that results in greater efficiency. This generally results in a higher rate of pay as well. Conversely, a generalist with average skills in several areas might seem to have a higher survivability in times of trouble. The generalist can work anywhere, whereas your average company cannot hire Brent Ozar (Blog/Twitter) to be their IT guy.

While this may have been an issue at one time, the Internet has largely negated it. The top people in these fields will always have work and in my opinion, they will always be worth more than a good generalist. Now I realize that every job requires a certain amount of undesirable tasks. Perhaps you dislike checking backups, error logs, etc. I’m not suggesting that you stop doing that, but do your best to spend the bulk of your time working on specific skills to achieve mastery.

I had a conversation with a friend of mine recently about this and he told me, “I do specialize, I specialize on [name of his company here].” He’s banking on being indispensable and that his job security will carry him in this tough economic climate. Hate to burst your bubble, but….

There is No Job Security

Nobody is indispensable, and you shouldn’t want to be. The company survived before you started there, and they will find a way to do without you. They may have to spend more money or limp along with substandard service, but they don’t need you. Besides, in my experience being considered mission critical is more of a hindrance than a benefit. If you are absolutely necessary in your current role, you cannot be promoted and your skills may stagnate.

Focus on Skills in Demand

Back in the 70’s and 80’s Racquetball was a hot sport, and while I was in college, I used to work out with a Professional Racquetball Player. The popularity of racquetball has dropped since then, and Rocky Carson, the current top pro, claims to earn six figure income from winnings and endorsements. Meanwhile Tiger Woods, another non-team athlete, earns nearly 1000 times as much. Is Tiger really 1000 times the athlete as Rocky? Maybe and maybe not, but I’d argue that the primary difference is the demand of their respective sports.

Being the world’s best Tiddlywinks player doesn’t matter much if you can’t make money doing it, unless of course you aren’t trying to make a career out of it. I’m just saying that if Turbo Pascal and C# .NET both fascinate you, I’d go with C# .NET.

Focus On Networking

Something I learned a few years ago is that I always need to network. People come and go from your work, community, and personal lives so you need to be in a constant state of expansion. Don’t start networking when you need a job, a mature network should already be in place beforehand. Notice that when I say networking, I’m not talking about prowling sites like Monster or Dice and I’m not suggesting that you should perpetually look for a new job. Networking is about making friends, sharing technical information, helping others, etc.

Although I respect both men a great deal, ultimately I’d rather end up like Thomas Edison than Nikola Tesla. Both were undoubtedly brilliant, but Tesla died in obscurity, alone and penniless.

Focus On Soft Skills

Anybody can learn to take backups, use Reporting Services, or write T-SQL code. If you’re reading this, you’re smart and want to succeed. However, the geek stereotype is that we’re often socially awkward, don’t relate well to people, and don’t express ourselves well in both verbal and written form. These are the kinds of skills that we all should focus on because they’re considered rare in our profession. To paraphrase an old saying:

If we’re running from Zombies, I don’t need to outrun the Zombies. I only need to outrun you.

Having participated in many interviews recently, soft skills are often much more important than the hard skills. If you give a good, motivated junior DBA a job, his technical skills will quickly improve. If you hire a Senior DBA with the stereotypical personality challenges, is he likely to become Dale Carnegie-esque at the same rate?

So, how do you get the soft skills? Well, the same way you can gain all of of the skills I’ve already mentioned.

Focus on Community Involvement

Geoff Hiten, Grant Fritchey, and Buck Woody at the 2010 Summit

There are so many benefits to being active in the community. You can make great friends and learn so much from other PASS Community members. Many of us blog and present at the regional and local level, and some of us act as personal mentors to more junior DBAs.

Get involved. Attend meetings, blog, and present. This will help you learn those soft skills, and become known for them. You can’t achieve these skills overnight, but if you want to jump start the process, you should….

Focus on Going to the Pass 2011 Summit

The PASS Summit is the premier event for SQL Server Professionals. You can network with the best and brightest. Get help with your technical problems, sometimes from the people who created the technologies. If your employer will send you, that’s great. If not then bite the bullet and pay your own way as I did last year.

I’m registering today and you should too, because today is the last day to take advantage of the $600 early bird discount. You may find that it’s one of the best things to happen to your career. I did last year.

I urge you to take the time to examine your focus and where it’s leading you. This is your life, your career, and your future.

I hope you’ll make the most of it and that I’ll see you at this year’s PASS Summit.

Find ways to change focus that are good for you and the employer, or find a new employer. This is your life.

Creating a Date Table/Dimension on SQL 2008.

In a previous article, I listed the benefits of using a dedicated date table and included a customizable script which enables you to quickly create your own version. One of my readers pointed out that he uses the date datatype, rather than using the smart integer key method, when working with SQL 2008+ databases. The smart date key is recommended by the Kimball Group and others.

I performed several tests comparing the performance of Date vs. Integer datatype joins and found the Date joins to perform faster as well.

As a result, I’ve modified my script to create a Date datatype dimension have begun using it. As before, it is in two parts. The first script can be found here. Open the script, replace DateDatabase with the database name of your choice, and run it to create the function.

Once again, I need to state that I created this script from a function called F_TABLE_DATE by Michael Valentine Jones from SQLTeam and have since gotten his permission to distribute it. That function is called in the statement below to create the date table/dimension.

     (SELECT 1
        DROP TABLE dbo.DimDate;
       into dbo.DimDate
 From dbo.F_TABLE_DATE ( '20000101','20351231' ) DTE
 order by 1
ON dbo.DimDate 

My example above generates every day through 2035, but nearly any date range can be used. As before, if you don’t want to have such a comprehensive Date Dimension or there are fields you don’t need, simply comment out items from the Select into query.

Querying against the Date Dimension is done the same way as with my previous script.

    MyTable MY
    DateDatabase.dbo.DimDate DTE
  ON >= AND < DTE.NextDayDate

Any questions or suggestions for improvement?

SQL Rally 2012 is Coming to Big D!

Last week PASS announced that Dallas and the North Texas SQL Server User Group have been awarded SQLRally 2012. This is great news for all of the nearby PASS User Groups and DBA’s.

We’ve put on three successful SQL Saturday events in 18 months, and I’m proud to have been on the planning committee for each. We’re a dedicated, cohesive team, so SQLRally is in good hands. I’m so geeked that we’re hosting the event and I just wanted to take a moment to thank the other members of the team who’ve worked so hard to build a strong community in Dallas.

Dave Ryan Vic

Ryan, Vic, and yours truly at SQL Sat 56 BI Edition.

You folks are awesome, and I’m glad to call you friends.

Bring on SQLRally!

Opening SSRS Reports Automatically From Other Systems

Users typically love SQL Server Reporting Services Reports (SSRS), especially if they’re accustomed to more primitive tools such as Visual FoxPro. However, One of the biggest complaints I hear about SSRS is that users want to be able to launch it directly from their ERP, accounting, and other systems. A conventional way to do this is through URL (Universal Resource Locator) manipulation.

Depending on the capabilities of the source system, reports can be launch from a button, combo box, list box, etc. and display a specific recordset based upon parameters you specify. The best way I’ve found to launch SSRS from Made2Manage is through VBA, which is a free optional module that any M2M customer can use.

Steps to Create a URL Link Customization

  1. Create a basic SSRS report. For this demo, I’ve created a very simple report which pulls a set of records from the Sales Order Master (SOMast) Table, which will be launched from the Sales Screen (SO).
  2. Determine the actual URL link. I’m not going to go through each step of obtaining the proper URL because there’s an excellent step by step tutorial here. In my case, the URL will be:
  3. http://jeditemple/ReportServer/Pages/ReportViewer.aspx%2fBlog+Projects%2fSales+Details+for+URL+Link&rs:Command=Render
    Use the tutorial link instructions to find your URL, and the rest of this article is about how to integrate this into M2M.

  4. Properly install VBA on the systems which will use this link. There are instructions on M2MExpert as well as on the M2M Install Disks, but beware that they are confusing and hard to follow.
  5. Customize your M2VEvents.prj file in VBA. There is a downloadable class on M2M’s website for VBA programming.
  6. You manipulate your M2VEvents.prj file by selecting Tools, Macros, and then Macros or by clicking on the Transfer button and typing VBPROJ. If your VBA project file is empty, you will have to access it by opening a screen in M2M, in this case SO, right clicking on the screen mnemonic, and selecting components. Choose INIT and the VBA Editor will open and the SO module will be created for you.
  7. Right click on the Module folder and select Insert and Module.
  8. Click on the new module and change the name to “DefaultModule” in the properties box.
  9. Open the default module and paste the following code into it. This is a public function that is called by each module when creating the report URLs. Notice that I’ve ended it after the Blog Projects folder. My test SSRS Server only has the one folder on it at this time. The point is that this function returns the base portion of the URL for every report you intend to launch from M2M. The reason I’ve used the DefaultModule is if I decide to commission a new server, with a new name and such, I only need to change this base portion in one place, not in every module of the VBA project. Keep in mind that you will need to customize this code to match your server, folder structure, report names, etc.
  10. Public Function GetReportString() As String
        GetReportString = "http://jeditemple/ReportServer/Pages/ReportViewer.aspx?%2fBlog+Projects%2f"
    End Function
  11. Open the SO Module, press CTRL-A to select all of the text in it and delete that text. Then copy and paste in the following code into the SO module.
  12. Public ReportStr As String
    Public Function INIT() As Boolean
       With ThisForm.m2mPageFrame1.page1
         .addobject "lstReports", "m2mListBox"
         .lstreports.Top = 300
         .lstreports.Left = 500
         .lstreports.Width = 80
         .lstreports.Height = 39
         .lstreports.Visible = True
         .lstreports.AddItem "Sales"
        End With
       ReportStr = GetReportString()
        INIT = True
    End Function
    Public Function M2mpageframe1_Page1_lstReports_CLICK() As Boolean
    Dim Rpt As String, Param As String, lcSomastSono As String, Newsite As Object
    Set Newsite = CreateObject("InternetExplorer.application")
    Select Case ThisForm.m2mPageFrame1.page1.lstreports.Value
        Case "Sales"
                lcSomastSono = Trim(ThisForm.gettablevalue("somast", "fsono"))
                Rpt = "Sales+Details+for+URL+Link&rs:Command=Render"
                Param = "&SONO=" + lcSomastSono
                Newsite.Navigate (ReportStr & Rpt & Param)
                Newsite.Visible = True
        Case Else
                MsgBox ("Problem occured. Contact your administrator for help. ")
    End Select
        M2mpageframe1_Page1_lstReports_LICK = True
    End Function
  13. The INIT code draws a listbox on the SO form, assigns it’s properties, adds a selection to it called “Sales”. I chose a listbox because it’s one of the few controls on an M2M form that isn’t constrained by the Edit State of the screen. In other words, you can click on it even if you’re just viewing orders rather than editing them. I’d like to find a way to create something that looks like a hyperlink, but have not yet been successful.
  14. The second function, which fires when the listbox is clicked, creates the URL from it’s basic parts.
    • ReportStr as stated before is the base of your URL including your server name and base folder.
    • Rpt is the URL portion with the name of your report.
    • Param is the name of your parameter for this specific report.
    • lcSomastSono contains the record value, in this case the current Sales Order Number.
  15. If you’ve coded it properly, when you open the SO screen in M2M, you should see the following:
  16. If you’ve modified the code properly to connect to your SSRS server, then the report should open up to the corresponding sales order. In my case the URL created is shown below. Notice that I’ve bolded the custom parameter portion.
  17. http://jeditemple/ReportServer/Pages/ReportViewer.aspx?%2fBlog+Projects%2fSales+Details+for+URL+Link&rs:Command=Render&SONO=000064

  18. There are additional parameter options shown in that tutorial which allow you to suppress the report toolbar, the parameter portion, etc.

Although the code may look cumbersome, once you work through it, it’s very easy to understand and replicate. The VBA Modules are available for download as well.

Any questions or suggestions for improvement?

Date vs Integer Datatypes as Primary Key for Date Dimensions

Every Kimball Group book I’ve read, as well as every Data Warehouse class I’ve attended, has indicated that a Date Dimension Primary Key should be a smart integer key in the format YYYYMMDD (20110518) so I’ve always built my Date tables that way. However, Barnaby (Blog/Twitter) pointed out that I should be using a Date Datatype key in SQL 2008 instead. His reasoning is sound in that the new Date datatype requires 3 bytes while an Int requires 4. This saves space and memory during processing and still facilitates table partitioning by year. The most obvious downside is that an unknown member value is required for Fact Table processing. However, I always use 19000101 as my unknown member, which can be entered as a date value anyway.

When I inquired on the internets about this prospect, I found conflicting opinions. One cited downside of using Date datatype was that the SQL Query Optimizer was more efficient when joining on Integers rather than dates or character types. However, I couldn’t find any test results involving the Date datatype to back that up so I thought I’d test it myself.

Let me preface the rest of this article with a disclaimer. I am not a query tuning expert, nor do I play one on TV. However, I ran the basics of these tests against the great Grant Fritchey (Blog/Twitter) and am grateful to him for the guidance.


I used my Date Table Script to generate two date tables, one with a smart integer key called DateTableInteger and another called DateTableDate with a Date datatype primary key. The primary key field for each is called DateID. Each table included an index on the [Date] column, which is a DateTime type in DateTableInteger and a Date datatype in DateTableDate. Each table is identical in every other way.

I created two source tables utilizing code written by Michael Valentine Jones from SQLTeam using his method of generating random numbers which can be used to create a column of random dates. For this test I created two tables with a single field of two million records each with dates ranging from 1/1/2000 to 12/31/2020. The table with the Date datatype is called DateSource and the other with smart integer keys is called IntSource. Each source table has exactly the same list of dates as well for test conformity.

Tested Queries and Methodology

I used three simple queries for my tests. Each represents a common scenario where the user needs to select records from a specific year.

set statistics io on
set statistics time on
Select DT.Date From IntSource ISO -- Integer Type Source
join DateTableInteger DT on DT.DateId = ISO.IntColumn
Where DT.Date >= '1/1/2010' and DT.Date < '1/1/2011'
Select DT.Date From dbo.DateSource DS -- Date Type Source
join DateTableDate DT on DT.DateId = DS.DateColumn
Where DT.Date >= '1/1/2010' and DT.Date < '1/1/2011'
Select DS.DateColumn From dbo.DateSource DS -- Date Type bypassing join. 
Where DS.DateColumn >= '1/1/2010' and DS.DateColumn < '1/1/2011'

The first query is an integer join, the second utilizes a Date datatype join, and the third is Date datatype but doesn’t require a join. I realize that the two field values in the second query, DateID and Date, are identical but I used it for consistency and it represents a scenario where the join is used to pull additional information such as Calendar Month. In my experience many queries against a Data Warehouse don’t require additional information about a date anyway, and I suspected that the greatest performance benefit would be found when the extraneous join was removed. In my testing, the Integer Join represents my control, the base to which I compare other values. Each query returns the same 100,086 records.

I performed two rounds of tests. The first used non-indexed source values and in the second I added a clustered index to each of the source tables, as Fact Tables often use a date field for their clustered indexes.

Prior to testing each query set, I called dbcc freeproccache and dbcc dropcleanbuffers. I then ran the query set once to eliminate compile time which I was not interested in. Each query was executed 10 times and I averaged the CPU and elapsed time to eliminate other factors. Note that this is a production level server, Dell R510 with Raid 10 arrays of 15K rpm drives, that has not yet been put in production so nothing should be running while I tested. The server has a setting of MAXDOP 1.


All recorded times are in milliseconds (ms).


Obviously using a Date datatype instead of the smart integer will save space on disk and in memory, however the savings are not dramatic. For example, the space difference is less than 2MB with two million records.

Also, in my opinion, the performance differences between the Integer and Date datatype joins isn’t statistically relevant. I consider performance deltas of less than 5% to be a wash because my testing methodology isn’t exhaustive. The clustered index date join outperformed the integer join by 24% but trailed it by 5% when not indexed. However, I find it interesting that in both sets of tests the number of logical reads is less for the Date datatype join than the integer join.

As I expected, the Date datatype key method easily outperforms the other two when a join is not necessary. The 52% performance boost without indexes and 48% for clustered indexes is considerable and were reflected in a second test run I performed to verify.

Based on these results, I am going to switch to using the Date datatype in my data warehouse projects using SQL Server 2008. Next week, I’ll post an updated script to reflect this.

Come See Me Present Wednesday at the Fort Worth SSUG.

This Wednesday I’ll be presenting at the Fort Worth SQL Server User Group. Come see me present:

Data Warehousing – How to Convince “The Bobs”

I got a meeting with the Bobs in a couple of minutes...

Building your first Data Warehouse is a long, and often difficult process. How can you get your boss to approve a Data Warehouse project? What’s the best way to explain dimensional modeling and the benefits of a Data Warehouse to a business person? What are the best/most cost effective ways of learning it? What kind of materials, hardware, software, etc do you need? What’s the best way to build a proof of concept that will impress your boss, as rapidly as possible?


Come to this presentation, and I’ll answer all of these questions plus the most important question of all.

“Is this good for the company?” Absolutely.

This presentation went over very well at SQL Saturday Chicago, so come laugh and learn with me.

Using a Date Table to Track Custom Fiscal Years/Periods

Calendar Tables are only useful if they reflect how your company evaluates its data. Therefore, any company using non-standard financial fiscal years and periods will require customization with their Calendar or Date Table. In this post, I’ll provide an example of how to do so using the M2M ERP System Database. For those who don’t use M2M, the notes should serve as an example of how to do so with other systems.

The most effective way to determine the fiscal setup of a company is to determine how its major systems track the data. In M2M the source is the GL Rules and Periods (GLRule) table. There are four primary fields of interest, which I’ve aliased, and they are listed in the following query and screenshot.

SELECT GLR.fcname AcctYearName
      ,GLR.fnnumber PeriodNo
      ,GLR.fdstart FirstDayOfPeriod
      ,GLR.fdend LastDayOfPeriod
  FROM dbo.glrule GLR

As you can see, there is one record for each fiscal period in GLRule. FCName is a Char(20) field and typically contains values like “FY 2005” or “Fiscal Year 2005”. FNNumber contains the period number and is an Int datatype. FDStart and FDEnd are Datetime fields and represent the first and last days of each period.

What follows are the update statements which I use to customize the fiscal periods of my calendar table. For your convenience, you can access the entire script here****. Copy and paste the entire query, replace the database and table name, and run it. I’ve dissected each step of the script below so those not using M2M can understand my logic and mimic those steps with their own systems.

Six fields are updated by the first statement. Notice that I’ve joined the Calendar Table to GLRule with a between statement. I’m using the pattern matching of Patindex to find the four digit number for Fiscal Year. However, if the user entered something like “Two Thousand Five” as a Fiscal Year, this method will not work. Start and end of period fields are taken directly out of GLRule as well. FiscalDayOfPeriod is calculated in this step as well for good measure.

Use M2MDataXX
   SET TST.[FiscalYearName] =
        substring (GLR.fcName, patindex ('%[1,2][9,0,1,2][0-9][0-9]%', GLR.fcName), 4)
      ,TST.[FiscalYearPeriod] =
        (substring (GLR.fcName, patindex ('%[1,2][9,0,1,2][0-9][0-9]%', GLR.fcName), 4))
        + + (GLR.fnnumber / 100.00)
      ,TST.[FiscalPeriod] = GLR.fnnumber
      ,TST.[FiscalStartOfPeriodDate] = GLR.fdstart
      ,TST.[FiscalEndOfPeriodDate] = GLR.fdend
      ,TST.[FiscalDayOfPeriod] = (DATEDIFF (dd, GLR.fdstart, TST.[DATE]) + 1)
  FROM  DateTestStnd.dbo.DimDate TST
        glrule GLR
       ON TST.DATE BETWEEN GLR.fdstart AND GLR.fdend

In this step, I’ve calculated the beginning and end of Fiscal Years. I group the record set in GLRule by Fiscal Year and then use min/max to determine the first and last day in each group.

   SET TST.[FiscalStartOfYearDate] = M2MYEARS.[FiscalStartOfYearDate]
      ,TST.[FiscalEndOfYearDate] = M2MYEARS.[FiscalEndOfYearDate]
  FROM  DateDatabase.dbo.DimDate TST
        (SELECT substring (GLR.fcName, patindex ('%[1,2][9,0,1,2][0-9][0-9]%', GLR.fcName), 4)
               ,min (GLR.fdstart) [FiscalStartOfYearDate]
               ,max (GLR.fdend) [FiscalEndOfYearDate]
           FROM glrule GLR
         GROUP BY GLR.fcname) M2MYEARS
       ON TST.FiscalYearName = M2MYEARS.FiscalYearName

In the next step, I calculate the FiscalDayofYear and FiscalYearDayOfYear field. While I could have included this code in the previous step, it’s much easier to read and understand as a separate step. The FiscalDayOfYear calculation is simple and is simply the number of days between the current record and the first day Fiscal Year calculated previously. The FiscalYearDayOfYear is the same calculation, but I convert the integer (day) to a decimal and add it to the Fiscal Year integer. This makes drill down and grouping easier.

   SET TST.[FiscalDayOfYear] = (DATEDIFF (dd, [FiscalStartOfYearDate], TST.[DATE]) + 1)
      ,TST.[FiscalYearDayOfYear] =
        (datepart (year, TST.[DATE])
         + cast (
            (datediff (dd, dateadd (yy, datediff (yy, 0, TST.[DATE]), 0), TST.[DATE]) + 1)
            / 1000.00 AS NUMERIC (3, 3)))
   FROM  DateDatabase.dbo.DimDate TST

The next section looks complex because I have to determine whether the number of Fiscal Periods can be broken into Quarters and Semesters. If a company used 10 Fiscal Periods per year, the concept of Quarters doesn’t make sense, but Semesters do. To make the code easier to understand, I’ve numbered the sections.

Section 1 checks whether the number of periods per fiscal year can be divided into quarters and semesters and the number of periods in each. Section 2 includes the period start and end dates so that the values of quarter and semester can be related back to the date table. Values for quarter or semester will be set to 0 if they aren’t valid in a particular M2M install.

   SET [FiscalQuarter] =
        CASE M2MYEARS.Quarterific
         WHEN 'Yes' THEN (ceiling (CAST (M2MYEARS.PeriodNo AS DECIMAL (4, 2)) / M2MYEARS.QuarterSize))
         ELSE 0
      ,[FiscalSemester] =
        CASE M2MYEARS.Semesterific
         WHEN 'Yes' THEN (ceiling (CAST (M2MYEARS.PeriodNo AS DECIMAL (4, 2)) / M2MYEARS.SemesterSize))
         ELSE 0
  FROM  DateTestStnd.dbo.DimDate TST
 -------------- 2. Include Start and End of Period Values. 
        (SELECT fcname FYName
               ,fdstart PeriodStart
               ,fdend PeriodEnd
               ,fnnumber PeriodNo
           FROM  dbo.glrule GLR
                INNER JOIN
  -------------- 1. Calculate whether Quarters and Semesters are Valid (and their values)
                 (SELECT fcname AGGFYName
                        ,MAX (fnnumber) PeriodCount
                        , (Max (fnnumber) / 4) QuarterSize
                        ,CASE WHEN Max (fnnumber) % 4 = 0 THEN 'Yes' ELSE 'No' END AS Quarterific
                        , (Max (fnnumber) / 2) SemesterSize
                        ,CASE WHEN Max (fnnumber) % 2 = 0 THEN 'Yes' ELSE 'No' END AS Semesterific
                    FROM dbo.glrule
                  GROUP BY fcname) GLRAGG
 -------------- End of 1
       ON TST.DATE >= M2MYEARS.PeriodStart AND TST.DATE <= M2MYEARS.PeriodEnd
  -------------- End of 2

Workday calculation is also somewhat difficult to understand. In M2M this information is kept in the Production Calendar (SCCALN) table. The fields we care about are:

  • fcYrMon – Year and month in the format of YYYY/MM (2011/02). Unfortunately these are not per Fiscal Year or Period, they are by Calendar Year and Month.
  • fcShifts – A Varchar (31) field with the number of Shifts per day.

Needless to say, we need an easier way than parsing through this table every time we need to calculate work days. The join is a little strange because the Date Table CalendarYear and CalendarMonth fields are SmallInt and need to be cast as Varchar. I’ve used a left join because many M2M companies don’t use the Production Calendar so SCCALN may lack records for fiscal periods.

Basically the code checks for a corresponding record in SCCALN. If one does not exist, then the typical Monday through Friday work week is used by default. If it does exist the fcShifts field is parsed, and if there are one or more shifts for a particular day, then the date is designated a work day.

   SET WorkDay =
         WHEN wc.fcShifts IS NULL THEN
           CASE WHEN TST.CalendarDayOfWeek IN (1, 7) THEN 'No Work' ELSE 'Work Day' END
            WHEN substring (WC.fcShifts, TST.CalendarDayOfMonth, 1) = '0' THEN 'No Work'
            ELSE 'Work Day'
      ,IsWorkDay =
         WHEN WC.fcShifts IS NULL THEN 
           CASE WHEN TST.CalendarDayOfWeek IN (1, 7) THEN 0 ELSE 1 END
           CASE WHEN substring (WC.fcShifts, TST.CalendarDayOfMonth, 1) = '0' THEN 0 ELSE 1 
  FROM  DateDatabase.dbo.DimDate TST
       LEFT JOIN
        dbo.ScCaln WC
       ON WC.fcYrMon = cast (TST.CalendarYear AS Varchar (4))
           + '/'
           + right ('0' + cast (TST.CalendarMonth AS Varchar (2)), 2)

The final bit of code updates the FiscalWeekName and FiscalYearWeekName. This code is tailored to my current employer, and I consider it optional because it may not pertain to you. The logic is as follows:

  1. The first fiscal week starts on the first day of the fiscal period.
  2. Fiscal weeks end on Friday and begin on Saturday.
  3. A “partial week” where the week starts on a day other than Saturday is added to the next full week. Partial weeks ending a period are considered their own week.
  4. Week names follow the pattern of [FiscalPeriod].[WeekNumber] both of which are integers. For example, the second week of the fifth period would be designated as “5.2”.

Again, this is a specific requirement and it may not apply to most companies.

   SET FiscalWeekName =
        + CASE ( (SELECT count (*)
                    FROM DateDatabase.dbo.DimDate TMP
                   WHERE TMP.CalendarDayOfWeek = 7
                         AND TMP.Date BETWEEN TST.FiscalStartOfPeriodDate AND TST.Date)
                / 10.0)
           WHEN 0 THEN
            ( (SELECT count (*)
                 FROM DateDatabase.dbo.DimDate TMP
                WHERE TMP.CalendarDayOfWeek = 7
                      AND TMP.Date BETWEEN TST.FiscalStartOfPeriodDate AND TST.Date)
             / 10.0)
  FROM DateDatabase.dbo.DimDate TST
-- Updates FiscalYearWeekName from FiscalWeekName
   SET TST.FiscalYearWeekName =
          cast (TST.FiscalYearName AS CHAR (4))
        + '.'
        + (CASE len (TST.FiscalWeekName) WHEN 3 THEN '0' ELSE '' END)
        + cast (TST.FiscalWeekName AS CHAR (4))
  FROM DateDatabase.dbo.DimDate TST

In most systems, new fiscal years and periods are added once a year. The update script will need to be run after that happens. With Made2Manage, and presumably other systems, triggers could be used to detect those changes and then run the update script. In my own situation, I’ve chosen to save this update script as a scheduled job which runs weekly. This may seem wasteful, but the update script completes very quickly.

Technically, I could have integrated this script with the one I posted previously which would have been faster and more efficient. However, I use two scripts for several reasons. First, this method is more flexible as I can create any Fiscal Update script I want without editing the main calendar script. Second, the Fiscal Update script will need to be run periodically to get updates and dropping/recreating the entire date table would be wasteful. Finally, most systems do not track Fiscal Years and Periods a decade or more into the future. Therefore, the first script is required to set default values for the Fiscal fields and only those that exist in the source system are updated. The user will not receive an error on a report because a future fiscal date value was not supplied.

Well, there you have it. The last two articles have described how I create date tables and customize the Fiscal fields from a source system, in this case Made2Manage.

Any questions or suggestions to make the process better?

Calendar Tables - Why You Need One.

*** Note: The code below is for use with SQL 2005 and previous versions. I have written new code to take advantage of the Date Datatype in SQL 2008 and above which can be found here.

What is a Date (Calendar) Table?

For the purposes of this article a Date Table is a dedicated table containing a single record for each day in a defined range. They include fields with descriptive attributes for each day such as Year, Month, Week, and whether a particular day is a work day or not. Date Tables (Dimensions) are an integral part of every Data Warehouse, but they can be used with a regular (OLTP) database as well.

Why do I need a Date Table?

There are several situations in which a date table is extremely helpful. Let’s consider just a few. Suppose you need to….

  • Generate a running sales total per day and include days in which you have no sales such as weekends.
  • Calculate the number of workdays between two dates.
  • Calculate projected ship dates for products based upon lead times.
  • Aggregate data by non-standard Fiscal Years or Periods. Perhaps your company uses the Fiscal Year of the US Government which runs from October 1st until September 30th. My current employer uses the 4-4-5 Calendar methodology, which is what originally led me to create this Date Table script.
  • Need to track data by seasons, whatever that means to your company. For example, if you manufactured bicycles you’d certainly want to compare sales figures in the spring versus fall.
  • Need to compare data using rolling months/periods. Suppose your company wants to know how March sales for this year compared to March for the past 5 years. Or you want to compare sales which occurred on Fridays to each other to look for trends.
  • Need to create Gantt Charts.

While many of these issues can be handled by writing T-SQL code, they’re much easier with a dedicated date table. Also, the Date Table can be customized to track anything about a particular day you want.

What about Space Considerations?

One concern that people have when creating a Date Table is the “wasted” space since each day is its own record. The space used is negligible, especially when compared to the benefits of having a dedicated date table. For example, when I execute my script for the years 2000 through 20035 the space used is less than 250MB.

How do I create a Date Table?

Kimball Date Dimensions
In the The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2and the previous 2005 version, the Kimball Group recommends that you create a Date Dimension using Excel. In the latest book, they provide a Date Dimension Spreadsheet which is available for Download and utilizes VBA to generate the table. They recommend you create a SSIS package to load that data into SQL Server.

While this will work, and some non-technical people might prefer this method, I prefer to use SQL Script, because I may not always have access to SSIS.

Using a SQL Script
When I searched for a Date Table Script a few years ago, I couldn’t find something suitable for my needs. Most of them include only one set of fields (regular Calendar) and I to report via custom fiscal calendars.

I created this script from a function called F_TABLE_DATE by Michael Valentine Jones from SQLTeam and have since gotten his permission to distribute it. I’ve modified the script so that it generates a Kimball style date dimension and it’s very comprehensive, yet simple to use and customize.

  1. Create a database called DateDatabase or whatever you’d like to call it. We’ll install F_TABLE_DATE in it and presumably the Date Table as well.
  2. Open the following script and replace “DateDatabase” (Find and Replace) with whatever database name you chose in step 1.
  3. Run the Script to create the function.

Your database should now contain the function used to create Date Tables. As a test, run the following script:

SELECT * FROM dbo.F_TABLE_DATE ('20000101', '20101231')

You should receive 4019 Rows, 11 years worth of days plus a record for unknown dates. Notice the extensive list of fields built into the function. All of them are documented in the script.

Use a Select Into statement to create the Date Table like so. Replace dbo.DimDate with whatever you’d like to call your Date Table.

     (SELECT 1
        DROP TABLE dbo.DimDate;
  dbo.DimDate -- Choose whatever name you like for your table.
  dbo.F_TABLE_DATE('20000101','20251231') -- Edit the parameters for a custom date range.
  Order By 1

Don’t panic, you don’t need to use all of the fields which is why this script is so highly customizable. Any fields you don’t want can be commented out before running the Select Into statement, which allows you to create your own custom Date Table.

The final step is to add a Primary Key to the DateID and an index for the Date and NextDayDate fields. As I can’t predict the usage patterns for your date table, you’ll have to implement further indexing if necessary.

ALTER TABLE [dbo].[DimDate]
ON DateDatabase.dbo.DimDate 

How do I use the Date Table?

Using the date table is simple, you simply link to it from the query from your source system. Use the following template.

    MyTable MY
    DateDatabase.dbo.DimDate DTE
  ON >= AND < DTE.NextDayDate

Both DTE.Date and DTE.NextDayDate represent midnight of the two consecutive days. Therefore the time portion of a Datetime field can be ignored.

What about the fiscal fields?

After running these scripts, and examining the Date Table, you’ll notice that the fiscal fields mostly mirror the calendar fields. This is by design. In the next article I’ll explain how to customize the fiscal fields and a script to extract the fiscal information out of Made2Manage. Even if you don’t use M2M, you can still use the fiscal script as an example of how to do it.

Any questions?

Page 8 of 33« First...678910...2030...Last »