Data cubes are more sophisticated OLAP structures that will solve the above concern. Despite the name, cubes are not limited to a cube structure. The name is inherited just because it has more dimensions. Don't visualize cubes as only 3-dimensional or symmetric; cubes are used for their multidimensional value.

A simple cube can have only three dimensions, such as those shown in Figure 1-5 where X is Products, Y is Region, and Z is Time.

Figure 1-5. A simple 3-dimensional cube

With a cube like the one in Figure 1-5, you can find out product sales in a given time frame. This cube uses Product Sales as facts with the Time dimension.

Facts (also called measures) and dimensions are integral parts of cubes. Data in cubes is accumulated as facts and aggregated against a dimension. A data cube is multi-dimensional and thus can deliver information based on any fact against any dimension in its hierarchy.

Dimensions can be described by their hierarchies, which are essentially parent-child relationships. If dimensions are key factors of cubes, hierarchies are key factors of dimensions. In the hierarchy of the Time dimension, for example, you might find Yearly, Half-yearly, Quarterly, Monthly, Weekly, and Daily levels. These become the facts or members of the dimension.

In a similar vein, geography might be described like this:

Figure 1-6 shows a multi-dimensional cube.

Figure 1-6. An OLAP multi-dimensional cube showing sales.

Now imagine cubes with multiple facts and dimensions, each dimension having its own hierarchies across each cube, and all these cubes connected together. The information residing inside this consolidated cube can deliver very useful, accurate, and aggregated information. You can drill down into the data of this cube to the lowest levels.

However, earlier we said that OLAP databases are denormalized. Well then, what happens to the tables? Are they not connected at all and just work independently?

Clearly, you must have the details of how your original tables are connected. If you want to convert your normalized OLTP tables into denormalized OLAP tables, you need to understand your existing tables and their normalized form in order to design the new mapping for these tables against the OLAP database tables you're planning to create.

In order to plan for migrating OLTP to OLAP, you need to understand a more about OLAP internals. OLAP structures its tables in its own style, yielding tables that are much cleaner and simpler. However, it's actually the data that makes the tables clean and simple. To enable this simplicity, the tables are formed into a structure (or pattern) that can be depicted visually as a star. Let's take a look at how this so-called star schema is formed and at the integral parts that make up the OLAP star schema.

OLAP data tables are arranged to form a star. Star schemas have two core concepts: facts and dimensions. Facts are values or calculations based on the data. They may be just numeric values. Here are some examples of facts:

Dimensions are the axis points, or ways to view facts. For instance, using the multidimensional cube in Figure 1-6 (and assuming it relates to Wal-Mart), we can ask

Values around the cube that belong to a dimension are known as members. In Figure 1-6, examples of members are Eastern (under the Region dimension), Prod 2 (under Products) and Yearly (under Time). You might want to aggregate various facts against various dimensions. Generating and obtaining a star schema is very simple using SQL Server Management Studio (SSMS). You can create a new database diagram by adding more tables from the AdventureWorks database. SSMS will link related tables and form a star schema as shown in Figure 1-7.

In Figure 1-7, the block in center is the fact table and those surrounding the center block are dimensions. This layout—a fact table in the center surrounded by the dimensions—is what makes a star schema.

OLAP data is in the form of aggregations. We want to get from OLAP information such as:

and so on.

Figure 1-7. A star schema

So far so good! Although the OLAP system is designed with these schemas and structures, it's still a relational database. It still has all the tables and relations as an OLTP database, which means that querying from these OLAP tables might still have performance issues and thus creates a bit of concern in aggregation.

It is the structure of the cubes that solves those performance issues; cubes are very efficient and fast in providing information. The next question then is how to build these cubes and populate them with data. Needless to say, data is an essential part of your business and, as we've noted, typically exists in an OLTP database. What we need to do is retrieve this information from the OLTP database, clean it up, and transfer the data (either in its entirety or only what's required) to the OLAP cubes. Such a process is known as Extract, Transform, and Load (ETL).

