第1页 / 共823页
第2页 / 共823页
第3页 / 共823页
第4页 / 共823页
第5页 / 共823页
第6页 / 共823页
第7页 / 共823页
第8页 / 共823页
Pro SQL Server 2012 BI Solutions(Apress,2012).pdf
Pro SQL Server 2012 BI Solutions
Contents at a Glance
Contents
Foreword
About the Authors
About the Technical Reviewers
Acknowledgments
Chapter 1: Business Intelligence Solutions
Who Should Read This Book?
What Is a Business Intelligence Solution?
Step 1: Interview and Identify Data
Step 2: Plan the BI Solution
Step 3: Create a Data Warehouse
Step 4: Create an ETL Process
Step 5: Create Cubes
Step 6: Create Reports
Step 7: Test and Tune the Solution
Step 8: Approve, Release and Prepare
Practice Exercises and More
Downloadable Content
Our Example Scenarios
Setup Instructions
Think Small, Win Big
Rapid Application Development for BI Solutions
Moving On
What’s Next?
Chapter 2: A Big-Picture Overview
The 10,000-Foot View
Interviewing and Isolating Data
Plan the Solution
Creating Planning Documents
Adding Documents to Visual Studio
Creating Visual Studio Solutions and Projects
Using Visual Studio
Creating a Blank Solution
Working with the Blank Solution
Creating the Data Warehouse
An Example Data Warehouse
Using SQL Code to Create a Data Warehouse
Create the Database
Create the Tables
Using the Identity Option
Adding Primary Key Constraints
Adding Foreign Key Constraints
Running SQL Code from Visual Studio
Create the ETL Process
ETL with an SSIS Project
Creating an SSIS Package
Outlining the Control Flow Tasks
SSIS Connections
Configuring a Flat File Connection
Configuring a SQL Server Connection
Configuring an Execute SQL Task
Configuring Data Flow Tasks
Configuring Additional Data Flows
Configuring a Data Source
Executing an SSIS Task
Completing the Package Execution
Creating a Cube
Making a Connection to the Data Warehouse
Creating a Data Source View
Creating Dimensions
Creating Cubes
Deploying and Processing
Creating Reports
Using the SSRS Wizard
Manually Creating SSRS Reports
Testing the Solution
Approve, Release, and Prepare
Moving On
What’s Next?
Chapter 3: Planning Solutions
Outline the Steps in the Process
Interviewing
Why Do We Need It?
What Are We Building?
Additional Considerations for Determining What You Will Build
Determining Your Ability to Complete the Solution
How Long Will It Take to Build?
How Will We Build It?
Who Will We Get to Build It?
When Will We Need It?
How Will We Finish It?
“Hey, Wait! I’m a Developer, Not a Manager”
Documenting the Requirements
Locating Data
Defining the Roles
Defining the Team
Determining the Schedule
The IT, Security and Licensing Requirements
Estimating the Cost
Documenting the Solution Plan
Implementation
Moving On
What’s Next?
Chapter 4: Designing a Data Warehouse
What Is a Data Warehouse?
What Is a Data Mart?
Competing Definitions
Starting with an OLTP Design
A Typical OLTP Database Design
Normalized Tables
Table Relationships
Many-to-Many Tables
One-to-Many Tables
Parent–Child One-to-Many Tables
A Typical Data Warehouse Database Design
Measures
Granularity
The Fact Table
Dimensions
Stars and Snow fl akes
Performance Considerations
Comparing Designs
Foreign Keys
Missing Features
Dimensional Patterns
Standard Dimensions
Fact or Degenerate Dimensions
Time Dimensions
Tracking Dates and Times
Using DateTime Keys
Having It All
Using Foreign Key Constraints
Role-Playing Dimensions
Parent–Child Dimensions
Junk Dimensions
Many-to-Many Dimensions
Fact vs. Bridge Tables
Changing the Connection
Direct Many-to-Many Relationships
Indirect Many-to-Many Relationships
The Takeaway
Conformed Dimensions
Adding Surrogate Keys
Slowly Changing Dimensions
Type I
Type II
Type III
Moving On
What’s Next?
Chapter 5: Creating a Data Warehouse
SQL Server Management Studio
Connecting to Servers
Server Aliases
Configuration Manager
Management Studio Windows
Object Explorer
The Query Window
Changing the Query Window Focus
Executing a Query
Creating Data Warehouse Database
Setting the Database Owner
Setting the Database Size
Setting the Recovery Model
Performing Database Backups
Shrinking Log Files
Keeping Data Warehouse Backups
Using the Filegroups Option
Creating Tables
Using the Table Designer
Generating SQL Scripts
Changing an Existing Table
Using the Diagramming Tool
Creating New Tables with the Diagram Tool
Creating Foreign Keys with the Diagramming Tool
Using the Query Window
Creating a Date Dimension Table
Getting Organized
Backing Up the Data Warehouse
Scripting the Database
Organizing Your Files with Visual Studio
Moving On
What’s Next?
Chapter 6: ETL Processing with SQL
Performing the ETL Programming
Deciding on Full or Incremental Loading
Isolating the Data to Be Extracted
Formatting Your Code
Identifying the Transformation Logic
Programming Your Transformation Logic
Reducing the Data
Using Column Aliases
Converting the Data Types
Looking Up Surrogate Key Values
Provide Conformity
Generate Date Data
Dealing with Nulls
Nulls in a Fact Table
Nulls in a Dimension Table
A Null Lookup Table
The SQL Query Designer
Updating Your BI Documentation
Building an ETL Script
Working in the Abstract
Views
Stored Procedures
User-De fined Functions
Moving On
What’s Next?
Chapter 7: Beginning the ETL Process with SSIS
Starting Your SSIS Project
Adding a Project to an Existing Solution
Renaming Your SSIS Package
The Anatomy of an SSIS Package
The Control Flow Tab
The Data Flow Tab
Data Sources
Data Transformations
Data Destinations
Using Sequence Containers
Using Precedence Constraint Arrows
SSIS Variables
Outlining Your ETL Process
Data Connections
The File Connection Manager
The OLE DB Connection Manager
The ADO.NET Connection Manager
Configuring a Connection
Execute SQL Tasks
Editing Your Execute SQL Task
Executing Your Execute SQL Tasks
The Progress/Execution Results Tabs
Resetting Your Destination Database
Moving On
What’s Next?
Chapter 8: Concluding the ETL Process with SSIS
Data Flows
Outlining a Data Flow Task
Con figuring the Data Source
The OLE DB Source Editor
The Connection Manager Page
SQL Command and SQL Command from a Variable
The Columns Manager Page
The Error Output Page
Data Flow Paths
Error Outputs Paths
Configuring the Data Destination
The Connection Manager Page
Mappings Page
Error Output Page
Executing the Entire Package
Moving On
What’s Next?
Chapter 9: Beginning the SSAS Project
SQL Server vs. Analysis Server Databases
OLAP Cubes vs. Reporting Tables
SQL Server vs. Analysis Server Applications
SSAS Projects
Data Sources
Define a Connection
Impersonation Information
Service Account Option
Current User Account Option
Inherit Option
Specific Windows Account Option
Data Source Views
The Data Source View Wizard
Select a Data Source
Name Matching
Select Tables and Views
Completing the Wizard
The Data Source View Designer
The Explore Data Option
Friendly Names
Named Queries
Named Calculation
Relationships
Dimensions
The Dimension Wizard
Select Creation Method
Specify Source Information
Select Related Tables
Select Dimension Attributes
Setting Attribute Types
Completing the New Dimension Wizard
Building the SSAS Project
Moving On
What’s Next?
Chapter 10: Configuring Dimensions with SSAS
The Dimension Designer
Dimension Structure Tab
The Attributes Pane
The Hierarchies Pane
Creating Hierarchies
Configuring Hierarchies
Attribute Relationships Tab
Configuring Relationships
Testing Your Progress
Translations Tab
Browser Tab
Build, Deploy, and Process
Building
Deploying
The Build and Debugging Pages
The Deployment Page
Deployment Target Folder
Deploying from Visual Studio
The Deployment Process Window
Processing
Browsing the Dimension
Moving On
What’s Next?
Chapter 11: Creating and Configuring SSAS Cubes
Creating Cubes
Processing the Cube
Configuring Cubes
The Browser Tab
Validate the Measures
Review the Dimensions
Validating the Results
The Cube Structure Tab
Common Measure Properties
Measure Group Properties
The Dimension Usage Tab
Configuring a Relationship
The Calculations Tab
Adding a Calculated Member
Configuring a Calculated Member
Calculated Members vs. Derived Members
Making a Test Copy of a Cube
KPIs
Moving On
What’s Next?
Chapter 12: Additional Cube and Dimension Configurations
Additional Cube Configurations
Actions
URL Actions
Drillthrough Actions
Report Actions
Partitions
Partition Sources
The Partition Wizard
Partition Storage Designs
ROLAP and HOLAP
Partition Storage Settings
Aggregations
The Aggregation Design Wizard
The Set Aggregation Options Dialog Window
Perspectives
Translations
Browser
Additional Dimension Configurations
Parent-Child Dimensions
Role-Playing Dimensions
Reference Dimensions
Managing Your Cubes and Dimensions
SQL Server Management Studio
Visual Studio (Live)
Moving On
What’s Next?
Chapter 13: Creating Reports with SQL Queries
Identifying the Data
Joining Table Data
Ordering Results
Formatting Results Using SQL Functions
Filtering Results
Adding Dynamic Filters with Parameters
Adding Aggregations
Using Subqueries
Creating KPI Queries
Adding Abstraction Layers
Using Views
Using Stored Procedures
Using Your Code in Reporting Applications
Moving On
What’s Next?
Chapter 14: Reporting with MDX Queries
Key Concepts and Terms
Programming with MDX
Comments
Basic and Raw Syntax
Running Your MDX Code
Optional Syntax
Default Members
Using Key vs. Name Identifiers
Using the Axis 0 and 1 Instead of Column and Row
Cells and Tuples
Calculated Members
Member Properties
Members and Levels
The NonEmpty Function
Member and Level Paths
Common Functions
PrevMember and NextMember Functions
The Children Function
The Parent Function
The CurrentMember Function
The Order Function
The CrossJoin Operator (*) and Function
Joining More than Two Dimensions
Where Clause
Using Your Code in Reporting Applications
Moving On
What Next?
Chapter 15: Reporting with Microsoft Excel
Microsoft’s BI Reporting
Excel Reports from the Data Warehouse
Creating a Connection
The Data Connection Wizard
Creating a Report
Configuring a Report
Changing Connection Properties
Reconfiguring a Connection
Using Stored Procedures
Working with Excel Reports from a Cube
Connecting to Your Cube
Testing Your Reports
Creating Charts
Adding a Header
Saving to PDF
Moving On
What’s Next?
Chapter 16: Creating Reports with SSRS
SSRS Architecture
Developer Tools
Report Builder
Visual Studio
The Administrative Services
SSRS Web Applications
SSRS Services
SSRS Databases
SSRS Configuration Manager
Creating SSRS Objects
Data Sources
Datasets
Reports
Configuring Report Data
Adding Report Items
Configuring Report Items
Previewing Reports
Deploying the Report
Managing the Report
Moving On
What’s Next?
Chapter 17: Configuring Reports with SSRS
Creating a Report Template
Adding a Header and Footer
Setting Report Properties
Page Size and Report Margins
Designing the Header
Rectangles
Textboxes
Images
Lines
Renaming Report Items
Using Expressions
Category and Item Panes
Expression Editing Pane
Placeholders
Variables
Completing the Header
Configuring the Footer
Saving the Report Template
Using Network Templates
Using Local Templates
Moving On
What’s Next
Chapter 18: Testing and Tuning BI Solutions
Testing the BI Solution
Validation
Objective Verification
Improvement Identification
Tuning the BI Solution
ETL Performance
ETL Hardware Options
ETL Software Options
Processing Performance
Filter Data in SSAS
SSAS Hardware Options
Reporting Performance
Rendering Options
Filtering Data in SSRS
Indexing Options
Archiving Stored Data
Caching Report Data
Common Design Strategies
Performance Measurements
Absolute Performance
Relative Performance Measurements
Measuring Performance with SQL Profiler
Creating a Profiler Trace
Running a Trace
An Example Scenario
Creating a Metadata Database
Moving On
Chapter 19: Approve, Release, and Prepare
The End of the Cycle
The Final Approval Process
The Sign-Off Document
Announcing the Release
The Press Release Title
The Press Release Body
The Press Release Boilerplate
Releasing the Solution
Collecting the Solution Artifacts
Deploying the Files
Manual and Automated Deployment
Deploying the Data Warehouse with SQL Code
Deploying the SSIS ETL Process
Combining the SQL Server and SSIS Deployment Code
Deploying the SSAS Database
Deploying the SSRS Reports
Using SSIS Packages Instead of Batch Files
Release Documentation
SDKs
Developer Specifications
User Documentation
Style Guides
User Manuals
The Anatomy of a User Manual
Subject Headings
Step-by-Step Instructions
Figures
Figure Captions
User Manual Testing
Help Files
PDF Files
HTML Files
User Training
Say Thank You
Moving On
What’s Next?
Index
www.it-ebooks.info
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
Contents at a Glance
About the Authors ......................................................................................................xxiii
About the Technical Reviewers .................................................................................. xxv
Acknowledgments .................................................................................................... xxvii
■
Chapter 1: Business Intelligence Solutions
.................................................................1
■
Chapter 2: A Big-Picture Overview
............................................................................13
■
Chapter 3: Planning Solutions
...................................................................................85
■
Chapter 4: Designing a Data Warehouse
.................................................................131
■
Chapter 5: Creating a Data Warehouse
....................................................................161
■
Chapter 6: ETL Processing with SQL
........................................................................213
■
Chapter 7: Beginning the ETL Process with SSIS
....................................................253
■
Chapter 8: Concluding the ETL Process with SSIS
...................................................301
■
Chapter 9: Beginning the SSAS Project
...................................................................335
■
Chapter 10: Configuring Dimensions with SSAS
.....................................................389
■
Chapter 11: Creating and Configuring SSAS Cubes
.................................................435
■
Chapter 12: Additional Cube and Dimension Configurations
...................................483
■
Chapter 13: Creating Reports with SQL Queries
......................................................529
■
Chapter 14: Reporting with MDX Queries
................................................................565
■
Chapter 15: Reporting with Microsoft Excel
............................................................615
■
Chapter 16: Creating Reports with SSRS
.................................................................653
■
Chapter 17: Configuring Reports with SSRS
............................................................693
www.it-ebooks.info
v
■ Contents at a GlanCe
■
Chapter 18: Testing and Tuning BI Solutions
...........................................................731
■
Chapter 19: Approve, Release, and Prepare
............................................................767
Index...........................................................................................................................797
www.it-ebooks.info
Chapter 1
Business Intelligence Solutions
Business intelligence (BI) solutions are all the buzz as of late, and BI developers are highly sought after.
Considering the amount of data that needs to be tracked to run a business successfully, it is no wonder. When an
employee has been with a company for 20 years, how will management be notified? Perhaps staffing is suffering
because of vacation trends or sales need to be tracked after targeted advertising. Maybe product preordering for a
sales event needs to be estimated, or who sold what and when needs to be documented for an upcoming contest.
There is no end to how much data needs to be managed, and countless hours, money, and resources
are wasted in attempts to research the information, often with minimal results, multiple errors, and missed
opportunities in decision making. And when more than one employee needs access to the same information, the
errors are often multiplied.
With a well-designed BI solution, important data can be called up instantly in a user-friendly manner.
Calculations are made with a click of a button, and reports are easily generated. No longer will that 20-year
employee be unrecognized for such a long duration of loyalty and service. Staffing can be more properly
managed, advertising can be better targeted to the proper demographic, and so on.
This book shows how to build a successful BI solution step-by-step. We cover the entire process from initial
preparations and planning to complex layers of designing and configuring your project, and from creating reports
to drafting user instructions, and releasing your project. This book is simple in its approach. If you are new to
BI solutions, you will find the instructions thorough and easy to follow with clear images to demonstrate the
process. Yet, it is fast-paced and rich enough in information for even the most advanced database professional to
learn from.
Who Should Read This Book?
This book is for each professional who works with the many aspects of BI solutions. These include database
administrators, project managers, testers, support techs, report developers, and many others.
This book is not a sales pitch for the latest features of SQL server. Nor is it focused on technologies designed
only for very large companies. Instead, this book is about how small, medium, and large companies, as well as
departments within those companies, can take advantage of Microsoft SQL Server’s effective and inexpensive BI
software. This book defines the glue that is used to bind all four of Microsoft’s BI servers (MSSS, SSIS, SSAS, and
SSRS) together into a BI solution.
After reading this book and working through the recommended exercises, you will have the tools to build
your own BI solutions, as well as interact with other BI team members with a greater understanding of their roles
within the BI solution process.
www.it-ebooks.info
1
CHAPTER 1 ■ BusinEss inTElligEnCE soluTions
What Is a Business Intelligence Solution?
A BI solution is a collection of objects that allows data to be turned into useful information. These objects must be
designed, created, tested, and ultimately approved to create a working BI solution.
When creating a BI solution, it is important first to understand what that solution consists of, how each
component is combined to create the whole, and finally, how to recognize when you have achieved your goal.
Knowing where to begin is vital to the success of your project. In Figure 1-1 we have outlined eight steps to
use as a guideline. We progress through each of these steps and explain them in detail throughout this book. We
also develop working BI solutions in the exercises within each chapter to gain the skills necessary to complete
increasingly complex solutions in your future. Chapter 2 provides an overview of the entire process.
Figure 1-1. The BI solution life cycle
We chose to represent the tasks in Figure 1-1 as a circle, because the nature of a BI solution is one of continual
change. As time goes by, a company’s requirements change, the data that is available changes, and the technology
to bring these two aspects together changes. Because of this, the process of creating a BI solution can often begin
with the continuation of a prior solution, with each successive iteration refining and extending the current solution.
Perhaps the first step is to define the questions that your BI solution will answer. An example might be, how
are our products selling? Another question might be, how often do people use our website?
One common misconception about BI solutions is that they are useful only to large corporations. This is
simply not true. Clients as seemingly dissimilar as a dentist and a horse breeder will find they need to keep
detailed records of important information, from patient visits to horse lineage. This information is used to
determine their future plans or review past activities. Every business, group, and individual who needs to keep
track of data will have questions they would like to have answered that a BI solution can provide. Formulating
these questions and determining what to do with them lead us to the first step in developing a BI solution.
Step 1: Interview and Identify Data
The process of designing your solution begins with interviewing your client to determine what type of
information is needed. Chapter 3 discusses the types of questions to ask and what the interview process entails.
2
www.it-ebooks.info
The answers to these questions allow you to better locate the data necessary for your solution. Data can be found
in many forms, and you may use one or more types to fill your requirements.
Some common data sources include the following:
CHAPTER 1 ■ BusinEss inTElligEnCE soluTions
•
•
•
•
•
•
Spreadsheets
Existing databases
Simple text files
Log files
XML text files
Paper documents
Once the data is located, the next step is to decide how much of it is relevant to your needs. You also need to
decide whether your data’s current location is sufficient for your BI solution’s needs or whether you must copy
some or all of the data to a more appropriate location. This leads us to step 2.
Step 2: Plan the BI Solution
Few developers relish creating extensive documentation before building a project. And yet, just as it is necessary
for blueprints to be drawn up and approved before a home is built, projects must be planned and documented
before creating a working BI solution.
In Chapter 4 we discuss creating a description of what your solution will accomplish, documenting the
source and the destination objects, and beginning the formal documentation. A solution’s formal document can
be laid out with common tools such as Microsoft Excel or even Microsoft Word. These Excel or Word documents
can then be taken back to the client for approval. Once approved, these documents will become an outline that
can be worked with much like a blueprint. You then create Visual Studio projects that become the building blocks
of your BI solution from these blueprints.
Step 3: Create a Data Warehouse
Your BI solution data will typically end up stored in a data warehouse database. Microsoft’s SQL Server 2012
makes this very easy and cost efficient. Microsoft’s SQL Server takes time and effort to master, yet the vast
majority of tasks required to build your solution are performed using tools that are as simple to use as Microsoft’s
user-friendly Access database application.
In Chapters 4 and 5, we show how to design and implement a data warehouse database yourself, regardless
of your level of experience with Microsoft’s SQL Server. Various design options are demonstrated in these
chapters, such as star versus snowflake dimensions and how to create fact and dimension tables. Once complete,
you will understand the design differences between online transaction processing (OLTP) and data warehouse
tables similar to those shown in Figure 1-2.
www.it-ebooks.info
3
CHAPTER 1 ■ BusinEss inTElligEnCE soluTions
Figure 1-2. OLTP and data warehouse databases
Step 4: Create an ETL Process
Getting data from the original source to your data warehouse entails extracting the data from its original location,
transforming the data to be consistent with your new data warehouse design, and loading the data into the new
data warehouse location. This ETL process is discussed in great detail in Chapters 6, 7, and 8.
Although this process can be one of the most in-depth and complicated tasks in developing your BI solution,
Microsoft SQL Server 2012 provides invaluable tools to help you accomplish it, saving time and simplifying the
process for you. Using a combination of SQL programming and SQL Server’s Integration Server (SSIS), you will
create an ETL process much like the one shown in Figure 1-3.
4
www.it-ebooks.info
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
