Documents & Diagrams:

Diagram Downloads:

   Power BI End-to-End Features <--In need of major updates-coming soon

   MSBI Integration for 4 Primary Report Types <--Updated June 2017

Reporting Project Document Downloads:

   Report Requirements

   Report Style Guide

   Report Unit Testing Checklist


Presentation Info & Slides:

Architecting a Data Lake to Modernize Your Data Warehouse

This full-day session will focus on principles for designing and implementing a data lake. There will be a mix of concepts, lessons learned, and technical implementation details. This session is approximately 70% demonstrations: we will create a data lake, populate it, organize it, query it, and integrate it with a relational database via logical constructs. You will leave this session with an understanding of the benefits and challenges of a multi-platform analytics/DW/BI environment, as well as recommendations for how to get started.

Target audience: Technologists who are considering using a data lake. No data lake experience is required. Familiarity with a relational database such as SQL Server is suggested, as some of the scenarios discussed will focus on integrating a data lake with a relational data warehouse.

You will learn in this session:

  • Scenarios and use cases for expanding an analytics/DW/BI environment into a multi-platform environment which includes a data lake

  • Strengths and limitations of a logical data architecture which follows a polyglot persistence strategy

  • Planning considerations for a data lake which supports streaming data as well as batch data processing

  • Methods for organizing a data lake which focuses on optimal data retrieval and data security

  • Techniques for speeding up development and refining user requirements via data virtualization and federated query approaches

  • Benefits and challenges of schema-on-read vs. schema-on-write approaches for data integration and on-demand querying needs

  • Deciding between Azure Blob Storage vs. Azure Data Lake Store vs. a relational platform

Specific technologies discussed and/or demonstrated in this session include:

  • Azure Data Lake Store

  • Azure Data Lake Analytics

  • Azure SQL Data Warehouse

  • SQL Server

  • Azure Blob Storage

  • PolyBase

  • U-SQL

 

If you have an Azure account and your own laptop, you will be able to follow along during the demonstrations if you'd like. Demo scripts will be provided with the workshop materials.

Upcoming Sessions:

Presented At:


Tips for Getting Started with the Azure Data Platform

This session is packed with practical tips and lessons learned about using Azure as a database platform. You will learn the fundamentals about how Azure is structured to help you make architectural decisions. Ideas will be shared for planning resource groups, naming conventions, and the separation of Dev, Test, and Prod. We will discuss database platform options, data storage options, and why PowerShell and ARM are so important to deployment scenarios.

Level: Introductory

Slides: Tips for Getting Started with Azure

Target Audience: Database developers and DBAs who are looking for a primer on the Azure platform.

Presented At: 


Designing a Modern Data Warehouse + Data Lake

Join us for a discussion of strategies and architecture options for implementing a modern data warehousing environment.  We will explore advantages of augmenting an existing data warehouse investment with a data lake, and ideas for organizing the data lake for optimal data retrieval. We will also look at situations when federated queries are appropriate for employing data virtualization, and how federated queries work with SQL Server, Azure SQL DB, Azure SQL DW, Azure Data Lake, and/or Azure Blob Storage.

Level: This is an intermediate session suitable for attendees who are familiar with data warehousing fundamentals.

Slides: Designing a Modern DW + Data Lake <--Slides last updated March 2017

Presented At:


Fundamentals of Designing a Data Warehouse

In this session we will review sensible techniques for developing a data warehousing environment which is relevant, agile, and extensible. We will cover practical dimensional modeling fundamentals and design patterns, along with when to use techniques such as partitioning or clustered columnstore indexes in SQL Server. We'll also review tips for using a database project in SQL Server Data Tools (SSDT) effectively. The session will conclude with tips for planning the future growth of your data warehouse. 

Level: This is an introductory session best suited to attendees who are new to data warehousing concepts.

Slides: Fundamentals of Designing a DW <--Slides last updated February 2017

