Modern Data Warehousing Presentation

I will be presenting the session “Modern Data Warehousing” tomorrow (Wednesday, August 13th) at the Central New Jersey SQL User Group at 6:30pm.  The abstract for my session is below.  I hope you can make it!

Modern Data Warehousing

The traditional data warehouse has served us well for many years, but new trends are causing it to break in four different ways: data growth, fast query expectations from users, non-relational/unstructured data, and cloud-born data.  How can you prevent this from happening?  Enter the modern data warehouse, which is able to handle and excel with these new trends.  It handles all types of data (Hadoop), provides a way to easily interface with all these types of data (PolyBase), and can handle “big data” and provide fast queries.  Is there one appliance that can support this modern data warehouse?  Yes!  It is the Analytics Platform System (APS) from Microsoft (formally called the Parallel Data Warehouse or PDW), which is a Massively Parallel Processing (MPP) appliance that has been recently updated (v2 AU1).  In this session I will dig into the details of the modern data warehouse and APS.  I will give an overview of the APS hardware and software architecture, identify what makes APS different, and demonstrate the increased performance.  In addition I will discuss how Hadoop, HDInsight, and PolyBase fit into this new modern data warehouse.

Posted in Data warehouse, Presentation, Session, SQLServerPedia Syndication | Leave a comment

24 Hours of PASS videos online

All the videos for the sessions at 24 Hours of PASS (Spring 2014) are available for free.  Check the session schedule for a complete list.  Also, many of the PASS virtual chapters have recordings of their meetings online.  Enjoy!

Posted in PASS, SQL Server, SQLServerPedia Syndication, Videos | Leave a comment

SQL Server Agent job steps vs SSIS

When doing ETL, you have the choice of using T-SQL or SSIS (see When to use T-SQL or SSIS for ETL).  If you decide T-SQL is the way to go and you just want to execute a bunch of T-SQL statements (individually or within a stored procedure), it’s still a good idea to wrap them in SSIS Execute SQL Tasks because you can use logging, auditing and error handling that SSIS provides that T-SQL does not.  You can also easily run SSIS Execute SQL Tasks in parallel, so if those tasks are calling stored procedures that means you are able to run stored procedures in parallel.  Other benefits for using SSIS instead of a SQL Server Agent job include:

  • The ability to use a project data connection manager, so if the connection info changes you only need to change it in one spot
  • You can create checkpoints for restarting
  • You can add logic to check if packages have run by querying the status log (SSISDB catalog) instead of manually looking at the SQL Server agent job steps
  • You can do reporting off of the auditing info you capture
  • You can use select statements against the SSIS history for analysis (history stored in SSISDB catalog), which you don’t have for job steps in SQL Server agent
  • Ease of maintenance (but depends on knowledge of SSIS vs knowledge of SQL Server)

 

Posted in SQLServerPedia Syndication, SSIS | 2 Comments

IT books that should be on your shelf

I have a lot of books on my shelves dealing with business intelligence, data warehousing, master data management, and consulting.  Below are my favorites:

Data Warehouse Books:

Ralph Kimball Books

Building the Data Warehouse

Corporate Information Factory

DW 2.0

Mastering Data Warehouse Design

Data Warehouse from Architecture to Implementation

Microsoft SQL Server 2012 Internals

Star Schema: The complete reference

Microsoft Big Data Solutions

Business Intelligence Books:

Expert Cube Development with SSAS Multidimensional Models

Pro SQL Server 2012 BI Solutions

Business Intelligence Competency Centers

Data Mining with Microsoft SQL Server 2008

Performance Dashboards

How To Measure Anything

Dimensional Modeling: In a Business Intelligence Environment

The Balanced Scorecard

Information Dashboard Design

Microsoft SQL Server 2014 Business Intelligence Development

Reporting with Microsoft SQL Server 2012 (shameless plug of my book)

Master Data Management Books:

Master Data Management

Microsoft SQL Server 2012 Master Data Services

Data Quality: The Accuracy Dimension

Consulting Books:

How to win friends and influence people

Influence: Science and practice

The Secrets of Consulting: A Guide to Giving and Getting Advice Successfully

More Secrets of Consulting

The Rational Guide to: IT Consulting

The Nomadic Developer

Agile Project Management With Scrum

Posted in Career, SQLServerPedia Syndication | 1 Comment

PASS BA Conference 2014: Sessions Recordings Now Available

All PASS Business Analytics Conference 2014 recordings are now available.  If you purchased the online sessions, to view, log on to your myPASS account, enter your activation code, and watch the sessions from myRecordings.  You can also purchase all recordings on a USB drive.  While there, check out mine: Building an Effective Data Warehouse Architecture

Posted in PASS, SQLServerPedia Syndication, Videos | Leave a comment

Presentation slides for Modern Data Warehousing

Thanks to everyone who attended my session “Modern Data Warehousing” for Pragmatic Works last Thursday.  The abstract for my session is below and the recording is available here.  I hope you enjoyed it.

Here is the PowerPoint presentation: Modern Data Warehousing

Modern Data Warehousing

