SSIS Junkie : iCalendar

Get the SQLBits agenda on your phone, now and forever

jamiet — Thu, 25 Apr 2013 20:02:33 GMT

Regular readers of my blog might have realised that I am a huge advocate of subscribable calendars and the data format that underpins them – iCalendar. On 8th Feb 2012 I wrote a blog post entitled SQLBits now publishing all SQLBits agendas as an iCalendar where I told how the SQLBits committee had published the agenda of the forthcoming SQLBits conference in iCalendar format allow with instructions of how one could view the agenda on their phone. Back then I said:

…any changes to the SQLBits agenda (e.g. a room change) will automatically flow to your own calendar service and if you have that calendar service (e.g. Hotmail Calendar, Google Calendar) synced to your phone then the changes will automatically show up there too … That new SQLBits subscribable calendar lives at http://sqlbits.com/information/SQLBitsCalendar.ashx; note how it is not specific to a particular conference - subscribe to (don't import) that calendar and the agenda for future SQLBits conferences will automatically flow to you too.

Sure enough I took a look at the calendar on my phone today and saw this:

(That “Real world SSDT” session at 14:40 is being presented by Craig Ottley-Thistlethwaite and myself by the way. Hope to see you there!)

This is the value of subscribing as opposed to importing. The agenda for next week’s conference has already flowed to my phone without my having to do anything. This isn’t the same phone that I had a year ago either, by subscribing to it in my Outlook.com (nee Hotmail) Calendar those subscriptions are stored and flow onto any new phone as soon as I type in my credentials.

I have stated before that I believe subscribable calendars to be a transformative technology and this is why, I only had to subscribe to the calendar once and data that didn’t even exist back then simply flows into my calendar and thus onto my phone. If this interests you then maybe read how I think the same technology could be used to deliver BI data too at Thinking differently about BI delivery. And if you want to subscribe to the calendar yourself go and read the aforementioned blog post, that link again: SQLBits now publishing all SQLBits agendas as an iCalendar.

@Jamiet

SQLBits now publishing all SQLBits agendas as an iCalendar

jamiet — Wed, 08 Feb 2012 08:36:00 GMT

Three weeks ago I published a blog post Get the SQLBits agenda in your phone's calendar where I said:

If you want to get the SQLBits calendar onto your smartphone then the easiest way to do it is add my calendar [containing all SQLBits sessions] to whichever calendar service (i.e. Hotmail or Google) you have got synced to your phone and let technology do its thing.
I will keep the calendar updated with any changes to the agenda so, assuming you have subscribed, changes will just propogate to you without you having to do anything.

This is not the first time I have published a subscribable calendar (I did it for the 2010 World Cup for example, I also curate a calendar for my home town) nor the first time I have opined about them. The reason I bang on about subscribable calendars (aka iCalendars) all the time is that I truly believe that they are a transformative technology. In my humble opinion the world would be a better place if it ran on iCalendar and I'm not the only one who thinks so, Scott Adams (yes, THAT Scott Adams) says the same (but in a much more coherent way) in his blog post Calendar as Filter.

My super-smart former colleague Howard van Rooijen is fond of saying work smarter, not harder and, to me, subscribable calendars are the epitome of that mantra. Why should many people do the same work of downloading .ics files and importing them to their own calendar service when the content owner can simply make that information available to anyone? That is my motivation for publishing these subscribable calendars - I want to motivate the content owners to publish this information for themselves.

With that in mind I am delighted to tell you that the SQLBits organising committee have taken this on board and published the SQLBits agenda as an iCalendar. One benefit of that is I don't have to go through the rigmarole of keeping my own calendar up to date but more importantly any changes to the SQLBits agenda (e.g. a room change) will automatically flow to your own calendar service and if you have that calendar service (e.g. Hotmail Calendar, Google Calendar) synced to your phone then the changes will automatically show up there too. Very cool!

That new SQLBits subscribable calendar lives at http://sqlbits.com/information/SQLBitsCalendar.ashx; note how it is not specific to a particular conference - subscribe to (don't import) that calendar and the agenda for future SQLBits conferences will automatically flow to you too. Want to subscribe to that calendar yourself? Click one of the following links:

