Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for August, 2012

Minnesota Microsoft BI User Group 2012 Q3 Meeting–Sept 5

Posted by denglishbi on August 30, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

The next MN Microsoft Business Intelligence User Group is coming up next week on Wednesday, September 5. This meeting we will have two sessions starting out with a talk on Tabular Analysis Services and then a presentation about Big Data. Just a reminder that the location for this event is at the Microsoft Technology Center location in Edina.

MN Microsoft BI User Group 2012 Q3 Meetingimage

Sponsored by:

Microsoft - Annual Sponsor

Date: Wednesday, September 5, 2012

Check In: 2:30 PM Event Time: 3:00 PM – 6:00 PM

Location: Microsoft Technology Center – 3601 76th St W, Suite 600 Edina MN 55435 (in LOW 1, 2, and 3)

To join via Lync Click Here

Please Register Now


  • 2:30-3:00 : Registration
  • 3:00-3:15 : Microsoft BI User Group Updates & Announcements
  • 3:15-4:15 : Tabular BI Semantic Model – What? When? Where? Why? How? (William Weber)
  • 4:15-4:30 : Break
  • 4:30-5:30 : Big Data Buzzwords (Lara Rubbelke)
  • 5:30-6:00 : Social and Networking


Tabular BI Semantic Model – What? When? Where? Why? How? – When we’re building a new business intelligence solution we have a lot of options in SQL 2012. PowerPivot in Excel or SharePoint. SSAS multidimensional or tabular. When do we choose a tabular model over a multidimensional model, and why? What, if anything, do we sacrifice in doing so? How do we get started – through a promoted PowerPivot model or a new SQL Server Data Tools (SSDT) project? Let’s take one scenario – a new tabular model started from brand new SSDT project – and walk through the decision making and development process. Not every tabular BI solution has to start in PowerPivot. Tabular models are a first-class citizen in SQL 2012, with all sorts of bells and whistles that we’ve typically reserved for multidimensional solutions. We’ll start with a new project, talk about storage and query access models, security, end-user reporting and walk-through building it all. And if there’s time left, we’ll take a quick look at a couple of additional tools that can really help along the way.

William Weber (@WilliamWeber) is a business intelligence consultant at RBA Inc. He started his career as a professional juggler. Almost got a degree in Theatre. Actually got a degree in Chinese. And now builds business intelligence solutions for clients all around the Twin Cities.

Big Data Buzzwords – Do terms like Oozie make you woozie? Does Pig make you grunt? Are your customers buzzing about Hive? Hadoop has many data professionals trying to understand the new world of technologies to help deploy, develop, manage and integrate these new data sources. In this session we will make sense of these and other Big Data terms and tools, helping you understand the new world of Hadoop.

Lara Rubbelke (@SQLGal) brings her passion for architecting, implementing and improving data solutions to the community as a Principal Program Manager for Microsoft. Her expertise involves Big Data, data management, security and compliance, ETL, and the Business Intelligence lifecycle. She regularly delivers technical presentations at local, regional and national conferences, user groups and webcasts.

Please Register Now

Hope to see you there!

Posted in Business Intelligence, Training | Tagged: , | Leave a Comment »

24 Hours of PASS Fall 2012

Posted by denglishbi on August 28, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

Are you ready for some free SQL Server training? A little warm up for the PASS Summit in November? Well you are in luck, coming up in September is the next 24 Hours of PASS.  There are six tracks, 24 sessions, and 27 speakers!  The Knights kick off the event with a great BI session on “Choosing the Right Reporting Platform”.24 Hours of PASS

Session Track List

BI Platform Architecture, Development & Administration (BIA)

Enterprise Database Administration (DBA)

Application Development (AppDev)

BI Information Delivery (BID)

Cloud Application Development & Deployment (CLD)

Professional Development (PD)

Session 01 (BIA) – Starts at 12:00 GMT, Sept 20
Choosing the Right Reporting Platform
Presenter: Brian Knight, Devin Knight

Session 02 (DBA) – Starts at 13:00 GMT, Sept 20
Best Practices for Upgrading to SQL Server 2012
Presenters: Robert Davis

Session 03 (AppDev) – Starts at 14:00 GMT, Sept 20
Three Ways to Identify Slow Running Queries
Presenter: Grant Fritchey


Session 09 (BID) – Starts at 20:00 GMT, Sept 20
Mobile Business Intelligence
Presenter: Jen Underwood


Session 05 (CLD) – Starts at 16:00 GMT, Sept 20
SQL Server Private Cloud = Azure
Presenter: Allan Hirt, Ben DeBow

