Jen's BI Blog

Color-blindness - why does it happen, and how can data visualisations help?

noreply@blogger.com (Jen Stirrup) — Sat, 11 Feb 2012 00:11:00 +0000

How people perceive colour is an interesting issue. The Young-Helmholtz Trichromatic theory of vision proposed that we have red/green/blue receptors, which are then combined to show different colours. It is thought that the red-green receptors are close together, and perhaps this is the root of the issue. Needless to say, the issue is complex but interesting.

According to popular wisdom, it is thought that the red-green chromatic channel developed in order to provide an evolutionary advantage for determining ripe fruits against a background of foilage. Tell that to your children, next time they refuse to eat fruit! However, this 'ripe fruit' theory has been difficult to observe in field studies. One group of researchers conducted field studies in black-handed spider monkeys, and found that luminance contrast was just as important in distinguishing fruits. If you're interested to read more, here is an interesting study that illustrates the complexities of perception, which involves the field study of primates. On the other hand, a separate study showed that trichromatic primates found it easier to determine and select ripe fruits, and you can find more information here.

How does this impact data visualisation? It is possible to produce visualisations that make the most of luminosity in order to encode values, along with the size of the data point, in order to convey the message of the data visualisation. Another issue is that determining colour and luminosity can be a subjective issue, and point size may help to provide additional cues. I envisage it as if it is the detection of fruit in viticulture. Therefore, one winemaker might ascertain that a grape's optimal point of ripeness is at one point, and another viticulturist might determine that the ripeness point is at another point in time. Similarly, it isn't always easy to ask experimental subjects to ascertain the amount of 'greenness', 'redness', or 'blueness' of a point. There has been some work in computer vision, aimed at distinguishing the RGB in fruit, which is interesting to read.

It is suggested that about 12% of males are colour-blind, which means that they are restricted from using the red-green channel. If you are interested in reading more about the experience of a colour-blind person, please do read this entertaining blog by Geoffrey Hope-Terry.

To summarise, data visualisations can therefore augment understanding by assisting the perceptual processes involved in luminance and the blue-yellow colours. It is also possible to use the size of the data point to convey the message of the data. In other words, data visualisations should aim not to exclude members of the audience by including lots of red and green together.

SQLPass Business Intelligence Virtual Chapter References and Slides

noreply@blogger.com (Jen Stirrup) — Tue, 07 Feb 2012 20:41:00 +0000

Thank you to everyone who attended my SQLPass Business Intelligence Virtual Chapter webinar! I have lots of questions to follow up, from both during and after the event. Please watch this space for more responses to questions over the next few days, and I will try to ensure that I answer every question. The slides are at the bottom of this blog post.

I've provided references below to some of the Data Visualisation people and material that I mentioned today. I hope that these are useful to you.

Ben Schneiderman - Ben's Eight Golden Rules of Interface Design is an interesting read. In the presentation, I refer to one of Schneiderman's essays, "The Eyes have it." The guideline is summarise -> zoom and filter -> details-on-demand, and is known as the 'Visual Information-Seeking Mantra'.

Stephen Few - Stephen has written a number of books, which you can find at his site - there is a wealth of information there! If you're a fan of data visualisation, then you should consider his blog, Perceptual Edge, a must-read. In particular, I'd like to shout out the following books:

Now You See It: Simple Visualization Techniques for Quantitative Analysis
Information Dashboard Design

Malcolm Gladwell - Malcolm has a great deal of interesting things to say on a whole range of topics, and I'm never going to be able to introduce him properly.

Hans Rosling - What I love about Rosling's work, is that he highlights health and social factors, poverty and health. This is data visualisation in action, to help people in poor circumstances - the sort of people who cannot always read what is written about them. He promotes a 'fact-based' world view on these issues. The YouTube video of Rosling's data visualisation is below, which was made by BBC4.

I should add that I'm not in any way associated with this video - but I get asked about it a lot, so I've embedded it here from YouTube.

Here are the slides from today. I look forward to your comments, and I will answer the remaining questions in due course.

-J

Sql pass business intelligence chapter bi and data viz in sql server 2012

View more presentations from Jennifer Stirrup.

Tiny correction to MSDN Blog - adding report server content types to a Sharepoint Library

noreply@blogger.com (Jen Stirrup) — Tue, 07 Feb 2012 00:36:00 +0000

I spotted a small error in MSDN article entitled Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode) It's just a tiny error, but small enough that someone looking for it, might run into problems. The second half of the document should read:

To add report server content types

1.Open the library for which you want to add Reporting Services content types.

2.On the Library Tools ribbon tabs, click the Library tab.

3.On the Settings ribbon group, click Library Settings.

4.Under Content Types, click Add from existing site content types.

5.In the Select Content Types section, in Select site content types from, click the arrow to select Reporting Services. << This should actually say 'SQL Server Reporting Services Content Types. Please see the image below:

6.In the Available Site Content Types list, click Report Builder, and then click Add to move the selected content type to the Content types to add list.

7.To add Report Model and Report Data Source content types, repeat steps 5 and 6.

8.When you finish selecting all of the content types that you want to add, click OK.

I hope that helps someone out.
-Jen

Find the missing sessions in the 2011 SQLPass Summit DVD set

noreply@blogger.com (Jen Stirrup) — Sun, 05 Feb 2012 22:51:00 +0000

Is everyone enjoying their newly-minted 2011 PASS Summit DVD set? I am, you betcha! Some of the sessions are missing, though.

The following sessions are missing from the 2011 PASS Summit DVD:

Application and Database Development
Working With XML in SQL Server
Kendal Van Dyke, Insource Technology

SQL Azure
This Ain't Your Father's Cloud
Buck Woody, Microsoft and Kevin Kline, Quest Software

BI Architecture, Development and Administration Topics
Common Analysis Services Design Mistakes and How to Avoid Them
Devin Knight, Pragmatic Works

BI Client Reporting and Delivery Topics
End-user Alerting with SQL Server Code Name "Denali" Reporting Services
Lukasz Pawlowski, Microsoft

To stream the sessions, please go here.

You will need your PASS Summit username and password to access the sessions. If you don't have a login, then you should sign up! SQLPass offer tons of great, free SQL Server training. With SQL Server 2012 on the horizon, you don't want to miss out!

Poland SQL Day 2012 Women in Technology Panel Event

noreply@blogger.com (Jen Stirrup) — Mon, 30 Jan 2012 11:05:00 +0000

In May, 25th and 26th May, we would like to invite all of you for a ‘Women in Technology’ Panel at SQL Day2012 in Wrocław (Poland). We are looking forward to meeting you at SQLDay Poland in May 2012. I will be participating in the Panel, and I'm very excited about it!

The SQL Day 2012 conference is a great opportunity to make friends and learn with fellow SQL Family members in Poland. Therefore, we would like to add to the SQLDay fun! We are holding a 'Women in Technology' (WIT) panel meeting SQL Day 2012 in Wrocław (Poland) on 25th and 26th of May. We will have a team of women, who will swap ideas and discussions. We look forward to hearing your ideas too! The Panel will be held in Polish and English.

We will talk about the following topics. We are also keen to hear your ideas, too!

1. Keeping women in IT by mentoring, networking and career advice
2. How to attract younger women in IT
3. Career - what are the best ways to progress?

Men in Technology are also invited to the 'Women in Technology' panel meeting. Men are very welcome to the Panel too. It would be good for men to share their ideas, too.

Although the panel members are women, these are 'people' topics in which everyone can learn something about career advancement.

The 'Women in IT' panel is arranged for the first day of the conference. If you would like more information, please email us on WiTPanel@plssug.org.pl You can also find us here:

