PASS Business Analytics Conference Discount from Oregon SQL

16 04 2014

The 2014 PASS Business Analytics Conference is just a few weeks away and you know you want to go to see top speakers give over 70 sessions on how to use all the new BI tools and technologies that could make you the most important person in your organization.  This year the PASS BAC is May 7-9 in San Jose, California.  You can save $150 by using this discount code provided by the Oregon SQL PASS Chapter: BA4K8P.  Enter this when prompted on the registration page.

Aside from the great content, the greatest value I get from these conferences is networking with community leaders, authors, speakers, attendees and product developers.  Please stop me and say Hi


SQL Saturday Lisbon Portugal

14 04 2014

We’re on our way home from Lisbon, Portugal after speaking at SQL Saturday #267.  Having had the pleasure to attend and speak at quite a few SQL Saturday events over the years, I’ve never seen a group of event organizers work so tirelessly with as much passion and dedication.  Thanks to Niko Neugebauer, Vitor Pombeiro, Paulo Matos, Quilson Antunes, Murilo Miranda, André Batista and Paulo Borges for the late nights and long hours you all invested to make this a very special SQL Saturday.  The event was a great success; as well as a special day of sightseeing the day afterward for all the speakers.  After recruiting an impressive list of speakers from all over the globe, these volunteers went well beyond the call of duty to chauffer speakers from the airport to hotels, the event venues and around the city.  It was quite a treat.  Thank you!

SQL Saturday Preconference Optional System Requirements

7 04 2014

If you are attending the preconference session on Building a Complete BI Solution on April 11 in Lisbon at SQL Saturday #267, I will provide all of the project and data files that I will be using at the beginning of the session.  You are welcome to follow-along with the finished project files but this is not a requirement to attend the session.  You are also welcome to simply attend and take notes and then you can download the samples later.

We will be using a fairly large volume of data for a laptop machine which increases the system requirement but you can load a subset of the data files if you prefer.  There are two different demo solutions and the system requirements are different for each.

system requirements

participation options:

  • watch & take notes
  • download workshop files & work with them later
  • follow along with all or part of the demonstrations, using the finished solutions*

Personal BI solution

  • Office 2013/365 ProPlus edition
  • Power Query
  • Power Map

Enterprise BI solution

  • SQL Server 2012 Developer edition
    all features installed
  • SSAS tabular instance
  • SSRS in native mode
  • 20 – 40 GB of disk space
    (whether you load part or all data)

* please use provided solutions rather than building as you go.  we can’t provide support or troubleshooting for your development environment.

Creating a Business Intelligence Solution with D&B Business Data & Excel 2013 BI Tools

26 03 2014

This series of video sessions will demonstrate how to create a business intelligence solution with sets of business data from multiple sources.  In the first session, I import business records from 5 different D&B data feeds using the Windows Azure Marketplace with desktop BI tools in Excel 2013 ProPlus.  In future sessions, I will combine public data sets, use features of Power BI and migrate to an enterprise scale BI solution with SQL Server, SSAS tabular and SharePoint.

D&B data fees in the Windows Azure Marketplace can be used to get detailed, current demographic and financial information about specific businesses or companies in different regions and categories.  These business records can be used for things like market analysis, sales, verification and research.

Session 1 – Getting started (48:00)
Analyzing Dunn & Bradstreet business records data from the Windows Azure Marketplace
using Excel 2013 Power Query, Power Pivot and Power View to create a basic dashboard

To watch this video with more playback controls, you can also view it on ScreenCast here.

Session 2 – Enhancing the Solution (32:00)
Combining Dunn & Bradstreet Customer Records with Public & Enterprise Data
Geographic visualization with Power Map, publishing to Power BI and using Q&A to search a model with natural language



Digg This

Sessions at SQL Saturday, Lisbon April 11 & 12

22 03 2014

If you plan to attend SQL Saturday 267 in Lisbon, Portugal; I hope you can join me.  My three presentations  include a full-day preconference on Friday, the 11th of April on Building a Complete BI Solution with Office & SQL Server.  There is still plenty of time to register.

On Saturday, the 12th of April, I have a session at 3:30 PM titled Visualization Choices: What, When, and How? and at 5:00 PM titled Tabular Models: Easier & Faster Than Cubes; Really?

Here are the preconference details:

Building a Complete Business Intelligence Solution with Microsoft Office 2013 & SQL Server 2012

This full-day, interactive demonstration will show attendees how to use Business Intelligence tools to create a complete solution delivering dashboards, self-service and mobile BI.  We will create a solution with and without SharePoint integration using on-premises data sources.