Session 12 (PD) – Starts at 23:00 GMT, Sept 20
Leadership – Winning Influence in IT Teams
Presenter: Kevin Kline

Session 13 (BIA) – Starts at 00:00 GMT, Sept 21
BI Architecture with SQL 2012 & SharePoint 2010
Presenter: Rod Colledge

Session 08 (DBA) – Starts at 19:00 GMT, Sept 20
PowerShell 101 for the SQL Server DBA
Presenters: Allen White

Session 04 (AppDev) – Starts at 15:00 GMT, Sept 20
Fasten Your Seatbelt – Troubleshooting the Most Difficult SQL Server Problems
Presenter: Klaus Aschenbrenner

Session 10 (BID) – Starts at 21:00 GMT, Sept 20
Slow MDX Queries: The Case of the Empty Tuples
Presenter: Stacia Misner

Session 23 (CLD) – Starts at 10:00 GMT, Sept 21
Best Practices and Lessons Learned Using SSIS for Large Scale Azure Data Movement
Presenter: Steven Howard

Session 24 (PD) – Starts at 11:00 GMT, Sept 21
Mentoring for Professional Development
Presenter: Andy Warren

Session 15 (BIA) – Starts at 02:00 GMT, Sept 21
SSIS Design Patterns for Fun and Profit
Presenter: Jessica Moss, Michelle Ufford

Session 11 (DBA) – Starts at 22:00 GMT, Sept 20
Using SQL Server 2012 Always On
Presenters: Denny Cherry

Session 06 (AppDev) – Starts at 17:00 GMT, Sept 20
What are the Largest SQL Server Projects in the World?
Presenter: Kevin Cox

Session 22 (BID) – Starts at 09:00 GMT, Sept 21
Big Data Analytics with PowerPivot and Power View
Presenter: Peter Myers


Session 17 (BIA) – Starts at 04:00 GMT, Sept 21
What’s All the Buzz about Hadoop and Hive?
Presenter: Cindy Gross

Session 14 (DBA) – Starts at 01:00 GMT, Sept 21
DBCC, Statistics, and You
Presenters: Erin Stellato

Session 07 (AppDev) – Starts at 18:00 GMT, Sept 20
Practical Demos of Text Mining and Data Mining using SQL Server 2012
Presenter: Mark Tabladillo


Session 19 (BIA) – Starts at 06:00 GMT, Sept 21
Agile Data Warehousing with SQL Server 2012
Presenter: Davide Mauri


Session 16 (AppDev) – Starts at 03:00 GMT, Sept. 21
Characteristics of a Great Relational Database
Presenter: Louis Davidson




Session 21 (BIA) – Starts at 08:00 GMT, Sept 21
Introduction to Microsoft’s Big Data Platform and Hadoop Primer
Presenter: Denny Lee


Session 18 (AppDev) – Starts at 05:00 GMT, Sept 21
Taking SQL Server Into the Beyond Relational Realm
Presenter: Michael Rys


Session 20 (AppDev) – Starts at 07:00 GMT, Sept 21
Digging Into the Plan Cache
Presenter: Jason Strate


This is an outstanding lineup and a real all star cast to boot.  You will not want to miss this opportunity to hear these speakers, head on over and register for these sessions now and get them on your calendar.

Session Track List

Posted in Training | Tagged: , | Leave a Comment »

PASSMN August 2012 Meeting

Posted by denglishbi on August 20, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

The next Minnesota SQL Server User Group meeting is tomorrow! This month Mark Vaillancourt (@MarkVSQL) will be talking about MDX (Multidimensional Expressions).  Be sure to register so that your name badge will be available for you at the Microsoft Technology Center when you arrive.

Minnesota SQL Server Users Group
The sponsor for this month’s meeting is Digineer.
Location: 3601 West 76th Street, Suite 600 Edina, MN 55437

Date/Time: Tuesday, August 21 from 4 to 6 p.m.