http://ewity.pl/sqlday-2012-wit-panel
http://sqlday.pl/women-in-technology-panel
Our Facebook page: https://www.facebook.com/pages/Women-in-Technology-Panel/222338211190377
Our Twitter name: https://twitter.com/#!/SQLDayPLWIT

We look forward to meeting you!

Power View connection error - Resolved

noreply@blogger.com (Jen Stirrup) — Mon, 30 Jan 2012 09:21:00 +0000

When you're starting off to create a visualisation in Power View, you might notice the following error:

rsCannotRetrieveModel

400

http://www.microsoft.com/sql/reportingservices">An error occurred while loading the model for the item or data source 'http://sapphire/PowerPivot Gallery/HelloWorldPicnicPowerViewTutorialRC0.xlsx'. Verify that the connection information is correct and that you have permissions to access the data source.

When you read down to the foot of the error message, the error message becomes more specific. Here is an example:

A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running.

When you start up the 'SQL Browser' service, then the Power View visualisation works. The SQL Server Browser operates as a Windows Service. It has two main roles: it listens for incoming requests for Microsoft SQL Server resources, and it provides information about SQL Server instances installed on the computer. If it isn't running, then the connection fails since the data source can't be found.

Upcoming National and International Speaking Events

noreply@blogger.com (Jen Stirrup) — Tue, 17 Jan 2012 10:54:00 +0000

The blog has been a bit quiet since I'm busy preparing for some up and coming events. Here is a list of some things which are keeping me busy!

Attunity hosted webinar - Faster Business Insights By Enabling Real-time Data For BI & Analytics, Webinar, 26th January

Reserve your space today! If users rely on reports that include stale or outdated information, the impact on 'same day' decision cycles is far greater than you think. Join us for this special webinar to hear industry business intelligence (BI) expert, Jen Stirrup share her insights about new advances in technologies to better enable real-time data for BI and analytics. Attend this webinar to learn about:

Best practices to achieve low-latency data movement
How to overcome costly obstacles to provide low-latency data movement
New cost-efficient techniques to enable real-time data for BI and analytics
New approaches for implementing change data capture (CDC) technology with data replication
Live demonstration
And a lot more!

In addition, Richard Thomas, Attunity's Director of Technical Services will discuss how Attunity Replicate software plays a critical role to deliver real-time information across your organization. Plus, Jeff Cole, an Attunity Solutions Architect, will provide a live product demonstration. Don't miss this opportunity.

Sweden SQL Server User Group at the World Trade Center, Stockholm, 30th January

We are pleased to announce that Jen Stirrup, joint owner of Copper Blue Consulting, will be participating in the Swedish User Group meeting on 30th January at the World Trade Center in Stockholm. Jen will be speaking, in English, on Data Visualisation and Business Intelligence. If you'd like to register, please click here.

SQL Saturday Ireland Technical Launch for SQL Server 2012 at the Hilton Hotel, Dublin, 24th March

We are pleased to announce that Allan Mitchell and Jen Stirrup, joint owners of Copper Blue Consulting, will be giving individual presentations at the SQL Saturday event in Dublin on 24th March. Allan will be discussing Data Quality, and Jen will be discussing Data Visualisation and Business Intelligence. The event is supported by the Professional Assocation of SQL Server and we will be attending the after-event party. We hope to see you there! To Register, please click here.

SQLBits UK Technical Launch for SQL Server 2012, London, 31st March

Allan and I are both presenting individual presentations at SQLBits. Allan will be talking about CDC in SQL Server 2012. I'll be talking about Data Visualisation and Business Intelligence in SQL Server 2012. The Saturday event is free, so please do come along and join us. The event will be held at the Novotel London West and for more information, please register here. Incidentally the SQLBits site is Powered by SQL Server 2012, so it's worth a look for that reason, too!

Importing Google Spreadsheets into Windows Azure Data Explorer

noreply@blogger.com (Jen Stirrup) — Sun, 01 Jan 2012 11:02:00 +0000

Hi! This blog will take you through the steps of importing a Google spreadsheet into Microsoft Azure Data Explorer. You could then play with this data by using data from the Windows Data Market. I think I love the Data Explorer so much because it allows a nice, easy format for mixing up data from different sources. This activity takes the form of two steps: ensuring that the Google spreadsheet is published, and then importing it into Data Explorer.

The Google spreadsheet came from the fantastic Guardian Datablog, and it focuses on the New Years Honours list for 2012. In case you're not British or from the Commonwealth, and wondering what I'm blathering on about, the New Years Honours List is a quaint British tradition which recognises outstanding achievement to people who serve their communities. The original Guardian commentary can be found here.

The Google spreadsheet obviously belongs to them, so I needed to take a copy of it, and publish it to my own Google account. To do this, you click on 'File' and then 'Publish to the web' It is very straightforward to do this, but if you need an image, click here.

You then need to make sure that you publish the spreadsheet as a CSV format. This is quicker and easier for importing. You can see an example of this below, or if you need the original image, you can find it here:

The other item to note is that you should just select one sheet, and not 'All Sheets'. In doing so, you're making the data easier to import. Here, the sheet is called 'Full List'.

Once you've selected the sheet, you should copy the link that appears in the box. You'll need this in order to import the data. I didn't import this file as 'web content' - instead, I did 'Import File' and then copied the link into the 'Open' Dialog box. This imported the file as text. You then get the following options:

Upon importing the data as a table, you might find that you get the following error message:

The CSV input has rows with varying numbers of columns, and the first row does not have enough columns to specify the input width for all rows. Specify a value for the ColumnCount option to prescribe the number of columns to include in the output. Here is an example of the image here:

If this is the case, then it is perhaps easier to import the data as text in order to examine more clearly to see if there are issues. To do this, just open the 'Text' box at the top left hand side, or right-click and select 'Open as Text'. Here is an example:

Once you've converted it to text, the Data Explorer screen will appear as follows. You can click on the image or click here for a larger image:

Now, it is a bit clearer to see the status of the data since we can see the text. The next step is to try and re-import it as a table. You can do this by clicking 'Table' at the top left hand side. You'll now get the following screen:

And another thing...

If you're sharp eyed, you'll have noticed an anomaly on line 10. Why does Herbert Douglas have the title 'Charity'? Shouldn't this be in 'For Services to' column? If you look back at the first picture of this blog, you'll see that the same thing happens in the Google spreadsheet; since it matches the source, then this isn't an issue with the data import, but the raw data. You can see a snapshot of the raw data below and if you need a larger image, click here.

Don't be fooled by the nice clean appearance of the Data Explorer - there are lots of customisation and nice things that can be done with the Data Explorer, so let's look forward to more fun in future blogs!

Don't you just love data? I do! I hope that this helps.

Sharepoint and PowerPivot: Connections fail to refresh in SQL Server 2008 Denali CTP3

noreply@blogger.com (Jen Stirrup) — Wed, 21 Dec 2011 21:08:00 +0000

Sharepoint requires a range of troubleshooting skills because it can involve the integration of various applications, which are necessarily resting on the application stack. I have the deepest respect for SharePoint specialists since i know that the troubleshooting skills can cut across applications and an understanding of the 'glue' that welds SharePoint together. Take, for example, the issue which I found recently whilst importing a PowerPivot into SharePoint. Everything looked absolutely fine: the PowerPivot loaded nicely into the PowerPivot gallery, and I could see my Excel spreadsheets too. The black and lime-green SharePoint theme was carried on throughout the Excel workbooks, and the whole thing was looking great. The Excel slicers were highly customised to match the SharePoint theming and customer branding, and the whole pieces, put together, looked fresh.

The PowerPivot was created on a Windows 2008 R2 Server 64 bit machine, which directly accessed the underlying SQL Server 2008 R2 database source. It is roughly 1.8Gb in size, which is just under the 2Gb limit for SharePoint files. I uploaded the PowerPivot from the original machine to the PowerPivot gallery as normal, on the target machine.

