sqlsathistory

Nr: 334

Back to Main list

SQLSaturday #334 - Boston - BI Edition 2014

Start Time (24h) Speaker Track Title
00:00:00 Britton Gray Theoreau Data-ing Advice: Profiles are Important
00:00:00 Jason Brimhall Bunker Hill Reporting Services for Mere DBAs
00:00:00 Jason Brimhall Theoreau Murder They Wrote
00:00:00 Bryan Cafferky Longfellow Dimensional Modeling: The Heart of a Data Warehouse
00:00:00 Charles Hyman Constitution Using Change Data Capture (CDC) to build a Data Warehouse.
00:00:00 Chris Seferlis Hawthorne Scratching the surface: An overview of SSRS and building your first BI Report
00:00:00 Daniel Bowlin Hawthorne SSIS-restart, scale up/out, load balance.
00:00:00 George Walters Hawthorne What is MDS and DQS (Master Data Services/Data Quality Services)
00:00:00 James Serra Longfellow Building an Effective Data Warehouse Architecture
00:00:00 James Serra Concord Best Practices to Deliver BI Solutions
00:00:00 Jim Sinclair Bunker Hill Advanced Power Query Techniques
00:00:00 James Phillips Hawthorne SSIS Performance Tuning Tips and Tricks
00:00:00 Justin Randall Concord Shade Tree Mechanic's Guide to SSAS
00:00:00 Mark Vaillancourt Theoreau MDX Trek: First Contact
00:00:00 Robert McCauley Bunker Hill See the Future with SQL Running Totals - Never Run Out of Anything Again
00:00:00 Paresh Motiwala Concord Setting up your BI Infrastructure
00:00:00 Philip Taylor Longfellow Exploiting Excel Cube Functions in your Reports and Dashboards
00:00:00 Steve Simon Constitution Power to the people!!
00:00:00 Steve Simon Theoreau SQL Server 2014 Data Access Layers
00:00:00 Steve Simon Constitution A dive into Data Quality Services
00:00:00 Stevo Smocilac Bunker Hill Using Power BI to Deliver Self-Service BI
00:00:00 Sunil Kadimdiwan Concord Develop Impressive Dashboards using SSRS 2014
00:00:00 Thomas Grohser Longfellow Security for your BI data
00:00:00 Adam Jorgensen Longfellow On-Demand Compute and Analytics with Powershell, Hadoop and Azure
00:00:00 Anthony Martin Hawthorne Scaling Power Pivot Solutions
00:00:00 Andy Roberts Constitution The Modern Data Warehouse
00:00:00 Andy Roberts Concord Azure Machine Learning Studio
00:00:00 Anthony Martin Constitution Securing Analysis Services Tabular

SessionID: 10406

Data-ing Advice: Profiles are Important

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Theoreau

Speaker: Britton Gray

Title: Data-ing Advice: Profiles are Important

Abstract:

Data warehousing professionals often consider data profiling to be drudgery and/or an unnecessary part of the larger warehouse design process. However, information unearthed during this process can prove instrumental in avoiding pitfalls later on. This session will explore the reasons for profiling and scenarios in which it proves particularly useful. We will also discuss profiling methods, including the SSIS profiling task and supplemental profiling techniques that provide both developers and business users insight into the quality and consistency of source data.

SessionID: 10873

Reporting Services for Mere DBAs

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Bunker Hill

Speaker: Jason Brimhall

Title: Reporting Services for Mere DBAs

Abstract:

As a DBA in the modern era, you may be required from time to time to do something outside of your comfort zone. One of these things may be to become quickly acquainted with SSRS. Even better is that you may be required to do things you have not considered in a standard report. In this session, you will learn how to implement a framework to help provide a common ground for your reports. This session will delve into fun topics such as dynamic grouping and dynamic sorting. We are not talking about the interactive sorting that your accountant may use. Attendees will also be introduced to a few quick methods of exporting Reports from the report server - this is from a DBA perspective after-all!

SessionID: 10875

Murder They Wrote

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Theoreau

Speaker: Jason Brimhall

Title: Murder They Wrote

Abstract:

Join Microsoft Certified Master, Jason Brimhall, as he examines numerous database design and coding implementations that they have seen over the years. They will explore these implementations and show how they can be murder on SQL Server in terms of performance, security, storage and even accuracy. You will learn how you can identify these “killers” and how you can implement alternatives that will keep data professionals, management, and end-users happy. All while keeping your databases happy, secure, turbo-charged and healthy.

This is the Solo version of the Joint version normally done as an all-day precon

SessionID: 11031

Dimensional Modeling: The Heart of a Data Warehouse

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Longfellow

Speaker: Bryan Cafferky

Title: Dimensional Modeling: The Heart of a Data Warehouse

Abstract:

