Converting dates and times in SQL Server

Heres a great link to a page which displays the correct SQL syntax for converting your dates into other formats.

It’s helped me out on several occasions!

SQL Bits V conference – 19th – 21st November 2009

A date for your diaries if you are interested in all things SQL Server (including BI).

The excellent SQLBit conference is making its way to Wales and is hosting SQLBits Goes West V at Celtic Manor, Newport on 19th – 21st November 2009.

I attended SQLBits Cubed III held at the University of Hertfordshire in Hatfield one sunny Saturday last September. I found the various tracks and speakers of a high standard and came away with several ideas to implement. I wrote about this in the following post.

I thoroughly recommend attending one of these SQLBits conferences if you can. They are normally held on a Saturday and are free. This latest one is spread over three days, and there will be a chargable element if you attend during a weekday. The Saturday community conference day will be free. However, I foudn the content of previous conferences to be of high value, so it is well worth attending all three days.

If you are interested in attending the event, you can register here.

Performance Point Server – Product Update

Microsoft confirmed that they are planning to stop development of Performance Point Server (PPS) as a standalone product after Service pack 3. It goes on to say to PPS features will become embedded as Performance Point Services within SharePoint Server. Video announcement (5m:12s).

To be honest, I half expected this announcement from Microsoft regarding their PPS product. I spent some time working with the PPS product and became MCTS certified in PPS. Based on the clients I worked with, PPS didn’t really take off as a standalone BI product. It now looks like PPS itself is set to follow a similar fate as that of ProClarity (a product which I really liked).

So where does this leave organisations that invested in PPS or looking for a Microsoft BI solution? Well, it will still be there in some form or another within SharePoint, so all is not lost. Microsoft have also stated that it will continue to be supported as per their licence agreements with end users. Users of SharePoint Enterprise will now find that Performance Point Services will come bundled for free as part of their licence. This may have the effect of increasing its usage and popularity. So if you were a Microsoft shop using the excellent SharePoint portal and were looking to use PPS, then this is good news.

For those companies looking for a standalone planning application, what else is available? Well, an option from the SAP Business Objects camp is the SAP Business Planning and Consolidation product which integrates the planning software developed by OutlookSoft (purchased by SAP in May 2007).

And for those that are looking for a good data visualisation tool, consider Xclesius.

For more comment about this announcement, take a look at the following blog entries:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!4133.entry

http://www.timoelliott.com/blog/2009/01/microsoft_kills_performancepoi.html

http://peterkol.spaces.live.com/Blog/cns!68755AEAC31F9A6C!992.entry

http://www.panorama.com/blog/?p=129

BusinessObjects v Other BI Tools

So what is the best BI Reporting Tool on the market today?

This is a difficult decision and one which is subject to personal preferences. I’ve worked with a number of BI tools over the years using products such as:

  • Business Objects
  • Cognos
  • ProClarity (now Performance Point Server)
  • Panorama
  • SSRS (SQL Server Reporting Services)
  • Oracle Forms & Reports
  • …a bit of Crystal

So which of these is the best BI reporting tool?

Panorama and ProClarity provide a ‘freshness’ to reporting. Users find their tools easy to use and the type of output these products generate should really make the larger BI vendors sit up and take notice. ProClarity introduced a number of new graphical representations of data which I haven’t seen in other products. I’m not sure why, but once you play around with these charts, you can’t help but be impressed. One of my favorites graphical displays is the heat map. An example of a heat map in action can be found here. This example shows the the NASDAQ-100 in one dimension. ProClarity allows you to add another dimension which will determine the size of boxes depending on a second variable. Nice.

I haven’t used Cognos products for a while, so my opinion may be outdated. However, I didn’t find it’s product suite as integrated as that of BusinessObjects. It does have it’s fan base however, and it’s customer base suggests it is no small player. Recently bought out by IBM, it is going to give BusinessObjects a real fight. I welcome this. You need companies to be competing with each other to push forward innovation.