The issue came, however, when the user tried to filter the Excel spreadsheets or use the slicers in the Excel Services workbook, based on the PowerPivot. Even though the Excel workbook was part of the file uploaded to Sharepoint, the filters would not refresh. The file was uploaded with 'All' so it was not the case that any data had been filtered. When a filter item was selected, the Excel spreadsheet froze, and then produced the following error message:

Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: PowerPivot Data

This issue is covered by MSDN here. However, none of their resolutions worked for me. The way I resolved this issue was as follows:

My resolution: I recreated a new Excel workbook, which used the PowerPivot as the source. The issue was due to the fact that I had developed the PowerPivot on one machine, and then moved it to another. Otherwise, the initial PowerPivot worked fine, and a new test PowerPivot on the subsequent machine worked fine too. The second machine did not have access to the source data since the PowerPivot was generated from a static data source, so it did not actually need to be refreshed.

The MSDN suggested resolutions were as follows:

Scenario 1: The cause might be that a domain controller is not available to validate the user identity - this was a test VM that was a full administrator, and this wouldn't explain why it worked for a PowerPivot that was 'home grown' on the server, and not a PowerPivot that had been moved.

Scenario 2: Differing versions of PowerPivot. I was very careful to ensure that the versions of PowerPivot were identical across machines

Scenario 3: IIS Reset in Sharepoint might work; no, I did try.

In the end, it was just easier and quicker to recreate the Excel workbook. The PowerPivot was otherwise unharmed during its movement from one server to another. I could see that the 'PowerPivot data' connection, when it was cracked open, pointed to a file that didn't exist on the target machine. To make sure everything ran smoothly, it was easier just to recreate the Excel workbook in a completely new Excel workbook, which then worked perfectly.

These scenarios show the multi-disciplinary elements of troubleshooting Sharepoint and PowerPivot, since it involved aspects of Sharepoint such as IIS, Windows Claims authentication, and so on. Despite this, the end result of using Sharepoint and PowerPivot together was an great solution and if you haven't tried it out yet, I suggest that you think about installing a Sharepoint and MS Business Intelligence Virtual Machine and try it out for yourself! Here is a link which might help you to build a VM for yourself.

Hope that helps someone else!

SQLSaturday in Dublin - a new member of the SQLPass SQLFamily

noreply@blogger.com (Jen Stirrup) — Mon, 19 Dec 2011 14:04:00 +0000

SQL Saturday #105 in Dublin is a one day FREE training event for anyone interested in SQL Server! I'm delighted and honoured to say that my session called 'SQL Server 2012: Business Intelligence & Data Viz' has been selected and I will do my very best for the Irish SQL Family! The one day, completely free event will be held on 24th March 2012, at the Hilton Hotel in Dublin. If you need a map, you can find it here.

Why would you attend SQLSaturday in Dublin? Well, there are a whole ton of reasons!

The Irish SQLFamily is a growing, vibrant community of sql enthusiasts, and it is great that SQLPass are supporting this growing community! We have a whole sql family next to us, here in the UK, and it would be wonderful to get to know more sql enthusiasts!

There are a range of great speakers who have been selected. At the time of writing, I only know of the following people, but this list is sure to grow:

Allan Mitchell Making Data Fit for Business using DQS

Christina Leo (twitter) SQL Server Internals: It's What's for Dinner!

Mark Broadbent (twitter) Moves like Jagger-Upgrading to SQL Server 2012

Mark Stacey (twitter) Analysis Services for the DBA

Neil Hambly (twitter) Effective Data Management using Data Compression

Nuno Godhino (twitter) Advanced SQL Azure - Performance, Scalability

There is no need to mention that is the event is held in central Dublin, which is a great location for a conference! Dublin has a lot to offer for tourists, so why not make a weekend of it? If you are a literary person, then you might like to know that Yeats, Bernard Shaw and James Joyce were all born here, and that Dublin was designated the UNESCO City of Literature in 2010. And there is always the Guinness, of course!

For Dublin, the number of delegates are limited at 240, so you'll need to register as soon as you can in order to get a place! Here is the link, so please head on over to register and I'll see you there!

Blogging is a two-way conversation!

noreply@blogger.com (Jen Stirrup) — Thu, 15 Dec 2011 19:34:00 +0000

The community blogging event #meme15 created by Jason Strate ( Blog | Twitter ) challenged SQL bloggers to write about blogging, in the hope that this will encourage people. This challenge provoked me to think about the reason that I blog. I have a number of reasons for blogging:

Blogging is a two-way conversation. I get comments and emails via my blog and I cherish them all. Even the challenging comments are superb, because I am glad that someone has taken the time to comment and it helps me to learn.

Blogging helps me to connect with the community. At SQLBits and other community events, I don't always get the chance to speak properly with the people that I'd like to meet. I also don't get the chance to answer all the questions that I receive during my sessions. My blog is a way of reaching out to people, and it is always here. I can use it to respond to queries,and then write to the individual to say that I've answered via my blog without naming them.

There are some lonely people in the community. I'm sad to say that I occasionally come across some very lonely people in the community. Blogging means that you can start to touch people, and they can touch you.

I love Business Intelligence so much because it is a people subject as well as a technical subject. The 'people' aspect is very much a part of the heart of my passion for Business Intelligence. Blogging is my way of understanding different people's perspectives.

Blogging has also helped me to feel part of the community, regardless of distance or time. It helps me to connect. As we all know, it can be difficult to juggle family, work, life, learning, reading books and 'me time'. Blogging is, for me, a way of reaching out to people regardless of their distance or time difference or my commitments or their commitments.

Blogging helps me to really know my stuff. I'm a firm believer in that, to really know something, you have to be able to explain it. I also want to put back into the community some of the help I've had over the years, including people on this list, from whom I've learned a great deal over the years. It's always nice to know that you've made someone's day a bit easier just by lending a 'helping hand' remotely.

I know that this is more personal than what I usually write, but I hope it helps you to see why I blog. Your feedback is always welcome!

Jen x

SQLBits X - All signed up!

noreply@blogger.com (Jen Stirrup) — Thu, 08 Dec 2011 14:41:00 +0000

SQLBits is the largest European community conference, dedicated to Microsoft SQL Server. This SQLBits is going to be extra-special, however: it is the UK Technical Launch for SQL Server 2012, and is held on 29^th – 30^th March. So if you’re interested in the new SQL Server – and who isn’t? – then you better register quick. If you haven’t been before, the event involves a series of quality sessions focusing on various aspects of SQL Server, ranging from hard-core DBA beefy subjects right through to end-user topics. It’s fair to say that there is something for everyone who has an interest in SQL Server.

There are preconference events, which are paid-for, and free community events on the Saturday. I’ve been privileged to hold two preconferences now, one on Data Visualisation, and the other on Sharepoint Business Intelligence. I’ve also been privileged to conduct general one-hour sessions on Reporting Services at SQLBits 7, and Data Visualisation at SQLBits 8, and one on Mobile Business Intelligence on the iPad at SQLBits 9. You are most welcome to download the presentations from Slideshare, which you can do here. I’ve registered to do the full three days at SQLBits.

You’re probably wondering why I would pre-pay for a precon without the full list being published yet? Basically, I want to pick their brains of the Microsoft SQL Server product teams for a whole day and the SQLBits precon are the best way for me to do just that.

I feel that the SQLBits precon are an opportunity to ‘touch’ Microsoft Product teams. I believe that, by attending a precon from the Microsoft SQL Server Product teams, there is no better way of getting the real information, straight from the people who manage, run and write SQL Server as well as direct and shape its future. Basically, these are the people who know SQL Server inside out – because they make SQL Server. I would love to spend a day with the Product teams to listen to what they have to say and quiz them on relevant issues. I want to hear them. This is particularly the case where there is a new SQL Server version coming out; I want to make sure I get every detail possible!

