SQL Server 2005 : Basic OLAP - Building Your First Cube (part 1) - Creating the Project

8/28/2012 8:45:48 PM
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.


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

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.


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.

Figure 1. Creating a new Analysis Services project in the New Project dialog box

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.

Figure 2. Filling out the Connection Manager dialog box to create an Analysis Services project data source

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.

  •  SQL Server 2005 : Basic OLAP - OLAP 101
  •  SQL Server 2005 : Report Server Architecture
  •  SQL Server 2005 : Report Management - Publishing, SQL Server Management Studio
  •  SQL Server 2005 : Report Access and Delivery (part 2) - Presentation Formats, Programming: Rendering
  •  SQL Server 2005 : Report Access and Delivery (part 1) - Delivery on Demand, Subscriptions
  •  Transact-SQL in SQL Server 2008 : Change Tracking (part 2) - Identifying Tracked Changes, Identifying Changed Columns, Change Tracking Overhead
  •  Transact-SQL in SQL Server 2008 : Change Tracking (part 1) - Implementing Change Tracking
  •  SQL Server 2005 : Report Definition and Design (part 3) - Report Builder
  •  SQL Server 2005 : Report Definition and Design (part 2) - Report Designer
  •  SQL Server 2005 : Report Definition and Design (part 1) - Data Sources, Report Layouts
    Top 10 Video Game
    -   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Total War: Warhammer [PC] Demigryph Trailer
    -   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
    -   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
    -   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
    -   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
    -   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
    -   Satellite Reign [PC] Release Date Trailer