You will learn about some of the ETL tools later in this chapter. We'll start by taking a look at data transfer from an OLTP database to an OLAP database using ETL, at a very high level. But we can't just jump right in and convert these systems. Be warned, ETL requires some preparations, so we'd better discuss those now.

The ETL process pulls data from various data sources that can be in a form as simple as a flat text file or as complex as a SQL Server or Oracle database. Moreover, the data may come from different sources of unknown formats, as when an organization has merged with another. Or it could be an even worse scenario, where not only the data schemas are different but the data sources are completely be different as well. There might be different databases such as SQL, Oracle, or DB2, or, for that matter, even flat files and xml files. And these data sources may very well be real-time OLTP databases that can't be directly accessed to retrieve information. Furthermore, the data likely needs to be loaded on a periodic basis as updates happen in real time—probably every second. Now imagine that this involves terabytes of data. How much time would it take to copy the data from one system and load into another? As you can tell, this is likely to be a very difficult situation.

All of these common issues essentially demand an area where you can happily carry out all your operations—a staging or data preparation platform. How would you take advantage of a staging environment? Here are the tasks you'd perform:

Let's begin with a simple flow diagram, shown in Figure 1-8, which shows everything put together very simply. Think about the picture in terms of rows and columns, as we have three rows—for system, language used, and purpose, and 2 columns—one for OLTP and the other for OLAP.

On OLTP databases you use the T-SQL language to perform the transactions, while for OLAP databases you use MDX queries instead to parse the OLAP data structures (which, in this case, are cubes). And, finally, you use OLAP/MDX for BI analysis purposes.

Figure 1-8. Converting from OLTP to OLAP

What is ETL doing in Figure 1-8? As we noted, ETL is the process used to migrate an OLTP database to an OLAP database. Once the OLAP database is populated with the OLTP data, we use MDX queries and run them against the OLAP cubes to get what need (the analysis).

Now that you understand the transformation, let's take a look at MDX scripting and see how you can use it to achieve your goals.

MDX stands for Multidimensional Expressions. It is an open standard used to query information from cubes. Here's a simple MDX query (running on the AdventureWorks database):

select [Measures].[Internet Total Product Cost] ON COLUMNS,
[Customer].[Country] ON ROWS
FROM [AdventureWorks]
WHERE [Sales Territory].[North America]

MDX can be a very simple select statement as shown above, which consists of the select query and choosing columns and rows, much like a traditional SQL select statement. In a nutshell, it's like this:

Select x, y, z from cube where dimension equals a.

Sound familiar?

Let's look at the MDX statement more closely. The query is retrieving information from the measure "Internet Total Product Cost" against the dimension "Customer Country" from the Cube "AdventureWorks." Furthermore, the where clause is on the "Sales Territory" Dimension, as we are intrested in finding the Sales in North America.

MDX queries are not so different from SQL queries except that MDX used to query an analysis cube. It has all the rich features, similar syntax, functions, support for calculations, and more. The difference is that SQL retrieves information from tables that basically results in a two-dimensional view. In contrast, MDX can query from a cube and deliver mutidimensional views.

Go back and take a look at Figure 1-6, which shows sales (fact) against three dimensions: Product, Region, and Time. This means you can find the sales for a given product in a given region at a given time. This is simple. Now suppose you have regions splitting the US into North, East, West and South, and the time frame further classified as Yearly, Quarterly, Monthly, and Weekly. All of these elements serve as filters, allowing you to retrieve the finest aggregated information about the product. Thus a cube can range from a simple 3-dimensional one to a complex hierarchy where each dimension can have its own members or attributes or children. You need a very clear understanding of these fundamentals in order to write efficient MDX queries.

In a multidimensional cube, you can either call the entire cube a cell or count each cube as one cell. A cell is built with dimensions and members.

Using our example cube, if you need to retrieve the sales value for a product, you'd do it as

(Region.East, Time.[Quarter 4], Product.Prod1)

Notice that squarebrackets—[ ]—are used when there's a space in the dimension/member.

Looks easy, yes? But what if you need just a part of the cube value and not the whole thing? Let's say you need just prod1 sales in the Eastern region. Well that's definitely a valid constraint. To address this, you use tuples in a cube.