There is always room for unscripted discussion with the Microsoft SQL Server Product teams. Sometimes I find that the ‘value add’ from a course comes from the unscripted discussion from the presenters. That’s the golden stuff for me, and that’s why I’ll be attending one of the precons. I don’t know which one yet so it is pot luck! However, I’ll be there and I cannot wait!

See you there!

SQLPass Board of Directors Election: Why I voted, and who I voted for!

noreply@blogger.com (Jen Stirrup) — Tue, 06 Dec 2011 10:30:00 +0000

The PASS Elections 2011 are among us! I’m going to provoke some discussion by telling you who I am going to vote for, and why. I’m not doing this so that you’ll vote for a specific person, although I am going to share my thoughts with you. My main aim is to encourage people to consider the direction of the SQL community and to place a vote. Before we begin, please note that I’m not on the Committee for any community events such as SQLBits or SQLPass. This entirely reflects my own opinions, which you are welcome to take or leave as you wish. My wish is that you’ll vote in the SQLPass Board of Directors election. It is your chance to help to shape and direct the community. You don’t have to speak at events in order to shape the community. You don’t have to speak at an event – ever. It only takes a minute.

What I believe:

You are the community. Own it. Shape it. Use your vote.

Futurology: I think that SQLPass will become a truly global organisation. I support the emerging communities in Sweden, for example, by volunteering to speak at their events. These communities need the chance to grow and with support and advice from ‘old hands’ SQLPass, they can mature more quickly.

Share the passion: Tell people about technical community events. Reach out, make the next generation of new community members. The community is fluid; we are always seeing new bright young things who might have real contributions to the community. Encourage them.

Transparency: I’m all for complete financial transparency in organising community events. If people want to know how much I’ve spent in sponsoring user group or providing coffee or pizza or drinks, come and ask me. It isn’t a lot of money since I am just embarking on the whole sponsorship strategy and it is new to me. To be fair, but I do it because it is a form of Scottish hospitality to offer a drink at events – I mention this because it was Saint Andrews Day recently, after all.

I believe that community organisations such as SQLPass and SQLBits are driven by passion. People who are driven by such a passion for their technical discipline that they want to share this passion and knowledge with other people. You show your passion by turning up to SQL Saturday events, community events such as SQLFaq in the UK, SQLRally in Sweden or SQLServerDays in Belgium. In order to make the community hear you, you should vote.

What I don’t believe:

I don’t believe that the community organisations are about making money. I think that the organisations are about community. Let’s face it, if your objective was to make serious money, would you organise a SQL Server conference to do that? No, I didn’t think so. There. I said it and you can pull me up on it if you like. I think that the community organisations can be a nightmare to organise; they require lots of hard work and long hours by community volunteers. Not a particularly smart or quick way to raise a ton of cash, in my opinion.

Here are my key thoughts on the future of PASS over the next year:

Global: I believe that the global growth of SQLPass will become more prevalent globally. For example, in November, there were eight new chapters stretching from Nepal, Egypt, the UK and the US. For me, that’s just simply amazing. Personally I’m glad that PASS can support these new communities as they reach out to new members in their early stages. The new chapters can have PASS as a resource to depend on, which is particularly vital in the early days.

WIT: I think that WIT will grow more prevalently, and PASS already have a WIT focus already. It saddens me that there is a need for a WIT organisation, since someone’s sex should be irrelevant to their ability to do their job in the IT world. I believe that skills, knowledge and experience are what counts –what projects you have done, not what sex you are. However, I know I can call upon the support of other WIT if I need to, since the framework is already there. I haven’t taken up this facility but I do understand that some WIT may feel that they need the support and encouragement of other WIT for advice.

Passion: Community is at the core, and it needs to be nurtured. Some folks are givers, and others are takers. Which brings me to the key attributes that I think a PASS Director needs:

International focus and global relationships
WIT support
Passion
Proven community Leadership
Astute Business Sense
Knowledge of the technology

So, who will I be voting for? Well, here are some caveats:

They are all outstanding candidates. If they don’t get picked this time, I’m sure that they will get it on another occasion. I am not telling you who to vote for! Just use your vote. Please.

I will be voting for Rob Farley ( blog | twitter | campaign statement ) for the following reasons:

He’d do a brilliant job on the BoD. He’d be a voice for the wider community and has all the credentials. More detail:

· International focus and global relationships – I am writing this from Europe, so this is my perspective from the Old World. I’ve only met Rob twice but he has been incredibly supportive of my technical and personal life from afar. Rob has a global reach, and is well known here in the UK. Rob’s previously supported community events here in the UK, such as SQLBits, by flying over from Australia to provide sessions. He’s the furthest travelled speaker, as far as I’m aware – what a commitment and a huge personal effort, and I’d like to use this as an opportunity to thank Rob for his huge support for us here in the UK.

· WIT support – Rob helped to organise the 24 hop WIT campaign. This was an incredibly brave thing to do, and I know this involved a lot of effort for him personally. He stepped me through it all the way and I’d like to thank him again.

· Passion – about sql, yes!

· Proven community Leadership – building the user group in Adelaide from scratch, along with other commitments such as non-voting position at sqlpass.

· Astute Business Sense – building a consultancy from scratch is hard! I should know. I’m still learning!

· Knowledge of the technology – Rob contributed two chapters to the first MVP Deep Dives book, and has contributed another chapter to the second MVP Deep Dives book.

I also voted for two others but I think that this blog is probably long enough! To summarise, I’ve given this hard thought and I think that SQLPass needs an international perspective at this critical growth time. That’s why I’ll be voting Rob for SQLPass Director. #VoteForRob

I look forward to your comments

SQLUniversity: PowerPivot, Tableau and Jedi Knights

noreply@blogger.com (Jen Stirrup) — Sat, 26 Nov 2011 02:31:00 +0000

This blog will show an overview of how I mobilised PowerPivot using Tableau. I've previously given this session at SQLBits, NEBytes Microsoft Technology User Group, and SQLServerDays in Belgium but thought it would also be useful to supply the files for you. The steps are very simple since I intended to show the end-to-end solution simply as a proof of concept, as follows:

creation of a PowerPivot which mashed up UK Census data with geographical data
creation of the report in Tableau
deployed to Tableau Public for consumption by mobile devices such as the iPad

The example was deliberately kept simple in order to prove the concept of PowerPivot being mobilised.

The data sample involved mashing up two sources:

Jedi Knight census, data, which can be downloaded from here This is a basic file but the final PowerPivot can be downloaded from a link later on in this article
Geonames offer an excellent free download service, which you can access here

The Jedi Knight data, along with the geographical data, were joined using the outcode of the postcode data. If you need more definitions of the UK postcode system, I've previously blogged about this here.

Essentially, a very simple RELATED formula was used in order to look up the latitude and longitude from the UKGeography table, and put it into the Jedi Knights data, and produce the necessary data in a simple Excel table. The formula looks like this:

=RELATED(UKGeography[Latitude])

=RELATED(UKGeography[Longitude])

Once these very simple formula were put in place, it was time to load the data into Tableau.

Tableau can take both PowerPivot and Excel data - which driver to use? I used version 6 of Tableau. Whilst this version of Tableau does see the PowerPivot correctly as an Analysis Services cube, it does not always read the date as a 'date' type, but instead as an attribute. There is a forum posting on the Tableau website which tells you how to fix this issue, which involves changing the date so it appears as a measure, which means it can then be used for trends and so on.

