Microsoft Power BI Reviews

Note: This article is an update of a presentation given when I was at the University of Oklahoma’s Health Sciences Center. At that time, I was using Cognos version 7.3. This article is updated for Cognos 10 and SQL Server Analysis Server 2012.  Revisiting this subject ten years later, my opinion is that Microsoft has finally surpassed IBM Cognos.

Most businesses, including medical clinics, want some method for forecasting business trends. The Cognos BI powerplay presentation tools contain the tools needed for basic forecasting.  The trick in getting accurate forecasting is select the correct algorithm.

I recommend trying the model against only a portion of your data, to see how closely forecasts the remaining data.  For example, if you have 2 years worth of data, try entering only 18 months of data into the model and let it predict the final 6 months.  Then you have some actual data with which to measure ‘fit’ of the prediction.  The new Microsoft SQL tools mentioned below, allow training data and measuring fitness.

Forecasting SSAS vs Cognos

IBM Cognos Forecast Models:

The old Cognos website used to list more details about each model, but that has long since been eaten by IBM’s user un-friendly website.  I’m sure a few of my friends still enjoy ‘Recreational Math’ magazine, so here are some algorithms to play with in your spare time.

For more details,  read about the terms of service and limitations of liability on their forecasting algorithms pages.

Microsoft SQL Analysis Services

If you are using the Microsoft Business Intelligence stack, there are several data mining options in Analysis Services (SSAS).  The SQL team has worked hard to lower the barriers to entry and learning curves for data mining.   The Excel add-in makes the SSAS data mining models easily accessible to business users at the desktop.  I will have more about these tools soon.

Again, for details and limitations, see the Microsoft articles on each algorithm.

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.

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:

Technical Benefits:

• 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)

Functional Benefits:

• 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)

Cost Benefits:

• 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:

1. 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)
2. 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.
3. 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.
4. PowerPivot – Let’s be honest.  It’s just cool!  In-memory BI is a hot topic.  We also like tools like Tableau and Qlikview.

Last year ServiceNow released ODBC access to their database, enabling customers to produce their own high quality reporting.

As direct reporting from ServiceNow is in increasing demand, I thought I’d put together a brief overview of the more popular choices of Reporting Software on the market.

Of course, if anyone has experience of any others, or wants to add to (or contradict!) my views, please feel free to post.

MS SQL Reporting Services (SSRS)

MS SQL Reporting Services is part of the MS BID package of Business Intelligence software but is a capable piece of reporting software in its own right which produces professional results.

But its main selling point is that it is not sold!  If you already have MS SQL Server, it is free.  This is fantastic news for software of this calibre.  And because SSRS belongs to a suite of programs within SQL Server, there is no extra cost to schedule reports to run at set times automatically.

Report development in SSRS is quite SQL heavy, which is great for DBAs and programmers, not so much for people used to Excel as a reporting tool.

Of course, the flip side of it being free if you already have MS SQL Server is that it is expensive to buy a database just to get the free reporting software.

Because SSRS is intended for use with a suite of other software, it can struggle to do some of the more complex things that other programs in this list can as it is not a full solution in its own right.  This can lead to a ‘bitty’ architectural solution with functionality being spread out without any apparent order.

SSRS is primarily designed for use with MS SQL Server.  As a result, pointing it at over databases can be trickier than the other software in this list (who were created independently of any particular database).

Hiring SSRS expertise can be difficult as consultants tend to be DBAs, rather than SSRS specialists and are in high demand.

Business Objects

‘Business Objects’ tends to refer to an actual suite of software which together form an impressive toolset for data manipulation and display.  The Business Objects component itself is focused on taking the tables from a database and transforming them into a structure ideal for reporting: called a Universe.

With the latest version of Business Objects there is a choice of Web Intelligence (WebI) and Crystal Reports for Enterprise to actually cut and display the data gathered in the Universe.

(Older versions of BO have Desktop Intelligence, but I strongly recommend avoiding this option, it is not future proof and produces reports that just look dated.)

Crystal Reports is covered as a separate solution later, but viewed purely as a means to report on a Universe, it is a match for WebI and arguably better in some respects.  But Business Objects and WebI have been used together for longer and most BO developers are also WebI developers, whereas Crystal Reports is often a separate skill set.

Most of the shortcomings in WebI functionality is covered by the work already done when developing the Universe.

One of the best things about a Universe is that all the statistics you want to create can be done in one place and then included in reports as needed.  There is no duplication of effort that tends to occur in standalone reporting and a standardisation of reporting metrics is enforced automatically.

Scheduling reports is not a problem, as the scheduling software is included within the standard Business Objects suite of products.