Tuples and Sets

A tuple is an address within the cube. You can define a tuple based on what you need. It can have one or more dimensions and one measure as a logical group. For instance, if we use the same example, data related to the Eastern Region during the fourth Quarter can be called one tuple. So

(Region.East, Time.[Quarter 4], Product.Prod1)

is a good example of a tuple. You can design as many as tuples you need within the limits of dimensions.

A set is a group of zero or more tuples. Remember that you can't use the terms tuples and sets interchangeably. Suppose you want two different areas in a cube or two tuples with different measures and dimensions. That's where you use a set (see Figure 1-9).

For example, if (Region.East, Time.[Quarter 4], Product.Prod1) is one of your tuples, and (Region.East, Time.[Quarter 1], Product.Prod2) is the second, then the set that comprises these two tuples looks like this:

{(Region.East, Time.[Quarter 4], Product.Prod1), (Region.East, Time.[Quarter 1],
Product.Prod2) }

Figure 1-9. OLAP cube showing tuples and a set

Note

When you create MDX queries, it's always good to include comments that provide sufficient information and make logical sense. You can write single-line comments by using either "//" or "—" or multiline comments using"/*...*/".

For more about advanced MDX queries, built-in functions and their references, please consult the book Smart Business Intelligence Solutions with Microsoft SQL Server 2008, available for purchase at microsoft.com/learning/en/us/book.aspx?ID=12663&locale=en-us.

Table 1-2 gives an overview of the database models and their entities, query languages, and the tools used to retrieve information from them.

Before proceeding, let's take a look at some more BI concepts.

As discussed earlier, data needs to be extracted from the OLTP databases, cleaned, and then loaded into OLAP in order to be used for business intelligence. You can use the SQL Server Integration Services (SSIS) tool to accomplish this. This section will run through various steps detailing how to use Integration Services and how it can be used as an ETL tool.

SSIS is very powerful. You can use it to extract data from any source that includes a database, a flat file, or an xml file, and you can load that data into any other destination. In general, you have a source and a destination, and they can be completely different systems. A classic example of where to use SSIS is when companies merge and they have to move their databases from one system to another, which includes the complexity of having mismatches in the columns, etc. The beauty of SSIS is that it doesn't necessarily use a SQL Server database.

The important elements of SSIS packages are control flows, data flows, connection managers, and event handlers. Let's look at some of the features of SSIS in detail, and we'll demonstrate how simple it is to import information from a source and export it to a destination.

Since we will be working with the AdventureWorks database here, let's pick some its tables, extract the data, and then import the data back to another database or a file system.

Figure 1-15. SSIS project package creation

Open SQL Server BIDS and from the File menu, choose New, then select New Project. From the available Project Types, choose Business Intelligence Projects and from the Templates, choose Integration Services Project. Provide necessary details (such as Name, Location, and Solution Name) and click OK.

Once you create the new project, you'll land on the Control Flow screen as shown in Figure 1-15. When you create an SSIS package, you get a lot of tools in the toolbox pane that is categorized by context based on selected design window or views. There are four views namely Control Flow, Data Flow, Event Handlers and Package Explorer. Here below we will discuss two main views, the Data Flow and the Control Flow:

Data Flow

Control Flow

Let's now see how to import data from a system and export it to another system.

Figure 1-16. Using the SSIS Import and Export Wizard

Here are the steps to import from a source to a destination:

Importing can also be done by using various data flow sources and writing custom event handlers. Let's do it step-by-step now:

PROBLEM CASE

The Sales data from the AdventureWorks database consists of more than seven tables but for simplicity let's consider the seven tables displayed in Figure 1-17. Now, you need to retrieve the information from these seven tables, extract the data, clean some of the data or drop the columns that aren't required, and then load the desired data into another data source—a flat file or database table. You might also want to extract data from two different data sources and merge it into one.

Figure 1-17. Seven AdventureWorks sales tables

Here's how to accomplish our goals:

1. Open BIDS and from the File menu choose New, then select New Project. From the available project types choose Business Intelligence Projects and from the Templates choose Integration Services Project. Provide necessary details (such as Name, Location, and Solution Name) and click OK.