However, I wasn't comfortable with this solution because I like dates to be in date format. I've also run into this issue at customer site, where the customer wanted to use SSAS as a source and Tableau as the presentation layer. They were data-warehouse savvy and didn't like the 'measures approach' fix.

On customer site, I got around it instead by using the Excel data source, and importing all of the PowerPivot columns into an Excel 2010 sheet. By doing it in this way, date formats were preserved. In this example, I didn't have date format so it didn't matter - but this is a useful tip for the future if you are using PowerPivot with Tableau. The final data, in an Excel PowerPivot, can be obtained in zip format here or if you can't access it, please email me at jenstirrup [at] jenstirrup [dot] com.

Once the data was accessible by Tableau, I used the Tableau Desktop version to upload the data into Tableau's memory. I did this so that I could eventually upload the Tableau workbook to Tableau Public. The instructions to save to Tableau Public are given here.

Once the data was in Tableau Public, I just needed to access the data using the Safari browser on the iPad. In case you are interested, the demos are publically accessible and you can access the final result by clicking on the hyperlinks below.

Jedi Knight Actuals of UK Census 2001 Dashboard
Jedi Knights Percentage of UK Census 2001 Dashboard
AdventureWorks Sales by Geography Dashboard
AdventureWorks Actuals Sales
AdventureWorks Analysis Dashboard

I hope that's been a useful overview of PowerPivot, and the ease of which it was mobilised. This blog forms a use case of how it might be useful to use PowerPivot, since I think that people sometimes need examples of how PowerPivot can benefit them. In this case, the clear benefit of PowerPivot is to provide an easy way of mashing up different data sources.

I look forward to your comments and thank you for sticking with me for the PowerPivot SQLUniversity discussions!

SQLUniversity: Introduction to PowerPivot and Mobile Business Intelligence

noreply@blogger.com (Jen Stirrup) — Thu, 24 Nov 2011 16:00:00 +0000

Here is the second post for SQL University, where we introduce the topic of mobilising PowerPivot data. As an introduction, I thought it might be useful to share the presentations that I did recently. Tomorrow's blog will talk more about the details of mobilising PowerPivot data using Tableau, but I thought that these articles might provide some introductory material for people to use, and re-use, in order to expose the usefulness of PowerPivot as a data source. Here is a brief overview:

Essentially,the Ordnance Survey and Census data were mashed up together and linked together in PowerPivot. The PowerPivot was then used as a source to Tableau, which was used to visualise the data. As an introduction to the process, I generated some presentations to use when I was talking around this solution. and I have provided these presentations here.

Here is a very introductory presentation on PowerPivot in Sharepoint. This presentation was designed to be given in around 5 minutes before being supported a series of demos, and the intended audience was people who hadn't seen PowerPivot previous to the presentation.

Power pivot in Sharepoint Introduction

View more presentations from Jennifer Stirrup.

The following presentation came from SQLBits, where I gave a discussion on mobilising business intelligence with PowerPivot. Tableau Software was used in order to display the data from the PowerPivot, and the next blog in the series will present more technical detail on how the solution was built.

iPad and Mobile Business Intelligence

View more presentations from Jennifer Stirrup.

Here is the presentation that I produced for SQL Server Days in Belgium in November 2011:

Sql server 2012 mobile business intelligence

View more presentations from Jennifer Stirrup.

I hope that you enjoy these presentations, which I've provided for people to use and enjoy as they wish. Tomorrow's SQLUniversity post will provide more detail on the mobilisation of PowerPivot with Tableau.

SQLUniversity: PowerPivot and ATOM feed mashup Source to PerformancePoint

noreply@blogger.com (Jen Stirrup) — Wed, 23 Nov 2011 09:45:00 +0000

This is the first episode of SQL University’s week of PowerPivot! In this episode, we will look at how PowerPivot can be used to mash up PowerPivot and ATOM Feed data, which in turn is then shaped and surfaced in PerformancePoint Services for Sharepoint. All of the images are hosted on Flickr so please feel free to click on them to see the larger versions.

Mashing together PowerPivot with an ATOM feed means that the disparate data sources appear unified, which can give us something business users can use very smoothly. The business users can also see the detailed Powerpivot in Sharepoint, so they are able to go back and check the detail of the data if they require. The ATOM feed means that they can change the target data using Sharepoint Lists, and it will pass through Sharepoint so it is visible in their scorecards. Ultimately, the business users want accurate information as quickly as possible in order to make a decision, whether it is operational or strategic – and mashing together data sources in PowerPivot can help them to do just that!

There are a number of steps involved in this sequence:

Creating a Sharepoint List
Create Custom List in Datasheet View
Export the Data to an ATOM Feed
Connecting PerformancePoint to a PowerPivot data source
Creating your KPIs and Scorecard with your PowerPivot source

Each of these steps will be detailed in the following sections.

Creating a Sharepoint List

On the left hand side of the page, look for ‘All Site Content’.

Create Custom List in Datasheet View

Right click on the column called ‘Title’, and change it to something convenient.

Once the columns are added, enter information in the rows. This is done by directly typing into the cells.

Once the table is complete, we need to export the data to PowerPivot via a data feed. The next steps to export the data to PowerPivot are given next.

Export the Data to a ATOM Feed

If you can’t find the custom list, look for the link called ‘All Site Content’ in the browser. You should find your custom list, which will appear half way down the page under the heading ‘List’.

Click on the orange button called ‘Export as Data Feed’. This can be seen in the toolbar below.

Next, you need to save the atom feed output to a workbook. From the box entitled ‘PowerPivot’, choose a workbook.

Click on ‘OK’ to create a dedicated workbook for the PowerPivot model that will hold the target data.

The ‘Table Import Wizard’ will appear. Assign a name to the PowerPivot model that is appropriate to your network.

Keep the Data Feed as-is for the moment, and click ‘Next’.

Next, the Table Import Wizard offers you the opportunity to filter the table. For this exercise, this is not necessary. We will click ‘Finish’.

Once you have clicked ‘Finish’, you should hopefully get a ‘Success’ feedback window. Choose the ‘Close’ option.

Once you have done this, your PowerPivot model of the Target data appears. Save it to your preferred Sharepoint location for the PowerPivot models. This is URL to the PowerPivot gallery on your Sharepoint site.

Now you have a PowerPivot model that contains your target data. Now we want to mash it together with your actual data to give us a complete model of merged Actual and Target data in PowerPivot. This ‘mashed together’ model will give us a nice data set for visualising Actual versus Target data, and this is something that we are going to do in the next step.

PerformancePoint is a very nice way of displaying Actual versus Target data that is intuitive to the user. We will now look at some of the different ways that PerformancePoint allows us to display Actual versus Target data. As we proceed, we will look at the best ways of visualising the Actual versus Target data so that business users can understand the message of the data.

To use PerformancePoint, you firstly need to open Sharepoint in a browser since it may not be obvious where PerformancePoint is hiding! If you haven’t used PerformancePoint Services before, then it needs to be installed from the Business Intelligence Center in Sharepoint. The install process is straightforward, but if it does not work, then there may be an issue with your permissions.

In the Business Intelligence Center, click a section, such as Monitor Key Performance, Build and Share Reports, or Create Dashboards.

Additional information about each area is displayed in the center pane.

In the centre window, click a link that says, “Start using PerformancePoint Services.”

The PerformancePoint site template opens. Next, you need to click the orange button that says ‘Start Using PerformancePoint Services’. The Dashboard Designer should start to install itself and run.

You do not need to install the Dashboard Designer every time. Instead, you can go to Start - All Programs - Sharepoint - PerformancePoint Dashboard Designer.

Connecting PerformancePoint to a PowerPivot data source

To create a PowerPivot data source connection, the following steps need to be followed:

Click the Create tab, and then click Data Source.

