PowerPivot and Excel 2013 Overview Slides

Here are the slides for my recent presentation that I did recently, which was organised by Dell. Unfortunately, for some reason the recording didn’t turn out properly. However, the slides are available here for download:

http://static.slidesharecdn.com/swf/ssplayer2.swf?doc=questionpowerpivotspiderman-120729174846-phpapp02&stripped_title=powerpivot-and-excel-in-office-2013&userName=jenstirrup

Any questions, please do get in touch. I will try to arrange another webinar in the future, so that I can share the information in a timezone that suits people better. I’ll advertise when that will be.

Many thanks to Richard for his help in organising this event.

SQL Server 2008 R2 Service Pack 2 (SP2) is Released!

Note the release of SQL Server 2008 R2 Service Pack 2 (SP2). Both the Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. 
This a free upgrade, so please head over to the Download Center for a look.
This particular pack is interesting because it contains a few key community Reporting Services requests

  • Reporting Services Charts Maybe Zoomed & Cropped
    Customers using Reporting Services on Windows 7 may occasionally find charts are zoomed in and cropped. To work around the issue some customers set ImageConsolidation to false.This is tackled in the Service Pack
  • Batch Containing Alter Table not Cached
    In certain situations with batch files containing the alter table command, the entire batch file is not cached.
  • Collapsing Cells or Rows, If Hidden Render Incorrectly
  • Some customers who have hidden rows in their Reporting Services reports may have noticed rendering issues when cells or rows are collapsed. When writing a hidden row, the Style attribute is opened to write a height attribute. If the attribute is empty and the width should not be zero.

I’m holding a Business Intelligence and Data Visualisation Precon!

I’m delighted to announce that I’m holding a pre-conference one-day training in Data Visualisation in SQL Server 2012 in Cambridge on 7th September, 2012. For more details, and to register for the precon, please click here and to register for SQL Saturday 162 event, please click here.
My thanks go to SQLPass and to Mark Broadbent in particular, who have worked extremely hard in order to put on this community event. Here is an outline for my precon and please don’t hesitate to get in touch if you have any other queries!
If you want to conduct advanced, business-oriented Business Intelligence analysis in SQL Server 2012, then it is essential to understand data visualisation.

Using the new SQL Server 2012 Tabular Model and PowerPivot as data sources, this course will aim to teach about the new self-service Business Intelligence features in SQL Server 2012 whilst focusing on data visualisation. We will also look at SharePoint, and what it can offer.

This session is aimed at Excel and/or Business Intelligence developers who want to make informed data visualisation decisions about reporting, with supporting cognitive psychology theory where relevant. The takeaways will focus on:

  • Finding patterns in the data. 
  • Further Data Visualisations – learn about visualisations that are perhaps not so well-known including Stephen Few’s bullet charts and Tufte’s Sparklines in SSRS.
  • The complexities of displaying multivariate data. For example, we will look at Tufte’s “small multiples” in Power View and in Reporting Services.
  • Putting it all together: Considerations for Dashboards with PerformancePoint
9:00   Welcome and What is data visualisation? What it is not, and why is it important? What products make up the SQL Server 2012 Business Intelligence stack?
09:30  Tabular Model – what is it? When is it best used? What distinguishes it from multidimensional cubes?
10:30   PowerPivot – what is it? When is it best used? What distinguishes it from multidimensional cubes? What are the new features in SQL Server 2012?
11:15     Break
11:30   Finding patterns in the data. You will learn about the process involved in finding patterns in the data, looking at some of the more well-known data visualisation examples. 

 – We will learn about: Power View, using PowerPivot and the Tabular model as a basis. 
 – We will also look at tips and tricks in optimising Tabular Models so that they can render Power View effectively.

12:30     Lunch
13:30  Further Data Visualisations. In this section, you will learn about visualisations and how they are best used. This includes bullet charts, and other visualisations based on Tufte’s “small multiples” such as sparklines. We will learn:

 – We will look at PerformancePoint, and how we can use PowerPivot as a basis and ‘gotchas’ in using PowerPivot as a basis for PerformancePoint
 – We will look at Excel Services, and how we can provision self-service using the Tabular Model and PowerPivot as the basis for Excel and Excel Services
