Now that you can imagine the
design and implementation of fact tables and dimension tables, how do
you go about creating a cube? Let’s go over the steps required to do
this now.
Preparing Star Schema Objects
Rather than using the sample data
warehouse and cubes based on the AdventureWorks database that comes with
Analysis Services, we’d like to create our own from scratch. To do so,
we’ll base our sample on the older Northwind SQL Server sample database.
Note
SQL
Server 2005 does not ship with the Northwind database because the
Adventure Works database was designed to replace it. However, you may
use the version of Northwind that came with SQL Server 2000, if you have
it, and attach it to your SQL Server 2005 server. Microsoft has also
published an MSI file that will install both Northwind and the older
pubs sample databases on your server. The MSI provides both MDF/LDF
files that can be directly attached, as well as T-SQL scripts, which can
be executed to create the databases from scratch. At press time, the
download page for this MSI is http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en.
|
Once the Northwind database has been installed on your server, making sure that tbl Time.sql is run before
tblFact.sql. (The order of execution for the other scripts is
insignificant.) Once these steps are complete, your copy of Northwind
should contain a new table called tblFact that will serve as the fact
table, a new table called tblTime that will serve as the time dimension
table, and the following views, which will serve as the other dimension
tables: vwEmployees, vwGeography, vwProducts, vwShippers, and
vwSuppliers. Each of these tables and views is created by its namesake
SQL script. Once these tables and views are in place, you can proceed to
design, build, and query your cube.
A Tool by Any Other Name
You’re ready to build
your cube now, and in order to do so, you’ll need special design tools.
Under SQL Server 2000 Analysis Services, cubes were designed and managed
in a tool called Analysis Manager, a Microsoft Management Console (MMC)
snap-in that was, in effect, the Analysis Services analog to SQL Server
Enterprise Manager. Back in the late 1990s, MMC snap-ins were a popular
vehicle for management and design tools. These days, Visual Studio,
with its rich extensibility APIs, is a more popular choice.
The Analysis Services
team decided to use Visual Studio 2005 as the host for a number of
business intelligence (BI)–related designers, including those for
Reporting Services, Analysis Services. It turns out, that SQL Server Management Studio can also be used as a
management tool for Analysis Services databases; however, the Analysis Services designers are hosted inside Visual Studio.
Microsoft realized this, of course, and knew they needed a
workable solution to the problem. In effect, Microsoft needed a way to
ship the bare-bones Visual Studio integrated development environment
(IDE) with SQL Server 2005 so that the various designers and project
types supported by the SQL Server BI components that use Visual Studio
could be created and manipulated by users who do not have a license for
the Visual Studio product itself.
When you install the BI
components of SQL Server on a machine that does not have Visual Studio
2005 installed, the SQL Server installer will place just such a
scaled-down version of Visual Studio on the target machine; if a copy of
Visual Studio 2005 is already installed on the machine, the BI
designers will use it instead.
Note
The
Express editions of Visual Studio are not sufficient to accommodate the
various SQL Server BI designers. Machines with any of the Visual Studio
Express Edition SKUs are treated by SQL Server identically to machines
that have no Visual Studio 2005 bits on them at all. |
So far, all of this
seems reasonable, but there is one detail of nomenclature that can make
this a bit confusing. The SQL Server 2005 installer will create a
shortcut to Visual Studio on your machine (either the bare-bones copy it
might have installed or the existing copy you otherwise already had).
This shortcut’s name is not “Microsoft Visual Studio 2005” but “SQL
Server Business Intelligence Development Studio.”
It is important to
realize that this shortcut actually points to Visual Studio, be it the
bare-bones version or the full product, and that there really is no such thing
as Business Intelligence Development Studio. You may find this name
useful to identify the bare-bones version of the Visual Studio IDE (and
the BI designers) we have just discussed, but that explanation becomes
inaccurate when you realize that the shortcut labeled “SQL Server
Business Intelligence Development Studio” actually links to the full Visual Studio product on machines that have it.
The real reason
it is important to refer to the BI designers as being hosted in Visual
Studio and not in Business Intelligence Development Studio is to make
clear that a single Visual Studio solution can indeed contain a mix of SQL Server BI project types and more conventional Visual Studio .NET application project types.
For example, a
single Visual Studio 2005 solution could contain an Analysis Services
project, an Integration Services project, a Reporting Services project, and various C# or Visual Basic .NET
projects including a Windows Forms application, a Class Library
project, and/or an ASP.NET Web site.
Creating the Project
With our understanding of
the toolset and its proper name now established, it’s time to create an
Analysis Services project and design our cube. Start Visual Studio and
select the File/New/ Project... option from the main menu. In the New
Project dialog box, select Business Intelligence Projects from the
Project Types tree view on the left and select Analysis Services Project
from the Templates pane on the right. Enter Chapter17 as your project name, with a file path that works conveniently for you. (Figure 1 shows how the dialog box should appear.) You’re now ready to click OK and create your project.
You’ll notice that
your new project is empty, unlike in .NET projects in Visual Studio,
where a default object is created for you and you merely need to open
it. In Analysis Services projects, you start from scratch. The best
things to add right away are a data source and a data source view.
Create a new data source by right-clicking on the Data Sources folder in
Solution Explorer and selecting New Data Source... from the shortcut
menu to bring up the Data Source Wizard.
Click Next on the
wizard’s Welcome page to advance to the Show How to Define the
Connection page. Click the New... button to bring up the Connection
Manager dialog box, and then for the provider, specify .Net
Providers\SqlClient Data Provider. For the Server Name, enter the
name of your server, supply your login credentials (Windows
Authentication is likely your best choice here), and select Northwind as
the database name. The dialog box should appear similarly to what is
shown in Figure 2.
Click
OK to dismiss the Connection Manager dialog box. Back in the Data
Source Wizard, click Next to advance to the Impersonation Information
page, where you should select the Default option button (that is, the
last one in the list) and click Next again. In the Completing the Wizard
page, set the name of the data source to Northwind, and click Finish.
