So what does SQL 2005 mean to an ISV? Part 2

  • Article

Business Intelligence

 

Folks, if you really want to seize an opportunity to drive some demand for your products you have to take a serious look at embedding some Business Intelligence into your product.  Don't believe me? Have a look at this Gartner survey of 1,300 CIO's for 2005. A couple of interesting things to note:

  1. The second most important technology priority (after security) for CIO's is Business Intelligence.
  2. Not only that, but the most important business priority for them is business process improvement

This is a huge opportunity for you and I'm telling you straight up that SQL 2005 will give you an easy way to deliver this functionality in your product. I say that because SQL Server is Microsoft's Business Intelligence platform - simple as that. If you choose to leverage SQL 2005 as your database of choice, you have the ability to leverage the BI capabilities that come along with it. More importantly, SQL 2005 really makes the effort involved to harness this functionality dramatically less. Let's talk a little bit about why that is... 

 

To start with, one of the great things about SQL 2005 is the fact that creating BI solutions is so much easier. I can say that with confidence as I'm even able to figure out the basics ;-) There is something called the Business Intelligence Development Studio in SQL 2005 that you use in order to create your data source, run a wizard to create a cube and dimensions (and the wizard does a really good job of suggesting what a lot of the various settings should be), use a great new visual designer to explore or fine tune it, and finally deploy. So yes, there are some great new tools to help you get the job done much easier. For details on this check out this tutorial included in the documentation. However, there's more to this story if you peek under the hood so lets talk a bit about one of the exciting new pieces of Analysis Services in SQL 2005 - the Unified Dimensional Model (UDM).

 

Ok, I admit, when I first heard about this I think I just gapped out for a bit and my eyes glazed over as I was struggling to get what the big deal was about this "UDM" I kept hearing about. So, I'm sure you're likely thinking the same thing <HomerSimpson>"BORING!!"</HomerSimpson>… However, once I understood more about it, I now see why it's such a revolutionary type of thing and why all the BI power nerds were talking about it so much.

 

To kick things off, Paul Sanders wrote an excellent whitepaper that does a great job of explaining the UDM but here's my stab at the general gist of it…

 

Here's what you don't want to have users, or even power users for that matter, having to wrestle their data from:

 

Honestly, when most people here about "BI" they conjure up a complex image similar to what is shown above and then conclude it's not worth their while, or, it costs too much money to get this functionality from a third party.

 

 

Here's something that's far easier for people to understand and therefore actually use:

 

What you are able to build fairly easily in SQL 2005 Analysis Service is something much more user friendly. Don't get me wrong, this isn't the UI you would be displaying to the user, this is actually the UI in the development environment but it gives you a good idea of what you can do. Specifically, note the following:

  • Notice "Employee" and "Product". These are both dimensions that the user is able to drill into (or "slice and dice") however they want. The key point here is that in SQL 2005 you are able to associate lots and lots of attributes and hierarchies to the dimension. So what you say? Well, take a look on the right hand side of the screen shot. What that's showing is that someone drag and dropped "Product Category" from the product dimension, then they drag and dropped "Sales Amount" and "Sales Quota". By simply dragging and dropping three items they are able to immediately see the sales amount and quota by product category - and this is just scratching the surface!! To relate this all back to the UDM I started talking about, the UDM enables you to surface all the attributes of each dimension so instead of having to only pick 5 or 6 attributes you think the users will need, the UDM allows you to pull them all in and then be able to slice and dice the data in ways you never thought of doing. For example, dragging and dropping "days to manufacture", or "Color", or "geography" etc.
  • Two key points I mentioned in the point above - attributes and hierarchies. These are very core to the functionality we're ultimately trying to provide the end user. The cool thing about the UDM is that it doesn't force you to be selective in what attributes or hierarchy you choose to create. The UDM actually provides the exact opposite approach! The UDM allows you to pull in all the necessary attributes and hierarchies you need as well as allowing you to further customize them to your hearts content. In addition, the UDM handles the details of how to navigate between the levels of the hierarchy. Here's a good example of someone being able to leverage hiearachies in order to drill into products by category, and then by subcategory.

 

To really make this easier for end users, the UDM provides support for Key Performance Indicators. KPI's are important metrics that businesses use to gauge the performance of the business. Or in other words, KPI's really help tease out the decision making out of the data by associating visual aspects to the data such as traffic lights, arrows etc. Here's a good example:

 

Finally, the UDM is great as it's ultimately just was it says it is - a "Unified Dimensional Model". Or in other words, it's a great thing as what you can do is pull together data/databases from all sorts of places and then use Analysis Services in order to build a single unified way of analyzing this data. This saves countless headaches as you're really putting a nice layer of abstraction over top of all these various data sources. In addition, you have complete flexibility in the UDM to go in and create new relationships in the data (of course this doesn't affect the underlying data source), new columns, new hierarchies etc. in order to really create a powerful representation of this underlying data.