In this presentation, Bryan will explain the principles of Dimensional Modeling, the foundation of a data warehouse. He will identify best practices and pitfalls to avoid especially if you come from a relational database background. The focus is to show how dimensional modeling is applied and provide you with the skills to build your own dimensional models. If you've had a hard time understanding dimensional modeling or have put off learning this critical subject, you need to come to this presentation.

SessionID: 11427

Using Change Data Capture (CDC) to build a Data Warehouse.

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Constitution

Speaker: Charles Hyman

Title: Using Change Data Capture (CDC) to build a Data Warehouse.

Abstract:

This session will de-mystify Change Data Capture.

How to capture Inserts Updates and Deletes WITHOUT the use of Triggers, Populate an OLAP structure and Mine the Data for GOLD. CDC Configuration CDC Stored Procedures CDC Transformation in SSIS Creating an OLAP cube with SSAS and POWERPIVOT from the CDC Data Learn what CDC is and how to implement it from the ground up. The session will include: how CDC can be used as an AUDIT tool, as a replacement for triggers, A Data warehouse feeding tool, to incrementally load the warehouse without refreshing the data. Integration with SSIS and Stored procs will be shown. Generation of BI Reports and PowerView Reports based on CDC data and Statistics will be shown and Discussed. Best Practices for Incremental BI ETL's will be focused on.

SessionID: 12055

Scratching the surface: An overview of SSRS and building your first BI Report

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Hawthorne

Speaker: Chris Seferlis

Title: Scratching the surface: An overview of SSRS and building your first BI Report

Abstract:

This session will act as an introduction to SSRS, its infrastructure, and the basics of report design. There will be a brief overview of the development environment, wizards, datasets, grouping, subscriptions, permissions and some basic graphing alongside the data.

SessionID: 12891

SSIS-restart, scale up/out, load balance.

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Hawthorne

Speaker: Daniel Bowlin

Title: SSIS-restart, scale up/out, load balance.

Abstract:

This presentation is a journey through some of the common challenges facing DBAs and SSIS developers: scaling up and/or out to increase throughput, load balancing, recoverability and restartability, basic process auditing, multiple job dependency checking. Next we will review the essential elements to building a framework based on the work pile design pattern that will help resolve many of the issues these challenges may bring. The end goal is a framework that will manage a set of jobs that can recover from common errors, be spread across multiple servers, check load consistency and make sure that nothing runs unless the appropriate upstream data is available.

SessionID: 14562

What is MDS and DQS (Master Data Services/Data Quality Services)

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Hawthorne

Speaker: George Walters

Title: What is MDS and DQS (Master Data Services/Data Quality Services)

Abstract:

This will be a high-level overview of what Master Data Services is, and Data Quality Services is.

Mostly a discussion of possible uses for these relatively recent additions to the SQL Server stack.

SessionID: 15600

Building an Effective Data Warehouse Architecture

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Longfellow

Speaker: James Serra

Title: Building an Effective Data Warehouse Architecture

Abstract:

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’s the best methodology to use when creating a data warehouse? Should I use a normalized or dimensional approach? What’s the difference between the Kimball and Inmon methodologies? Does the new Tabular Model change things? What’s the difference between a data warehouse and a data mart? Is there any hardware that is optimized for a data warehouse? Join this session for the answers to all these questions. You’ll leave with information that will amaze your boss and lead to a big raise… – or at least lead you down the correct path to adding business value to your organization!

SessionID: 15601

Best Practices to Deliver BI Solutions

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Concord

Speaker: James Serra

Title: Best Practices to Deliver BI Solutions

Abstract:

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.

SessionID: 16824

Advanced Power Query Techniques

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Bunker Hill

Speaker: Jim Sinclair

Title: Advanced Power Query Techniques

Abstract:

Power Query, the new data discovery and access add-in for Microsoft Excel offers a powerful set of capabilities that allow users to gather the data they need for analysis. In this session we will look at some of the more advanced capabilities made available through the user interface, as well as demonstrating how to use the underlying M language to solve even more sophisticated data acquisition requirements

SessionID: 17552

SSIS Performance Tuning Tips and Tricks

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Hawthorne

Speaker: James Phillips

Title: SSIS Performance Tuning Tips and Tricks

Abstract:

So you have developed SSIS packages and your now banging your head against the wall on how to get the performance increased. Stop by this session and I will share some of my tips and tricks on how to properly set the Buffer size and engine threads, as well as go over how threading in SSIS works. I will also share with you some common mistakes that are made when picking the wrong source or destination driver and some helpful components you can use to reduce the number of operations and get your packages on the right track.

SessionID: 17583

Shade Tree Mechanic's Guide to SSAS

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Concord

Speaker: Justin Randall

Title: Shade Tree Mechanic's Guide to SSAS

