Category Archives: data warehouse

Tenets of a Real-time Data Warehouse

I’ve written a couple of blog posts this week on Tabular Models, and for a good reason.  I’m presenting a session called Data Modeling Best Practices for Enterprise Tabular Models at the PASS Summit next week, so naturally it’s a topic that’s on my mind.  But I’m also presenting another session on how to develop a Real-time Data Warehouse, so I decided that I should write a post on that topic too, since it’s also been on my mind.

Real-time DW Tenets

Instead of discussing the technology implementation and associated difficulties of loading a data warehouse in real-time,  I thought I would start with the three tenets that I think everyone needs to consider before embarking on a project like this.  Everyone develops a real-time solution differently based on their needs, technology, hardware, and other factors, but there are a few basic ideas to keep in mind no matter how you go about it.

Process only the data you must and nothing more.  To meet the challenge of real-time, don’t do anything extra, and don’t touch any data you don’t have to touch.  You always want to handle as little data as possible while still getting the job done.

Don’t impact the Source Systems. Contention with the source databases will almost certainly cause the real-time process to fail.  Make sure  your real-time processes don’t use resources that are needed by the applications that generate the source data.  Try to be as invisible to them as possible.

Take advantage of what SQL Server does for you.  SQL Server is a rich product with a great number of features and tools that can help you with this endeavor, so take advantage of them.  In my session I discuss how Replication, CDC, SSIS and other tools can be used as part of the solution.  Don’t write code you don’t have to write.

PASS Summit 2012

To see me build a functioning real-time data warehouse in real-time, come to my presentation at the PASS Summit on Friday, November 9.  The session is at 9:45am and is title Real-Time Data Warehouse and Reporting Solutions

Advertisements

SQL Saturday #165 – Lincoln, NE

SQL Saturday descends upon Lincoln, Nebraska this weekend and it’s another good chance to get a day of free SQL Server training delivered by many of the top people in the business.  A total of 6 tracks at SQL Saturday #165 covering every part of SQL Server will be enough to get you up to speed on whatever topic you’re interested in.  It’s going to be held on the University of Nebraska campus and it’s one of the reasons I submitted to speak at this event.  I’ve really enjoyed the SQL Saturday events on campuses such as the ones I’ve attended at Baton Rouge on the LSU campus and Portalnd on the University of Portland campus.  The setting works really well for obvious reasons.

I’m presenting two sessions in Lincoln.  The first is titled Data Modeling Best Practice for Tabular Models and the second is Real-time Data Warehouse and Reporting Solutions.  What’s really cool is that both of these sessions are previews of the two sessions I’m doing at the PASS Summit next month in November.  If your interested in learning more about one of the great new features of SQL Server 2012, then come to my Tabular Models session.  It will show you how to effectively make use of existing data sources in Tabular Models for your Business Intelligence solutions.  The Real-time DW session will show you how to use the rich feature set of SQL Server to get a real-time data warehouse running successfully.

Data Modeling Best Practices for Tabular Models

The new Tabular models in SQL Server 2012 give us a powerful analytic engine that is faster and easier to implement than traditional multi-dimensional cubes using Analysis Services. In this session, you’ll see how to develop analytic models using your existing data sources and learn best practices for preparing data so that it can be effectively used as a Tabular database. In addition to creating the model, you’ll see how to quickly add measures, hierarchies, and calculated columns to provide a rich user experience and how the model works with Excel and Power View.

Real-time Data Warehouse and Reporting Solutions

In this demo-rich session, we will explore database and ETL architectures that let you smoothly move data from your application databases across the enterprise in real-time to your data warehouse. You will see how to use SQL Server features such as replication, Change Data Capture, SSIS, and other techniques to extract, transform, and load data fast and dependably. We will develop an actual sample system during the session and discuss and walk through alternative real-time architectures, including the use of Data Vault methodologies. We will then see how to use Reporting Services and Power View to present data with a minimum amount of latency.

SQL Saturday #125 – Oklahoma City

There’s another opportunity this weekend in Oklahoma City to get a day of free SQL Server training delivered by many of the top people in the business.  The 4 tracks at SQL Saturday 125 are jam-packed with so many good sessions that the only problem you might have is choosing which ones to attend.  I personally have that problem, since I was really hoping to see John Sterrett’s session Performance Tuning for Pirates, but he’s speaking at the same time I am.  He did a great job when he did that session for the Performance Virtual Chapter that I volunteer for.

I’m presenting a session called Real-time Data Warehouse and Reporting Solutions at 9:45.  It’s a session I’ve done several times before, and it’s also a preview of one of the sessions I’m doing at the PASS Summit this year.  The presentation has evolved in a way I didn’t expect when I first started doing it.  In less than one hour, I go through the steps to setup a real-time data warehouse and actually get it running.  I’ve been surprised at how excited people get when they seem me do this, almost as if I’ve done something magical or impossible.  If you’re attending SQL Saturday on August 25, I invite you to come see how it’s done without any tricks or sleight of hand.

Real-time Data Warehouse and Reporting Solutions

In this demo-rich session, we will explore database and ETL architectures that let you smoothly move data from your application databases across the enterprise in real-time to your data warehouse. You will see how to use SQL Server features such as replication, Change Data Capture, SSIS, and other techniques to extract, transform, and load data fast and dependably. We will develop an actual sample system during the session and discuss and walk through alternative real-time architectures, including the use of Data Vault methodologies. We will then see how to use Reporting Services and Power View to present data with a minimum amount of latency.