In the Category pane of the Select a Data Source Template dialog box, click Multidimensional and then click Analysis Services. Click OK.

In the left navigation pane (workspace browser), type the name of your data source.

In the center pane, click the Editor tab. In the Connection Settings section, select Use the following connection.

Type the connection string to the PowerPivot data source by using the following format:

PROVIDER=MSOLAP;DATA SOURCE= http://testsp01/sites/powerpivot_test/ABC/PowerPivot Base Data Hub/Operations PowerPivot Book.xlsx

Select the cube from the drop-down menu. The cube name for a PowerPivot model will always be Sandbox.

In the Data Source Settings section, select the method on which to authenticate to the data source.

From the Formatting Dimension list, select desired dimension formatting needed for the report.

From the Cache Lifetime list, type the refresh rate (in minutes) for the cache. Data from this data source will update at this interval.

Here are the details below:

Click Test Connection to confirm that the connection is configured correctly.

Note: at the time of writing, Time Intelligence is not supported for PowerPivot data sources in SQL Server 2010. You should always test your connection, and hopefully you should get this result:

The next step is to create a Scorecard that will display the performance of the Actual data compared to the Target data. The Actual data will come from the PowerPivot Actual source, and the target data will come from a separate table in the same PowerPivot source.

The Scorecard PowerPivot data source appears the same as an Analysis Services data source:

Technet offer an excellent walkthrough of the PerformancePoint scorecard creation here. The key difference is in selecting the data source. Note that the data source comes up as ‘Analysis Services’ rather than PowerPivot, but do not worry: remember PowerPivot is powered by Analysis Services, so that’s ok. Here is an example:

When we set up a KPI source, the source also appears to be Analysis Services. Here is what the next step looks like:

Once you have created your scorecard containing your actual and target KPIs, it appears as a unified scorecard rather than coming from different mashed-up data sources.

To summarise, mashing together PowerPivot with an ATOM feed can give us something business users can use very smoothly since the disparate data sources appear unified. The business users can also see the powerpivot in Sharepoint, so they are able to go back and check the detail of the data if they require, which is something that they can find reassuring. The ATOM feed means that they can change the target data if they like, and it will be visible in their scorecards. Ultimately, the business users want accurate information as quickly as possible in order to make a decision, whether it is operational or strategic – and mashing together data sources in PowerPivot can help them to do just that!

Photos from SQLRally Nordic in Stockholm

noreply@blogger.com (Jen Stirrup) — Sun, 13 Nov 2011 21:08:00 +0000

As you know, I recently presented at SQLRally in Sweden. I don't normally post photographs, but I thought that some of you might like to see what it was like! This blog is just a bit of fun and I'll wait and see what you think of my 'off-topic' blog!

In a previous life, I used to work in Stockholm, so returning to Sweden was very exciting for me. Although it was great to be back, on the previous evening, I had walked around Stockholm with Dave Ballantyne. For SQL folks in the UK, Dave will need no introduction, but in case you'd like to see some of his presentations for SQLBits, please take a look here.

As you can see here, the conference centre at Aronsborg was waiting to receive us. It tickled me to see that we were the 'mightiest SQL Server and Business Intelligence in Midgaard'! Here are some photos from the Conference Centre that give a flavour of our welcome:

I had work to do whilst I was in Stockholm, so I made use of the Regus office in Stockholm for the day. No time for sightseeing, I'm afraid! As a coffee-drinking workaholic, the occasional use of their business lounges suits me. I have a weakness for Nespresso! There is one near my home. I've got a TripIt membership and a 10-visit Gold pass, which means I can use their business lounges anywhere in the world. Here are some photos of their lounge:

Sometimes people ask me how I fit it all in, and the truth is that I work everywhere I go, and squeeze something in as often as I can. Therefore, the occasional use of a business lounge, wherever I am, is very helpful in keeping things ticking over.

It means I don't 'smell the roses' but I love my work so much, it is my passion! It makes me happy.

SQLPass SQLRally Nordic - a Viking treasure!

noreply@blogger.com (Jen Stirrup) — Wed, 09 Nov 2011 23:57:00 +0000

I've been at SQLRally Nordic this November, and I've had a fantastic experience. I hope that they will organise this event again! Here are some highlights:

High quality sessions from a variety of speakers on various subjects, including a presence from the SQLCat team such as Alexei Khalyako, Mark Souza (who is also a PASS Director), Thomas Kejser and Tobias Thernström.

Business Intelligence experts such as Peter Myers, Chris Webb, Peter Myers, Marco Russo and Alberto Ferrari all gave sessions. We also had a precon from Itzik Ben-Gan and Tim Peterson. Johan Ahlen - chief organiser - also gave a session in Business Intelligence.

It was also great to see long-serving MVPs support the event, so many thanks to Aaron Bertrand and the Quest team, supported by Kevin Kline. I'd also like to thank Johan Ahlen for his hard work in organising the event. I am sure that there were plenty of others who helped, whom I didn't come across - but their hard work was visible because SQLRally Nordic went flawlessly. Well done everyone!

The event wasn't all 'work and no play'! There was a party in the evening, and if you're interested in seeing what the Swedish IT Computing press made of the event, please do visit their site here.

We also had a WIT lunch, and it was great to have this facility where people could network and contribute. We talked on a number of subjects, and our main focus was on how we could individually mentor and support people as they progress through their IT and professional careers. It wasn't a 'pity party' attitude at all! The main focus was to allow people to connect and network if they wished. I'd personally like to thank Simran Jindal and Johan Ahlen for facilitating the 'Women in IT' lunch.

I enjoyed giving my session in data visualisation. There was a very strong presence in business intelligence delegates, and I love meeting other Business Intelligence fanatics in order to share experiences! The Swedish delegates were very friendly, and they made the event very special with their welcome.

As a final note, the event was duly supported by a number of sponsors, and if you'd like to see the list, then you can find the sponsor details here.

IT Recruitment Strategies: tailoring towards women?

noreply@blogger.com (Jen Stirrup) — Wed, 09 Nov 2011 23:38:00 +0000

Recently, I commented on my LinkedIn page that thirty plus recruitment agents had looked at my profile within a couple of days. This surprised me since it seemed to be a flurry of activity all at once, and I couldn't understand why there was such a sudden interest in my profile. Then it became clear. A recruitment agency in the UK has started a dialogue with me about an emphasis on recruiting women into IT roles. I am purely guessing that I've come up on their radar for that reason. I haven't named them, but here are my thoughts on the issue.

I disagree with the idea of tailoring IT recruitment services towards women. Recruitment should be about skills. If you read some of my previous posts, I've been in the situation where companies are keen to hire women into IT roles and I've been contacted initially on that basis. This strategy has switched me off since I don't want to be hired over someone else simply because it might be 'trendy' or 'marketable' to have a technical woman on board.

I don't want someone to hire me because I'm a technical woman. I want to be taken on board for my experience, attitude and skill set and what I can contribute to their business. The 'Women in IT' thing doesn't help because it sets me apart from the (usually male) people I work beside. I want to blend in, and contribute as part of a team. Either I can do the job, or I can't. If someone else is better suited, then they should get the role. I guess that's very simplistic but I focus on skills when I'm hiring someone, so I'm coming from that perspective.

In terms of recruitment strategies which emphasis hiring women for IT roles, the truth is that the numbers are simply not there. The proportion of technical women is small, so it is tailored towards a tiny proportion of the whole potential audience. I get concerned that it sends out a message that WIT need special help in some way. For me personally, I don't. I will be interested to see what others think, and how the recruitment agencies shape their women-focused strategies.

