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 (TSQL) code and a diagramming feature that allows for creating and managing tables and relationships. Figure 1.12 provides a view of a typical star-schema data warehouse from the SSMS perspective.
Figure 1.12 SQL Server Management Studio
Using Dashboard Designer
The starting point for authoring Performance Point dashboards, discussed earlier in the section “Working with Performance Point,” is Dashboard Designer. Dashboard Designer is a click-once application available in SharePoint that is installed on each individual machine that will author Performance Point content. It is not available by default. Once installed, you can launch Dashboard Designer to build and deploy dashboards to a SharePoint site, which is shown in Figure 1.13.
Figure 1.13 Dashboard Designer displaying scorecard
Using Dashboard Designer, you can develop each individual item that appears on the dashboard and then create a dashboard that displays the finished product. Once complete, you can then deploy the dashboard to view it.
Using Report Builder
Similar to Dashboard Designer, Report Builder is also a client tool that you must install on each individual developer's machine. You use Report Builder to develop SQL Server Reporting Services reports. When you compare it to designing reports using SSDT, the features and functionality are almost exactly the same. The difference is mainly in regard to team development and source control. A report designed in SSDT is included within a solution and a project. When designing reports with Report Builder, you can develop only a single report at a time. The concept of team collaboration is not introduced. Report Builder is a light-weight tool for people that need to quickly create and/or modify reports as needed. The application itself is installed on demand, similar to PerformancePoint.
Figure 1.14 displays a sample report being developed with Report Builder.
Figure 1.14 Sample Report Builder report
Summary
This chapter discussed the different Microsoft business intelligence tools that you can use to author, deploy, and maintain a business intelligence solution. In addition, overviews were provided for each tool, and, in some cases, comparisons among the different features and capabilities were provided.
The next chapter provides an overview and some discussions around designing an effective and efficient business intelligence architecture.
Chapter 2
Designing an Effective Business Intelligence Architecture
Whereas the tools that you use to develop the business intelligence solution are one of the top priorities in the project, designing an effective and performant solution may be almost as important, if not more. Once the solution is developed and placed in the hands of the end users, accessibility, availability, and responsiveness now become the top priorities of the solution and of those who manage and maintain it. If either fails, then the repercussions could be detrimental or catastrophic to the entire project. As a result, prior to development and deployment, you should carefully consider identifying who the audience is and what the goals of the project are.
This chapter helps you define your audience and goals, explains the reasoning behind data sources, and gives you the information you need to determine if you need a data warehouse. You also find discussions on data governance, analytical models, and delivery solutions. Happy planning!
Identifying the Audience and Goal of the Business Intelligence Solution
Careful consideration must be taken when identifying the audience and goal of the business intelligence solution. These are key factors in most development projects, not just business intelligence projects. Although the audience often dictates the goals, it is important to realize that in comparison they are both equally important. More specifically you consider:
● What are the data requirements?
● Is there a need for a data warehouse or a semantic model?
● What are the hardware needs?
Without adequate knowledge, these questions could result in the development of a solution that does not meet the needs or goals of an organization. Even worse, they may create a solution that meets the requirements, but cannot physically support an organization because of poorly sized hardware.
Who's the Audience?
Identifying the audience should be the starting point, because if you do not have an intended group of end-users, what is the purpose of the project in the first place? Most successful projects succeed because they have some type of buy-in or sponsorship from a larger group that is not part of the development team. For example, a common business intelligence project involves determining past, current, and future sales for a given company. The request for this may come from the CEO, Finance or Marketing department, or from a branch office. Either way, now that the project is aligned with a specific group or groups, obtaining resources to produce the solution becomes much easier, which is an advantage that any development project can benefit from. These resources, include, but are not limited to software, hardware, and people.
The newfound partnership comes with a list of items that may or may not positively assist the project. One item is goals, discussed in the next section. Another, and probably
