Data Model and Source Data Map Template

Documenting Your Data Warehouse

If you are like me, you find documentation to be the bane of your existence.  However, you also find it super helpful when six months down the road one of your end users asks you how a certain measure is calculated.  Without documentation, we have to go through the code (whether it be T-SQL or SSIS packages) to see how the ETL job is performing the calculation.  Documentation allows us to provide an answer faster and with more confidence than just by reviewing the code.

Continue reading “Documenting Your Data Warehouse”

Data Model and Source Data Map Template

Five Ways to Give Back to SQL Community Without Being an Expert

If you have participated in the SQL Server community, you probably have found that it is very helpful and very friendly.  In fact, it might even have inspired you to give back to the community.  The only problem is that you may not feel like an expert, and may think you have to be at the Brent Ozar, Jes Borland, or Pinal Dave level in order to give back to the community.  If that is the case for you, then read on to discover 5 ways to give back to the SQL Server community without being a SQL Server expert yourself.

Continue reading “Five Ways to Give Back to SQL Community Without Being an Expert”

Data Model and Source Data Map Template

Resolving Duplicate Record Issues During Upsert

Would you look at this?  I am actually writing a technical post!  Today’s post is going to cover how I troubleshoot an issue that occurs when you have more than one row in staging that matches up to a row in the data warehouse.  We’ll go over the error, the code that causes it, and how I fix it.  Join us after the break to see more!

Continue reading “Resolving Duplicate Record Issues During Upsert”

Data Model and Source Data Map Template

WausauPASS After Action Report – August 2016

Last night (8/17) was the first meeting of the WausaPASS SQL Server user group. We had 15 people turn out for the event, including chapter representatives from FoxPASS and MadPASS. The sponsor for the meeting was Redgate (B | T) and we ended up with a double prize night thanks to PASS providing a jump drive of the PASS Summit 2015 sessions.  Overall the session ran very smoothly with only a few minor technical glitches that were a result of me not reading directions more than anything else.  We even successfully broadcasted the meeting via Skype for Business and had a user from Canada join on.  So yeah, we are international now 🙂

Continue reading “WausauPASS After Action Report – August 2016”

Data Model and Source Data Map Template

Building an ETL Framework: Laying the Foundation

An ETL framework is one of the perfect tools in developing a data warehouse.  It helps you remain consistent as you build the system.  Development time is decreased because you are not re-writing the same code over and over again.  A good ETL framework can also include error trapping and performance monitoring in the system.  In this series of blog posts, I will build a basic ETL framework that will build the various databases and tables in our data warehouse, create the routines to extract, transform, and load data, and even include a data quality engine to enforce business rules on the data.

Continue reading “Building an ETL Framework: Laying the Foundation”

Data Model and Source Data Map Template

10 Skills Every BI Pro Should Have

Business intelligence has been listed near the top of the fastest growing and highest paid IT disciplines for several years now.  As a result, new practitioners are entering the field on a daily basis.  However, the field is so broad with so many tools and technologies it can be hard to know where to start learning.  This post outlines my top ten skills that I think every BI pro should have.

Continue reading “10 Skills Every BI Pro Should Have”

Data Model and Source Data Map Template

Joys and Frustrations of the Lone Data Pro

Being the only data professional in a company with over 600 employees can seem like a nightmare to some people.  But to others, it is an everlasting playground of joy.  I fall somewhere in the middle.  There are days where I absolutely love my job.  There are also days where I have to use every bit of willpower I have to get out of bed and go to work.  In this post, I will attempt to quantify my love/hate relationship with being the only data professional on staff at my organization.

Continue reading “Joys and Frustrations of the Lone Data Pro”

Data Model and Source Data Map Template

Reverse Engineering SQL Relationships

You have a vendor database that you need to develop a report against, or load the data into the data warehouse.  The problem is that you have no idea what tables relate to which tables because the vendor didn’t define any foreign key constraints.  Your mission: reverse engineer the database schema.  Now of course you could use 3rd party tools to explore the database, but let’s earn our whip and fedora and do it by hand.  Read on to see how I explore databases with nothing more than just SSMS and maybe SQL Profiler for those pesky databases.

Continue reading “Reverse Engineering SQL Relationships”

Data Model and Source Data Map Template

Finding Help in the SQL World

In my job, I am the only dedicated data professional.  We either have no DBA or myself as an accidental DBA, depending on your perspective.  When I need help, there is no one at my office that I can go to in order to get help.  This means that I spend lots of time trying to solve problems that others could solve in minutes.  Below I share my best resources for finding SQL help when I need it the most.

Continue reading “Finding Help in the SQL World”

Data Model and Source Data Map Template

Loading SharePoint List Data to the Data Warehouse

In this post, we are going to look at how I load SharePoint list data into the data warehouse.  We are going to cover how to determine which fields are dimensions and which are measures, as well as how we load that data into the data warehouse.

Continue reading “Loading SharePoint List Data to the Data Warehouse”