I found that Oracle Forms/Reports and Crystal Reports good for static reporting – the kind of reports that are not likely to change any time soon. Crystal is an excellent product, especially if you want to embed reports into existing code or applications. It is fairly simple to pick up and I find it quite powerful. One of BusinessObjects’ better purchases I think. To be fair to Oracle, I haven’t used it’s BI product suite. Hyperion, which was bought by Oracle, was a highly regarded BI vendor, so I am sure Oracles BI suite is impressive. Note to self: I should really become familiar with Oracle BI.

And finally, SSRS. To be honest I’m not a massive fan of SSRS. It not really and end user type of product.  However, its low entry price (and often no additional cost as most sites will be using SQL Server) make this a popular choice. However, I don’t think it offers the features of some other products in my list. Things are changing in the Microsoft camp however. Following it’s purchase of ProClarity, it has integrated it’s BI toolset into Performance Point Server. Microsoft has quite a strong BI case now. For sites using SQL Server as it’s database, it now has an attractive stack whereby for a low cost, users can product OLAP cubes, develop powerful reports, and integrate all of this into the well established Office suite of products. At the end of the day, many sites have been using Excel as their ‘reporting tool’. It is going to be difficult to wrestle users away from Excel, and with Microsoft building on that platform, it is making life difficult for the bigger BI players.

So what about BusinessObjects? It is now quite a mature product, and immensely powerful. I dare say that most users merely scratch the surface of what BusinessObjects is capable of. Its products are very well integrated, and it offers a full product stack from the warehouse ETL to dashboards. But that is exactly the point. I find BusinessObjects strong in Query and Analysis and ETL, but weak in Analytics and Dashboards. Yes, BusinessObjects has Xcelcius and Performance Manager, but I do believe that Panorama and Performance Point Server/ProClarity offer a better Analytic/Dashboard tool.

So what do  prefer? Difficult to nail it down, but if I were setting up from scratch:

  1. BusinessObjects Data Integrator for ETL. (I know Informatica is the market leader, but Data Integrator really is a fantastic product and integrates well with the rest of the BusinessObjects stack).
  2. BusinessObjects XIR2 for Query and Analysis (XI 3.0 is not yet established enough)
  3. Panorama or Performance Point Server for Analytics and Dashboards.

It should be interesting over the next few months. XI 3.0 will become more established, PPS should begin to mature and integrate more functionality from it’s ProClarity purchase, and it will be interesting to see how much influence SAP and IBM have on their recent purchases.

I’m keen to hear your views. Feel free to share your views and comments – I’ll add them to the post. It is quite lonely in the blogosphere…

Addendum: SAS are also a strong player in this market space. However, it appears to be somewhat of a closed shop. I have no experience of SAS. Anyone care to add a comment comparing SAS to the other tools I have discussed?

MCITP SQL Server: Business Intelligence

I need to set some personal objectives for the next six months and I’ve been considering working towards the Microsoft MCITP SQL Server: Business Intelligence certification.

This requires that I pass two exams:

  • 70-445: Microsoft SQL Server 2005 Business Intelligence – Implementation and Maintenance
  • 70-446 exam: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005

Passing the 70-445 gives you the MCTS SQL Server BI certification. Passing the 70-446 gives you the MCITP. The 70-445 is a prerequisite to the 70-446.

According to the latest MCP figures, there aren’t that many MCITP BI developers out there, so this may be a challenge I’ll take on.

From the company perspective, it will be a big plus as it will earn them their third competency – Business Intelligence, and will enhance our Microsoft Gold Partner status. Hitting this milestone should also yield a good bonus at the end of the year! On a personal level, it will allow me to build on my existing BusinessObjects and Performance Point Server skills and pick up more of the Microsoft BI toolset.

I’ve worked with Microsoft SQL Server a great deal and am currently playing around with SQL Server 2008. I’ve also used most of the Microsoft BI tools as well; SSIS, SSAS (for cubes) and SSRS. Having looked through the exam preparation guides, I think I can clear these two exams in the next few months.

