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
What tools end users utilize is determined by what they're developing and who does the development. In some cases, all tools are used, and in others cases, only an abbreviated set. Some instances may require the use of tools beyond the Microsoft stack. However, for the sake of brevity and because this book is focused on the Microsoft business intelligence, we'll discuss only tools specific to Microsoft.
Using SQL Server Data Tools
SQL Server Data Tools (SSDT) is the most comprehensive set of tools in the list. SSDT offers a full-range experience from which developers can address almost every facet of a business intelligence solution from within a single environment. Figure 1.11 displays a list of the templates available to developers from within SSDT.
Figure 1.11 SQL Server Data Tools template list
In this tool, you can develop SQL Server Integration Services, SQL Server Reporting Services, and semantic model solutions individually or as a team. You can also develop database solutions using SSDT, which is a perfect environment for developing the data warehouse schema because SSDT provides capabilities such as refactoring code and schema compare. In addition, by leveraging SSDT as the development tool, you can version-control the entire solution using Microsoft Team Foundation Server or other third-party version-control tools like Subversion.
Using SQL Management Studio
SQL Server Management Studio (SSMS) is often considered a Database Administrators (DBA) – centric tool. However, you can also use it to develop a data warehouse schema. You could argue that it does not provide a complete development environment because it lacks certain features like version control and refactoring. Although this is true, it does provide an interface for testing and debugging Transact-SQL