Microsoft Office Tutorials and References
In Depth Information
You might be wondering how we arrived at 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 this case,
we would like to know how Sales did with stores, territories, 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,
and Sales Order Header. Each of these dimensions will join to the Facts Table containing sales order details as facts.
As with the SSIS example you saw earlier (importing data from a database and exporting it to a flat file), you can
now directly move the information into these newly generated tables instead of exporting it to the flat files.
One very important and useful data flow transformation is the Script Component, which you can use to
programmatically read the table and column values from the ADO NET Source (under the AdventureWorks database)
and write to the appropriate destination tables (under the 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 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
■ For more information on using the script Component to create a destination, see the references at
■ Many other operations—including Ftp, e-mail, and connecting web services—can be accomplished using
ssis. another good real-time example of when you could use ssis is when you need to import a text file generated from
another system or to import data into a different system. You can run these imports based on schedules.
Now that we’ve created 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 the results.
Creating Cubes Using SSAS
Microsoft SQL Server Analysis Services (SSAS) is a powerful tool you can use to create and process cubes. Let’s look
at how cubes are designed, what the prerequisites are, and how cubes are created, processed, and queried using the
MDX query language.
■ sQl server 2012 comes with a lot of new features. (Visit msdn.microsoft.com/en-us/library/bb522628.aspx
for more information.) One of the key features is the new analysis services tabular Model. the cubes you will create in
this section are based on the conventional analysis services Multidimensional Model. We will briefly introduce the tabular
Model in this chapter and explore it further in the subsequent chapters. Whenever we talk about “analysis services” in
this chapter, you can assume we are referring to Multidimensional Models, unless otherwise stated.
Search JabSto ::