I recently had a client ask me to explain the different types of BI in SQL Server 2012, and explain what each tool could be used for and who the ideal target audience would be. I thought this would be a great blog post, as I’ve heard this question passed around quite a bit in the community and there always seems to be some confusion. I’ve placed a quick description of each below, in order to help. A really important one that I feel gets overlooked a lot is the concept of Team BI.
There are 3 distinct types of BI: Personal, Team, and Corporate.
Personal BI is designed for the individual. This is data that can be dumped very quickly for consumption by an individual business user. This gives the user extreme flexibility into having access to the data they will know best and has the flexibility to join multiple data sources together.
Team BI is the next phase in the evolution. Team BI is a platform designed for sharing amongst individual teams and departments, but not necessarily across the entire company. Departmentalized reporting is another way to describe this. Once an individual builds a personal BI report (in Excel or PowerPivot) and wants to share it with others by loading it into a SharePoint site (such as an Excel document library or PowerPivot gallery) would be an example of Team BI.
Corporate BI drives the other 2. This is the gathering of the data, putting it in a data warehouse, building analysis services databases, and delivering the mechanism. It is the standard that drives all of the others. It is available to all users across the company, can handle huge workloads, and has a very large feature set. SSAS Multidimensional, SSAS Tabular, PerformancePoint are all examples of Corporate BI. Notice how SSAS Multidimensional and SSAS Tabular are corporate tools, and the excel front end is personal. The process becomes full circle.
Next I’ll break down each of the tools and what they’re capable of.
Personal BI Solutions
Excel is the most widely used personal analytic tool in the world. As a part of a BI implementation, Excel is an extremely powerful BI tool that can be used to connect to Analysis Services for straight Pivot table reporting regarding aspects of the business. Personal dashboards can be created in excel across multiple tabs and scheduled for refresh upon the workbook opening.In Excel 2013, there is also a Power View add in for Excel that can be used to enhance the personal BI experience.
Target Audience: Supervisors/Managers
Power Pivot is a new tool Microsoft introduced in 2010 to handle very large datasets in memory. Power Pivot uses a compression technology called Vertipaq that compresses the data up to 90% of the original source size, making it extremely useful for loading larger workbooks and for combining multiple datasets together. Power Pivot workbooks cannot be refreshed through excel, only from within the Power Pivot window directly. This makes them personal in nature, as they can’t be refreshed once shared.
Target Audience: Analysts/Finance/Accounting
Team BI Solutions
Power Pivot for SharePoint
Once a Power Pivot workbook has been built and published from a personal perspective, it can be shared amongst the team. Rather than needing to email the workbook all around, uploading the document to SharePoint puts it in a controlled location where it can be shared amongst the team, and scheduled to be refreshed on a nightly basis through an automatic process. As this workbook grows in use, IT can monitor and then move to an Analysis Services cube if necessary.
Target Audience: Departments
Power View is a self-service reporting tool that can be used on top of a tabular database model and provide ad hoc reports for consumption. Power View reports can only be housed inside of a Power Pivot gallery within SharePoint.
Target Audience: Departments
Corporate BI Solutions
Tabular SSAS databases are extremely fast and can support many different data sources, are much faster than multidimensional, less time is needed to build them, and are more flexible in terms of database design. Additionally, in situations where a measure requires the use of snapshots (i.e. quantity totals by day), tabular is better because it can avoid snapshots by making up-to-date calculations at query time
Target Audience: Supervisors/managers (delivered via Excel or Power Pivot), analysts, finance, accounting
SSAS Multidimensional uses MDX and preaggregates the data across all available dimensions prior to browsing (at the time of processing). It also allows writeback, many to many relationships, data mining, rollups, translations, and custom drillthrough actions.
Target Audience: Supervisors/managers (delivered via Excel or Power Pivot), analysts, finance, accounting.
SSRS is a corporate BI reporting tool that’s great for use of list driven reporting that is needed and can just be delivered. Reporting is 2 dimensional and list-driven, with data presented in columns and rows. Great for workflow reporting (giving someone a specific list of people to call, things to do, etc.)
Target Audience: data feeds, frontline workers (list consumers)
PerformancePoint Services is a dashboard/performance management interface that can be utilized within SharePoint to provide the insights needed from a company perspective without going in to too much detail. It is great for performance management, and can create rich visual displays for consumption.
Target Audience: High-level managers, C-Level executives
Sources, Credits, and just some good reference points, because I believe in promoting other awesome authors.