–   We will look at other helpful visualisations such as sparklines, bullet charts and marimekko charts.

15:00 Multivariate statistical data. In this section, we will cover the complexities of displaying multivariate data since is potentially more complex. Here, we look at ways of displaying multivariate data such as table lenses and crosstab arrangements of Tufte’s ‘small multiples’. We will use Reporting Services as a technology to surface multi-dimensional data.
15:15 Break
16:15 Dashboards – Putting it all together. We will look at different ways of implementing Dashboards, KPIs and other visualisations. This will involve a range of technologies, from KPIs in PowerPivot, to the new features in Power View.
17:30 Close

Are SQLPass right to hold a Business Analytics conference?

Recently, the Professional Association of SQL Server announced that they were planning to hold a Business Analytics Conference in the spring of 2013. I read the announcement, and I’m super-excited about it for a number of reasons.
I’m happy that there is a demand for a subject which I’m passionate about, Business Analytics and Business Intelligence. If you’ve ever tried to get into some of the popular Business Intelligence sessions at SQLPass Summit, then you’ll know that sometimes you can’t get in the door since there’s no room. In other words, the Business Intelligence and Analytics sessions are just mobbed. I know that I’ve repeated Business Intelligence sessions at SQLBits and at TechEd Europe, since people want to know about Business Intelligence. Other people who’ve repeated sessions include Peter Myers, for example. For TechEd Europe, the Business Intelligence ‘Power Hour’ was one of the top sessions out of a whole gamut of IT-professional oriented topics.
I believe that PASS are responding to community demand for a Business Analytics event, and I’d personally welcome a ‘gathering’ of people who are excited about this subject as I am.
I believe in this Business Analytics event…. but then it became clear that there was a lot of genuine anger as well. Some seemed to say that SQLRally was chopped to make way for this Business Analytics event, and let’s be fair, some people are real SQLRally fans – myself included. Some even went as far as to argue that this was just a money-making machine since Business Intelligence is a popular topic, which I think is a really negative way to look at it.
All this stramash genuinely made me sad. I was in two minds whether to write this blog, and then I thought that I’ve the same right to express my opinion, and others are free to disagree or agree as they see fit.
I think that, if you want to make serious money, you’d be nuts to put on a SQL Server conference as a way to do it. If you look at the details of the Budget, we’re not talking millions of pounds. The profit is $100K, which will flow back into their reserves. You have to have money aside in order to set up events such as SQLPass Summit; for example the event venue will want security and paid up front; you’ll have to pay for other things such as advertising, marketing and the other things that will encourage people to participate.
All the ‘goody bags’ that people are eager to open: someone has got to pay for those. The meals that we all enjoy; the free beers that we expect; the events we expect to attend out-of-hours; the prizes, the raffles etc etc. As an audience, we have high expectations from these events. Have you heard people whinge about goody bags and food? See what I mean?

 

What I’d really like to see is PASS do something that doesn’t come with a ton of baggage. A clean start. Remember that a lot of these events and hard community work – and it is hard work, preparation and sweat – comes from volunteers. For example, some people, including myself! flew from here in London to Dallas to present at SQLRally – at our own expense, away from our families, and volunteered our time for our sqlfamily. It is my privilege to be flying out for SQLPass Summit in November 2012 in order to see my favourite SQLFamily members, old friends and new community members alike.
The volunteering doesn’t just end with the events: there are webinars and user groups and chapter meetings and online events.. the list goes on.
So, I’d like to say a huge Thank You to the volunteers who put on this massive effort for us, the sqlfamily, the sql server community. I think that I’d like to see more ‘thank you’ to the volunteers, and to PASS… and less politics. I’m hoping that this Business Analytics event will go ahead with a real community spirit in place, with people behind it. Remember that some of these attendees will be novices, who are looking to learn and share in the community. You could be sitting next to the next Brent Ozar or Kalen Delaney – who knows?
I think that people just need to be given a chance sometimes, and I wish the organisers and volunteers at PASS my very best wishes and thanks for all of their hard work in putting this event together for us, the sql family. As Mother Theresa said, Peace starts with a smile…. and this is a smile from me.