For me it isn't relevant because I am more interested in hiring than being hired these days! I jointly run a consultancy business with another SQL Server MVP. As a team, we both bring a mix of technical and business expertise to running Copper Blue, and delivering enterprise business intelligence solutions to our clients. By employing myself, the WIT issue is a non-issue and means I can focus on my customers and projects as part of a team whilst expanding my skills - which is where my focus should be.

Women In Technology Event at Nordic SQLPass - MIT as well as WIT

noreply@blogger.com (Jen Stirrup) — Wed, 19 Oct 2011 01:50:00 +0000

The SQLPass Nordic team organisers have decided to hold a 'Women In Technology' lunch at the SQLPass event in Stockholm next month. It is being held on the first day of the conference on 8th November. As a female speaker at the event, I was asked if I would like to participate. I was pleased to receive the invitation but this left me in a quandary. This blog is aimed at describing my quandary, what I decided to do and why, and to receive your feedback on my decision.

I have previously blogged before about my reluctance to be involved with 'Women in Technology' events. Specifically, I believe that the way forward for women in technology is integration into the wider technological community, not separation. My roadmap for integration would involve an emphasis on skills and achievements, rather than an emphasis on biological characteristics or a perceived socialisation based on gender construction. My emphasis on skills and achievements would include these benefits by males and by females, and those who are trans-sexual or even androgynous. My quandary is that I would like to help people in my beloved SQL community, but without an emphasis on helping a particular subset of the community i.e. women.

With WIT events, I am concerned that they nurture an implicit assumption that 'we women are over here, you men are over there'. By marking women out as different, is this really progress? Where does it leave individual choices or inclinations within a wider context? Ideally, it shouldn't make any difference whether someone is male, female, both or neither. By marking a group as distinctive, it's not clear that the goal of integration will ever be attained. This is the main reason that WIT events do not sit comfortably with me.

Ultimately, I have decided to help at the Nordic WIT event - my first one - since it gives me a platform to support a growing SQL community in Europe, whilst ensuring that men will also be invited to participate equally with women. Fundamentally, I believe in MIT as well as WIT. I see this event as a way in which I can serve the community. I believe that positive discrimination is still discrimination against somebody, and I do not like the idea of MIT being excluded.

I am also hoping that this activity will somehow support the growing Nordic community - friendships made, support given, and technical knowledge shared. I think that the SQLPass team have got it right by ensuring that MIT are also welcomed to the event. It is excellent that there is an event which is focused on the social aspects of the conference, rather than technical only. Therefore I am happy to be involved since it should be about equality of opportunity and knowledge sharing, regardless of whether the event is male or female participants.

By helping women - and men - who approach me as part of the community, I hope to be able to do my bit to reach community members at an individual level. However, I would like to be able to help people in the community without the WIT badge. I don't want to be perceived to be a feminist. Instead, I want to be able to support male and female community members who reach out for help and support, whether it is technical or otherwise.

I recognise that, as a woman, I probably have experiences that I can use in order to help other women at an individual level. For example, issues surrounding pregnancy in the workplace, or how to handle customers or clients who ask you out. The WIT badge will allow me to be approachable by individuals who just need to bounce ideas around, or pointed in the right direction, or just to chat about their experiences. I hope I can achieve the balance of offering support in these areas whilst also trying to ensure that I don't alienate the MIT. I also need to try and balance my activities so that I don't alienate the WIT who believe passionately that women play a distinctive role, that is a good thing, and that it should be promoted and celebrated. I appreciate and respect their right to have differing perspectives from me, particularly over the WIT issue since it can be emotive.

To summarise, it's all about balance and people, and I am not sure that I've cracked the way forward yet. My main concern is that SQLPass Nordic is a great success,particularly given the huge effort that's gone into organising it. There are a lot of stellar speakers and I think it will be a memorable event which I'm hugely privileged to participate in. I'm hoping that the WIT lunch will cement friendships and provide an open forum for an exchange of ideas within a welcoming framework, regardless of whether the attendees are male or female.

I look forward to your comments!

Jen x

Windows Azure Marketplace - what data sources would you like to see?

noreply@blogger.com (Jen Stirrup) — Mon, 17 Oct 2011 23:44:00 +0000

During my presentations at SQLBits, SQLRelay and other UK User Group meetings, I have been dismayed by the lack of awareness of the Windows Azure Marketplace. This blog aims to explore some of the reasons that this may be happening, and I'd also like to canvass you, dear reader, so you can highlight the data sources that you would like to have in the Datamarket.

First of all, the Windows Azure Datamarket is not to be confused with the Datamarket, which is a company based in Iceland which sounds similar. The Windows Azure Datamarket is a broad reaching collection of subscription-based data services, including applications and a variety of data for consumers and businesses to utilise. It is available in 26 countries, as at the time of writing in October 2011. It is a marketplace in the sense that it is possible to purchase and sell data and applications. The types of data available include financial, property, geographical data, retail data and even fun sports data. The data from the Windows Azure Marketplace can be consumed by Excel, Tableau and Visual Studio.

One intention of the Windows Azure Marketplace is that it will support business analysts everywhere, in their quest for clean, up-to-date data. I believe it is potentially a very powerful source of data for enterprises. For example, by provisioning clean, "looked after", up-to-date datasets, it can reduce the amount of effort in looking after external data. In other words, companies who already 'clean up' external data sets might look to the Windows Azure Marketplace in order to see if there are existing datasets that could be rented. It's the old problem of 'outsource or internal spend' - but at least it is good to have options to explore.

So, given the potential for the Windows Azure Marketplace as a potential data store, why the lack of awareness or uptake? Out of my recent travels to various User Groups, SQLBits and so on, hardly anybody had heard of it, never mind actually used it in production. I am guessing that one reason for this is that the data stores aren't plentiful with UK-focused datastores. My research showed that there were a number of UK data sources available. These included:

UK Foreign and Commonwealth Office Travel Advisory Service

Greater London Authority

In other words, not very many sources! My search was hampered for the fact that the search string must contain at least three characters. Therefore, if you are searching for 'UK' then you are stuffed! I am guessing that the uptake isn't very strong since the UK-focused data needs to be grown. In my opinion, I guess that this will happen over time. Since there is an Excel add-in for the Marketplace, the route to uptake of this service is clear. I think that this will take time, and it is potentially a very powerful tool for analysts and researchers.

Hence this blog: I am wondering what UK data sources you would like to see? Here is my list of free data sources that I'd love to see on the Marketplace as a one-stop-shop for data requirements:

The Guardian Datastore - basically anything that they produce. Love it!

UK Census data - since the next Census is out soon in the UK, it would be particularly relevant to have this information

The Data Archive - Social Sciences and Humanities data for the UK. Not as esoteric as they might sound since they also discuss the future of data sources. This is a reflective data store, and I'd recommend that you take a look at it.

Health and Safety Executive Data - Risk Control, Public health and comparison with other European countries

Heidi - I have never been able to access this, but it is available to Education planners.

The Treasury also offer UK data on finance and key financial indicators

The Bank of England offers a wealth of financial data, focused on the UK

Office for National Statistics - data on agriculture, children, economy, government, travel... you name it!

If you can think of any other data sources you would like to see on the Windows Azure Datamarket, then please leave a comment. I'd love to hear from you and you'd also satisfy my never-ending thirst for more data sources!

Office365 Getting rid of Custom Theme Images in External Sites

noreply@blogger.com (Jen Stirrup) — Sun, 16 Oct 2011 00:11:00 +0000

If you are using Office365, you probably want 'quick and easy' wins in order to make your external site look better. At Copper Blue Consulting, we use Office365 for our email, web hosting and our Sharepoint activity. The current blog series will look at some of our findings which we've used in order to improve the appearance of our Office365 site.

I know that the Copper Blue site needs some love and attention, and now that SQLBits, SQLRelay and SQLPass is out of the way, I hope that I can start to make our corporate website look, well, corporate.

