Why Analysis Services?

I'm going to do a series of blog posts around Analysis Services this month to show you how to get up and running with Microsoft's flagship product in their Business Intelligence offering. Its been available in SQL Server for a while, but has recently been available in Excel through an add-in called PowerPivot. Don't worry, we'll get through all the terminology later. For now, I want to step back and make a business case for Analysis Services.

Business owners, executive boards, and directors only have one thing in mind: How to grow their business. Technologists have a tendency to go after cool new technology, when all the big-picture thinkers want to know is "when are you gonna get it done" and "how is it going to make us money". I'm partially writing this post for business owners, but I'm also helping IT folks try to shift their focus from the technology aspect of the BI stack, to selling the business case for Analysis Services. Get clearer on the ROI, the value-added, and how its going to increase profits or make business run better. So let's step back and see "why Analysis Services?" I'll break it down into a few topics:

1. What is Analysis Services?
2. Why Analysis Services adds value to your business?
3. How does Analysis Services compare with other 3rd party tools?

What is Analysis Services?

Analysis Services is a lot of things now-a-days, but all of these things can be simplified if they are just thought of as one technology. Here's some terms you've likely heard:

OLAP Cubes (Online Analytical Processing) Multidimensional UDM (Unified Dimensional Model) MOLAP/ROLAP/HOLAP Data Mining

Tabular Model PowerPivot BISM (BI Semantic Model) xVelocity (previously Vertipaq)/DirectQuery

Sound familiar? Well guess what, all these terms are talking about Analysis Services! My favorite of these terms is the BI Semantic Model (BISM). Here's a graphic that I feel says a lot about what exactly Analysis Services, or the BI Semantic Model, is and how it fits within Microsoft's BI stack.

Please notice that Analysis Services comes in two flavors: Multidimensional and Tabular. One is pre-aggregated, the other is aggregated on the fly in-memory, respectively. Don't let the PowerPivot icon fool ya; PowerPivot is still just Analysis Services inside of Excel.

So Analysis Services is a data source that serves as the Semantic layer for all your BI clients. Its where you can define important business entities such as product lines or service offerings in one place with all the key metrics supporting them. It can also hold sales targets and forecasts which are updatable and visualized through KPIs. You can create complex calcuations and relationships between the different areas of your business. You can also provide translations and dynamic security. Lots of good stuff in this Semantic layer which can be easy to setup with recent developments in Analysis Services.

Why Analysis Services?

Do you currently switch back and forth between reports to compare business figures in different regions, across product lines, or prior time periods? Do you need work out scenarios by grouping products or services in different verticals, product lines or categories? Have you ever wanted to take two unrelated measures and combine them, but thought it was impossible? All of these can be done with Analysis Services and a good data model.

Many technologists will say that SSAS is awesome because of performance. Although this is true, its a weak value proposition to businesses unless real-time is of real value. I had a COO tell me once that real-time is not a good enough excuse to go with Analysis Services. It took me a while to appreciate what he was trying to say, but the truth is that unless it helps the business run better or make more profits, business owners don't care about split-second performance. Many times, they are willing to wait 1-day, 1-week, 1-month to get a report handed to them created by one or more people in Excel. Why? Because that report is valuable to run the business, its easy to use, and intuitive.

So instead of performance, BI Pros should be much more interested in creating interesting analysis with SSAS that are easy to use, intuitive, and give the right insight at the right time. Help make businesses run more efficiently or make better decisions. Look for those golden nuggets like key metrics, useful KPIs, badly needed drill-through reports that you know the business will need to keep the engine running. It's a hard thing sometimes when all you want to write down what business users want, but in many cases, they're not sure what they want. Try to get intimately familiar with how a business runs first, then look to see what kind of data will be useful to put inside of Analysis Services and how it can be served up to people to help them do their job.

How does Analysis Services compare with other 3rd party tools?

Analysis Services is sometimes compared to other BI tools, but the fact is that its a data source. So be careful what you compare it with. It doesn't help clean your data, model it, or visualize it. This is where many other tools in Microsoft BI stack come in like EIM, SSIS, or Power View. However, out of all these tools, Analysis Services is in fact a great asset to have in your BI arsenal.

A couple 3rd party tools that may come to mind are QlikView and Tableau. Although these are great visualization and self-service BI tools, they don't promote best practices when it comes to data modeling. They're extremely powerful in terms of visualization, but that's not what need Analysis Services addresses. Analysis Services provides the pre-calculations, hierarchies, and data structure which these other 3rd parties can leverage. Analysis Services also requires a star schema data model which is essential in any BI solution for a company.

I've had a couple clients now that have used Tableau to dig deeper into their business data. They come up with great ideas using Tableau visualizations, but they call me in when they need to distribute this information to their enterprise. In each case, Analysis Services helped each client in slightly different ways. One client couldn't afford Tableau Server to share the analysis with all 200 users of the company, so we developed an SSAS cube and an ASP.NET dashboard so that dynamic filters and drill-down reports could be easily accessed. Another client had two Fact Tables with different grains and dimensionality which they wanted to mashup in Tableau. Custom code (MDX or DAX) in Analysis Services was used to accomplish what would could not be done in normal SQL statement.

Conclusion

Analysis Services cubes can be one of the greatest assets to a company for its ability to serve advanced analytics, predictive analytics, and structure in an intuitive way for the whole enterprise to use. Hierarchies, KPI's, and drill-through actions are built-in to provide flexible searching and discovery. Business entities are related to one another over time, region, or business division. It reinforces best practice by requiring you to build a good data model, so in a lot of ways, Analysis Services may make your data cleaner by slicing it in different ways. The most important aspect of Analysis Services is its ability to serve insight around the things that matter most to your company, whether that be customers, vendors, products, services, or projects.

Look forward to posting more details on Analysis Services this month. Until then, much success in all your BI and database projects.