The traditional data warehouse has served us well for many years, but new trends are causing it to break in four different ways: data growth, fast query expectations from users, non-relational/unstructured data, and cloud-born data.  How can you prevent this from happening?  Enter the modern data warehouse, which is able to handle and excel with these new trends.  It handles all types of data (Hadoop), provides a way to easily interface with all these types of data (PolyBase), and can handle “big data” and provide fast queries.  Is there one appliance that can support this modern data warehouse?  Yes!  It is the Parallel Data Warehouse (PDW) from Microsoft, which is a Massively Parallel Processing (MPP) appliance that has been recently updated (v2 AU1).  In this session I will dig into the details of the modern data warehouse and PDW.  I will give an overview of the PDW hardware and software architecture, identify what makes PDW different, and demonstrate the increased performance.  In addition I will discuss how Hadoop, HDInsight, and PolyBase fit into this new modern data warehouse.

Posted in Data warehouse, Presentation, Session, SQLServerPedia Syndication | Leave a comment

Presentation slides for Houston SQLSaturday

Thanks to everyone who attended my two sessions at SQLSaturday in Houston (more info).  Here are the abstracts and PowerPoint links:

Best Practices to Deliver BI Solutions

If your company is planning to build a data warehouse or BI solution, you need to be aware that BI projects have high failure rates. Gartner says between 70% to 80% of corporate business intelligence projects fail. And with “big data” adding more complexity you can expect even more failures. However, the major causes of these failures are well known and can be avoided by implementing a set of best practices.

I have worked on dozens of end-to-end BI projects and have seen my share of successes and failures. I will talk about the reasons BI projects fail and share best practices and lessons learned so your BI project will fall into the “successful” category.

Here is the PowerPoint presentation: Best Practices to Deliver BI Solutions

Enhancing your career: Building your personal brand

In three years I went from a complete unknown to a popular blogger, speaker at PASS Summit, and a SQL Server MVP.  Along the way I saw my yearly income triple.  Is it because I know some secret?  Is it because I am a genius?  No!  It is just about laying out your career path, setting goals, and doing the work.  It’s about building your personal brand and stepping out of your comfort zone.  It’s about overcoming your fear of taking risks.  If you can do those things, you will be rewarded.  I will discuss how you too can go from unknown to well-known.  I will talk about building your community presence by blogging, presenting, writing articles and books, twitter, LinkedIn, certifications, interviewing, networking, and consulting and contracting.  Your first step to enhancing your career will be to attend this session!

Here is the PowerPoint presentation: Enhancing your career: Building your personal brand

Posted in Business Intelligence, Career, Presentation, Session, SQLServerPedia Syndication | 1 Comment

Presentation Slides for Building an Effective Data Warehouse Architecture

Thanks to everyone who attended my session “Building an Effective Data Warehouse Architecture” at the PASS Business Analytics conference.  The abstract is below.  I hope you enjoyed it!

Here is the PowerPoint presentation: Building an Effective Data Warehouse Architecture

Building an Effective Data Warehouse Architecture

You’re a DBA and your boss asks you to determine if a data warehouse would help the company.  So many questions pop into your head: Why use a data warehouse?  What is the best methodology to use when creating a data warehouse?  Should I use a normalized or dimensional approach?  What is the difference between the Kimball and Inmon methodologies?  Does the new Tabular model in SQL Server 2012 change things?  What is the difference between a data warehouse and a data mart?  Is there any hardware I can purchase that is optimized for a data warehouse?  What if I have a ton of data? During this session James will help you to answer these questions so your response to your boss will provoke amazement and lead to a big raise.  Or at least help to lead you down the correct path!

Posted in Data warehouse, Presentation, Session, SQLServerPedia Syndication | Leave a comment

SQLSaturday in Houston

This upcoming Saturday I will be presenting two sessions at SQLSaturday in Houston (more info).  I hope to see you there.  Here are the abstracts:

Best Practices to Deliver BI Solutions

If your company is planning to build a data warehouse or BI solution, you need to be aware that BI projects have high failure rates. Gartner says between 70% to 80% of corporate business intelligence projects fail. And with “big data” adding more complexity you can expect even more failures. However, the major causes of these failures are well known and can be avoided by implementing a set of best practices.

I have worked on dozens of end-to-end BI projects and have seen my share of successes and failures. I will talk about the reasons BI projects fail and share best practices and lessons learned so your BI project will fall into the “successful” category.

Enhancing your career: Building your personal brand

In three years I went from a complete unknown to a popular blogger, speaker at PASS Summit, and a SQL Server MVP.  Along the way I saw my yearly income triple.  Is it because I know some secret?  Is it because I am a genius?  No!  It is just about laying out your career path, setting goals, and doing the work.  It’s about building your personal brand and stepping out of your comfort zone.  It’s about overcoming your fear of taking risks.  If you can do those things, you will be rewarded.  I will discuss how you too can go from unknown to well-known.  I will talk about building your community presence by blogging, presenting, writing articles and books, twitter, LinkedIn, certifications, interviewing, networking, and consulting and contracting.  Your first step to enhancing your career will be to attend this session!

