Monthly Archives: August, 2011

SQL Saturday #64 in Baton Rouge

If SQL Saturday was college football this August would be a really exciting month.  We would see LSU taking on Miami in the first part of the month and Oklahoma vs. Nebraska on August 27.  What a doubleheader!  Well, even without the football, it’s still a great month, you’ll just have to go check it out for yourself.

I’m looking forward to traveling to Louisiana today to attend and speak at SQL Saturday #64 in Baton Rouge this weekend on the LSU campus.  This is probably my 10th SQL Saturday and I’ve had a really good experience at each one.  If you’re anywhere near the Louisiana area this weekend I urge you to attend, you won’t regret it, and it’s FREE.  There are many things that have made SQL Saturdays successful, but in my opinion one of the most important is that you don’t have to be a DBA to benefit from it.  In fact, you don’t have to know much about databases at all, and you don’t actually have to be working directly in technology.  While SQL Saturday focuses on the SQL Server product line, there are sessions presented on career advancement, the Cloud, Windows Phone development, Data Visualizations, Business Intelligence projects, and much more.  There is something for any one who wants to learn about technology and who wants to network with leaders in the industry.

If you do attend, please find me and say hello.  I’ll be presenting Business Intelligence for Managers/Decision Makers for people who want to start or fix a BI project and a beginner version of Tune a Star Schema for High Performance Reporting.

Good Business Intelligence depends on a good Data Warehouse

Good BI, Good DW

There are certain components that are essential for a successful Business Intelligence implementation and in this post I want to emphasize the importance of the data warehouse.  It’s obviously a foundational component and doing it right requires choosing the right DW model and implementing a quality design.

My Baseball Woes

I play on a baseball team that hasn’t been very successful this season.  Last week we were winning early in the game and had a very good chance, but starting in the 4th inning our pitchers lost control and walked batter after batter, totaling 12 walks in a span of three innings.  We could live with some of our other failings, but one thing we can’t live with and be successful is to walk the opposing team’s batters.  The team’s fortunes are sometimes documented by our stalwart third baseman and well-known database author Steve Jones.

The BI ‘Team’ of Technical Components

A Business Intelligence project is also a team effort and while it’s important to put together the human part of the team properly, the numerous technical components that make up BI must work as a team also, and this is what I want to focus on in this post.  While all of the components are important, we can live with weakness in some areas, just like my baseball team.  There are some failings we can’t succeed with, and one of these is the data warehouse.  I’ve never seen a successful BI implementation without a really good data warehouse.

The success of a Business Intelligence project often depends on starting with a clear vision as to what you need in a data warehouse.  The DW is rightly known as the foundation of BI but it can still be a vague concept to digest.  Do you create one big dimensional model or a lot of small data marts?  Should you implement a data vault model or a corporate information factory?  There’s no one size fits all answer and each implementation should be evaluated individually.

It’s important that the direction you choose be based on the BI requirements, which hopefully you have completed in a comprehensive manner.  Make sure it’s an autonomous data model, meaning that it should be independent and live on its own, and not simply be a mirror of source application databases.

How to Choose the DW Model?

Take a look at these links to get an idea of some of the different types of data warehouse models that are generally accepted as good practice.  Choosing whether to go with a Dimensional Model, Corporate Information FactoryData Vault, OLAP Cubes, or some combination or variation of these methodologies, is a good place to start.  There are various schools of thought on how to use and implement these models and their strengths and weaknesses, so get comfortable with them before moving forward.

After you learn about each models strengths and weaknesses, here’s a list of questions to ask yourself when deciding what direction to take.

What are your primary needs?  This is a short list to start with to determine where to head next:

  • Keep a record of every transaction.
  • Centralized Reporting.
  • Distribute data for analytics to specialized teams.
  • Operational reporting.
  • Create cubes for ad-hoc data analysis.
  • A full Audit Trail of data modifications.
  • Ad-hoc SQL queries by power users.

Sample Models

There are as many solutions as there are implementations.  These are a few different scenarios that I have been a part of over the last few years in my consulting work:

Company A doesn’t have large data and needs quick reporting and analysis.  They were able to build a few data marts using a dimensional model.  Some data staging was necessary but in many cases the DW can be populated directly from its source(s).

Company B is a manufacturer that requires a record of every process, tool and measurement in their factories, while the engineers want to rapidly analyze the data to improve the quality of the product they’re building.  To record the data this company implemented a Data Vault warehouse model and from there populate a Dimensional Model with a number of targeted data marts.  The data marts are used for reports but are also the source for a number of engineering and manufacturing OLAP cubes that allow engineers to perform quick data analysis in Excel.

Company C wants to create a set of consistent reports that can be deployed to anyone within the company that accurately reflect what the company is doing.  To produce the reports correctly, the data is gathered from multiple databases within the company and stored in a staging area.  A dimensional model is then populated from which all reports are produced, using a single controlled data source for all reports, providing the consistency the organization requires.

Informed Choices, Successful BI

Make an informed choice you can grow with and don’t make the data warehouse the sink hole of an otherwise solid Business Intelligence implementation.  Throw more strikes than balls and you’ll have a data warehouse that will let your team develop great BI; don’t be the pitcher who loses by walking the opposing batters.