If you use Hotmail, click here
If you use GMail/Google Calendar, click here

N.B. I am assuming that Hotmail & Google Calendar covers the majority of folks that are going to be reading this. If you use a different service (e.g. Yahoo) then perhaps you could find out what the appropriate link should be and it as a comment below.

You may also want to unsubscribe from my calendar because I am no longer going to keep it updated.

My thanks go to the SQLBits committee for doing this; more accurately the thanks should go to Simon Sabin because it was he that made this happen.

@jamiet

P.S. If you agree with me that iCalendar is a transformative technology and would like to get involved then take a look at Jon Udell's Elmcity project (FAQ) to curate your own calendar for your home town.

UPDATE: Some folks are saying that the link to Google Calendar produces the message: "You do not have access to <the calendar>". The exact same problem was reported with the calendar that I produced three weeks ago so I am assuming that the problem is at Google's end. The workaround is to subscribe to URL http://sqlbits.com/information/SQLBitsCalendar.ashx in Google Calendar by clicking Other calendars->Add by URL.

Get the SQLBits agenda in your phone's calendar

jamiet — Wed, 18 Jan 2012 11:36:00 GMT

For SQLBits 8 in April 2011 I published a calendar containing all of the sessions from the conference; anyone could subscribe to that calendar on their phone or calendar service (i.e. Hotmail or Google Calendar).

For the upcoming SQLBits X conference I have done the same again by adding all of the sessions to that same calendar. If you are already subscribed to that calendar from SQLBits 8 then you have nothing to do - all the SQLBits X sessions will automatically flow to your phone/Hotmail calendar/Google calendar (go take a look now - they should already be there).

If you want to get this SQLBits calendar onto your smartphone then the easiest way to do it is add my calendar to whichever calendar service (i.e. Hotmail or Google) you have got synced to your phone and let technology do its thing.

If you are on Hotmail this is dead simple – just click this link: http://calendar.live.com/calendar/calendar.aspx?rru=addsubscription&url=webcals://cid-550f681dad532637.calendar.live.com/calendar/SQLBits/calendar.ics&name=SQLBits
If you're on Google then you will have to subscribe to URL http://cid-550f681dad532637.calendar.live.com/calendar/SQLBits/calendar.ics in Google Calendar by clicking Other calendars->Add by URL.

I will keep the calendar updated with any changes to the agenda so, assuming you have subscribed, changes will just propogate to you without you having to do anything. Remember, to save yourself work in the future make sure you subscribe to the calendar as opposed to importing it.

Hope this is useful

@jamiet

UPDATE: I have just discovered an even easier way to subscribe to this SQLBits calendar using the Google Calendar service - simply click this button:

If Google Calendar reports that you do not have permission (as it seems to be doing for some people) then follow the instructions that I provided above. I promise you, the calendar *is* publicly available so if this button doesn't work its Google that is doing something wrong.

Subscribable iCalendar for UK SQL Server events from John Sansom

jamiet — Fri, 15 Jul 2011 12:10:00 GMT

I have written on numerous occasions about my advocacy of iCalendar as the means by which, I believe, time-related information should be disseminated. (Unfortunately I'm not too good at articulating why but thankfully I don't need to be because Dilbert creator Scott Adams has done a fine job of that in his blog post Calendar as filter). To that end I tried to start an initiative earlier this year where folks hosting SQL Server webinars around the world could make information about their webinars available in an iCalendar feed and I would then use an aggregation service (called ElmCity) to produce a single feed containing all that information so that the likes of you and I could subscribe to it and see those webinars turn up in our smartphone/Outlook calendars. Unfortunately I couldn't round up enough willing volunteers (thank you to the one person that did contribute) so it didn't get very far.

I was, therefore, pleased to see that John Sansom (blog | twitter) has begun a similar initiative for aggregating information about SQL Server user group events in the UK; the difference being that John is maintaining the calendar himself and inviting others to contribute rather than using an aggregation service like I was trying to do. John has made the calendar available for subscription at https://www.google.com/calendar/ical/gnbu8hpgv4f1gpnueo8lif06mo%40group.calendar.google.com/public/basic.ics which you can subscribe to using Outlook, Hotmail Calendar, Google Calendar etc... If you use one of those online services (like Hotmail Calendar or Google Calendar) and you have that account synced to your smartphone then it should automatically turn up there too.