2. Once the project is created, in Solution Explorer right-click on Data Sources, Add a New Data Source to the project, and choose the option "Create a data source based on an existing or new connection". The list of data connections will be empty the first time. Click the New button and connect to the server using one of the authentication models available.

3. Select or enter a database name—AdventureWorks in this example—and click the Test Connection button as shown in Figure 1-18. On success, click OK and then Finish to complete the Data Source Wizard.

   

   Figure 1-18. SSIS Connection Manager

4. Now double-click on Package.dtsx and open the Data Flow tab. This is where you build the data flow structures you need. You might not have the data flow ready when you run the project first time. Follow the instructions onscreen to enable the Task panel, as shown in Figure 1-19.

   

   Figure 1-19. Enabling the Data Flow Task panel

   Clicking on the link 'No Data Flow tasks have been added to this package. Click here to add a new Data Flow task' (Figure 1-19) would enable the data flow task pane where you can build a data flow by dragging and dropping the tool box items as shown in figure 1.-20. Add an ADO NET Source from the Toolbox to the Data Flow Task panel as shown in Figure 1-20.

   

   Figure 1-20. Adding a Data Flow Source in SSIS

5. Double-click on the ADO NET Source and configure the Connection Manager (see Figure 1-21). Click the New button and follow the same steps to make the connection.

6. Under Data access mode, choose SQL command, as shown in Figure 1-21.

   

   Figure 1-21. SSIS Connection Manager data access mode settings

7. Under SQL command text, enter a SQL query or use the Build Query option as shown in Figure 1-22. (Since I had already prepared a script for this, that's what I used. You'll find the SQL script 'salesquery.txt' in the resources available with this book.) Then click on the Preview button to view the data output.

   

   Figure 1-22. ADONET Source Editor settings

8. Check the Columns section to make sure the query was successful, then click OK to close the editor window (See Figure 1-23).

   

   Figure 1-23. ADONET Columns

9. From the Data Flow Destinations section on the Toolbox, choose a destination that meets your needs. For this demo, we'll select Flat File Destination.

10. Drag and drop the Flat File Destination onto the Data Flow Task panel as shown in Figure 1-24. If you see a red circle with a cross mark on the flow controls, hover over the cross mark to see the error.

11. Connect the green arrow from the ADO NET source to the Flat File Destination.

    

    Figure 1-24. Setting an SSIS Data Flow destination

12. Double-click on the Flat File Destination, which will open the Editor (Figure 1-25).

13. Click the New button to open the Flat File Format window, where you can choose among several options. For our demo, select Delimited.

    

    Figure 1-25. Flat File Format settings

14. The Flat File Connection Manager Editor window will open next, with options for setting connection manager name and, most importantly, the delimited format, as shown in Figure 1-26. Click OK to continue.

15. On the next screen, click on Mapping. Choose the Input Column and Destination Columns and then click OK.

    

    Figure 1-26. SSIS Connection Manager settings

16. The final data flow should look like what's shown in Figure 1-27.

    

    Figure 1-27. SSIS Data Flow

17. Start debugging by clicking F5, or click Ctrl+F5 to start without debugging.

18. During the process, you'll see the data flow items change color to yellow.

19. Once the process completes successfully, the data flow items change to green, as shown in Figure 1-28.

    

    Figure 1-28. SSIS Data Flow completion

20. Click the Progress tab to view the progress of the execution of an SSIS package (see Figure 1-29).

    

    Figure 1-29. SSIS package execution progress

21. Complete the processing of the package by clicking on Package Execution Completed at the bottom of the package window.

22. Now go to the destination folder and open the file to view the output.

23. Notice that all the records (28,866) in this case are exported to the file and are comma-separated.

24. In a more general case, you would probably be using Data Flow Transformations such as Aggregate or Data Conversion. If you did this, the output would then be the aggregation of a chosen input column. You can set the Data Flow Transformations as shown in Figure 1-30.

    

    Figure 1-30. SSIS data flow using the Aggregate transformation