First thing on the list was to get rid of the theme header logo on the top right hand side. These are automatically added when you select a theme in Sharepoint. If you've set up an external website in Sharepoint, then the configuration items are quite clearly laid out in the ribbons. Here is an example here:

We can see that none of the themes are blank, but each has a little picture somewhere. On the Copper Blue site, I just want a plain header with a logo. Nothing more, nothing less. How do we achieve this?

When we open up the Site in Sharepoint Designer, we notice that there are custom images in the Office365 structure. I wasn't able to delete these. However, I was able to amend the file. Therefore, I navigated to the offending image and clicked on 'Edit File'. You can see the structure in the following image:

Since I couldn't delete the image, I decided instead to simply blank it out. Here is the editor for changing the image:

On the right hand side, you can change the brightness. I simply made it pure white, so that it matched the header of the site. I know that this is a 'fudge' but it gave me a quick easy win in terms of getting rid of the annoying logo, that didn't sit with the rest of the branding.

If anyone has other ideas on how to do this, I'd be glad to hear them. In the meantime, more adventures of Office365 will appear as I move the Copper Blue site from being very Sharepoint looking, to something that's much more customisable. If you want to see the end result, then please do look up the Copper Blue website to have a look!

Mobile Business Intelligence - Try it out!

noreply@blogger.com (Jen Stirrup) — Tue, 04 Oct 2011 16:22:00 +0000

Thank you to everyone who attended my SQLBits 'Mobile Business Intelligence in Action' session recently. If you are interested to try out Mobile Business Intelligence on your iPad or mobile device, here are the links below:

Jedi Knight Actuals of UK Census 2001 Dashboard
Jedi Knights Percentage of UK Census 2001 Dashboard
AdventureWorks Sales by Geography Dashboard
AdventureWorks Actuals Sales
AdventureWorks Analysis Dashboard

I haven't tried this on every browser and every device, so I would be very interested in your feedback.
I look forward to hearing from you. Please leave a comment below, or email me at jenstirrup [at] jenstirrup.com

SQL Relay - Exciting new UK event! A relay rather than a rally

noreply@blogger.com (Jen Stirrup) — Sun, 18 Sep 2011 21:43:00 +0000

In the UK, an exciting and innovative event will be held in October, called SQL Relay! The SQL Server User Groups in the UK will be holding 16 User Group events in the space of four days, commencing on Monday 3rd October.

The buzz of activity culminates on Thursday 6th October, where Itzik Ben-Gan will be presenting at the Relay Finish Line at Microsoft's office in Cardinal Place in London. If you don't know who Itzik Ben-Gan is, then you don't know your SQL heritage and it's even more reason to go!

We will be presenting at three separate events. Allan Mitchell MVP is your host and presenter for the User Group in Manchester on 3rd October. Allan will be discussing Data Quality and Master Data Management for the evening.

In addition to technical people, I'd strongly recommend Allan's discussion for anyone within an organisation who has dirty data, and is planning to do something pro-actively about it. When facing a data quality project like this, it is useful to gain advice from experts like Allan so that you can skip some of the learning curve and put a robust plan in place. Allan's happy to take questions as well so I'd take advantage of the opportunity to ask someone who can help you. So, if you are considering taking a friend to a SQL Server UG, this is one to consider.

I am presenting on 4th October in Leeds, on the topic of Mobile Business Intelligence. I chose to present in Leeds because I wanted to meet some of the active SQL Server Twitterati who are based there, and it was a good opportunity to get 'face to face' with some of the people that I've been tweeting for some time.

Allan and I are both presenting at the Cambridge User Group on 5th October. This will be the first meeting of the Cambridge User Group, which has been organised by one of my best friends and DBA superhero Mark Broadbent. Mark's a Community Contributor Award holder, in addition to keeping me right with his DBA expertise. So we were pleased to be in attendance at this event.

I keep hearing this event being called SQL Rally, which is a completely different - and still wonderful - event. I just thought I'd mention that the two events are different, so that there is no mixup! Both events are amazing in their own ways and if you like one, you may well like the other. It's worth keeping an eye on both!

SQLPass 24hop Review: Slicers in Reporting Services

noreply@blogger.com (Jen Stirrup) — Fri, 09 Sep 2011 17:29:00 +0000

Twice a year, SQLPass put on a stellar free '24 hour hop' which is aimed at engaging with SQL Server fans all over the world. Basically, SQL experts from all over the world give an hour of their time in order to present you, the SQL Server viewing public, with some SQL goodness, advice and tips and tricks to make your lives easier. Ever since I have got involved with the SQL Server community in the UK and the US, I have learned so much from my peers. I can say that SQLPass and SQLBits have both enriched my life so much in terms of my skills, and also introducing me to some wonderful friends that I've made my home in the community. For that, I am grateful. The 24 hop is one of the ways in which we can learn more about SQL!

The SQLPass 24 hop sessions, given in Fall 2011, have been designed to give you a flavour of the precons that will be provisioned as a part of SQLPass Summit. The 24hop sessions are given by world-class SQL experts. This fall, I listened to a total of 5 different sessions and learned a lot!

I'd like to thank the following people for giving up their time to educate the wider SQL Community (and me!): Denny Cherry, Stacia Misner, Paul Turley, Rob Farley, Simon Sabin and Peter Myers for their excellent sessions and for giving up their time to help people in the community by provisioning free training. If you are interested, I'd recommend that you take the time to look at the SQLPass preconference training for each of these sessions since you'd be trained by the 'best of the best'.

In this blog, I wanted to call out Simon's Reporting Services session since I thought that his session was particularly outstanding. Simon's webinar focused on provisioning slicers for Reporting Services. Yes, you read that right! Slicers are available in Excel and in Project Crescent. Every time I show slicers to an Excel user for the first time, the customer is usually impressed by their simplicity and ease of configuration. It's also possible to 'theme' slicers so that they match the rest of the dashboard elements. They also increase usability because they are consistent with Schneiderman's Visual Information Seeking Mantra: 'overview, filter and zoom' methodology with respect to data navigators 'surfing' their way through the data.

Basically, Simon used VB and SSRS to produce the slicers in the report. For SSRS people who'd like to know more about what .Net can offer them, this is an excellent route towards learning some .net whilst enhancing report usability in line with the best data visualisation thinking as advised by gurus like Schneiderman. What I especially liked is that Simon paid attention to the .Net requirement from the SSRS writers' perspective, and was careful to call out any potential pitfalls or mistakes that the SSRS report writer might make.

The end result was great to see, and Simon produced the report, which you can see on his blog, on less than an hour. It seems to me that his SSRS precon would be especially interesting since you'd obtain lots of useful practical advice, packed into a one-day event, that would really make a difference to writing SSRS reports.

As a business intelligence specialist, I believe firmly that accurate and useful reporting can drive a business from data towards business intelligence and customer intelligence; listening to the stories that the data is telling you. If you can't 'hear' the data because the reports are poor or don't have the user in mind, then you are still not using the data properly - even if you have lots of it. Quantity of data is not quality of data - it has to be clean and well-presented before it can be used to support the enterprise.

It seems to me that an investment in report writing is fundamentally an investment in business. SSRS precon and training like Simon's session offers a real 'value-add' to the business long term, by supporting the provisioning of reports by report writers to the decision-makers who need the reports to drive the business forward. SSRS can be left behind a bit, in the fanfare over Excel Services, and of course, Project Crescent. Despite the new technologies and new 'self-service' business intelligence outlook, there is always a role for straight reporting in running a business knowledgeably and accurately, based on the data.

If you're interested in the SQLPass pre-cons, then please do head over to the site and take a look! If you decide to go, please do let me know by tweeting me and hopefully I'll see you at SQLPass Summit!