Live Meeting:
•URL: https://www.livemeeting.com/cc/usergroups/join?id=W7WDDT&role=attend&pw=3%5BncxTw%28n
•Meeting ID: W7WDDT
•Entry Code: 3[ncxTw(n
•4:00-4:15 : Registration / hospitality / networking
•4:15-5:45 : MDX Trek: First Contact – Mark Vaillancourt 
•5:45-6:00 : Announcements and prize giveaways
Please click here for meeting details and to RSVP for the event

MDX Trek: First Contact – 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.
Mark Vaillancourt is an Information Management consultant for Digineer, a Microsoft partner based in Plymouth, MN. He has been working with SQL Server for 5 years and holds the MCITP – Business Intelligence Developer 2008 certification. With a background in Theater and Improvised Comedy, Mark makes sure that his presentations are both informative and fun.

Posted in Training | Tagged: , , | Leave a Comment »

Visualizing Data with Power View: Pan-Geo goes PowerPivot

Posted by denglishbi on August 9, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

Back in June we released our book on Power View, Visualizing Data with Microsoft Power View.  Not only does the book contain great information about creating reports with Power View, but it also contains content on building tabular models that can be utilized with Power View.  The current release only works with tabular models, so either PowerPivot or Tabular Analysis Services (SSAS).  With our book we primarily focused on building out a Tabular SSAS solution that gets used for the majority of the Learn By Doing exercises.  Sure there are a few times where a PowerPivot file is referenced or we do some initial modeling in PowerPivot, but most of the work gets done with the tabular solution that gets deployed to Analysis Services.image

Well just a few weeks ago when Office 2013 Preview was released I decided to build out the entire Pan-Geo Hospitality & Travel model in PowerPivot.  I went ahead and replicated all of the exercises that we did in the tabular project into PowerPivot (using Excel 2010 and the SQL 2012 PowerPivot add-in).  I then took that same file and upgraded it up to Excel 2013 as well – Power View Meet Excel 2013.


So since I took the time to build this out I decided why not go ahead and share:)

If you are interested in Power View and especially if you have already purchased the book (hint, hint) I have gone ahead and uploaded the two Excel files to my SkyDrive here – Power View Book.

  • PGHT Tourism PowerPivot Model 2010.xlsx (Excel 2010 uses the SQL 2012 PowerPivot add-in)
  • PGHT Tourism PowerPivot Model 2013.xlsx (Excel 2013 uses the PowerPivot for Excel 2013 add-in)

Now in order to get a good download of these don’t open the Excel files up in the browser in SkyDrive, simply check the box next to the file and chose the download option on the right-hand side of the screen.  I would do them separately.  Then simply open them up and have at it.  Now if you want to use Power View in Excel you will need to download and install the Office 2013 Preview.  If you want to build out map reports with that you will need to make sure you have an Internet connection so that it can connect to the Bing Map service.

I would be very interested in some feedback and seeing what people build out with the PGHT dataset.  So feel free to leave a comment here or send me a tweet (@denglishbi).

Just an FYI, the Power View book is available on McGraw-Hill Professional, Amazon, Barnes & Noble, Google Books, as well as Kindle, Nook, and other eBook formats and sites.  Please check it out and leave us your feedback and comments:)


Posted in PowerPivot, Reporting Services | Tagged: , , , | 2 Comments »

Pre-Cons at Minnesota SQL Saturday 149

Posted by denglishbi on August 8, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

This year for the first time not only is the Minnesota SQL Saturday on a Saturday, but we are also going to have pre-con events the day before!

Pre-con events are all day long events limited to 20 people for each session and they cost $99.  There will be four pre-cons, two database and two business intelligence related.  If you register for a pre-con you will receive your lunch for the SQL Saturday event for free.  If you have already paid you will be refunded your $10 charge.image


BI1 – Dan English & Brian Larson:  Unlocking Insight – Be A Data Hero

Today, gaining insight from an organization’s data is not a luxury, but a necessity – no matter what that organization’s size and specialty. This all-day pre-conference session will equip DBAs, developers, and BI professionals alike to utilize the powerful data analysis arsenal offered by Microsoft. Together we will learn to use tools such as PowerPivot, Power View, Reporting Services, and the Tabular BI Semantic model in an agile approach that minimizes time to insight and maximizes return on investment. Along the way we’ll explore methods to empower subject matter experts making them partners in this pursuit.

By the end of the day you’ll discover that you don’t need to be a full-time BI specialist to be your organization’s Data Hero!

BI2 – Steve Hughes: Exploring Analytic Data Storage Options On SQL Server

With the release of PowerPivot and SQL Server 2012, Microsoft expanded their analytic data storage options.  Now with in-memory database support in SQL Server 2012, three analytic storage options exist.  This preconference session will explore all three – SQL Server Analysis Services Multidimensional Model, SQL Server Analysis Services Tabular Model, and SQL Server PowerPivot.  In each case, you will learn about design considerations, system impact, performance tuning and data visualization usage within each storage choice.  As part of the session, we will do a quick build with each tool to understand the development differences as well.  Armed with this information, we will compare the platforms and make the case for using each of the options in your environment.  Whether you are new to Analysis Services or have worked extensively with it, this is a great opportunity to learn about the new choices and how they will impact your environment, both from a capability and maintenance perspective.

