Featured Article(s)
Is it Time to Terminate an Employee
A few years ago I had to make an incredibly hard decision on whether I let an employee go or if I tried to keep that person on my team and develop that persons skill set. Far too often people determine a DBAs skill set by only factoring in how much they know about SQL Server. But there are other factors to consider.
100% Success Rate with Data Conversions
That’s a record that’s hard to beat. Do you need expert conversion of complex EBCDIC (mainframe) data into ready-to-import ASCII files? Greenview’s conversion tools and turn-key services convert nearly any EBCDIC file and absolutely any COBOL-based mainframe database into ASCII tables. 10 years experience with over 1000 different files – FDR, credit card processing, Defense Logistics Agency (DLA), Texas Gas/Oil, telephone, IRS, banking, healthcare, real estate, etc. Don’t take chances with your data conversions – check out Greenview Data.
Building Out a BI Infrastructure
Jeremy and many others have already written in with approaches to BI. Send in your thoughts here. Here’s how Jeremy approaches it:
First, my questions:
1. Are you archiving information to a secondary server for query/reporting applications?
2. If yes, when you archive, do you REMOVE it from the first database?
3. If yes, what is your retention time-frame? Or does it vary based on the type of information?
4. What have you done on hardware? Did you get a lesser machine for the BI stuff, or was it actually a beefier machine? (technical term: beefier)
…and his comments.
"I’ve done this in a couple of different shops.
First answers to your questions.
1.) Yes
2.) Yes
3.) (Will get to this below)
4.) Beefier (in a different way)
For question number three the answer depends upon the data and its retention requirements. I’d also like to point out that the traditional OLAP db retention is a question as is the data on the BI stack.
In my experience there is typically a need for 2 – 3 years available via the oltp system. However I do not keep the data on that server for that long due to performance implications. I move it over to the BI stack and leave it in it’s original form (schema).
Hard to explain in an e-mail but what I have is a set of partitions (pre 2005) for each object that’s not classified as a type 2 scd. For type 2 scd’s I run transactional replication. The partitions then get rolled into the BI stack or from the BI stack to the DW on the BI stack when certain time intervals hit. This changes based on the size of the object but for the most part I do it in quarterly increments. Once the data has been moved into it’s new home (partition) then the old one is dropped.
With 2005/2008 the scheme is the same as the only thing that changes is the how the partitioning is implemented.
I personally recommend this approach to everyone (regardless of data size) as the cost for deletion is unnecessary with the solutions offered out of the box today.
With that said I am in the process of creating a significant schema where there will never be a deletion statement run against the data. The preservation of state and audit trails are beginning to require that all action on all data be represented, regardless of it’s need and/or use.
Retention time frames range from a few years to many years. Most of them fall in the category of many years (10). Even after the 10 year time frame has elapsed there remains a need for the data to be present from an OLAP perspective. So while the data gets removed from the DW (star schema), it does not get removed from the cube itself.
This does indeed present a challenge when cube data has its own issues. For this reason I also fully partition the cubes into monthly increments so that in the event something really really bad happens that cannot be recovered from (fraud, intentional interference etc…) the best case scenario is losing a weeks worth of data. The worst case is more likely a legal matter that is out of my control anyhow.
But to sum up your question I think that its imperative for folks out there to understand what you are talking about and why its important. I’ve been into too many shops where there is basically no plan in place and that comes at the determent of other SQL server professionals, the company and RDBMS technology as a whole.
So many "DBA’s" that I’ve come across look at me like I’m crazy with this overboard concept but I will tell you that their bosses, bosses boss LOVES this kind of thinking into today’s world.
To sum it up, if you want to be a hero and a rock star with your compliance, legal and EVP level colleagues, adopt this type of thinking with your data. Adopt the "overkill" when it comes to data permissions, security and availability."
Reminder: I Need Your Help! (And I’m willing to wrap cash around it!)
Please – if you have a minute or two – we have a new concept we’d like to first explain to you, then get your feedback on. I need to know what you think of it, what you’d like to see, what type of experiences you’ve had, etc. I’ve created a short video explaining the idea, then we have a survey. Now, I know, I know. Surveys are not everyone’s favorite thing, but I really need to get your feedback and know what you think about this.
We’ve added some incentive too for the survey – by filling out the survey, you’re automagically entered in the drawing for a prize! The prizes are sure to be of interest, it’s cold hard cash. We’ll have one (1) $200 grand prize, one (1) $100 second prize and three (3) prizes at $50. So, you have *5* chances to win.
This all hinges on feedback we receive, so we’ll be running this for a few weeks here to get as much feedback as possible.
Click here to see the video and take the survey – and please do let others know about this. I’m not trying to generate traffic (no login or membership is required) – but I do need to hear from as many people as possible.
*Thanks* for your help! (If you have any trouble with the page or the survey, please email me and let me know).
Featured White Paper(s)
The Why, What and How of Migrating Lotus Notes Applications
Organizations looking for solutions to migrate their Lotus Notes applications and developers from groupware like Lotus Notes … (read more)
Meeting Sarbanes-Oxley Requirements with DB Audit
… (read more)
Crystal Reports Feature Comparison by Edition
This handy whitepaper lets you compare features of Crystal reports at a glance. Check out the various features and determine… (read more)
Top 10 SQL Server Questions Answered by SQL Stan
Work – and look – like a SQL Server rock star. "Top 10 Questions Answered by SQL Stan" will sharpen your skills with tables, … (read more)