However, this standardisation can also be a hindrance.  Professional level ITIL reporting often requires a very flexible approach to data interrogation to cover certain measures.  Often some measurements are at logical odds with other measures based on the same data.  This is where Business Objects can get mired down trying to accommodate all requirements in one place.

Even with free software (and Business Objects is far from free!) implementing an ITIL reporting solution costs money.  Expertise usually has to be hired in, time and money is spent on requirements gathering, hardware and so on.  Setting up a Business Objects Universe and then a complementary suite of reports can take weeks or even months.

This can be too long a wait for a business eager to give their managers the information they need to work at full capacity.  This is a shame, as the table structure of the ServiceNow database is so well thought out there is almost no need for a Universe anyway beyond the aforementioned efficiency of effort.

Crystal Reports

Crystal Reports can report from virtually any data source including of course, the ServiceNow ODBC, is quick to develop with and can produce a wide variety of reporting styles.

Crystal’s inbuilt scripting language allows a huge amount of control though can take some time to learn for those new to programming.

Crystal Reports is hugely popular and used across all business sectors and is versatile enough to do just about any job.  This versatility can also be a problem and without proper work practices in place a suite of Crystal Reports can become an unmaintainable mess.

The main negative thing for Crystal Reports is that it requires a separate piece of scheduling software to automate reporting.  Whichever scheduling software is chosen, be sure to thoroughly test it within your business before deployment, especially the security if you intend distributing reports outside your own intranet.

The charting can also be a bit limiting and is starting to look a little dated now, but still crisp and clear.

Another possible minus for Crystal Reports is that if you do not have the skills in-house already, it can be tricky to hire an effective consultant.  Unlike SSRS and Business Objects developers, who tend to come from a DBA or programmer background, a large percent of Crystal Reports consultants started in office admin jobs and tend not to have the technical experience needed to solve the more difficult questions.

Xcelsius / Crystal Visualation / SAP Dashboards

This product of many names was originally developed as an add-on to MS Excel and still uses Excel for much of its underlying functionality.

The result is reporting software with a shallow learning curve which produces gorgeous, interactive looking Dashboards that can be easily exported and distributed online.

Of course, there is a downside, or two.

The main one is that plugging Xcelsius directly into databases is a pain.  It does not have that underpinning ODBC foundation like the other products in this list and data must either be piped in via another product entirely or through a third party component that plugs straight into Xcelsius.

The good news on this front is any company using ServiceNow probably has some good Java developers at their disposal that can develop Web services to connect Xcelsius to the database.

On a final note, anyone using Business Objects and/or Crystal Reports should add Xcelsius to their arsenal.  It integrates well will both software and is definitely worth the effort in this case, both for dashboard designs and more flexible/nicer looking charting in standard reports.

JasperReports

This software is not really in the same league as the above products in many respects and may look like the odd one out.

But it does have a number of strong benefits in its favour:

1.  Very capable software and produces professional results to challenge any other product in this list.

2.  It is free.

3.  Very, very similar to Crystal Reports, so similar that a Crystal Reports expert can quickly get to grips with JasperReports.

4.  Java based and can be distributed through your organisation with relative ease.

JasperReports is definitely worth a look for any serious ServiceNow reporting implementation.

Summary

All of the above software has its own pros and cons which are largely dependent on the target organisation’s existing software, infrastructure and skill set.  With this in mind, I cannot recommend a specific piece of software, but am happy to answer any questions I can.

A quick look at the whole idea on another weblogs gives you a sense that all of them just talked about very brief things like report refresh feature in BO or cube feature in MS Analysis Service. I choose MS BI and I want to share my reasons and opinions on why I choose it and give you another quick but a little deeper compare on these two Business Intelligence platforms.

As we all know both Business Objects and Microsoft are big companies who are working on BI solutions and both have their own advantages. It’s not true to compare them in term of which one is better, we have to check what is our requirements and then depend on requirements take the decision whether MS or BO. A vision like this could help us relief from religious decisions against a software or technology.

In a BI architect first of all we have the data store level, I mean the storage of the raw data not the stage or olap cubes or universe data source, I mean the first place of our data. This is important to know that where your raw data is and what is the type of storage used to store them. Whether file system or Access or Fox database or a complex database solution like oracle, sqlserver or a web service can made our place of raw data. We have to check our tools against them; check to see which one gives us a smooth way to transfer them among ETL process to destination. So take a look at what Business Objects gives us.

