Pro SQL Server 2012 BI Solutions (Expert's Voice in SQL Server)

V413HAV Pro SQL Server 2012 BI Solutions 803 Contents at a Glance 3 Contents 806 Foreword 820 About the Authors 821 About the Technical Reviewers 822 Acknowledgments 823 Chapter 1: Business Intelligence Solutions 5 Who Should Read This Book? 5 What Is a Business Intelligence Solution? 6 Step 1: Interview and Identify Data 6 Step 2: Plan the BI Solution 7 Step 3: Create a Data Warehouse 7 Step 4: Create an ETL Process 8 Step 5: Create Cubes 9 Step 6: Create Reports 10 Step 7: Test and Tune the Solution 12 Step 8: Approve, Release and Prepare 12 Practice Exercises and More 12 Downloadable Content 12 Our Example Scenarios 13 Setup Instructions 13 Think Small, Win Big 14 Rapid Application Development for BI Solutions 15 Moving On 15 What’s Next? 16 Chapter 2: A Big-Picture Overview 17 The 10,000-Foot View 17 Interviewing and Isolating Data 18 Plan the Solution 19 Creating Planning Documents 20 Adding Documents to Visual Studio 21 Creating Visual Studio Solutions and Projects 21 Using Visual Studio 22 Creating a Blank Solution 23 Working with the Blank Solution 24 Creating the Data Warehouse 31 An Example Data Warehouse 31 Using SQL Code to Create a Data Warehouse 32 Create the Database 32 Create the Tables 33 Using the Identity Option 33 Adding Primary Key Constraints 34 Adding Foreign Key Constraints 34 Running SQL Code from Visual Studio 35 Create the ETL Process 43 ETL with an SSIS Project 43 Creating an SSIS Package 45 Outlining the Control Flow Tasks 47 SSIS Connections 48 Configuring a Flat File Connection 48 Configuring a SQL Server Connection 49 Configuring an Execute SQL Task 50 Configuring Data Flow Tasks 51 Configuring Additional Data Flows 52 Configuring a Data Source 53 Executing an SSIS Task 54 Completing the Package Execution 55 Creating a Cube 61 Making a Connection to the Data Warehouse 63 Creating a Data Source View 65 Creating Dimensions 68 Creating Cubes 69 Deploying and Processing 70 Creating Reports 78 Using the SSRS Wizard 79 Manually Creating SSRS Reports 85 Testing the Solution 88 Approve, Release, and Prepare 88 Moving On 88 What’s Next? 88 Chapter 3: Planning Solutions 89 Outline the Steps in the Process 90 Interviewing 91 Why Do We Need It? 92 What Are We Building? 93 Additional Considerations for Determining What You Will Build 93 Determining Your Ability to Complete the Solution 94 How Long Will It Take to Build? 95 How Will We Build It? 96 Who Will We Get to Build It? 96 When Will We Need It? 98 How Will We Finish It? 98 “Hey, Wait! I’m a Developer, Not a Manager” 99 Documenting the Requirements 101 Locating Data 105 Defining the Roles 122 Defining the Team 122 Determining the Schedule 123 The IT, Security and Licensing Requirements 125 Estimating the Cost 126 Documenting the Solution Plan 126 Implementation 127 Moving On 133 What’s Next? 133 Chapter 4: Designing a Data Warehouse 134 What Is a Data Warehouse? 134 What Is a Data Mart? 134 Competing Definitions 135 Starting with an OLTP Design 136 A Typical OLTP Database Design 137 Normalized Tables 137 Table Relationships 138 Many-to-Many Tables 138 One-to-Many Tables 138 Parent–Child One-to-Many Tables 138 A Typical Data Warehouse Database Design 139 Measures 139 Granularity 140 The Fact Table 141 Dimensions 141 Stars and Snow fl akes 142 Performance Considerations 143 Comparing Designs 144 Foreign Keys 146 Missing Features 146 Dimensional Patterns 147 Standard Dimensions 147 Fact or Degenerate Dimensions 147 Time Dimensions 148 Tracking Dates and Times 149 Using DateTime Keys 149 Having It All 149 Using Foreign Key Constraints 150 Role-Playing Dimensions 151 Parent–Child Dimensions 152 Junk Dimensions 153 Many-to-Many Dimensions 154 Fact vs. Bridge Tables 154 Changing the Connection 155 Direct Many-to-Many Relationships 155 Indirect Many-to-Many Relationships 156 The Takeaway 158 Conformed Dimensions 158 Adding Surrogate Keys 158 Slowly Changing Dimensions 159 Type I 159 Type II 159 Type III 160 Moving On 162 What’s Next? 162 Chapter 5: Creating a Data Warehouse 163 SQL Server Management Studio 163 Connecting to Servers 164 Server Aliases 165 Configuration Manager 167 Management Studio Windows 170 Object Explorer 170 The Query Window 171 Changing the Query Window Focus 172 Executing a Query 172 Creating Data Warehouse Database 173 Setting the Database Owner 174 Setting the Database Size 175 Setting the Recovery Model 177 Performing Database Backups 178 Shrinking Log Files 178 Keeping Data Warehouse Backups 179 Using the Filegroups Option 180 Creating Tables 183 Using the Table Designer 183 Generating SQL Scripts 186 Changing an Existing Table 187 Using the Diagramming Tool 188 Creating New Tables with the Diagram Tool 189 Creating Foreign Keys with the Diagramming Tool 192 Using the Query Window 192 Creating a Date Dimension Table 196 Getting Organized 199 Backing Up the Data Warehouse 199 Scripting the Database 199 Organizing Your Files with Visual Studio 203 Moving On 212 What’s Next? 213 Chapter 6: ETL Processing with SQL 214 Performing the ETL Programming 214 Deciding on Full or Incremental Loading 215 Isolating the Data to Be Extracted 219 Formatting Your Code 220 Identifying the Transformation Logic 220 Programming Your Transformation Logic 221 Reducing the Data 221 Using Column Aliases 222 Converting the Data Types 223 Looking Up Surrogate Key Values 224 Provide Conformity 225 Generate Date Data 227 Dealing with Nulls 228 Nulls in a Fact Table 229 Nulls in a Dimension Table 230 A Null Lookup Table 231 The SQL Query Designer 234 Updating Your BI Documentation 240 Building an ETL Script 241 Working in the Abstract 246 Views 247 Stored Procedures 248 User-De fined Functions 250 Moving On 252 What’s Next? 252 Chapter 7: Beginning the ETL Process with SSIS 253 Starting Your SSIS Project 254 Adding a Project to an Existing Solution 255 Renaming Your SSIS Package 256 The Anatomy of an SSIS Package 259 The Control Flow Tab 260 The Data Flow Tab 261 Data Sources 263 Data Transformations 263 Data Destinations 264 Using Sequence Containers 265 Using Precedence Constraint Arrows 267 SSIS Variables 270 Outlining Your ETL Process 273 Data Connections 278 The File Connection Manager 279 The OLE DB Connection Manager 280 The ADO.NET Connection Manager 280 Configuring a Connection 281 Execute SQL Tasks 284 Editing Your Execute SQL Task 284 Executing Your Execute SQL Tasks 286 The Progress/Execution Results Tabs 290 Resetting Your Destination Database 292 Moving On 300 What’s Next? 300 Chapter 8: Concluding the ETL Process with SSIS 301 Data Flows 301 Outlining a Data Flow Task 303 Con figuring the Data Source 303 The OLE DB Source Editor 304 The Connection Manager Page 304 SQL Command and SQL Command from a Variable 307 The Columns Manager Page 308 The Error Output Page 309 Data Flow Paths 309 Error Outputs Paths 310 Configuring the Data Destination 312 The Connection Manager Page 313 Mappings Page 315 Error Output Page 316 Executing the Entire Package 330 Moving On 333 What’s Next? 334 Chapter 9: Beginning the SSAS Project 335 SQL Server vs. Analysis Server Databases 335 OLAP Cubes vs. Reporting Tables 338 SQL Server vs. Analysis Server Applications 339 SSAS Projects 341 Data Sources 344 Define a Connection 344 Impersonation Information 345 Service Account Option 346 Current User Account Option 347 Inherit Option 348 Specific Windows Account Option 348 Data Source Views 351 The Data Source View Wizard 352 Select a Data Source 352 Name Matching 353 Select Tables and Views 354 Completing the Wizard 355 The Data Source View Designer 357 The Explore Data Option 357 Friendly Names 359 Named Queries 360 Named Calculation 361 Relationships 363 Dimensions 366 The Dimension Wizard 366 Select Creation Method 367 Specify Source Information 369 Select Related Tables 371 Select Dimension Attributes 372 Setting Attribute Types 374 Completing the New Dimension Wizard 376 Building the SSAS Project 378 Moving On 388 What’s Next? 388 Chapter 10: Configuring Dimensions with SSAS 389 The Dimension Designer 389 Dimension Structure Tab 390 The Attributes Pane 390 The Hierarchies Pane 392 Creating Hierarchies 392 Configuring Hierarchies 396 Attribute Relationships Tab 408 Configuring Relationships 409 Testing Your Progress 411 Translations Tab 418 Browser Tab 419 Build, Deploy, and Process 419 Building 419 Deploying 421 The Build and Debugging Pages 422 The Deployment Page 422 Deployment Target Folder 424 Deploying from Visual Studio 425 The Deployment Process Window 426 Processing 428 Browsing the Dimension 431 Moving On 434 What’s Next? 434 Chapter 11: Creating and Configuring SSAS Cubes 435 Creating Cubes 435 Processing the Cube 441 Configuring Cubes 443 The Browser Tab 443 Validate the Measures 446 Review the Dimensions 447 Validating the Results 448 The Cube Structure Tab 449 Common Measure Properties 449 Measure Group Properties 452 The Dimension Usage Tab 453 Configuring a Relationship 454 The Calculations Tab 460 Adding a Calculated Member 461 Configuring a Calculated Member 462 Calculated Members vs. Derived Members 464 Making a Test Copy of a Cube 467 KPIs 476 Moving On 481 What’s Next? 481 Chapter 12: Additional Cube and Dimension Configurations 482 Additional Cube Configurations 482 Actions 482 URL Actions 483 Drillthrough Actions 485 Report Actions 487 Partitions 489 Partition Sources 490 The Partition Wizard 492 Partition Storage Designs 496 ROLAP and HOLAP 497 Partition Storage Settings 498 Aggregations 500 The Aggregation Design Wizard 500 The Set Aggregation Options Dialog Window 503 Perspectives 507 Translations 507 Browser 508 Additional Dimension Configurations 508 Parent-Child Dimensions 509 Role-Playing Dimensions 512 Reference Dimensions 514 Managing Your Cubes and Dimensions 519 SQL Server Management Studio 519 Visual Studio (Live) 521 Moving On 526 What’s Next? 527 Chapter 13: Creating Reports with SQL Queries 528 Identifying the Data 529 Joining Table Data 530 Ordering Results 532 Formatting Results Using SQL Functions 536 Filtering Results 537 Adding Dynamic Filters with Parameters 543 Adding Aggregations 545 Using Subqueries 548 Creating KPI Queries 549 Adding Abstraction Layers 551 Using Views 551 Using Stored Procedures 554 Using Your Code in Reporting Applications 560 Moving On 562 What’s Next? 563 Chapter 14: Reporting with MDX Queries 564 Key Concepts and Terms 564 Programming with MDX 567 Comments 567 Basic and Raw Syntax 568 Running Your MDX Code 568 Optional Syntax 570 Default Members 572 Using Key vs. Name Identifiers 574 Using the Axis 0 and 1 Instead of Column and Row 577 Cells and Tuples 578 Calculated Members 583 Member Properties 586 Members and Levels 588 The NonEmpty Function 589 Member and Level Paths 592 Common Functions 595 PrevMember and NextMember Functions 595 The Children Function 597 The Parent Function 597 The CurrentMember Function 598 The Order Function 600 The CrossJoin Operator (*) and Function 603 Joining More than Two Dimensions 604 Where Clause 605 Using Your Code in Reporting Applications 608 Moving On 612 What Next? 613 Chapter 15: Reporting with Microsoft Excel 614 Microsoft’s BI Reporting 614 Excel Reports from the Data Warehouse 618 Creating a Connection 618 The Data Connection Wizard 620 Creating a Report 623 Configuring a Report 624 Changing Connection Properties 627 Reconfiguring a Connection 628 Using Stored Procedures 631 Working with Excel Reports from a Cube 632 Connecting to Your Cube 632 Testing Your Reports 636 Creating Charts 638 Adding a Header 642 Saving to PDF 643 Moving On 650 What’s Next? 651 Chapter 16: Creating Reports with SSRS 652 SSRS Architecture 652 Developer Tools 653 Report Builder 654 Visual Studio 654 The Administrative Services 655 SSRS Web Applications 656 SSRS Services 658 SSRS Databases 659 SSRS Configuration Manager 660 Creating SSRS Objects 667 Data Sources 668 Datasets 670 Reports 673 Configuring Report Data 675 Adding Report Items 677 Configuring Report Items 677 Previewing Reports 678 Deploying the Report 683 Managing the Report 687 Moving On 689 What’s Next? 690 Chapter 17: Configuring Reports with SSRS 691 Creating a Report Template 691 Adding a Header and Footer 693 Setting Report Properties 695 Page Size and Report Margins 695 Designing the Header 696 Rectangles 697 Textboxes 698 Images 699 Lines 701 Renaming Report Items 705 Using Expressions 706 Category and Item Panes 708 Expression Editing Pane 709 Placeholders 710 Variables 712 Completing the Header 714 Configuring the Footer 715 Saving the Report Template 719 Using Network Templates 719 Using Local Templates 720 Moving On 726 What’s Next 727 Chapter 18: Testing and Tuning BI Solutions 728 Testing the BI Solution 728 Validation 729 Objective Verification 731 Improvement Identification 731 Tuning the BI Solution 732 ETL Performance 732 ETL Hardware Options 733 ETL Software Options 736 Processing Performance 737 Filter Data in SSAS 737 SSAS Hardware Options 738 Reporting Performance 738 Rendering Options 739 Filtering Data in SSRS 739 Indexing Options 739 Archiving Stored Data 740 Caching Report Data 740 Common Design Strategies 740 Performance Measurements 741 Absolute Performance 741 Relative Performance Measurements 745 Measuring Performance with SQL Profiler 745 Creating a Profiler Trace 745 Running a Trace 747 An Example Scenario 749 Creating a Metadata Database 752 Moving On 762 Chapter 19: Approve, Release, and Prepare 764 The End of the Cycle 764 The Final Approval Process 765 The Sign-Off Document 765 Announcing the Release 766 The Press Release Title 766 The Press Release Body 766 The Press Release Boilerplate 767 Releasing the Solution 767 Collecting the Solution Artifacts 768 Deploying the Files 769 Manual and Automated Deployment 769 Deploying the Data Warehouse with SQL Code 769 Deploying the SSIS ETL Process 770 Combining the SQL Server and SSIS Deployment Code 771 Deploying the SSAS Database 771 Deploying the SSRS Reports 773 Using SSIS Packages Instead of Batch Files 774 Release Documentation 775 SDKs 775 Developer Specifications 779 User Documentation 780 Style Guides 781 User Manuals 781 The Anatomy of a User Manual 782 Subject Headings 784 Step-by-Step Instructions 784 Figures 785 Figure Captions 786 User Manual Testing 786 Help Files 789 PDF Files 789 HTML Files 790 User Training 790 Say Thank You 791 Moving On 791 What’s Next? 791 Index 793 www.it-ebooks.info IT eBooks V413HAV Pro SQL Server 2012 BI Solutions 803 Contents at a Glance 3 Contents 806 Foreword 820 About the Authors 821 About the Technical Reviewers 822 Acknowledgments 823 Chapter 1: Business Intelligence Solutions 5 Who Should Read This Book? 5 What Is a Business Intelligence Solution? 6 Step 1: Interview and Identify Data 6 Step 2: Plan the BI Solution 7 Step 3: Create a Data Warehouse 7 Step 4: Create an ETL Process 8 Step 5: Create Cubes 9 Step 6: Create Reports 10 Step 7: Test and Tune the Solution 12 Step 8: Approve, Release and Prepare 12 Practice Exercises and More 12 Downloadable Content 12 Our Example Scenarios 13 Setup Instructions 13 Think Small, Win Big 14 Rapid Application Development for BI Solutions 15 Moving On 15 What’s Next? 16 Chapter 2: A Big-Picture Overview 17 The 10,000-Foot View 17 Interviewing and Isolating Data 18 Plan the Solution 19 Creating Planning Documents 20 Adding Documents to Visual Studio 21 Creating Visual Studio Solutions and Projects 21 Using Visual Studio 22 Creating a Blank Solution 23 Working with the Blank Solution 24 Creating the Data Warehouse 31 An Example Data Warehouse 31 Using SQL Code to Create a Data Warehouse 32 Create the Database 32 Create the Tables 33 Using the Identity Option 33 Adding Primary Key Constraints 34 Adding Foreign Key Constraints 34 Running SQL Code from Visual Studio 35 Create the ETL Process 43 ETL with an SSIS Project 43 Creating an SSIS Package 45 Outlining the Control Flow Tasks 47 SSIS Connections 48 Configuring a Flat File Connection 48 Configuring a SQL Server Connection 49 Configuring an Execute SQL Task 50 Configuring Data Flow Tasks 51 Configuring Additional Data Flows 52 Configuring a Data Source 53 Executing an SSIS Task 54 Completing the Package Execution 55 Creating a Cube 61 Making a Connection to the Data Warehouse 63 Creating a Data Source View 65 Creating Dimensions 68 Creating Cubes 69 Deploying and Processing 70 Creating Reports 78 Using the SSRS Wizard 79 Manually Creating SSRS Reports 85 Testing the Solution 88 Approve, Release, and Prepare 88 Moving On 88 What’s Next? 88 Chapter 3: Planning Solutions 89 Outline the Steps in the Process 90 Interviewing 91 Why Do We Need It? 92 What Are We Building? 93 Additional Considerations for Determining What You Will Build 93 Determining Your Ability to Complete the Solution 94 How Long Will It Take to Build? 95 How Will We Build It? 96 Who Will We Get to Build It? 96 When Will We Need It? 98 How Will We Finish It? 98 “Hey, Wait! I’m a Developer, Not a Manager” 99 Documenting the Requirements 101 Locating Data 105 Defining the Roles 122 Defining the Team 122 Determining the Schedule 123 The IT, Security and Licensing Requirements 125 Estimating the Cost 126 Documenting the Solution Plan 126 Implementation 127 Moving On 133 What’s Next? 133 Chapter 4: Designing a Data Warehouse 134 What Is a Data Warehouse? 134 What Is a Data Mart? 134 Competing Definitions 135 Starting with an OLTP Design 136 A Typical OLTP Database Design 137 Normalized Tables 137 Table Relationships 138 Many-to-Many Tables 138 One-to-Many Tables 138 Parent–Child One-to-Many Tables 138 A Typical Data Warehouse Database Design 139 Measures 139 Granularity 140 The Fact Table 141 Dimensions 141 Stars and Snow fl akes 142 Performance Considerations 143 Comparing Designs 144 Foreign Keys 146 Missing Features 146 Dimensional Patterns 147 Standard Dimensions 147 Fact or Degenerate Dimensions 147 Time Dimensions 148 Tracking Dates and Times 149 Using DateTime Keys 149 Having It All 149 Using Foreign Key Constraints 150 Role-Playing Dimensions 151 Parent–Child Dimensions 152 Junk Dimensions 153 Many-to-Many Dimensions 154 Fact vs. Bridge Tables 154 Changing the Connection 155 Direct Many-to-Many Relationships 155 Indirect Many-to-Many Relationships 156 The Takeaway 158 Conformed Dimensions 158 Adding Surrogate Keys 158 Slowly Changing Dimensions 159 Type I 159 Type II 159 Type III 160 Moving On 162 What’s Next? 162 Chapter 5: Creating a Data Warehouse 163 SQL Server Management Studio 163 Connecting to Servers 164 Server Aliases 165 Configuration Manager 167 Management Studio Windows 170 Object Explorer 170 The Query Window 171 Changing the Query Window Focus 172 Executing a Query 172 Creating Data Warehouse Database 173 Setting the Database Owner 174 Setting the Database Size 175 Setting the Recovery Model 177 Performing Database Backups 178 Shrinking Log Files 178 Keeping Data Warehouse Backups 179 Using the Filegroups Option 180 Creating Tables 183 Using the Table Designer 183 Generating SQL Scripts 186 Changing an Existing Table 187 Using the Diagramming Tool 188 Creating New Tables with the Diagram Tool 189 Creating Foreign Keys with the Diagramming Tool 192 Using the Query Window 192 Creating a Date Dimension Table 196 Getting Organized 199 Backing Up the Data Warehouse 199 Scripting the Database 199 Organizing Your Files with Visual Studio 203 Moving On 212 What’s Next? 213 Chapter 6: ETL Processing with SQL 214 Performing the ETL Programming 214 Deciding on Full or Incremental Loading 215 Isolating the Data to Be Extracted 219 Formatting Your Code 220 Identifying the Transformation Logic 220 Programming Your Transformation Logic 221 Reducing the Data 221 Using Column Aliases 222 Converting the Data Types 223 Looking Up Surrogate Key Values 224 Provide Conformity 225 Generate Date Data 227 Dealing with Nulls 228 Nulls in a Fact Table 229 Nulls in a Dimension Table 230 A Null Lookup Table 231 The SQL Query Designer 234 Updating Your BI Documentation 240 Building an ETL Script 241 Working in the Abstract 246 Views 247 Stored Procedures 248 User-De fined Functions 250 Moving On 252 What’s Next? 252 Chapter 7: Beginning the ETL Process with SSIS 253 Starting Your SSIS Project 254 Adding a Project to an Existing Solution 255 Renaming Your SSIS Package 256 The Anatomy of an SSIS Package 259 The Control Flow Tab 260 The Data Flow Tab 261 Data Sources 263 Data Transformations 263 Data Destinations 264 Using Sequence Containers 265 Using Precedence Constraint Arrows 267 SSIS Variables 270 Outlining Your ETL Process 273 Data Connections 278 The File Connection Manager 279 The OLE DB Connection Manager 280 The ADO.NET Connection Manager 280 Configuring a Connection 281 Execute SQL Tasks 284 Editing Your Execute SQL Task 284 Executing Your Execute SQL Tasks 286 The Progress/Execution Results Tabs 290 Resetting Your Destination Database 292 Moving On 300 What’s Next? 300 Chapter 8: Concluding the ETL Process with SSIS 301 Data Flows 301 Outlining a Data Flow Task 303 Con figuring the Data Source 303 The OLE DB Source Editor 304 The Connection Manager Page 304 SQL Command and SQL Command from a Variable 307 The Columns Manager Page 308 The Error Output Page 309 Data Flow Paths 309 Error Outputs Paths 310 Configuring the Data Destination 312 The Connection Manager Page 313 Mappings Page 315 Error Output Page 316 Executing the Entire Package 330 Moving On 333 What’s Next? 334 Chapter 9: Beginning the SSAS Project 335 SQL Server vs. Analysis Server Databases 335 OLAP Cubes vs. Reporting Tables 338 SQL Server vs. Analysis Server Applications 339 SSAS Projects 341 Data Sources 344 Define a Connection 344 Impersonation Information 345 Service Account Option 346 Current User Account Option 347 Inherit Option 348 Specific Windows Account Option 348 Data Source Views 351 The Data Source View Wizard 352 Select a Data Source 352 Name Matching 353 Select Tables and Views 354 Completing the Wizard 355 The Data Source View Designer 357 The Explore Data Option 357 Friendly Names 359 Named Queries 360 Named Calculation 361 Relationships 363 Dimensions 366 The Dimension Wizard 366 Select Creation Method 367 Specify Source Information 369 Select Related Tables 371 Select Dimension Attributes 372 Setting Attribute Types 374 Completing the New Dimension Wizard 376 Building the SSAS Project 378 Moving On 388 What’s Next? 388 Chapter 10: Configuring Dimensions with SSAS 389 The Dimension Designer 389 Dimension Structure Tab 390 The Attributes Pane 390 The Hierarchies Pane 392 Creating Hierarchies 392 Configuring Hierarchies 396 Attribute Relationships Tab 408 Configuring Relationships 409 Testing Your Progress 411 Translations Tab 418 Browser Tab 419 Build, Deploy, and Process 419 Building 419 Deploying 421 The Build and Debugging Pages 422 The Deployment Page 422 Deployment Target Folder 424 Deploying from Visual Studio 425 The Deployment Process Window 426 Processing 428 Browsing the Dimension 431 Moving On 434 What’s Next? 434 Chapter 11: Creating and Configuring SSAS Cubes 435 Creating Cubes 435 Processing the Cube 441 Configuring Cubes 443 The Browser Tab 443 Validate the Measures 446 Review the Dimensions 447 Validating the Results 448 The Cube Structure Tab 449 Common Measure Properties 449 Measure Group Properties 452 The Dimension Usage Tab 453 Configuring a Relationship 454 The Calculations Tab 460 Adding a Calculated Member 461 Configuring a Calculated Member 462 Calculated Members vs. Derived Members 464 Making a Test Copy of a Cube 467 KPIs 476 Moving On 481 What’s Next? 481 Chapter 12: Additional Cube and Dimension Configurations 482 Additional Cube Configurations 482 Actions 482 URL Actions 483 Drillthrough Actions 485 Report Actions 487 Partitions 489 Partition Sources 490 The Partition Wizard 492 Partition Storage Designs 496 ROLAP and HOLAP 497 Partition Storage Settings 498 Aggregations 500 The Aggregation Design Wizard 500 The Set Aggregation Options Dialog Window 503 Perspectives 507 Translations 507 Browser 508 Additional Dimension Configurations 508 Parent-Child Dimensions 509 Role-Playing Dimensions 512 Reference Dimensions 514 Managing Your Cubes and Dimensions 519 SQL Server Management Studio 519 Visual Studio (Live) 521 Moving On 526 What’s Next? 527 Chapter 13: Creating Reports with SQL Queries 528 Identifying the Data 529 Joining Table Data 530 Ordering Results 532 Formatting Results Using SQL Functions 536 Filtering Results 537 Adding Dynamic Filters with Parameters 543 Adding Aggregations 545 Using Subqueries 548 Creating KPI Queries 549 Adding Abstraction Layers 551 Using Views 551 Using Stored Procedures 554 Using Your Code in Reporting Applications 560 Moving On 562 What’s Next? 563 Chapter 14: Reporting with MDX Queries 564 Key Concepts and Terms 564 Programming with MDX 567 Comments 567 Basic and Raw Syntax 568 Running Your MDX Code 568 Optional Syntax 570 Default Members 572 Using Key vs. Name Identifiers 574 Using the Axis 0 and 1 Instead of Column and Row 577 Cells and Tuples 578 Calculated Members 583 Member Properties 586 Members and Levels 588 The NonEmpty Function 589 Member and Level Paths 592 Common Functions 595 PrevMember and NextMember Functions 595 The Children Function 597 The Parent Function 597 The CurrentMember Function 598 The Order Function 600 The CrossJoin Operator (*) and Function 603 Joining More than Two Dimensions 604 Where Clause 605 Using Your Code in Reporting Applications 608 Moving On 612 What Next? 613 Chapter 15: Reporting with Microsoft Excel 614 Microsoft’s BI Reporting 614 Excel Reports from the Data Warehouse 618 Creating a Connection 618 The Data Connection Wizard 620 Creating a Report 623 Configuring a Report 624 Changing Connection Properties 627 Reconfiguring a Connection 628 Using Stored Procedures 631 Working with Excel Reports from a Cube 632 Connecting to Your Cube 632 Testing Your Reports 636 Creating Charts 638 Adding a Header 642 Saving to PDF 643 Moving On 650 What’s Next? 651 Chapter 16: Creating Reports with SSRS 652 SSRS Architecture 652 Developer Tools 653 Report Builder 654 Visual Studio 654 The Administrative Services 655 SSRS Web Applications 656 SSRS Services 658 SSRS Databases 659 SSRS Configuration Manager 660 Creating SSRS Objects 667 Data Sources 668 Datasets 670 Reports 673 Configuring Report Data 675 Adding Report Items 677 Configuring Report Items 677 Previewing Reports 678 Deploying the Report 683 Managing the Report 687 Moving On 689 What’s Next? 690 Chapter 17: Configuring Reports with SSRS 691 Creating a Report Template 691 Adding a Header and Footer 693 Setting Report Properties 695 Page Size and Report Margins 695 Designing the Header 696 Rectangles 697 Textboxes 698 Images 699 Lines 701 Renaming Report Items 705 Using Expressions 706 Category and Item Panes 708 Expression Editing Pane 709 Placeholders 710 Variables 712 Completing the Header 714 Configuring the Footer 715 Saving the Report Template 719 Using Network Templates 719 Using Local Templates 720 Moving On 726 What’s Next 727 Chapter 18: Testing and Tuning BI Solutions 728 Testing the BI Solution 728 Validation 729 Objective Verification 731 Improvement Identification 731 Tuning the BI Solution 732 ETL Performance 732 ETL Hardware Options 733 ETL Software Options 736 Processing Performance 737 Filter Data in SSAS 737 SSAS Hardware Options 738 Reporting Performance 738 Rendering Options 739 Filtering Data in SSRS 739 Indexing Options 739 Archiving Stored Data 740 Caching Report Data 740 Common Design Strategies 740 Performance Measurements 741 Absolute Performance 741 Relative Performance Measurements 745 Measuring Performance with SQL Profiler 745 Creating a Profiler Trace 745 Running a Trace 747 An Example Scenario 749 Creating a Metadata Database 752 Moving On 762 Chapter 19: Approve, Release, and Prepare 764 The End of the Cycle 764 The Final Approval Process 765 The Sign-Off Document 765 Announcing the Release 766 The Press Release Title 766 The Press Release Body 766 The Press Release Boilerplate 767 Releasing the Solution 767 Collecting the Solution Artifacts 768 Deploying the Files 769 Manual and Automated Deployment 769 Deploying the Data Warehouse with SQL Code 769 Deploying the SSIS ETL Process 770 Combining the SQL Server and SSIS Deployment Code 771 Deploying the SSAS Database 771 Deploying the SSRS Reports 773 Using SSIS Packages Instead of Batch Files 774 Release Documentation 775 SDKs 775 Developer Specifications 779 User Documentation 780 Style Guides 781 User Manuals 781 The Anatomy of a User Manual 782 Subject Headings 784 Step-by-Step Instructions 784 Figures 785 Figure Captions 786 User Manual Testing 786 Help Files 789 PDF Files 789 HTML Files 790 User Training 790 Say Thank You 791 Moving On 791 What’s Next? 791 Index 793 www.it-ebooks.info IT eBooks Business intelligence projects do not need to cost multi-millions of dollars or take months or even years to complete! Using rapid application development (RAD) techniques along with Microsoft SQL Server 2012, this book guides database administrators, SQL programmers, and report specialists in creating practical, cost-effective business intelligence solutions for their companies and departments. Pro SQL Server 2012 BI Solutions provides practical examples of cost-effective business intelligence projects. Readers will be guided through several complete projects that build a foundation for real-world solutions. Even with limited experience using Microsoft's SQL Server, Integration Server, Analysis Server, and Reporting Server, you can leverage your existing knowledge of SQL programming and database design to provide users with the business intelligence reports they need. Provides recipes for multiple business intelligence scenarios Progresses from simple to advanced projects using several examples Shows Microsoft SQL Server technology used to complete real-world business intelligence projects