To summarise, I think that PASS are right to hold this event. The sheer numbers of BI people at events – not just SQLPass, but SQLBits and TechEd too – are testament to the fact that there is a real community need for a BI ‘gathering’.  I hope that you will start to become as super-excited as I am, and I hope to see you there!

Data Whistleblowers: does your organisation need one?

Does your organisation need data ‘whistleblowers’? You may think that there’s enough noise about data as it is.  This blog is about trying to let data quality permeate through the whole organisation by proposing that organisations sometimes need a data ‘whistleblower’ to tackle the issues.

What is a whistleblower? Well, a data whistleblower is basically an individual who raises issues, sometimes subversively.

I see a data ‘whistlerblower’ as a ‘go to’ person, to whom employees can raise issues around data quality.  It’s basically a role, which denotes someone whom anyone can go to, in order to express a confidential concern about the quality of a piece data. There could be more than one whistleblower, for example; you could have one for each department.

Alternatively, team members could take turns to be the data whistleblower. This might help to promote adoption of data as a corporate-wide asset, in which it is everyone’s interest to protect and maintain assiduously.

There are different ways in which this role could function. As a technical function, it could be as simple as setting up a company SkyDrive account where people can go and record data quality issues anonymously. Alternatively, a more formal approach could be taken, whereby the data ‘whistleblower’ will take the collated data issues to a monthly business intelligence meeting. Results and feedback regarding data quality issues could be given via a SharePoint portal, or a monthly email newsletter.

Why might you need a data whistleblower? In my experience, I’ve seen cases where team members don’t feel that they can raise data quality issues with source data owners, because it is simply too political and contentious, and they don’t want to be in the firing line.  This could be an indicator of the ‘Anger’ stages in Data Quality: please read Jim Harris’ blog for more details on the Data Quality stages. 

I’ve also seen cases of the Bystander Effect in data quality. The ‘Bystander Effect’ is where people don’t intervene to help when they see a problem, perhaps because they think that the problem is so well-known, or highly-visible, that someone somewhere must be doing something about it. In other words, they might see a problem and do nothing about it, because of the dissemination of responsibility throughout the organisation. 

What would a ‘whistleblower’ role mean to a business? It would allow users to become more involved in the data quality issues in an organisation, thereby allowing the ‘business’ in business intelligence to have a greater say in shaping their own data. It also means that data quality, which should be a corporate habit rather than a one-off project, can be made a part of the business culture.

Data is a corporate asset that belongs to everybody, so everyone can help to look after it without risking their own comfort in the workplace; yes, data quality can be that contentious! The idea of a data ‘whistleblower’ is to try and find a way through it. If you have any other ideas, I’d love to hear about them!

Some Thoughts on Power View in Office 2013 Preview Install Sequence

I’ve successfully installed Office 2013 Preview on a Virtual Machine, which is running Windows 2008 R2 Enterprise Server. And I have to say – I LOVE it, and I LOVE having PowerView embedded in Excel. I wasn’t sure if it was really there, so I downloaded it and tried it out for myself. And there it is! I’ve been producing Power View reports this morning, saved to SkyDrive, and all within Excel. You’ve no idea how happy that makes me!

Given my happiness at finally seeing PowerView in Excel, I had to nitpick.  However, I did have two thoughts about the install sequence:

There seems to be no ‘dependency checker’ prior to the installation sequence.

.NET Framework Dependency

Power View (and many other things as well!) needs the Microsoft .NET framework, version 3.5 as a minimum. You can install Office 2013 without the .NET Framework version 3.5, but you will soon get the following error message:

Silverlight Dependency

Power View in Office 2013 does still need Silverlight, but this isn’t included as part of the Installation Sequence. Perhaps this should be included as part of a ‘dependency checker’, like the .NET framework?

If you don’t install Silverlight, you will get a message in the ‘Information Bar’.

I will submit the feedback more officially via Connect, when I can see where to do it 🙂 and will post the links here.

I hope that helps!
Jen