Attendees should have a basic knowledge of SQL Server design and management tools and should know how to use Office Excel 2013.  At the conclusion, attendees will know how to use SQL Server Data Tools (SSDT), the Excel PowerPivot add-in, Report Builder and Power View to create and deliver dashboard and self-service reports.  We will also demonstrate the use of 3rd-party tools to deliver mobile extensions to an on premise BI solution.

BI Solution Components

Begin with a brief and high-level introduction to the components of traditional BI solutions to help attendees appreciate the usual scale and cost.  Discuss the options to scale these items for small and large projects.  Discuss the trade-offs for each and the value of centralized solutions, data quality and master data management.  Discuss the challenges of designing, building and maintaining large-scale BI solutions.

  • Data loading & transformation (ETL using SSIS)
  • Staging (SQL Server relational)
  • Dimensional model (SQL Server relational)
  • Semantic model (SSAS Tabular)
  • Report portal (SharePoint & native)
  • Dashboards (SSRS & Power View)
  • Reports (SSRS & other tools)

Introducing Self-Service Semantic Models

  • Introduce xVelocity (briefly compare PowerPivot & Tabular SSAS)
  • Introduce the PowerPivot add-in
  • Importing data
  • Accessing & integrating data sources
  • Add an Excel linked table
  • Add relational, multidimensional & text files
  • Use an SSRS report Atom Feed

Build a PowerPivot Model

  • Import tables from multiple sources
  • Model relationships
  • Set table & column properties
  • Create calculated columns
  • Create simple calculated measures using auto sums
  • Browse the model using an Excel pivot table
  • Set data types, formatting & basic content types

Advanced PowerPivot Properties

  • Create KPIs
  • Create hierarchies
  • Browse the model using Excel

Sharing Models & Collaboration

  • Deploy the Excel model to SharePoint
  • Discuss settings, security & reporting options
  • Demonstrate the PowerPivot Gallery
  • Refresh options & scheduling

Introduce Power View

  • Create basic reports in Power View
  • Add tables, matrices, column & bar charts
  • Convert tables to slicers & analyze measures
  • Add a KPI
  • Use a hierarchy to drill-down in a Power View matrix

Use Advanced PowerPivot Features to Enable Power View Visuals

  • Add geographic content types
  • Add date part content types & a hierarchy
  • Create a map visual with a geographic hierarchy
  • Visualize geographic hierarchies with a drill-down map
  • Visualize data points using latitude & longitude
  • Add Power View filters
  • Add multiples
  • Add cards & panels
  • Create multiple views in Power View
  • Style a Power

Introducing DAX Calculations

  • Relationship navigation
  • Contextual filters
  • Time intelligence functions
  • Advanced & conditional aggregation
  • Add a time intelligence calculation (rolling summary, parallel period, etc.)
  • Create a hierarchy with calculated date columns
  • Browse the added calculations & columns using Power View

Using Excel as a BI Dashboard & Reporting Tool

  • Use the Excel pivot table with measures & calculations
  • Use advanced pivot charts
  • Add slicers
  • Conditional formatting rules & visuals
  • KPI indicators
  • Deploy Excel to SharePoint and open report in browser
  • Discuss sharing & collaboration options for Excel Services

Tool Choices & Criteria

  • Solution options with SharePoint and without SharePoint
  • Discuss the criteria for choosing among different tools for browsing & presenting results. 
  • Create a dashboard using SSRS Report Builder 
    • Create dashboard elements using Report Builder 
      • Balanced scorecard with KPI indicators & drill-down
      • Gauge panel (bullet graphs)
      • Column or line chart for trending & comparisons
  • Use Power View to create a dashboard
  • Considerations for mobile dashboard design & self-service using tablet devices (iPad & Windows Surface)

Enterprise Scale

  • Migrating PowerPivot to a tabular model
  • Introduce partitioning
  • Introduce SSAS security
  • Introduce perspectives
  • Discuss advanced business solution scenarios

Final Steps & Summary

  • IT & business partnering, support tasks 
  • Business user education
  • Report sandboxing & branding
  • Periodic report review & consolidation
  • Planning your first implementation
Digg This

SQL Server and BI Events in San Jose, California

13 03 2014

San Jose is the place to be for two big events this spring.  SQL Saturday Silicon Valley (#276) is this weekend, March 15.  Current registrations will make this one of the largest SQL Saturday events in history.  Follow #SqlSat276 and go to for event information, session schedules and downloadable content.

Join the BI Expert Panel Group on LinkedIn

Ask us your tough BI questions and we’ll use them for discussion in our BI Expert Panel session.


The PASS Business Analytics Conference is also coming to San Jose on May 7-9 and will be the premier event for the Business Intelligence and Business Analytics communities.  Please join us as we bring the community together once again.

Digg This


Get every new post delivered to your Inbox.

Join 216 other followers