Posted in Presentation, Session, SQLServerPedia Syndication | Leave a comment

Hadoop and Data Warehouses

I see a lot of confusion when it comes to Hadoop and its role in a data warehouse solution.  Hadoop should not be a replacement for a data warehouse, but rather should augment/complement a data warehouse.  Hadoop and a data warehouse will often work together in a single information supply chain: Hadoop excels in handling raw, unstructured and complex data with vast programming flexibility; Data warehouses, on the other hand, manage structured data, integrating subject areas and providing interactive performance through BI tools.  As an example in manufacturing, let’s look at using Hadoop and a data warehouse on a Parallel Data Warehouse (PDW):

Untitled picture

There are three main use cases for Hadoop with a data warehouse, with the above picture an example of use case 3:

  1. Archiving data warehouse data to Hadoop (move)
    Hadoop as cold storage/Long Term Raw Data Archiving:
    – So don’t need to buy bigger PDW or SAN or tape
  2. Exporting relational data to Hadoop (copy)
    Hadoop as backup/DR, analysis, cloud use:
    – Export conformed dimensions to compare incoming raw data with what is already in PDW
    – Can use dimensions against older fact table
    – Sending validated relational data to Hadoop
    – Hadoop data to WASB and have that used by other tools/products (i.e. Cloud ML Studio)
    – Incremental Hadoop load / report
  3. Importing Hadoop data into data warehouse (copy)
    Hadoop as staging area:
    – Great for real-time data, social networks, sensor data, log data, automated data, RFID data (ambient data)
    – Where you can capture the data and only pass the relevant data to PDW
    – Can do processing of the data as it sits in Hadoop (clean it, aggregate it, transform it)
    – Some processing is better done on Hadoop instead of SSIS
    – Way to keep staging data
    – Long-term raw data archiving on cheap storage that is online all the time (instead of tape) – great if need to keep the data for legal reasons
    – Others can do analysis on it and later pull it into data warehouse if find something useful

Note there will still be a need for some Hadoop skills: Loading data into Hadoop (unless all done thru Polybase), maintenance, cleaning up files, managing data, etc.  But people who need access to data don’t need any Hadoop skills or special connections – they can use all skills they have today.

Here are some of the reasons why it is not a good idea to have only Hadoop as your data warehouse:

  • Hadoop is slow for reading queries.  HDP 1.0 is a batch processing infrastructure, not interactive/realtime.  HDP 2.0 will support interactive queries but is new and still will not perform anywhere near PDW for interactive querying.  This is why PolyBase is so important, as it bridges the gap between the two technologies so customers can take advantage of both the unique features of Hadoop and realize the benefits of a EDW.  Truth be told users won’t want to wait 20+ seconds for a MapReduce job to start up to execute a Hive query
  • Hadoop is not relational, as all the data is in files in HDFS, so there always is a conversion process to convert the data to a relational format
  • Hadoop is not a database management system.  It does not have functionality such as update/delete of data, referential integrity, statistics, ACID compliance, data security, and the plethora of tools and facilities needed to govern corporate data assets
  • Restricted SQL support, such as certain aggregate functions missing
  • There is no metadata stored in HDFS, so another tool needs to be used to store that, adding complexity and slowing performance
  • Finding expertise in Hadoop is very difficult: The small number of people who understand Hadoop and all its various versions and products versus the large number of people who know SQL
  • Super complex, lot’s of integration with multiple technologies to make it work
  • Many Hadoop tools/technologies/versions/vendors to choose from, with no standards.  See all the various Apache Hadoop technologies here
  • Some reporting tools don’t work against Hadoop, as well as some reporting tools require data in OLAP
  • May require end-users to learn new reporting tools and Hadoop technologies to query the data
  • The new Hadoop solutions (Tez, X, Spark, etc) are still figuring themselves out.  Customers should not take the risk of investing in one of these solutions (like MapReduce) that may be obsolete
  • It might not save you much in costs: you still have to purchase hardware, support, licenses, training, migration costs.  And then there is the possibility of a big company acquiring a Hadoop company and the cheap solution becoming much more expensive
  • If you need to combine relational data with Hadoop, you will need to move that relational data to Hadoop since there is no PolyBase-like technology

I also wanted to mention that “unstructured” data is a bit of a misnomer.  Just about all data has at least some structure.  Better to call it “semi-structured”.  I like to think of it as data in a text file is semi-structured until someone adds structure to it, by doing something like importing it into a SQL Server table.  Or think of structured data as relational and unstructured as non-relational.

More info:

Using Hadoop to Augment Data Warehouses with Big Data Capabilities

WEBINAR Replay: Hadoop and the Data Warehouse: When to Use Which

Hadoop and the Data Warehouse: When to Use Which

Video Big Data Use Case #5 – Data Warehouse Augmentation

How Hadoop works with a Data Warehouse

Design Tip #165 The Hyper-Granular Active Archive

No, Hadoop Isn’t Going To Replace Your Data Warehouse

A fair benchmark for Impala 2.0 – part 1

Posted in Data warehouse, Hadoop, PDW/APS, SQLServerPedia Syndication | 2 Comments