I don’t think I’ll take any formal classroom based training for these exams. I’d rather use my training budget on something else, possibly Crystal Reports or BusinessObjects Data Integrator. Hence, I’ll be using the MS book MCTS Self-Paced Training Kit (Exam 70-445): Microsoft® SQL Server™ 2005 Business Intelligence—Implementation and Maintenance to get me going.

Once this is out of the way, I might start looking at Sharepoint, and portals in general.

Performance Point Server Exam

As a consultant working in the ever changing Business Intelligence arena, I have to keep up to date with the various Business Intelligence applications available on the market.

We are all familiar with the applications available from the big players like Business Objects and Cognos. We must not however lose sight of what the other players are doing. Microsoft is rapidly climbing the Business Intelligence tree and is building on it’s existing product suite through it’s purchase of ProClarity. Panorama recently announced it is working with Google to provide BI functionality to it’s Google Apps. I blogged about this in March 2008. And other companies like Tableau and Bizzantz offer exciting products in their own right.

I’ve helped a number of clients with their ProClarity solutions in the past, and with the release of Performance Point Server, I felt it would be worth becoming familiar with the product and getting certified.

For the past few months I’ve been studying and practicing for the Microsoft Performance Point Server exam – 70-556. I took the exam last week and passed.

This gives me the MCTS – Office Performance Point Server 2007, Applications certification:

MCTS

Performance Point Server introduces some of the technologies gained from their ProClarity acquisition and merges it with technology from their Business Scorecard Manager application.

Taken straight from the Microsoft website, “Microsoft Performance Management allows customers to monitor, analyze, and plan their business as well as drive alignment, accountability, and actionable insight across the entire organization.” Yes…well.

Performance Point Server is indeed a powerful analytical tool allowing you to generate meaningful information from the vast quantities of data available to most, if not all, organisations today.

For those considering taking this exam, there are a wealth of resources available. I have used ProClarity on a number of occasions in the past, and was familiar with some of it’s reporting functionality and back end configuration. However, I strongly recommend further reading to prepare you for this exam.

First off, I recommend two books. The first is The Rational Guide to Monitoring and Analyzing with Microsoft Office PerformancePoint Server 2007. The second is The Rational Guide to Planning with Microsoft Office Performancepoint Server 2007.

Both books are straighforward and provide a good overview of Performance Point Server. Note that these books do not drill down into every detail of Performance Point Server, and none of the books covers every aspect of Performance Point Server individually. However, I don’t think this was the aim of the books. I believe their aim is to give a good overall understanding on some the aspects of the product, and I think they have succeeded.

You might find that there are still some gaps in your knowledge. You can fill these gaps by supplementing your reading with some of the additional resources I’ve listed further down.

Microsoft has three useful and important guides, available via TechNet:

PerformancePoint Server 2007 Planning & Architecture Guide

Deployment Guide for PerformancePoint Server 2007

PerformancePoint Server Operations Guide

Have a read of the useful Performance Point Server team blog. These are some very handy posts which will come in handy in the future.

Don’t forget to visit the Performance Point Server section of Technet. This has a wealth of information, including links to downloads, service packs, tutorials, documents and blogs.

Finally, although there isn’t an official Microsoft course, you must make use of the free learning courses available from Microsoft. These are 14 short modules which cover most aspects of the syllabus.

To prepare for this exam, I loaded the exam prep guide into Excel, and checked off every point as I covered it. I also downloaded the Performance Point Server 180-day trial to play around with. Use the AdventureWorksDW as your data source, and you should be able to create a few reasonable reports.

So there you have it. Enough information and resources to help you become familiar with Performance Point Server and with some study and practice, pass the 70-556 exam. Good luck!

Microsoft BI Conference 2008

Registration has opened for the second Microsoft Business Intelligence Conference.

This will be held in Seattle, WA on October 6-8.

With Microsoft rapidly climbing up the BI ladder, this should be an interesting conference.

%d bloggers like this: