Knight's Microsoft Business Intelligence 24-Hour Trainer E-Book

Brian Knight

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Veröffentlichungsjahr: 2011

Beschreibung

A book-and-video introduction to Microsoft's Business Intelligence tools

If you are just starting to get a handle on Microsoft Business Intelligence (BI) tools, this book and accompanying video provides you with the just the right amount of information to perform basic business analysis and reporting. You'll explore the components and related tools that comprise the Microsoft BI toolset as well as the new BI features of Office 2010. After a basic primer on BI and data modeling, the expert team of authors provides you with step-by-step lessons in the book and videos on the accompanying DVD on how to use SQL Server Integration Services, SQL Server Analysis Services, SQL Server Reporting Services, Excel BI (including PowerPivot), and SharePoint.

• Integrates instructional videos with each of the lessons found in the book to enhance your learning experience
• Explores the Microsoft Business Intelligence (BI) toolset as well as the new BI features of Office 2010
• Encourages you to practice what you've learned in "Try It Out" sections
• Contains video demonstrations that walk you through how to tackle each lesson featured in the book

With Knight's Microsoft Business Intelligence 24-Hour Trainer, veteran authors present you with an ideal introductory book-and-video package so that you can get started working with the BI toolset immediately!

Note: As part of the print version of this title, video lessons are included on DVD. For e-book versions, video lessons can be accessed at wrox.com using a link provided in the interior of the e-book.

Leseprobe

Table of Contents

Section I: Data Warehousing and Business Intelligence

Chapter 1: Why Business Intelligence?

How Intelligent Is Your Organization?

Try It

Chapter 2: Dimensional Modeling

Key Dimensional Modeling Elements

How Does Dimensional Modeling Work?

Try It

Chapter 3: Fact Table Modeling

Try It

Section II: SQL Server Integration Services

Chapter 4: Understanding SSIS

Business Intelligence Development Studio (BIDS)

Solution Explorer

SSIS Designer

Variables

SSIS Architecture

Try It

Chapter 5: Using the Control Flow

Control Flow Containers

Control Flow Tasks

Precedence Constraints

Connection Manager

Control Flow Designer

Try It

Chapter 6: Using the Data Flow

Data Flow Sources (Extracting Data)

Data Flow Transformations

Data Flow Paths

Data Flow Designer

Try It

Chapter 7: Solving Common SSIS Scenarios

Try It

Chapter 8: Loading Dimensions

Using the Slowly Changing Dimension Task

Try It

Chapter 9: Loading a Fact Table

Try It

Chapter 10: Deploying SSIS Packages

Deploying a Single Package Using SSMS

Deploying a Single Package Using BIDS

Creating a Deployment Utility Using BIDS

Try It

Part Section III: SQL Server Analysis Services

Chapter 11: Understanding SSAS

SSAS Architecture

Cubes

MDX

BIDS for SSAS

Try It

Chapter 12: Configuring a Data Source and Data Source View

Creating a Data Source

Creating a Data Source View

Try It

Chapter 13: Using the Cube Wizard

Try It

Chapter 14: Editing Your Dimension

Dimension Editor

Attribute Relationships

Key Columns

Dimension and Attribute Types

Try It

Chapter 15: Editing Your Cube

Cube Editor Tour

Browsing the Cube

Try It

Chapter 16: Adding New Dimensions and Measure Groups

Adding a Measure Group and Measures

Adding a Dimension

Try It

Chapter 17: Using MDX

Anatomy of Basic MDX

Navigation Functions

Try It

Chapter 18: Creating Calculations

Calculation Basics

Color-Coding Measures

Named Sets

More Advanced Calculations

Try It

Chapter 19: Data Mining

Introduction to Data Mining

Data-Mining Process

Creating a Mining Model

Exploring the Model

Evaluating the Model

Querying the Model

Try It

Chapter 20: Administering the SSAS Instance

Securing the Database

Partitioning the Data

Aggregations

Usage-Based Optimization

Processing the Cube

Deploying Change

Try It

Section IV: SQL Server Reporting Services

Chapter 21: Understanding SSRS

Building Your First Report

Try It

Chapter 22: Using Report Wizard

Try It

Chapter 23: Building a Matrix Report

Try It

Chapter 24: Parameterizing Your Reports

Creating Parameters

Default Parameter Values

Parameter Available Values

Multi-Value Parameters

Altering Properties with Parameters

Try It

Chapter 25: Building Reports on Your Cube

Try It

Chapter 26: Using Maps in Your Report

Try It

Chapter 27: Building a Dashboard

Tables and Filters

Drill-Downs

Try It

Chapter 28: Deploying and Administering SSRS

Stored Credentials

Subscriptions

Shared Schedules

Security

Datasets and Caching

Report Builder 3.0

Report Parts

Viewing Reports

Try It

Chapter 29: New Reporting Services Visualizations — Sparklines, Data Bars, and Indicators

Adding Sparklines

Using Data Bars

Configuring Indicators

Try It

Chapter 30: Using Report Builder

Opening Report Builder

Working with the Table, Matrix, or Chart Wizard