Abstract:

This is a session for DBAs and Developers working with SSAS who are not sure what happens under the hood when it is queried, or how to tell if it's performing at its best. This is not a session on query optimization. Rather, this session will help you better understand if you should focus on query optimization or if some basic configuration changes might provide that needed performance boost. We'll discuss the basic architecture of SSAS and cover some of the most pertinent metrics to give you a quick and easy view into your SSAS server's performance.

SessionID: 19666

MDX Trek: First Contact

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Theoreau

Speaker: Mark Vaillancourt

Title: MDX Trek: First Contact

Abstract:

Cube space; the final frontier. In this Star Trek themed introduction to MDX, we will discuss the fundamentals of cube structure and vocabulary, including tuples, members, sets, hierarchies, and more. We will introduce and demonstrate the basic syntax of MDX with queries that include navigating hierarchies and even some time-based expressions. This session will give you the tools you need to write simple, yet meaningful, MDX queries in your own environment.

SessionID: 19962

See the Future with SQL Running Totals - Never Run Out of Anything Again

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Bunker Hill

Speaker: Robert McCauley

Title: See the Future with SQL Running Totals - Never Run Out of Anything Again

Abstract:

Have you ever run out of something? Do you need to order things now that you know will be needed by a certain date?

This seemingly simple problem can get complicated, whether you are making a shopping list for a party, or doing supply chain planning for a factory. Special functions in T-SQL and SSRS can help you build a visual model of future supply and demand, so you will know if all your bases are covered. We will demonstrate algorithms for tracking running totals and check that they scale well to handle big data sets, and build a flexible report to answer "what if" scenarios. Come learn how to use SQL Server tools to build a looking glass to the future!

SessionID: 21677

Setting up your BI Infrastructure

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Concord

Speaker: Paresh Motiwala

Title: Setting up your BI Infrastructure

Abstract:

We know BI in bits and pieces. But imagine if you had to setup the BI infrastructure from scratch.

What do we need to consider, which players do we consider, what are the components, the HA/DR scenario for the same, what technology to use? These are the questions we will attempt to answer along the way. What are the best practices involved in creating a BI infrastructure. We can discuss whether to outsource this project or rise above our DBA job and do it ourselves.

SessionID: 22373

Exploiting Excel Cube Functions in your Reports and Dashboards

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Longfellow

Speaker: Philip Taylor

Title: Exploiting Excel Cube Functions in your Reports and Dashboards

Abstract:

A best kept secret, cube functions retrieve data from Analysis Services cubes and Power Pivot data models. Flexibility of formatting and layout (any cell, anywhere) make cube functions ideal for building Excel dashboards and reports requiring very specific placement.

This session introduces the seven cube functions, while focusing on those most useful. Live demos will highlight report building techniques, practical considerations, leveraging MDX, legacy report migration, text data retrieval from Power Pivot and more.

SessionID: 25565

Power to the people!!

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Constitution

Speaker: Steve Simon

Title: Power to the people!!

Abstract:

Power Pivot and Power View provide new and innovative ways for end users to view their data and to convert this data to valuable information. Enabling and convincing end users to take ownership of their data (as Data Stewards) is half the challenge, and in practice is not all that problematic.

In this hands on presentation we shall be looking at practical ways and means of creating reports, utilizing Power Pivot and Power View. We shall take advantage of the BISM and have our data stored within a SQL Server tabular database. All examples come from actual implementations and we shall be looking at the constructive feedback provided by end users.

SessionID: 25567

SQL Server 2014 Data Access Layers

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Theoreau

Speaker: Steve Simon

Title: SQL Server 2014 Data Access Layers

Abstract:

How often have our SQL Servers been bogged down with a myriad of users issuing lengthy and poorly constructed or poorly performing queries against our tables and views.More often than not,nearly bringing our systems to their knees.Nowhere is this truer than within the financial world where heavy number crunching is par for the course.This beginners to intermediate level hands-on presentation is aimed at the SQL Server developer and business analyst alike and will show the attendee how we had managed to eliminate 90 % of our poorly performing queries by creating a data access layer which provides our end users with a suite of well developed and tuned SQL Server FUNCTIONS and UDT's with which to render their data.

SessionID: 25570

A dive into Data Quality Services

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Constitution

Speaker: Steve Simon

Title: A dive into Data Quality Services

Abstract:

SQL Server 2014: Data quality and data cleansing have always been major challenges to any enterprise that deals with data. For those of us who have dealt with Data Profiler Tasks in SQL Server 2008, we were shocked yet pleasantly surprised with the great advances that Microsoft has made with the advent of Data Quality Services, in the SQL Server 2012 release. In this hands-on presentation we shall be looking at how to set up a new knowledge base, based upon an existing one, set up rules, do knowledge discovery within the new knowledge base and finally cleanse the data through a data quality project. The end results being more effective data, guaranteed to keep end users and management happy.