If you want to learn more go and check out John's blog post on the subject at SQL Server Events.

Great job on setting this up John!

@jamiet

Get the SQLBits agenda on your phone

jamiet — Sun, 03 Apr 2011 22:15:00 GMT

The agenda for the forthcoming SQLBits 8 conference in Brighton is currently available at http://sqlbits.com/information/Agenda.aspx. Unfortunately, useful though it quite clearly is, its only a web page and that means its hard to get the agenda data out and onto something more useful. Like your phone’s calendar.

I have tried to rectify that problem by providing all of the sessions in a subscribable calendar that can be viewed, as a web page, at http://cid-550f681dad532637.calendar.live.com/calendar/SQLBits/index.html. That calendar provides a mechanism that makes it easy to get it onto your phone; as well as publishing the data as a web page it also publishes it as an iCalendar feed (which you can think of as an RSS feed for calendar data). That iCalendar feed can be consumed in two ways:

webcal://cid-550f681dad532637.calendar.live.com/calendar/SQLBits/calendar.ics – Published using the webcal protocol that is understood by many clients (including Outlook & iPhone)
http://cid-550f681dad532637.calendar.live.com/calendar/SQLBits/calendar.ics – Published using http so is effectively a download link for a .ics file containing all of the sessions

Here’s how to view the calendar on various clients:

Outlook

Hit the webcal link above from your Windows machine and (if you have it installed) it should open up in Outlook and allow you to subscribe to the calendar. Subscribing is better than importing because if I update the calendar for any reason those changes will automatically propagate into Outlook.

iPhone

iPhone understands the webcal protocol too so hit the webcal link above on your iPhone and you should be subscribed to the calendar.

Windows Phone 7

Windows Phone 7 doesn’t understand webcal and also doesn’t allow you to view calendars from anything other than the accounts registered on your phone. Furthermore, whilst Windows Phone 7 can sync multiple accounts it can only sync the *primary* calendar from each of those accounts. Hence you will need to hit the http link above from your desktop machine, save the .ics file, then import it into the primary calendar of an account that you have synced to your phone (e.g. Hotmail Calendar, Google Calendar, Exchange, etc...). The sessions should then show up on your phone next time it syncs. Note the distinct disadvantage that importing has compared to subscribing on iPhone & in Outlook – any changes I make to the calendar will not propagate to your phone.
Windows Phone changed in the 7.5 release to allow multiple calendars per account to be synced to your phone. Hence head to http://calendar.live.com, login with the same Windows Live ID that your phone is registered with, and subscribe to the webcal link above.

Android and Blackberry

I don’t know the best steps for getting it onto Android or Blackberry devices so perhaps someone could let me know. I’m sure the same technique as used for Windows Phone 7 will work though.

UPDATE: Phil Nolan has replied answering the question about Android. See his comment below.

Hope this is useful.

@Jamiet

P.S. I am a huge advocate of iCalendar and, if you’re at all interested, my thoughts on it are perfectly articulated in this article by Dilbert creator Scott Adams: Calendar as Filter.

PDC schedule published as OData, but where's the iCalendar feed?

jamiet — Tue, 26 Oct 2010 13:51:00 GMT

Chris Sells announced on twitter earlier today that the schedule for the upcoming Professional Developers' Conference (PDC) has been published as an OData feed at: http://odata.microsoftpdc.com/ODataSchedule.svc