Report Parts

Shared Datasets

Try It

Section V: Containers

Chaper 31: Reporting against a Cube with Excel

Try It

Chapter 32: Loading Data into a PowerPivot Workbook

What Is PowerPivot?

Try It

Chapter 33: Creating a PowerPivot Report

Components of a PowerPivot Report

Building the Report

Adding Bells and Whistles

Try It

Chapter 34: Data Mining in Excel

Getting Ready for Excel Data Mining

Exploring the Data Mining Add-Ins

Analyzing Key Influencers in Excel

Try It

Section VI: SharePoint

Chapter 35: Understanding SharePoint for Business Intelligence

Try It

Chapter 36: Deploying and Using Reporting Services in SharePoint 2010

Try It

Chapter 37: Building PerformancePoint Dashboards in SharePoint 2010

Try It

Chapter 38: Deploying and Using Excel Services

Try It

Chapter 39: Deploying and Using PowerPivot in SharePoint

Try It

Chapter 40: Managing SharePoint Business Intelligence

Try It

Appendix: What’s on the DVD?

System Requirements

Using the DVD

What’s on the DVD

Troubleshooting

Customer Care

Introduction

End-User License Agreement

Section I: Data Warehousing and Business Intelligence

Lesson 1: Why Business Intelligence?Lesson 2: Dimensional ModelingLesson 3: Fact Table Modeling

Chapter 2

Dimensional Modeling

Dimensional modeling is the process you use to convert your existing OLTP data model to a model that is more business-centric and easier for Business Intelligence tools to work with. Tools like SSIS, Analysis Services, and the others you’ll learn about in this book are geared specifically toward variations of this type of model. In this lesson you will learn what makes a dimensional model different and then have the opportunity to convert a simple model yourself.

As seen in Figure 2-1, the OLTP model is highly normalized. This is to enhance the quick insertion and retrieval of data. The goal in designing a data warehouse or star schema is to denormalize the model in order to simplify it and to provide wider, more straightforward tables for joining and data-retrieval speed. This denormalization allows you to “model” the database in a business-focused way that users can understand, and dramatically increases performance of the types of analytical queries that we’re performing.

Why do you need to do this denormalization in order to report on your data, you may ask? The largest reason is that you need to consolidate some of the redundancy between tables. Consolidating redundancy will put the database into a star schema layout, which has a central fact table surrounded by a layer of dimension tables, as shown in Figure 2-2.

As you can see in Figure 2-2, we have abstracted out tables such as DimProduct, DimCustomer, DimPromotion, and DimDate and put the additive and aggregative data, like sales amounts, costs, and so on into a single fact table, FactInternetSales (more on fact tables in Lesson 3; for now focus on the dimensions). This abstraction allows you to implement a number of important elements that will provide great design patterns for dealing with the challenges discussed later in this chapter.

Figure 2-1

Moving from the OLTP model to a dimensional model is important for a number of reasons, not the least of which is performance, but within the dimensional model we can handle many situations with the data that are very difficult, if not impossible, to handle in a more typical OLTP third-normal-form model. Some of these situations are:

Slowly changing dimensions: How do you handle historical changes and reporting, for instance, if someone’s last name changes and you need to show the old last name on the historical reporting, and the current on the new reporting? How can you efficiently handle that situation in a highly normalized fashion? This would involve multiple tables and require some complicated updates. We will go over this later in the lesson.

Figure 2-2

Role-playing dimensions: How are you going to handle multiple calendars with multiple relationships to different tables if the calendars change? For instance, your fiscal calendar or accounting periods may change from year to year, but will need to maintain relationships to the previous methods for historical and trend analysis. More on this later. Integrating primary key structures from multiple systems: Some systems will have alphanumeric keys, and others will have integer or composite keys. Integrating different key types is a real challenge that often leads to hybrid, system-crossing keys kept in sync with some complicated ETL. Hybrid keys are not necessary in the dimensional model because you abstract all of that.

Key Dimensional Modeling Elements

The key elements that make up the dimensional model system are as follows:

The dimensions in the model provide a single complete and historically accurate source for the data. For instance, in the example we discuss in this lesson the customer dimension has a record of all the customers and their historically accurate information based on the dates for which the record was accurate.The solution you will see later in this lesson supports changes with the StartDate and EndDate columns in DimProduct implemented to track the effective dates of the rows in the table.You’ll notice a new key on the dimension table called ProductKey and a column called ProductAlternateKey. These are added to support the new key structure put in place. There will be more on how to do this shortly. This structure provides portability for the warehouse and the ability to integrate numerous systems despite their key differences.

How Does Dimensional Modeling Work?

Before you try some dimensional modeling for yourself, we want to show you an example. For our example, we use the AdventureWorks2008R2 sample databases from Microsoft available at www.codeplex.com. We create a simple star schema from the reseller sales information in the OLTP version of the database. The tables we use from OLTP will be as follows:

CustomerPersonAddress

The table we will create will be called DimCustomer.