DB1 – Ted Krueger: Make Me A Tuning Believer

Are you an accidental DBA or developer that has been told to make a T-SQL query run faster?  How many times have you heard complaints from the business about a report taking 5 minutes, 10 minutes, or longer to run?  Did you go to a class, training, or past session and find yourself lost in the mix of execution plan operations and indexing and scans?

Start over. Come to this class to build your query and execution plan tuning skills from beginning to end.  We’ll focus on the everyday DBA’s and developer’s needs for tuning, and build up to more advanced methods which can be taken directly to work and bring immediate improvements in performance.

The workshop will show you skills that revolve around what you will see most commonly in an everyday database shop, such as:
•The life cycle of a transaction
•Preventing bad T-SQL coding practices
•Reading an Execution Plan
•Plan Cache and Monitoring
•Execution Plan Tuning
•Capturing bad execution plans that need to be tuned

DB2 – Jason Strate: Indexing For Performance

This pre-con is a full day session that looks at indexing from the basics down to the internals. With guidance on how to collect statistics on indexes and use that information to drive changes to indexing.

Indexing is an important aspect of maintaining an well-performing database. Without the proper indexes, database will not perform to their potential and users will inevitable help you share their pain. In this session, we’ll take a deep dive into indexes and how they work within the database. We’ll look at the indexes types available to the statistics collected on the indexes. From there, we’ll dig deeper into the internals, maintenance, and tools available for indexing your databases. The session concludes with a walk through of a process for collecting index statistics and using them to validate the indexes in place and drive changes that are needed.


Sessions BI1 and DB2 will be held at Benchmark Learning in Edina.  Sessions BI2 and DB1 will be held at the Doubletree by Hilton, Bloomington.

Please direct any questions regarding the pre-cons to the SQL Saturday #149 team at sqlsaturday149@sqlsaturday.com

Posted in Training | Tagged: , , | Leave a Comment »

SQL Saturday Event Analysis using Power View and Office 2013

Posted by denglishbi on August 6, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

Updated (8/7/2012): Added screenshot at end of post of which includes data broken out by fiscal calendar (starts in July) and link to that file as well.

Updated (8/9/2012): Added some information at the bottom of the post in regards to downloading issues and more information available in the comments section.

Last week I saw a post by Sam Vanga called SQL Saturday US Map.  Needless to say I found it interesting and a great idea.  That made me think a bit and I was like hmm… why not take that same data and use it with the new Excel 2013 with PowerPivot, Power View, and maybe even a little Office 365!  I had to leverage the Microsoft stack of course:)

So I went out to the SQL Saturday site and copied down all of the event details. Needless to say a little scrubbing was required because the data was a bit incomplete to get at some of the location information.  After a little while I felt fairly good with the data and loaded the information into PowerPivot for Excel 2013.  I added a few more columns around the date information to generate a calendar and to do some sorting.  Once that was done I went ahead and started to put together a little dashboard page.  I ended up with the following:


We can see that the SQL Saturday events over the past couple of years have really grown in popularity and after 2010 they went global as well.


We can zoom in on the map and get a close up as well.  Last year we had the SQL Saturday #99 in Eden Prairie, MN.


I even added the ability to drill down from the cities to the zip code level and also some drill down on the column chart for the counts as well.



Some pretty cool stuff.

We can even upload this into the Office 365 preview and share this information as well which support PowerPivot and Power View.


You might notice that the colors for the themes don’t match up quite yet, but other than that it works out well and you can interact with the Power View visualizations and reports!  In the above screenshot in Office 365 I have the tile where the map resides filtered to 2012.  You can see that the map really opens up now for the entire globe compared to the 2010 and 2011 views.  I can only assume that next year it will grow even more:)

The data that I grabbed for this was from the SQL Saturday events page here (just copy paste and a bit of cleanup).

If you would like to download the Excel 2013 file I created you can grab it from here – SQLData.xlsx

Fiscal Year Layout

I received a request from Karla Landrum the PASS Community Evangelist to see the data setup using the PASS fiscal calendar which starts in July versus the regular calendar.  Well I decided to add in a full date table and refresh the output.  Here is the result and the new file can be downloaded here – SQLDataFiscal.xlsx


If you are experiencing any issues with the downloads take a look at the comments section of this post.  I did some testing and found some issues if you try to directly download the file from the browser and a workaround if the SQLSat Dash does not load for you.  You can download the file(s) from my Public|Blog SkyDrive folder.


Posted in PowerPivot, Reporting Services | Tagged: , , , | 15 Comments »