Whoop-de-doo! Now we can, get this, view the PDC schedule as raw XML rather than on a web page or in Outlook or on our phone, how cool is THAT? (conveying sarcasm in the written word is never easy but hopefully I've managed it here!)

Seriously, I admire Microsoft's commitment to OData, both in their Creative Commons licensing of it and support of it in a myriad of products but advocating its use for things that it patently should not be used for is verging on irresponsible and using OData to publish schedule information is a classic example.

A standard format for publishing schedule information over the web already exists, its called iCalendar (RFC5545). The beauty of iCalendar is that it is supported today in many tools (e.g. Outlook, Google Calendar, Hotmail Calendar, Apple iCal) so I can subscribe to an iCalendar feed and see that schedule information alongside, and intertwined with, my personal calendar and any other calendars that I happen to subscribe to. Moreover the beauty of subscribing versus importing is that any changes to the schedule will automatically get propogated to me. Can any of that be achieved with an OData feed? No!

On the off-chance that anyone in the PDC team is reading this I implore you, please, publish the schedule in a format that makes it useful. OData is not that format.

As an aside, I am an avid proponent of iCalendar and have a strong belief that adoption of it both in our work and home lives could have significantly positive repercussions for all of us. With that in mind I actively canvas people to publish their data in iCalendar format and also contribute to Jon Udell's Elmcity project which you can read more about at Elmcity Project FAQ. I encourage you to contribute.

@Jamiet

Thinking differently about BI delivery

jamiet — Thu, 03 Jun 2010 20:27:13 GMT

My day job involves implementing Business Intelligence (BI) solutions which, as I have said before, is simply about giving people the information they need to do their jobs. I’m always interested in learning about new ways of achieving that aim and that is my motivation for writing blog entries that are not concerned with SQL or SQL Server per se.

Implementing BI systems usually involves hacking together a bunch third party products with some in-house “glue” and delivering information using some shiny, expensive web-based front-end tool; the list of vendors that supply such tools is big and ever-growing. No doubt these tools have their place and of late I have started to wonder whether they can be supplemented with different ways of delivering information.

The problem I have with these separate web-based tools is exactly that – they are separate web-based tools. What’s the problem with that you might ask? I’ll explain! They force the information worker to go somewhere unfamiliar in order to get the information they need to do their jobs. Would it not be better if we could deliver information into the tools that those information workers are already using and not force them to go somewhere else?

I look at the rise of blogging over recent years and I realise that what made them popular is that people can subscribe to RSS feeds and have information pushed to them in their tool of choice rather than them having to go and find the information for themselves in a tool that has been foisted upon them. Would it not be a good idea to adopt the principle of subscription for the benefit of delivering BI information as well? I think it would and in the rest of this blog entry I’ll outline such a scenario where the power of subscription could be used to enhance the delivery of information to information workers.

Typical questions that information workers ask might be:

What are my year-on-year sales figures?
What was my footfall yesterday?
How many widgets have I sold so far today?

Each of those questions includes a time element and that shouldn’t surprise us, any BI system that I have worked on includes the dimension of time. Now, what do people use to view and organise their time-oriented information? Its not a trick question, they use a calendar and in the enterprise space more often than not that calendar is managed using Outlook.

Given then that information workers are already looking at their calendar in Outlook anyway would it not make sense then to deliver information into that same calendar? Of course it would. Calendars are a great way of visualising information such as sales figures. Observe:

Just in this single screenshot I have managed to convey a multitude of information. The information worker can see, at a glance, information about hourly/daily/weekly/monthly sales and, moreover, he/she is viewing that information right inside the tool that they use every day. There is no effort on the part of him/her, the information just appears hour after hour, day after day. Taking the idea further, each one of those calendar items could be a mini-dashboard in its own right. Double-clicking on an item could show a plethora of other information about that time slot such as breaking the sales down per region or year-over-year comparisons. Perhaps the title could employ a sparkline? Loads of possibilities. The point is that calendars are a completely natural way to visualise information; we should make more use of them!

The real beauty of delivering information using calendars for us BI developers is that it should be so easy. In the case of Outlook we don’t need to write complicated VBA code that can go and manipulate a person’s calendar, simply publishing data in a format that Outlook can understand is sufficient and happily such formats already exist; iCalendar is the accepted format and the even more flexible xCalendar is hopefully on its way as well.

I’d like to make one last point and this one is with my SQL Server hat on. Reporting Services 2008 R2 introduced the ability to publish data as subscribable Atom feeds so it seems logical that it could also be a vehicle for delivering calendar feeds too. If you think this would be a good idea go and vote for it at Publish data as iCalendar feeds and please please please add some comments (especially if you vote it down).

Work smarter, not harder!

@Jamiet

Subscribable World Cup 2010 Calendar

jamiet — Tue, 25 May 2010 20:02:00 GMT

I bang on quite a lot on this blog about ways in which data can get published over the web and one of the most interesting ways, in my opinion, of publishing data in a structured manner that is well understood is to use the iCalendar specification. There isn’t much information in the world that doesn’t have some concept of “when” so iCalendar is a great way of distributing that information.

You have probably used iCalendar at some point without even knowing about it. All files with a .ics suffix are iCalendar format files and that is why you can happily import them into Outlook, Hotmail Calendar, Google Calendar etc… where they can be parsed and have the semantic data (when, where and who) extracted from them. Importing of iCalendar format data is really only half the trick though; in my opinion the real value of iCalendar-formatted calendar is the ability to subscribe to them.

Subscribing has a simple benefit over importing but that single benefit is of massive importance: a subscriber to an iCalendar calendar can periodically check to see if any updates have been made and, if they have, automatically update the local copy. The real benefit to the user is the productivity gain – a single update to an iCalendar means that all subscribers are automatically made aware of the change and there is zero effort on the part of the subscriber; as my former colleague Howard van Rooijen is fond of saying, “work smarter not harder” – nowhere is this edict more ably demonstrated than subscribing versus importing of calendars.

If you want to read some more thoughts about iCalendar then go and read my past blog post Calendar syndication - My big hope for 2009's breakthrough technology or better still go and seek out Jon Udell who speaks very authoritatively on the issue of iCalendar.

With this subject of iCalendar on my mind I was interested to discover (via Steve Clayton’s blog post Download the world cup fixtures) that the BBC had made a .ics file available containing all of the matches in the upcoming World Cup. As you can probably guess this was a file that was made available so that it could be imported into your calendar of choice. It had one obvious downside though, right now nobody knows who is going to be playing in the knock-out stages so the calendar looks like this:

with no teams being named after 25th June. How much more useful would this calendar have been if the BBC had made it possible to subscribe to the calendar instead, thus the calendar could be updated with the teams for the knock out stages when they are known and every subscriber would have a permanently up-to-date record of all the fixtures in their calendar. Better still, the calendar could be updated with match results as well or perhaps even post a match report from the BBC sport pages; when calendars are made subscribable a sea of opportunity opens up for distribution of information.

So with that in mind I have decided to go one better than the BBC. I have imported their .ics into a brand new Hotmail calendar and made it publicly available at the following URLs:

HTML	http://cid-dc1ed121af0476be.calendar.live.com/calendar/World+Cup+2010/index.html
iCalendar	webcal://cid-dc1ed121af0476be.calendar.live.com/calendar/World+Cup+2010/calendar.ics
Subscribable iCalendar link	http://cid-dc1ed121af0476be.calendar.live.com/calendar/World+Cup+2010/calendar.ics

The link you’re really interested in is the second one - click on that and it should open up in your calendar software of choice. Or, if you want to view it in an online calendar such as Hotmail Calendar or Google Calendar, copy and paste that URL into the appropriate place. Some people have told me they’re having trouble with the iCalendar link in which case hit the HTML link and then click “View ICS” at the resultant web page:

If you use Hotmail's calendar then you can simply hit the "Subscribe to this calendar" button.

I shall endeavour to keep the calendar updated throughout the World Cup and even if I don’t you’re no worse off than if you had imported the BBC’s .ics file so why not give it a try? If I do keep it up to date then you will have a permanent record of the 2010 World Cup available in your calendar. Forever. If you have your calendar synced to your smartphone then you’ll be carrying match reports around with you without you having to do a single thing. Surely that’s worth a quick click isn’t it?

If you have any thoughts let me have them in the comments below. Thanks for reading.

@Jamiet

UPDATE: It turns out I have done the BBC a bit of a disservice. Their calendar IS subscribable and they WILL be updating it as you can read about at Download the World Cup fixtures. My only criticism is that (a) they haven't gone to any lengths to explain the difference between importing and subscribing nor (b) pointed out the distinct advantage of subscribing. Regardless of this I will still be updating my calendar and I might also recruit some amateur journos to do a spot of match reporting as well!