First, take notice of the differences and key elements in Figures 2-3 and 2-4. Figure 2-3 shows the OLTP tables, and Figure 2-4 shows the new dimension table. We’ll walk you through the numbered items in Figure 2-4 to show you what key design elements we employed to make this a successful transition from normalized dimension data to a set of dimension tables.

1.New CustomerKey column to provide SurrogateKey. We are using a new column we created to provide the primary key for this new dimension table. Best practice is to add a suffix of “SK” to the name of this column, so it would read CustomerSK or CustomerKeySK.

2.We have modified the primary key column that is coming over from the source OLTP system to act as the alternate key. All this means is that if we need to bring in data from several systems whose primary keys have overlapped or are in different formats, we can do it with a combination of our alternate (or business) key and our surrogate key CustomerKey.

3.Much of the demographic data and store sales data was also tapped to get columns like DateFirstPurchase and CommuteDistance so you can find out more about your customers. Some of these columns could be calculated in the ETL portion of your processing by comparing information like a work and home address, for example.

Figure 2-3

Figure 2-4

Once the dimension tables are in place, you can easily see why this is a better model for working with large analytical queries and analysis. For instance, now if you refer to multiple customers in a single order, you need only one customer dimension with a fact table row that has multiple key relationships to the customer table. This is much better than having a bill-to customer table and a ship-to customer table to handle subsidiaries or other issues.

Multiple dates are also very common in most fact tables; for instance, an inventory fact table may have a product’s arrival date, ship date, expiration date, and return date. This requires multiple links to a product table for multiple columns; instead we can link directly to DimDate for these values with our numeric surrogate keys. Remember, these keys keep all the tables in the warehouse linked as your new key system.

You can see the StartDate and EndDate columns and how they control the historical loading. (The mechanics of historical loading are discussed in the SSIS lessons in Section II of this book.) These columns allow you to expire a row when a historical change is required. For instance, when a product line gets a new account manager, you would expire the current product line row and insert into the dimension table a new row with an EndDate of null that links to the new product manager. This way, all your historical reporting is accurate, and your current reporting is accurate as well. Otherwise, historical reporting could mistakenly tie sales to the wrong manager.

There are three main types of slowly changing dimensions:

Type I: Updates changing data only, no historical trackingType II: Tracks historical records, expires the row, and puts a new one into the tableType III: Same as Type II, but only tracks a certain number of revisions

It is common to have columns from each type in the same table; for instance, if you need to track history on last names for employees, but not on their addresses, you may have a Type II LastName column and a Type I Address column. This is perfectly acceptable and common.

This design has also been proven to improve performance significantly since the main goal of a data warehouse or BI system is to extract data as quickly as possible. The more denormalized type of this model lends itself to the quick retrieval of data from the tables to serve to populate a cube, run a report, or load data into Excel. You’ll do all of these things in later lessons!

Here are some general design tips for working with your dimension tables:

Try to avoid unnecessary normalizing. In a star schema, this practice is called snowflaking and while sometimes it is warranted, it usually isn’t, unless your dimension table is so large it needs to be physically separated on the storage array. CamelCasing, the capitalization of each word with no spaces or underscores, will help you down the road. You’re not working with Analysis Services yet, but you will be very soon, and CamelCasing will help streamline your work in SSAS. For now, you have to trust us.Begin your table name with Dim (Dim<Tablename>). This allows for better sorting of dimension and fact tables.Don’t over index. Until you know what queries you’ll be running against the tables, don’t assume you need lots of indexes on all your tables.

Try It

In this Try It you’re going to take what you’ve just read about and apply it to create your own product dimension table with columns from a typical source OLTP system.

Lesson Requirements

The columns you put in your table are up to you, but your dimension will need to track history. Also, the dimension table will be getting data from other sources, so it will need to be able to handle that. You will create your table in SQL Server Management Studio.

Hints

Make sure to remember to use the right types of key columns for your new dimension table.Remember the concepts this lesson discussed that are required for handling data coming from multiple systems.

Step-by-Step

1. The first thing you should do is identify some columns you might want in your table. Table 2-1 has a number of standard product dimension columns that you can pick from.

Table 2-1

2. Now, in order to make these into a proper dimension table, you need to review your requirements. Your first requirement was to make sure you can track history, so you need to make sure you have a StartDate and EndDate column so you can expire rows as they become updated.

3. Your next requirement was to make sure the dimension table could handle data from multiple systems either now or in the future, which means you need to apply the best practice you learned about surrogate keys. This will add a ProductKeySK and a ProductAlternateKey column to the table as well.

The finished product should look something like Figure 2-5.

Figure 2-5

This table will work with multiple systems with its surrogate key structure and will perform well if the rest of the warehouse follows similar best practices for design.

Congratulations, you have just designed your first dimension table. Don’t forget to remember these concepts and refer to them as they become relevant in the lessons in the rest of the book. Great job!

Please select Lesson 2 on the DVD with the print book, or watch online at www.wrox.com/go/vid24bi to view the video that accompanies this lesson.

Chapter 3

Fact Table Modeling

A fact table is modeled to be the center of the star schema in a data warehouse. It consists of two primary types of data: