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 SharePoint, you can develop custom pages that consume other technologies. Figure 1.7 integrates four different technologies into a single view. The resulting web page centralizes four distinct views of data spanning features that are made available by different software vendors. In addition to customized pages, business intelligence developers can leverage Performance Point, a dashboarding feature of SharePoint. The details of Performance Point are discussed in the next section.
While SharePoint does offer this extended capability of integrating disparate technologies, the Microsoft business intelligence stack does provide a sufficient number of tools and features for deploying a holistic business intelligence solution. Therefore, if an organization is evaluating vendor solutions, leveraging SharePoint typically addresses all the business intelligence needs for a given project.
Working with Performance Point
The previous section focused on SharePoint as a whole. However, when SharePoint is deployed, you have an option to configure Performance Point services. Using Performance Point, developers can create dashboards that aggregate data from a collection of sources such as Analysis Services, Excel Workbooks, and SharePoint lists. Similar to Power View, Performance Point gives your users a very interactive interface for analyzing data. Where Performance Point really shines over Power View is that it automatically exposes the metadata from the underlying data model as part of the end-user experience. In other words, once a dashboard deploys, end users can simply right-click a given visualization and change the look by drilling down to a different level of the data. Figure 1.8 shows a sample Performance Point dashboard.
Figure 1.8 Performance Point dashboard
This particular dashboard is a high-level view of medical discharges for a given year and service area. By right-clicking a bar in the bar graph (shown in Figure 1.9) or changing a filter on the dashboard, end users can dynamically analyze the data based on the underlying data model.
Figure 1.9 Performance Point dashboard with drill-down menu displayed
Therefore, instead of IT developing several reports of varying levels of the same data, you can create a single dashboard that provides end users with different views from one entry point.
Using Excel for Business Intelligence
When most people think of Excel, they think of spreadsheets and pivot tables. However, the latest release of Excel has definitely matured into a full-grown business intelligence authoring tool. Excel 2013 now comes with two new plug-ins in the product (Power Pivot and Power View) and has two additional plug-ins available for download (Power Query and Power Map), thus transforming Excel into a full-fledged business intelligence solution. By leveraging all four plug-ins, users of Excel can discover, model, and visualize data from a single tool. By including Excel as part or all of the business intelligence solution, you gain the primary advantage of providing a familiar tool to the entire population of end users. This results in a lower likelihood of resistance to adoption. The following sections provide a brief overview of each add-in.
What Is Power Query?
Microsoft Power Query is a self-service data discovery and data access tool. It enables end users to easily combine, transform, and share data. When Power Query is installed, not only can end users access structured data from within Excel, but they can also perform public searches. This search is similar to a Bing search; however; instead of returning a list of web page results, Power Query returns a list of datasets that match the entered query.
What Is Power Pivot?
Once all the data has been identified, end users can use Power Pivot to build in-memory data models – meaning they can perform data analysis directly inside Excel. Power Pivot has the ability to consume and process large amounts of data, beyond the normal Excel limits, while including those features of Excel familiar to most Excel users. In addition, Power Pivot introduces a new expression language, Data Analytic Expression (DAX), which provides new data analytic capabilities.
What Is Power View?
Now that you've transformed and modeled all the data, the next step is visualization. To accomplish this task, two new add-ins are now available: Power View and Power Map. Power View, previously available only via SharePoint, has been added to Excel. The Power View experience is very similar in Excel when you compare it to the discussion in the section “Working with SQL Server Reporting Services.” The primary difference is that authoring is done directly inside of Excel rather than SharePoint. Although this may not seem like a huge difference, if you are an Excel user, it may determine whether you'll use Power View or not. Besides that, there are a few slight variations, but nothing too significant.
Power Map
Power Map is a 3-D visualization add-in for Excel. This add-in consumes geographical and temporal data and maps it on a 3-D representation of the earth, shown in Figure 1.10.
Figure 1.10 Sample Power View map with field list
This means that end users can interact with and derive new insights from their data. The end-user result can be a video that tells a complete story of the data in different scenes that represent various views.
Which Development Tools Do You Need?
So far the discussion has focused on those Microsoft tools that host data and provide end-user consumption. The focus now shifts to the tools you actually need to develop the solution. Primarily four tools are used in the development process:
● SQL Server Data Tools (SSDT)
● SQL Server Management Studio (SSMS)
● Performance Point Dashboard Designer
● Report Builder
