In a recent post, I discussed the changes in the business intelligence landscape as outlined by Gartner in their 2013 Magic Quadrant. Today, I wanted to focus solely on Microsoft as a vendor in this space. Yes, I mentioned Microsoft – and I work in Higher Education!
In working with a number of higher education institutions over the years, I often hear direct concerns about “Microsoft.” In the academic world, we are concerned about the most open way of doing things. We like to share – and you may have noticed by the adoption of Sakai and the Open Source Portfolio (OSP).
The emergence of open-source tools was prevalent over the last few decades. You now see many organizations running miscellaneous versions of Linux, open source wiki tools, Drupal-type content management systems – and now many have implemented Google (Google Drive, Google Docs, GMail). If you mention “Microsoft” – you’d better start running. You’ll have someone from IT chasing after you pretty quickly – and not in a good way!
Ok – you’re not Jack Sparrow, so you can relax a bit! But, you can imagine the feelings of many of these IT organizations when you start to implement enterprise-level software that holds a significant cost and the source is proprietary. Think Sungard’s Banner (now Ellucian), or PeopleSoft, and maybe even Workday now in some cases. Somehow, Oracle has slipped through the cracks as many of these large ERP vendors require Oracle’s database platform. Oracle was also smart and acquired mySQL – so they have an almost natural support of the open source community. Oracle is an investment, too.
You’re probably asking – what’s your point? My point is that Microsoft isn’t bad. It’s actually very, very GOOD! Besides the educational licensing, and the obvious love for Microsoft Office (Excel, Word, PowerPoint, et al) – let’s look at some of the benefits of Microsoft’s SQL Server platform. Let’s start with a basic point that is often overlooked. It is a suite of tools, not simply a database platform. I have listed a basic table below, but you can read more on Microsoft’s website.
|SQL Server Database Engine
||SQL Server Database Engine includes the Database Engine, the core service for storing, processing, and securing data, replication, full-text search, tools for managing relational and XML data, and the Data Quality Services (DQS) server.
|Analysis Services (SSAS)
||Analysis Services includes the tools for creating and managing online analytical processing (OLAP) and data mining applications.
|Reporting Services (SSRS)
||Reporting Services includes server and client components for creating, managing, and deploying tabular, matrix, graphical, and free-form reports. Reporting Services is also an extensible platform that you can use to develop report applications.
|Integration Services (SSIS)
||Integration Services is a set of graphical tools and programmable objects for moving, copying, and transforming data. It also includes the Data Quality Services (DQS) component for Integration Services.
|Master Data Services
||Master Data Services (MDS) is the SQL Server solution for master data management. MDS can be configured to manage any domain (products, customers, accounts) and includes hierarchies, granular security, transactions, data versioning, and business rules, as well as an Add-in for Excel that can be used to manage data.
The great part of purchasing Microsoft SQL Server is that these tools come out of the box – and are included with the license for the database platform. There are several different editions which provide more or less horsepower as your project requires, but this is an added bonus that Microsoft bundles these tools.
Here are a few thoughts from my experience and why I enjoy working with Microsoft BI tools:
- Relatively easy to deploy and installation is wizard-based
- Learning curve to adopt SSRS and SSIS is reasonable in comparison with other tools
- Direct integration with Windows operating system and Active Directory (this is great if you have a nice active directory structure already in place; not so helpful if you do not).
- Direct integration with Team Foundation Server (TFS) for version control
- Platform is sophisticated enough to handle complex tasks (i.e. stored procedures, SSRS data driven subscriptions)
- All-in-one solution (combine with SharePoint for full functionality)
- End-user tools are intuitive and within a familiar Microsoft interface
- SharePoint can be used to pull information together in a one-stop-shop
- Office integration (i.e. Excel, PowerPivot)
- Educational and non-profit discounts are a nice way for Microsoft to give back.
- License costs, on average, are lower than combining multiple tools from multiple vendors (this always depends on your situation and the license agreements that you have in place).
- Total cost of ownership (TCO) tends to be lower. This is due to the license fees and also the availability of technical resources that are familiar with the Microsoft platform. Again, this is completely dependent on your situation, but this is what I have seen with other clients. It may also be indirect, but by having all of these tools with one vendor, you spend less time managing 4 or 5 invoices for maintenance and renewals as well. And, if you need to renegotiate anything – it is again done with a single vendor not 4 or 5.
My Favorite Features:
- SQL Server Management Studio (SSMS) – it seems silly, but this a great tool and I enjoy testing my queries within SSMS prior to loading them into SSRS. It has some really nice features built-in for ETL developers as well (i.e. the ability to script the creation/deletion of a table with a mouse click)
- SSIS Package Variables – I use them frequently to make dynamic filenames in my SSIS routines. They are flexible and allow SSIS routines to handle a number of complexities that would otherwise be very difficult to address.
- Data-driven subscriptions – this is a great way to deliver tailored content to your user base. Same report…different content. In a previous consulting organization, I used data-driven subscriptions to improve internal processes and implementation times for external projects.
- PowerPivot – Let’s be honest. It’s just cool! In-memory BI is a hot topic. We also like tools like Tableau and Qlikview.