In many cases, you might actually get a flat file like this as input and want to extract and load it back into your new database or an existing destination database. In that scenario, you'll have to take this file as the input source, identifying the columns based on the delimiter, and load it.

As mentioned before, you may have multiple data sources (such as two sorted datasets) that you want to merge. You can use the merge module and load the sources into one file or dataset, or any other destination, as shown Figure 1-31.

Figure 1-31. SSIS data flow merge options

Now let's see how to move our OLTP tables into an OLAP star schema. As we've chosen tables in the Sales module, let's identify the key tables and data that need to be or can be analyzed. In a nutshell, we need to identify one fact table and other dimension tables first for the Sales Repository (as you see in Figure 1-32).

The idea is to extract data from the OLTP database, clean it a bit, and then load it to the model shown in Figure 1-32. The first step is to create all the tables in the star schema fashion.

Table 1-3 shows the tables you need to create in the AdventureWorksDW database. Once you've created them, move the data in the corresponding tables of the AdventureWorks database to these newly created tables. So your first step is to create these tables with the specified columns, data types, and keys.

Figure 1-32. Sales fact and dimension tables in a star schema

After you build these tables, the final tables and their relations would look pretty much as shown in Figure 1-33.

Figure 1-33. Sample OLAP tables in a star schema

Notice the Fact_SalesOrderDetails table (fact table) and how it is connected in the star. And the rest of the tables are connected in the dimension model making up the star schema. There are other supporting tables but we'll ignore for a while since they may or may not contribute to the cube.

You may be wondering how we came to the design of the tables.

Rule #1 when you create a schema targeting a cube is to identify what you would like to accomplish. In our case, we would like to know about how Sales did with stores, territory, and customers, and across various date and time factors (recollect the cube in Figure 1-6). Based on this, we identify the dimensions as Store, Territory, Date/Time, Customer, Sales Order Header. Each of these dimensions will join to the Facts Table containing sales order details as facts.

As with the SSIS example we did earlier (importing data from a database and exporting to a flat file), we can now directly move the information into these newly generated tables instead of exporting them to the flat files.

One very important and useful data flow transformation is the Script Component, which you can use to programmatically read the tables and columns values from the ADO NET Source (under AdventureWorks database) and write to the appropriate destination tables (under AdventureWorksDW database), using a script. While you do the extraction from the source, you have full control over the data and thus can filter the data, clean it up, and finally load the it into the destination tables, making it a full ETL process. You simply drag and drop the Script Component from the Data Flow Transformations into the Data Flow, as shown in Figure 1-34.

Figure 1-34. Using the SSIS Script Component

Now that we've create the tables and transformed the data, let's see how to group these tables in a cube, create dimensions, run an MDX query, and then see for the results.

Microsoft SQL Server Analysis Services (SSAS) is a powerful tool that lets us create and process cubes. Let's now look at how cubes are designed, what the prerequisites are, and how cubes are created, processed, and queried using the MDX query language.

SSAS is used to build end-to-end analysis solutions for enterprise data. Because of its high performance and scalability, SSAS is widely used across organizations to scale their data cubes and tame their data warehouse models. Its simplicity and rich tools give developers the power to perform various drill-down options over the data.

Let's take care of the easy part first. One way to get started with cubes is by using the samples provided with the AdventureWorks database. BIDS sample projects are located at C:program filesmicrosoftsql server100 oolssamplesAdventureWorks 2008 Analysis Services Project. You should have two projects for both the Enterprise and Standard versions.

This project contains lots of other resources, including data sources, views, cubes and dimensions. However, these provide information relating to the layout, structure, properties, and so forth of BIDS, and little information about cube structures, and the like.

Once the project is deployed successfully (with no errors), open SSMS. You'll find the cubes from the previous deployment available for access via MDX queries. Later in this chapter you'll learn how to create a simple cube. For now, we'll use the available AdventureWorks cubes.

Executing an Analysis Services MDX query from SSMS involves few steps, as follows:

Figure 1-35. Executing a simple MDX query