Presented At:

  • SQL Malibu User Group, San Fernando Valley, CA - Feb 15, 2017
  • Pearl Hacks, Chapel Hill, NC - Feb 11, 2017 (an informal workshop/whiteboarding version of this presentation)
  • SQL Saturday BI Edition, Atlanta, GA - Dec 10, 2016 (under previous title: Good Habits of a DW Developer)

    Presentation Archives:

    Building Blocks of Cortana Intelligence Suite in Azure

    Join us for a practical look at the components of Cortana Intelligence Suite for information management, data storage, analytics, and visualization. Purpose, capabilities, and use cases for each component of the suite will be discussed. If you are a technology professional who is involved with delivering business intelligence, analytics, data warehousing, or big data utilizing Azure services, this technical overview will help you gain familiarity with the components of Cortana Intelligence Suite and its potential for delivering value.

    Level:  A fast-moving introductory session

    Target Audience: Technology professionals seeking to gain a high level understanding of the capabilities of the Cortana Intelligence Suite

    Slides: Building Blocks of Cortana Intelligence Suite <--Slides last updated April 2017

    Presented At:


    Tales from Building a SQL Server Data Warehouse in Azure

    In this session, we share our experiences and lessons learned from a recent migration to Azure for a SQL Server data warehousing environment. We begin with sharing our reasoning for IaaS vs. PaaS, our carefully-selected naming conventions, and how we structured development, test, and production within subscriptions and resource groups. We cover the what, why, and how for decisions around storage, encryption, and backups. Finally, the session wraps up with a brief discussion of the use of Azure Resource Manager (ARM) templates and PowerShell, as well as techniques for monitoring the environment in Azure.

    Level:  A fast-moving introductory session

    Slides: Tales from Building a SQL Server DW in Azure <--Slides last updated August 2017

    Target Audience: Technology professionals responsible for creating and managing resources in Azure

    Presented At:


    Power BI: Architecture, Integration Points, Implementation Options

    In this session we will review Power BI implementation approaches, architecture, delivery options, authoring tools, data access methods, data refresh options, security, as well as functionality for sharing and distributing content. Components of cloud, on-premises, and hybrid approaches will be explored. We will conclude with the concept of "Bimodal BI" with use cases for how Power BI might be utilized for both self-service BI and certain corporate BI initiatives. 

    Level:  A fast-moving introductory session

    Target Audience: Technology professionals seeking to understand the system components

    Slides:    Power BI: Architecture, Integration Points, Implementation Options <--slides last updated March 27, 2016

    Presented At:

    This session has evolved and was previously known as Power BI: Architecture, Use Cases, Strengths, and Shortcomings


    Administering and Managing the Power BI Environment (V1 - Power BI for Office 365)

    Power BI for Office 365 is Microsoft's new self-service BI offering. Just because it emphasizes self-service doesn't mean a system administrator isn't an important role! In this session we will discuss the overall system components and how a Power BI site in SharePoint Online differs from an on-premises SharePoint BI site.  We will walk through how to best handle setting up connectivity to data sources, when a gateway is needed, and what data refresh capabilities exist. We will also consider how and when to create OData feeds from your corporate on-premises data sources and how those OData feeds affect Enterprise Data Search functionality.

    Level:  A fast-moving introductory session

    Slides:  Administering & Managing the Power BI Environment  <--Applicable to V1 (Power BI for Office 365) which is now deprecated

    Presented At:


    Power BI Architecture & Functionality in SharePoint Online  (V1 - Power BI for Office 365)

    Power BI for Office 365 is Microsoft's new business intelligence offering which utilizes SharePoint Online and Office functionality. In this session we will discuss and demonstrate the key components of this hybrid cloud environment and what the prerequisites are for a Power BI site. Some key differences between a Power BI site in SharePoint Online versus a SharePoint BI site will be explored. We will conclude with a review of data connectivity options to be aware of. 

    Level:  Introductory

    Slides:  Power BI Architecture & Functionality in SharePoint Online  <--Applicable to V1 (Power BI for Office 365) which is now deprecated

    Presented At:


    Maximizing the Data Exploration Capabilities of Power View

    Description:  Would business users in your organization love to get their hands on an interactive, visually-oriented, data exploration tool? If so, Power View may be a useful addition to your BI environment to handle certain types of self-service reporting requirements. In this session we will demonstrate the features and functionality of Power View including highlighting, cross-filtering, play axis, small multiples, tiles, cards, maps, filtering, and exporting.

    We will identify which situations Power View is most suited for, as well as its strengths and limitations. Capabilities in SharePoint 2010/2013 vs. Excel 2013 will be reviewed, as well as restrictions on types of data sources allowed. We will also discuss how to prepare a dataset in order to maximize the usability and functionality of Power View.  

    Level:  Introductory 

    Slides:  Maximizing the Data Exploration Capabilities of Power View

    Presented At: 


    The Lifecycle of a Reporting Services Report

    Description:  In this session we will discuss various tips and best practices as we follow a report through its lifecycle via an end-to-end demo. Beginning with a discussion of requirements and useful templates, we will progress to a review of good report development and standardization practices, followed by suggestions for testing and validation. Next we will consider alternatives for deployment, report delivery, and handling ongoing enhancements and bug fixes. The lifecycle will wrap up with a discussion of maintenance and administration of the reporting environment.  

    Level:  Intermediate 

    Slides:  Lifecycle of a SSRS Report

    Presented At: 


    Tools and Techniques for Implementing Corporate and Self-Service BI

    Description:  Business Intelligence requirements frequently change and having only a Corporate BI solution can result in slower delivery cycles and information backlogs. Yet the introduction of Self-Service BI introduces challenges around governance, change management, standardization, training, support, and access to unregulated data sources. In this session we will consider the benefits, obstacles, and techniques for integrating Self-Service and Corporate BI. Demonstrations of Microsoft BI tools will include Power Pivot, Power Query, Power View, Power Map, Excel, and SharePoint 2013.  

    Level:  Introductory 

    Slides:  Tools and Techniques for Implementing Corporate and Self-Service BI

    Presented At: 


    So You Want To Be a Rockstar Report Developer?

    Description:  You don't want to settle for being an average report developer, right? In this session we'll walk through an end-to-end demo as we discuss various development, standardization, deployment, and documentation practices that will make your SSRS development life easier, your output of higher quality, increase maintainability, and ultimately save you time. Audience participation and sharing of experiences is encouraged as we cover choices you have for datasets, sorting, grouping, filtering, calculations, parameters, deployment, and delivery. This session focuses primarily on SQL Server Reporting Services 2012, although some concepts may apply to other BI tools as well.   

    Level:  Intermediate 

    Slides:  So You Want To Be a Rockstar Report Developer?

    Starter Templates:

    Report Requirements

    Report Style Guide

    Report Unit Testing Checklist

    Presented At: 


    Managing the "Power Pivot for SharePoint" Environment

    Description:  In this session we will discuss management of data models and reports stored in the Power Pivot Gallery document library. Data Refresh and security considerations will be reviewed in depth. The relational and SSAS databases present in a Power Pivot for SharePoint environment will be introduced, as well as options for reporting on usage and other key metrics. This session, which is applicable to SharePoint 2010 and 2013, will help you become prepared to tackle the support aspects of a Power Pivot for SharePoint environment.  

    Level:  Intermediate 

    Slides:  Managing the Power Pivot for SharePoint Environment

    Presented At: 


    Managed Self-Service BI

    Description:  

    Traditional Business Intelligence is changing … you heard about the ambitious data analyst down the hall who created a mashup of data from the internal data warehouse + Excel + an Azure feed?  Empowering knowledge workers is Microsoft's vision of delivering "BI for the Masses."  In this session we will dismiss the hype & examine how this new world of "Managed Self-Service BI" complements Corporate BI in SQL Server 2012 and SharePoint 2010/2013. 

    Goals: 

    • Provide overview of each Self-Service component and expectations for its use:  PowerPivot | Excel Services | Power View | Report Builder
    • Discuss techniques to successfully monitor, secure, & manage the Self-Service BI environment

    Level:  Intermediate

    Slides:  Managed Self-Service BI

    Presented At:


    Dashboards...How To Choose Which MSBI Tool?

    Description:  The Microsoft Business Intelligence (MSBI) stack has a few choices for visualization.  We will explore options, flexibility, limitations, and ease of use for creating dashboards & scorecards with:

    • PerformancePoint Services 2010
    • Reporting Services 2008 R2
    • SharePoint Server 2010
    • PowerPivot
    • Power View

    This session will be a fun one!  We will compare and contrast options available in each tool, such as:  charts, gauges, maps, indicators, sparklines, data bars, and decomposition trees. 

    Goals:

    • Examine strengths of each tool related to meeting data visualization requirements
    • Compare and contrast common visualization options available in each tool
    • Conclude when you might choose one tool in the Microsoft BI stack versus another

    Level:  Intermediate 

    Slides:  Dashboards...How To Choose Which MSBI Tool

    Companion Document:  MSBI Visualization Comparisons By Tool

    Presented At: