My fourth data warehousing tip is to trust your instinct when it comes to building solutions, and if to you the solution seems too complex then maybe it is.
Sometimes things are just complex. It’s the way that it is. However, sometimes things are more complex than they need to be.
Now when it comes to data warehousing this can be in the implementation of the solution or in the consumption of the data. Sometimes it can be both. Complexities in implementation are always preferred complexities in data consumption. Given a choice, you will want your ETL to hold complexity and performance issues rather than the data warehouse or data mart schema. It’s just because you want complexity and potential inefficiency hidden away in the back end. Not in the end user’s face.
Too complex to query
I’ve been in the situation before where I’ve built a fact table and I’ve loaded it, and it looks okay. And then I go to write one of the reports that will feed off of it, and I find that I’m really struggling to transform the data into a data set or data sets that I need for the report. Sometimes its hours of thinking and sometimes it’s a huge SQL statement or some really complex SQL just to get meaning out of the data.
In these scenarios you really need to take a step back and have a think. If you’ve built the system and are struggling to use the data then how is your end user going to get on? In the example above I completely redesigned the fact table to better meet the needs of the end user. Don’t be afraid of going back to the drawing board if you know it’s the right thing to do.
You don’t want to publish your new fact table knowing that the end user is going to struggle to get data out of it. Even if you have a situation where you’re writing all of the reports, where you are the developer and the consumer. Do you want to give yourself that headache? Telling yourself that you should have redesigned the fact table when you had the chance to? And eventually you could be handing over the reporting or the entire solution to someone else. Wouldn’t it be great if you handed over something intuitive rather than having to spend a couple of weeks talking someone through the limitations of the existing solution and all of the workarounds that you have to perform just to get meaning out of the data?
There will always be deadlines to meet so there will always be challenges, and there is no such thing as the perfect solution, but if it isn’t right, do something about it.
Too complex to load
If part of the ETL/ELT seems too complex or unnecessarily complex, then think about the following areas.
Wrong tool for the job
It could be that you are using the wrong tool for the job. For example, you could be calling a stored procedure when a C# script component would be better. One example of this is where you want to cleanse your full name field ensuring that all names are in title case (so chris jenkins becomes Chris Jenkins). Yes, you can do this with TSQL by setting your full name to lower case, and then finding the first letter of each word (by looking for a space as a separator) and then setting that letter to upper case. It’s really quite a challenge though, and really inefficient. If you are using SSIS for your transforms you can just create a script component in your data flow and do this:
#region Namespaces
...
// Added
using System.Globalization;
#endregion
...
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.FullName = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(Row.FullName);
}
TSQL is great at working with batches. It’s not great working on a record by record basis. We already know that. All languages have their strengths and weaknesses, so where possible try to use the right tool for the job. If you’re writing a really clunky and inefficient stored procedure or function to meet a requirement, or if SSIS is having to work really hard at something that seems straightforward then maybe have a look at what other tools could be more appropriate.
Nobody else is doing it…
It could be that you are googling for answers and can’t find much on the subject. That could mean that there aren’t too many people doing what you’re trying to do. This could be legitimate if you’re at the cutting edge of the technology, but it might also suggest that the way you’re doing it isn’t the best way, or the right way, and that there may be other ways of doing what you are trying to do.
It could be that there are answers on google but they seem way too complex. You need to ask yourself here if the solution is too complex, or maybe they are talking about using a technology that you have less or no experience in. This is another area to trust your judgement. If it seems too complex this is one thing. If it seems like a decent solution but you just don’t quite understand it then that’s another. You can always try to work your way through some examples if it is the latter, or ask for some help, all the while building up your knowledge in a new area. That’s all part of the process after all.
Some approaches to problem solving
If you are struggling with a technical challenge, or any challenge come to mind, then you can try the following. They are pretty standard techniques for dealing with challenges, but they don’t always come to mind, especially if you are bogged down with a problem.
Write it down
It’s good to write down any challenges you’re having. They don’t need to be articulate notes, but you can try and work it through on paper. Diagrams help too. Sometimes googling can get a bit overwhelming, especially if you’re going from one technical description to the next. Just getting away from the screen and the immediate problem at hand can help. Don’t underestimate the power of your brain to come up with a solution. It may just need a change of approach to find the answer.
Take some time
I’ve had situations where I’ve been trying to work something out for days or even weeks. Taking some time and taking your focus away from it can help too. I’ve come up with some of my clearest answers to questions while in the shower not even thinking about the problem at hand. Don’t underestimate your ability to come up with a solution, and don’t think that you can only achieve this while sitting in front of the screen or whiteboard.
Talk it through
If you’re struggling with the solution to your problem, then it is really good to talk to someone. They don’t have to be an expert in the field. Maybe they’re a .Net developer. They might be the office manager. They might be a friend. Just vocalising what you are struggling with really can help. You don’t need an elevator pitch to discuss the issue you’re having. Just talk about it. They may have a different perspective on the problem, or they may just say one thing that completely changes your approach. They might not need to say anything at all. It might just be the act of talking it through that formulates the solution or a plan of action in your head.
Ask for help
You can always ask for help, and it doesn’t have to be a last resort. You are never going to know everything and asking for help and getting advice is a really good way of building your knowledge. Maybe you don’t have the resources to hand in your place of work, but there are obviously a plethora of forums and groups online that you can post questions to as well. You could well have favourite authors on the subject. Why not try and contact them? A lot of them have blogs and publish their email addresses, so why not give it a go.
Feel free to ask me if you like. I don’t claim to know everything but if you’re struggling with anything regarding data warehousing, SSIS, SSRS, and SSAS then feel free to email me at chris@bidesign.uk.