There is a Data Integration platform in Business objects but the problem is that you have to buy that separately because it is not shipped with the BI system. In Microsoft sqlserver enterprise you have all the services and features needed for this part of the game. SSIS is the service that sqlserver deliver for data extract, integration and load. Both product gives you the ability to enhance the data quality and data cleansing portion of your integration phase, but when we down to details things change a little to the Microsoft side, because of the ability of using your Dot.Net knowledge to write complex parts of ETL process you have more room to think and do whatever you want in your process, and in BO side it is always look simple and it’s really not easy to take complex situation into it. There are advantages and disadvantages on this. First you can do many things with the ability of dot.net code but it could give you complexity in your development so you have to decide on your situation, if things looking normal both could fit your need, but if the situation is not stable and you have to make yourself ready for the changes in future it’s better to get the power of SSIS and spend a little more time development today to create a powerful and easily changeable mechanism that could help you in future. You can also do that with Business Objects Data Integration but you have to spend more bucks for the development and changes of ETL processes because development cost in Business Objects solutions is always a nightmare for a project.

At this point we have a brief understanding of differences in ETL process between two vendors, so it’s good time to take a look back to the source database. Here is a very quick answer, if you use mostly MS products to store your transactional data then take your decision and move to MS for a robust and compatible BI platform. Business Objects don’t have a database system and it always used other database solutions to store data for its universe.

So guess what happen ! from an administrator perspective performance tuning is somehow problematic ! since we should use other database systems we should use different technics for each database systems. And this is one of the areas that MS wins the competition because when you use Microsoft platforms there lots of joint mechanism for performance considerations.

Before the SQL Server 2012 we have SSAS with its famous aggregated cubes, because of the nature of SSAS in previous versions we couldn’t call it a semantic layer, here is a little why. A semantic layer provides translation between underlying data store and business-level language(Business semantic that business users familiar with). There was no actual translation in previous release of SSAS. Perhaps we had some difficulties over SSAS to understand for a business user. So Microsoft change its approach in SSAS 2012 from delivering a complex understandable solution to end users to a true semantic layer like what we has in Business Objects that called Universe. So from now MS BI users can use a powerful toolset like Microsoft Excel and use their existing knowledge to interact with semantic layer. What Microsoft do in backyard is to create aggregations in memory so the performance of this approach is really high ! I don’t want to deep dive into what Microsoft do in backyard in this post but it would be one of my next topics. (sounds like advertisement

I talked about aggregations so know that in BO there are no facility for aggregation tables, so you have to deal with DBAs to create aggregation tables manually and integrate them into the Universe.

One of the important aspects of a BI system is the learning curve of the solution, it was always the slogan of the Business Object that learning curve is very low ! yes for end users it is not hard to interact with Universe. BUT ! the thing that I say here is the problem of every BI platform from Microsoft to BO or Cognos that deliver Semantic layer, it is very easy for a user to get the wrong answer, because everything is behind the Universe or Semantic Model and know that tracking from report back to the base data is a Non-trivial task. So be aware about letting users create whatever they want with their own knowledge. There should always an IT professional observing the whole process. So never think about a fully out of the box solution, because you will shortly find it on Mars ! or your users may have the chance to take decisions based on wrong calculations and find their way to Mars again

Another important aspect of a BI systems is the cost of it, about the Business Objects we can definitely say that it is expensive and for sure Microsoft could be expensive ! but how can we decide ' the answer is to compare the detail parts, there are 4 main parts Database, ETL, Semantic Layer and Reporting or user interaction layer. If you choose to go over BO you have to find heads for your data warehouse, database solution and Java skills or tomcat or other J2EE platform professionals for ETL and development phase and BO specific heads for Universe Modeling, Design, Implementation, perhaps you need security administration and if you want to integrate your Active Directory with this platform it is problematic and integrating with other LDAP platforms is a nightmare ! so be aware of these costs. The point of Microsoft solution is that we can use our in house knowledge like Dot.Net and SqlServer, SharePoint, Windows Server and these knowledge are transferable to other skills. But with BO we need headcount dedicated to BO (Universe Design, Implementation, Maintenance, Security) since BO skills are not transferable to other skills, those extra heads blow the project’s budget ! Microsoft BI platform is a more manageable, more secure and less expensive solution, I see the BO as a consultant dream, as an endless font of billable hours

Conclusion

I decide to go over Microsoft BI platform but I would not suggest anyone at first place to choose Microsoft. This is really depend on the nature and scale of the project and what you did and what technologies you have used in past but a quick look gives an idea that Microsoft’s platform is looking more robust and coherent in different parts so it can be a very good and convenient choice and perhaps after the release of SQL Server 2012 and its BI Semantic layer the answer is more easier and acceptable than before.

I also would like to hear about your experience on either of these solutions.

Valuable Features:

Room for Improvement:

Other Advice: