|
|
Like many other technical bloggers, I receive requests for help on a regular basis. Since I’m constantly learning and evolving I approach others for help as well. A couple of years ago, I received an email from a new reader which began with:
Always two there are, no more, no less: a master and an apprentice.
The line is from Star Wars, of course. Being a huge geek, it’s easy to understand how his email got my attention right away. His approach was polite, respectful, and most importantly demonstrated that he had in fact read some of my blog. That got me thinking, what are some tips I could give my readers on how to ask for help? Of course, I couldn’t just produce a list. In my geekitude, I have prepared a list of movie scenes to explain my suggestions.
Show Respect
What have I ever done to make you treat me so disrespectfully? – Godfather
Be respectful in your request and if at all possible, offer your friendship before you need help. Attend user group meetings and get to know people. Volunteer. If it’s a forum, do your best to participate and help others in the forum. As in the movie, you’re more likely to get help when you need it if you already belong to a group.
Be Nice (Politeness)
 Be Nice If somebody gets in your face and calls you a &^*%*$!!!, I want you to be nice. Ask him to walk. Be nice. If he won’t walk, walk him. But be nice. If you can’t walk him, one of the others will help you, and you’ll both be nice. I want you to remember that it’s a job. It’s nothing personal.- Dalton in Road House
Don’t go overboard but a complimentary remark never hurts when asking for help. If you’ve seen them present, read their blog, or answer questions in forums, complement them on their abilities. Just make sure that your compliments are sincere. I can’t tell you how many “requests” I’ve gotten for help which sounded more like demands for help. People don’t tend to respond well to demands.
Communicate Clearly
Do you understand the words that are coming out of my mouth?!?!? – Detective Carter in Rush Hour
Take some time and compose your question so it can be easily read and understood. If I have to waste cycles trying to decode your question, or need additional information, chances are that I won’t bother. In my experience, most people are like this. Depending on your problem, explain the steps you have taken to remedy your issue and the result of those attempts. Take time to use a code prettify-er before posting it and if possible the code to generate a sample data set people can use to help you. Make it easy for people to assist you. Now that I’ve just told you how complete your questions should be, remember that at the same time you should….
Be Brief
Let me ‘splain… No, there is too much. Let me sum up. Buttercup is marry’ Humperdinck in a little less than half an hour. So all we have to do is get in, break up the wedding, steal the princess, make our escape… after I kill Count Rugen – Inigo Montoya in The Princess Bride
The longer your question, the less likely people are to read it. Therefore, it’s in your best interest to be as brief as possible while still providing the background information to solve your problem. Try to leave rants or opinion out of it and just state the facts.
Work for Your Own Answers
 Wax On Wax Off
Before asking for help, do your due diligence. RTFM, Google it, and try to find the answer yourself. Nothing exasperates people more than being asked questions with simple answers that can be found in two minutes with a search engine. Make sure you indicate that you have already searched for the answer so others will take time to answer you. Sometimes the answer to a problem is as simple as knowing the right search terms.
Ask for Guidance, Not Solutions
It is like a finger pointing away to the moon. Don’t concentrate on the finger or you will miss all that heavenly glory. Bruce Lee
This goes along with RTFM. People are more likely to help you if you ask for general direction and not the complete solution to your problem. People do not want to do your homework, but they will direct you to information sources that will help you. A question framed like, “Can anyone recommend a resource where I can learn to…..” is likely to be answered.
Be Patient
We’re running out of time!!!! – Jack Bauer in 24
You often see this in forums. “Help!!!! My Server is down and I have no backups!!!!” Then five minutes later the person bumps their topic again because people haven’t responded fast enough. If you have a time sensitive emergency, then pay for immediate support. Don’t expect a forum to substitute for that kind of assistance. Your behavior won’t get your question answered any faster, and may in fact alienate people who would normally be willing to help you.
Don’t be Greedy
The point is ladies and gentlemen that greed, for lack of a better word, is good.- Gordon Gekko in Wall Street
Yes, I know that the movie quote says the opposite, but stay with me. Similar to my last point, if you continuously ask questions without reciprocating, you may find that the answers stop coming. People don’t want to feel used, so you should prioritize your issues/questions and use community resources sparingly.
Don’t Argue
 Pai Mei That, my dearest, depends entirely on you. Now, remember: no sarcasm, no backtalk. At least not for the first year or so. You’re gonna have to let him warm up to you. – Bill in Kill Bill, Volume 2 (speaking about Pai Mei, plucker of eyes.)
I’ve seen this more times than I can count and it’s often hilarious. Some newbie asks for help on a forum regarding how often he should be shrinking his database. Paul Randal (Blog/Twitter), who has forgotten more about SQL Server than most will ever learn, advises him that shrinking should be avoided. The newbie then proceeds to argue because he heard somewhere that database shrinking is akin to nirvana. Will Paul pluck your eye out like the infamous Pai Mei? Probably not, though I wouldn’t bet against him having those skills. However, if you argue with folks trying to help you, you’ll most likely get less assistance next time.
If Possible, Offer to Pay
 Give Me Paw!!! Listen! We’re not just doing this for the money! We’re doing this for a S*** LOAD of money! -Lone Starr in Spaceballs
I’m not suggesting that everyone is motivated to help others by money, however it doesn’t hurt. In fact, you’ll most often find that people will refuse money that is coming out of our personal pocket. However, it does indicate to them that you are serious about getting help and answers. Also, offering to buy someone lunch or a drink or whatever goes a long way. I’ve bought more than a few meals in my day as a way of thanking people who helped me.
Follow Up
“Just pay it forward.” – Thorsen in Pay It Forward
If you ask a question on a forum, and you find the answer yourself, make sure to go back and update the thread with the answer you found. This gesture doesn’t benefit you personally, but it will help others when they have a similar problem.
Also, if a answer or suggestion helped you, follow up with the person and let them know. This seems obvious, but you’d be surprised how often people neglect to do this. Let’s face it, it feels good to receive a thank you message, and you’re more likely to get help with your next question.
So, that’s my list. Do you folks have any other suggestions?
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.
http://www.youtube.com/watch?v=3hQC3nkftrk
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?
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.
- VFP reports are difficult to write and maintain.
- VFP lacks chart and graph capability.
- 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?
 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.
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.
IF EXISTS
(SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'DimDate')
BEGIN
DROP TABLE dbo.DimDate;
END
SELECT DTE.Date,
DTE.NextDayDate,
DTE.CalendarYear,
DTE.CalendarYearQuarter,
DTE.CalendarYearMonth,
DTE.CalendarYearDayOfYear,
DTE.CalendarQuarter,
DTE.CalendarMonth,
DTE.CalendarDayOfYear,
DTE.CalendarDayOfMonth,
DTE.CalendarDayOfWeek,
DTE.CalendarYearName,
DTE.CalendarYearQuarterName,
DTE.CalendarYearMonthName,
DTE.CalendarYearMonthNameLong,
DTE.CalendarQuarterName,
DTE.CalendarMonthName,
DTE.CalendarMonthNameLong,
DTE.WeekdayName,
DTE.WeekdayNameLong,
DTE.CalendarStartOfYearDate,
DTE.CalendarEndOfYearDate,
DTE.CalendarStartOfQuarterDate,
DTE.CalendarEndOfQuarterDate,
DTE.CalendarStartOfMonthDate,
DTE.CalendarEndOfMonthDate,
DTE.QuarterSeqNo,
DTE.MonthSeqNo,
DTE.FiscalYearName,
DTE.FiscalYearPeriod,
DTE.FiscalYearDayOfYear,
DTE.FiscalYearWeekName,
DTE.FiscalSemester,
DTE.FiscalQuarter,
DTE.FiscalPeriod,
DTE.FiscalDayOfYear,
DTE.FiscalDayOfPeriod,
DTE.FiscalWeekName,
DTE.FiscalStartOfYearDate,
DTE.FiscalEndOfYearDate,
DTE.FiscalStartOfPeriodDate,
DTE.FiscalEndOfPeriodDate,
DTE.ISODate,
DTE.ISOYearWeekNo,
DTE.ISOWeekNo,
DTE.ISODayOfWeek,
DTE.ISOYearWeekName,
DTE.ISOYearWeekDayOfWeekName,
DTE.DateFormatYYYYMMDD,
DTE.DateFormatYYYYMD,
DTE.DateFormatMMDDYEAR,
DTE.DateFormatMDYEAR,
DTE.DateFormatMMMDYYYY,
DTE.DateFormatMMMMMMMMMDYYYY,
DTE.DateFormatMMDDYY,
DTE.DateFormatMDYY,
DTE.WorkDay,
DTE.IsWorkDay
into dbo.DimDate
From dbo.F_TABLE_DATE ( '20000101','20351231' ) DTE
order by 1
Go
ALTER TABLE dbo.DimDate
ADD PRIMARY KEY CLUSTERED ([Date])
WITH FILLFACTOR = 100
GO
CREATE INDEX idx_Dates
ON dbo.DimDate
(NextDayDate)
GO |
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.
SELECT
MY.date
,DTE.CalendarYear
,DTE.CalendarMonthName
FROM
MyTable MY
INNER JOIN
DateDatabase.dbo.DimDate DTE
ON MY.date >= DTE.date AND
MY.date < DTE.NextDayDate |
Any questions or suggestions for improvement?
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.
 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!
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
- 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).
- 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:
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.
- 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.
- Customize your M2VEvents.prj file in VBA. There is a downloadable class on M2M’s website for VBA programming.
- 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.
- Right click on the Module folder and select Insert and Module.
- Click on the new module and change the name to “DefaultModule” in the properties box.
- 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.
Public Function GetReportString() As String
GetReportString = "http://jeditemple/ReportServer/Pages/ReportViewer.aspx?%2fBlog+Projects%2f"
End Function |
- 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.
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 |
- 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.
- 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.
- If you’ve coded it properly, when you open the SO screen in M2M, you should see the following:
- 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.
http://jeditemple/ReportServer/Pages/ReportViewer.aspx?%2fBlog+Projects%2fSales+Details+for+URL+Link&rs:Command=Render&SONO=000064
- 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?
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.
Setup
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.
Results
All recorded times are in milliseconds (ms).
Conclusions
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.
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.
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
GO
UPDATE TST
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
INNER JOIN
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.
UPDATE TST
SET TST.[FiscalStartOfYearDate] = M2MYEARS.[FiscalStartOfYearDate]
,TST.[FiscalEndOfYearDate] = M2MYEARS.[FiscalEndOfYearDate]
FROM DateDatabase.dbo.DimDate TST
INNER JOIN
(SELECT substring (GLR.fcName, patindex ('%[1,2][9,0,1,2][0-9][0-9]%', GLR.fcName), 4)
FiscalYearName
,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.
UPDATE TST
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.
UPDATE TST
SET [FiscalQuarter] =
CASE M2MYEARS.Quarterific
WHEN 'Yes' THEN (ceiling (CAST (M2MYEARS.PeriodNo AS DECIMAL (4, 2)) / M2MYEARS.QuarterSize))
ELSE 0
END
,[FiscalSemester] =
CASE M2MYEARS.Semesterific
WHEN 'Yes' THEN (ceiling (CAST (M2MYEARS.PeriodNo AS DECIMAL (4, 2)) / M2MYEARS.SemesterSize))
ELSE 0
END
FROM DateTestStnd.dbo.DimDate TST
INNER JOIN
-------------- 2. Include Start and End of Period Values.
(SELECT fcname FYName
,fdstart PeriodStart
,fdend PeriodEnd
,fnnumber PeriodNo
,GLRAGG.QuarterSize
,GLRAGG.Quarterific
,GLRAGG.SemesterSize
,GLRAGG.Semesterific
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
ON GLR.FCNAME = GLRAGG.AGGFYNAME) M2MYEARS
-------------- 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.
UPDATE TST
SET WorkDay =
CASE
WHEN wc.fcShifts IS NULL THEN
CASE WHEN TST.CalendarDayOfWeek IN (1, 7) THEN 'No Work' ELSE 'Work Day' END
ELSE
CASE
WHEN substring (WC.fcShifts, TST.CalendarDayOfMonth, 1) = '0' THEN 'No Work'
ELSE 'Work Day'
END
END
,IsWorkDay =
CASE
WHEN WC.fcShifts IS NULL THEN
CASE WHEN TST.CalendarDayOfWeek IN (1, 7) THEN 0 ELSE 1 END
ELSE
CASE WHEN substring (WC.fcShifts, TST.CalendarDayOfMonth, 1) = '0' THEN 0 ELSE 1
END
END
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:
- The first fiscal week starts on the first day of the fiscal period.
- Fiscal weeks end on Friday and begin on Saturday.
- 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.
- 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.
UPDATE TST
SET FiscalWeekName =
TST.FiscalPeriod
+ 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
.1
ELSE
( (SELECT count (*)
FROM DateDatabase.dbo.DimDate TMP
WHERE TMP.CalendarDayOfWeek = 7
AND TMP.Date BETWEEN TST.FiscalStartOfPeriodDate AND TST.Date)
/ 10.0)
END
FROM DateDatabase.dbo.DimDate TST
-- Updates FiscalYearWeekName from FiscalWeekName
UPDATE TST
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?
|
|
Popular Articles