Figure 1.3 Business intelligence solution that includes SSAS semantic model
Although it is possible to report directly against a data warehouse using Excel and Performance Point (discussed later in the chapter), SSAS provides a more innate design experience with these tools. In addition, using SSAS provides end users with a larger surface of self-service capabilities that are unavailable when only a data warehouse is available. Therefore, they are excluded from Figure 1.2, but included in Figure 1.3.
For example, if you are the CEO of a company, you may require access to every aspect of data in the model. However, if you are a regional or departmental manager, you may only require access to data that is pertinent to your region or department. SSAS includes built-in capabilities that let you control access to data at the row level. In many cases, this is one of the most important and often overlooked requirements of a business intelligence solution. During most projects, you don't realize this until very late in the development process. However, when using SSAS, the implementation process is neither very difficult nor disruptive.
Working with SQL Server Reporting Services
Up to this point, all the data discussions have involved movement, transformation, and management of data. This section shifts to more data visualization and interactivity. Once the processes to implement the data warehouse and/or the semantic model are in place, your next decision is how the end users will access the data. When leveraging the Microsoft business intelligence stack, organizations have several reporting options. From an operational perspective, probably the most utilized is SQL Server Reporting Services (SSRS).
SSRS operates in two modes, which have a few slight differences, but are mostly similar in regard to features and tasks:
● Native mode: Access and management of reports are available via a web-based platform, also known as Report Manager.
● SharePoint Integrated mode: This is a site collection within SharePoint that has the same purpose as the Report Manager.
SSRS also provides two very different types of reporting experiences. Deciding which to use often poses the biggest challenge for most projects:
● The first, Operational Reports, are typically used when delivering highly-formatted, table-based and pixel perfect reports. They are designed to answer a specific question and are usually static in nature. In this case, you would use SSRS.
● The second type is of a more ad-hoc nature. End users typically access the underlying source directly, which would be a semantic model in this case, and build reports as needed. The reports are more visual containing charts, maps, gauges and scorecards. For these types of reports end-users would leverage Power View. Each one is discussed in the following sections.
In addition to developing these types of reports, SSRS provides additional capabilities that makes it a complete solution. Features include:
● Report export
● Subscription report delivery
● Data alerts (SharePoint Integrated mode only)
● Data caching
● Report printing
● Report snapshots
● Shared datasets
● Report parts
● Geospatial mapping
While this is not an exhaustive list of all the features, it should provide an overview of what is possible when developing and managing reports using SSRS.
Understanding Operational Reports
Operational reports, available since the inception of SSRS, can help you develop, deploy, and manage standard operational reports. What are operational reports? These are typically row- and column-based reports containing data that answers or meets a specific need. For example, the report shown in Figure 1.4 shows a sample Operating Summary developed using SSRS.
Figure 1.4 Operating Statement Summary using SSRS
This particular report was designed for a specific audience to solve a specific problem, which in this case was a need to dynamically view operating summaries by month for those individuals in the Accounting department.
Using SSRS, developers can also build very visual reports that resemble high-level dashboards often used by executives; Figure 1.5 illustrates this.
Figure 1.5 High-level dashboard created using SSRS
SSRS includes a complete toolbox of items that allow report developers to build complete reporting solutions including high-level dashboards that provide end users with drill-through ability to more detailed data.
Understanding Ad Hoc Reporting
Suppose end users want some control over the look and feel of reports. More specifically, what if they want ad-hoc access to data, which allows them to create and deploy reports as needed, instead of relying on a group of report developers creating canned reports. The latest release of SSRS integrated into SharePoint, discussed later in the book, exposes a new feature named Power View.
Power View is an ad-hoc, interactive, and presentation-ready self-service reporting tool designed specifically for end users. Instead of waiting on reports from the report development team, end users can quickly access data that is stored in either type of semantic mode and build highly visual and interactive reports. Figure 1.6 displays a sample of a Power View report.
Figure 1.6 Sample Power View report
With Power View, end users become the report authors. A view of the data is made available in a field list, which provides easy access to each data element exposed via a single semantic model.
Working with SharePoint
Traditionally, when Microsoft business intelligence is included in a business intelligence project, most people automatically think of SharePoint. That is because SharePoint acts as a central portal or repository that you can easily access using any modern web browser. Instead of using multiple disparate technologies for the business intelligence solution, SharePoint can render both Microsoft and non-Microsoft reporting visualizations.
It is a common practice that individuals, teams, departments, or organizations will evaluate and select a reporting tool based on a variety of factors. These tools then tend to proliferate themselves within the department and become part of daily operations. Once the discussion of a business intelligence solution begins, it is often difficult to persuade the groups away from their established tool of choice. However, using SharePoint means the IT group responsible for the business intelligence project can couple existing report artifacts with new technologies to produce a fully functional and comprehensive solution via a single interface. Figure 1.7 provides a pseudo view of what a SharePoint-developed solution may look like.
Figure 1.7 Pseudo SharePoint page displaying multiple technologies
In