SessionID: 25821

Using Power BI to Deliver Self-Service BI

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Bunker Hill

Speaker: Stevo Smocilac

Title: Using Power BI to Deliver Self-Service BI

Abstract:

Over the past few years Microsoft has been investing heavily in self-service BI, and today it is possible to deliver Business Intelligence using nothing but Excel 2013.

Join us as we demonstrate how using the latest Microsoft BI toolset you can easily discover, analyze, and visualize any data. We will begin the session by using Power Query and the M language to import and shape our dataset. Then we will use Power Pivot and some basic DAX to build out a powerful in-memory data model. Lastly we will demonstrate how to present our data using Power View, and share our newly gained insights using Power BI for Office 365 and Power BI QA.

SessionID: 25882

Develop Impressive Dashboards using SSRS 2014

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Concord

Speaker: Sunil Kadimdiwan

Title: Develop Impressive Dashboards using SSRS 2014

Abstract:

Microsoft Reporting Services 2014 has many exciting features. You can show your business data on Maps, Charts including Sparklines, Data bars and Indicators. Make them interactive, so that your users can drilldown to details. In this session I will show you how can make use of these and other enhancements in SSRS 2014.

SessionID: 26138

Security for your BI data

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Longfellow

Speaker: Thomas Grohser

Title: Security for your BI data

Abstract:

How the manage security for your data during the ETL and reporting process across multiple servers and in HA/DR situations.

Handling permissions on a read only copy (readable mirror or snapshot) or individual permissions on a column are just two examples of topics this session filled with best practices will cover. After covering the protection of the raw data we will have a look at all the tools (SSRS, SSIS, SSAS, ...) and how to secure them as well.

SessionID: 9144

On-Demand Compute and Analytics with Powershell, Hadoop and Azure

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Longfellow

Speaker: Adam Jorgensen

Title: On-Demand Compute and Analytics with Powershell, Hadoop and Azure

Abstract:

This session will take the beginner in big data and show them how to use powershell, HDinsight and Microsoft's Azure platform to enable on demand analytic and compute. These capabilities are critical in the modern data warehouse environment and can only be delivered by technologies working together to create an analytic fabric that the organization can use. From developers the analysts and operations folks, this session will have something for all of you.

SessionID: 9368

Scaling Power Pivot Solutions

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Hawthorne

Speaker: Anthony Martin

Title: Scaling Power Pivot Solutions

Abstract:

Building BI solutions with Power Pivot is an excellent option for technical resources to quickly build out proof of concept solutions or for business users to self-serve their analytical needs without waiting for technical resources for assistance. This session will review options to scale Power Pivot solutions that have become critical to the organization and need to scale to increasing amounts of data and users. Power Pivot for SharePoint, Analysis Services Tabular, and Office 365 Power BI Sites will be reviewed and demoed to understand what they can bring to the table to increase scalability.

SessionID: 9643

The Modern Data Warehouse

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Constitution

Speaker: Andy Roberts

Title: The Modern Data Warehouse

Abstract:

Current models of data warehousing are being challenged by data that is coming at us bigger, faster, and more variable that it ever has before. In traditional models we would throw out much of the data or aggregate to a course grain so that we could gain query performance but may loose some of the nuance needed for deep analytics. How is Microsoft bridging the GAP between the need for speed in both data ingestion and query performance? How is Microsoft integrating the worlds of relational and non-relational data? This session will discuss aspects of Microsoft's data warehouse vision ranging from traditional SMP, our MPP appliance, what we are doing in the cloud and how it all works together.

SessionID: 9644

Azure Machine Learning Studio

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Concord

Speaker: Andy Roberts

Title: Azure Machine Learning Studio

Abstract:

Machine Learning is working its way into all of our businesses. One of the biggest challenges faced by the data science or analytic teams that perform work in predictive analytics is how to operationalize the models that they create. Microsoft Azure Machine Learning is a fully hosted machine learning platform that streamlines the process of creating, training and testing predictive models and allows you to put them into production hosted in the cloud.

In this session we will introduce the Azure ML platform, discuss some of the current uses, and understand how to overcome some of the common challenges with hosting models in the cloud.

SessionID: 9683

Securing Analysis Services Tabular

Back to calendar

Event Date: 27-09-2014 - Session time: 00:00:00 - Track: Constitution

Speaker: Anthony Martin

Title: Securing Analysis Services Tabular

Abstract:

This session will cover everything you need to know to secure your Analysis Services Tabular models. We'll start off by understanding the basics of the security model and build up to meet more advanced data security requirements that can allow a user to only see a specific subset of data in the Tabular model.