Ok, so far you've seen the basic cube (using one from the AdventureWorks database) and also how to query from the cube using a simple MDX query. Let's now begin a simple exercise to build a cube from scratch.

Using data from the Sales Repository example we looked at earlier, let's configure a cube now using the Dimension and Fact tables you created manually with the AdventureWorksDW database.

Now that you know the concepts, let's see some of the important factors to keep in mind while planning for a migration. First, you need to be completely aware of why you need to migrate to an OLAP database from an OLTP database. If you are not clear, it's a good idea to go back to the section "Why do I need to migrate to OLAP?" and make a checklist (review as well the need for a staging section).

Remember the single sign-on (SSO) functionality in MOSS 2007? SharePoint 2010 introduces Secure Store Service (SSS) as its next-generation SSO. This claims-aware (that is, using claims-based authentication) service is part of the Foundation Server and it enables mechanisms by which you store credentials for each application by ID, and reuse the credentials to gain access to external data sources. Visio Services, PerformancePoint Services, and Excel services work in conjunction with Secure Store Services. We will examine SSS in Chapter 2.

What if Visio diagrams came alive? Can they be powered by data in real time? The answer is yes! Visio Services is now available as part of SharePoint 2010 Enterprise edition, allowing you to connect real-time data and update your Visio diagrams on the fly. Share and view published Visio diagrams using the Visio Web Access Web Part, which provides the functionality to integrate Visio diagrams into the SharePoint platform. With a basic knowledge of JavaScript and the client object model, you can create compelling, rich, interactive UIs. Learn more about Visio Services in Chapter 2.

A lot of new functionality has been added to Reporting Services in SharePoint 2010. New features include support for claims-based authentication and user tokens, deployment utilities, support for multiple languages, and connectivity with list and document libraries.

Because report-authoring tools are an integral part of SQL Server Tools, they are integrated in SharePoint so that generating and customizing reports has been easy in a SharePoint Server (as well as MOSS 2007) environment.

Out-of-the-box Reporting Services web parts take care of other needs by providing a platform to load reports designed using Report Builder. Learn more about Reporting Services in Chapter 3.

With SharePoint 2010, PerformancePoint Services is now available as an integrated part of the SharePoint Server Enterprise license. You can now consume context-driven data, build dashboards with the help of the built-in Dashboard Designer, and take advantage of KPIs, and scorecards. Data from various sources can be integrated using SSIS, loaded using SSAS, and finally published into SharePoint. Support for SharePoint lists and Excel Services are additional benefits. The RIA-enabled Decomposition Tree is a powerful new visualization report type that lets you drill down into to multidimensional data. Learn more about PerformancePoint Services in Chapter 4.

Excel Services is also available now as part of SharePoint Server Enterprise licensing, and now support multiple data sources aggregation in a single Excel workbook. Excel Services allows these workbooks to be accessed as read-only parameterized reports via the thin web client. You can choose to expose the entire workbook or only portions of the workbook, such as a specific chart or a specific pivot table. Content in Excel can be accessed though various methods, such as REST services, Web Services, publishing to the SharePoint environment, and using JavaScript.

With the addition of PowerPivot to Excel 2010 and its in-memory data store capabilities, it's now possible to connect to the OLAP data sources that give users the flexibility to analyze their data. Learn more about Excel Services and PowerPivot in Chapter 5.

Business Connectivity Services (BCS) is a part of SharePoint 2010 Composites, prebuilt components you can use to create custom business solutions. BCS is the next version of Moss's well-known Business Data Catalog. BCS is now bi-directional (in that it can both display business data and, with appropriate permissions, update it), and it is much easier now to connect with various other systems. Tools such as SharePoint Designer and Visual Studio provide the functionality that lets you connect with external data sources such as SQL Server, WCF services, and .NET connector assemblies. Visual Studio and custom code make it possible to address almost any problem. While Business Data Connectivity services, External Data columns and External lists are available in the Foundation Server, data search, profile pages, and Secure Store belong to Standard version, and the rest of the external data Web Parts, and Office Client Integration reside under Enterprise Client Access License (CAL). Learn more about BCS in Chapter 6.