Problem
We are just getting started with SQL Server Analysis Services and trying to build our first cube. We have an existing relational data source to use as our starting point. There are a number of steps involved in building an SSAS cube. Can you guide us through the steps to build a cube and provide some explanations?
Solution
There are two ways to build a cube using Business Intelligence Developer Studio (BIDS). If you have existing relational data source(s) you can create a Data Source and a Data Source View (DSV), then proceed to build the cube using the DSV. If you do not have a relational data source, you can build a cube with BIDS by choosing to build a cube without a data source. In this case you focus on designing the dimensions and measures for your cube, then allow BIDS to generate the relational schema that you need to populate the cube. Please refer to our earlier tip How To Build a Cube Without a Data Source Using SQL Server Analysis Services for the details on building a cube without using a data source.
In this tip we will walk through the steps to build a cube from an existing relational data source.
Background
Before we create the cube, let’s discuss some key concepts at a high level:
- Dimension – a table that represents a real world entity; e.g. Customer, Product, Region, State, Calendar
- Fact – a table that contains foreign keys to dimension tables and the numeric values (i.e. measures) that we want to summarize, average, etc; e.g. Orders
- Dimensional Model or Star Schema – a group of dimension and fact tables designed for analyzing a business process where a fact table is joined to one or more dimension tables via a foreign key
The following diagram is an example of a star schema based on the AdventureWorksDW sample database that comes with SQL Server 2005:
In the above diagram FactInternetSales is the fact table (in the middle); it has foreign key relationships to the various dimension tables as shown by the connecting lines. We typically filter on columns in the dimension tables such as a date range, product, customer, etc. In SSAS a fact table is the equivalent of a measure group.
SSAS provides a cube wizard to guide you through the steps to create a cube. You create a Data Source which essentially represents the database connection string required to access the existing data. You then create a Data Source View which allows you to create a logical star schema from the underlying relational data source. In addition the Data Source View provides the ability to add new columns to an existing table (a Named Calculation), create new tables (a Named Query), and define primary key / foreign key relationships, all without actually modifying the underlying Data Source. The Data Source View itself encapsulates these schema changes.
Building the Cube
BIDS is the integrated development environment that comes with SQL Server 2005. Launch it from the Microsoft SQL Server 2005 program group and let’s walk through the steps to build a cube from an existing data source.
Step 1: Create a new project by clicking File, New, Project from the menu then fill in the dialog as follows:
After creating the project, you should see the Solution Explorer (shown below). If it is not visible, click the View menu item, then select Solution Explorer. In the steps following we will be referring to the various nodes that you see under the project.
Step 2: Create a Data Source; right click Data Sources in the Solution Explorer, then select New Data Source from the context menu. You can select from a list of a Data Sources that you have already defined or create a new one. Click New then fill in the Connection Manager dialog as follows (AdventureWorksDW is a sample database that comes with SQL Server 2005 and later verrsions):
Note that the Provider drop down list only shows .Net Providers for SQL Server and Oracle, as well as a selection of native OLE DB drivers.
After selecting the Data Source, you have to specify the credentials that SSAS will use to connect to the Data Source. SSAS connects to the Data Source when you are working with the Data Source View (our next step) and also when processing the cube (i.e. extracting data from the Data Source to populate the cube). Select the credentials to use in the Impersonation Information dialog:
Some points about setting the Impersonation Information:
- You can specify a user name and password; this might be a good choice when you have many data sources and you want to fine tune security
- Use the service account will use the credentials that the SSAS service runs as; this account must have at least read access to the data source
- Use the credentials of the current user requires that users have at least read access allowed for the data source
- Default uses the Data Source Impersonation Info setting for the SSAS database (right click the SSAS database in SQL Server Management Studio then select Properties to set or view)
When the Data Source Wizard is complete, you will see the confirmation dialog:
Step 3: Create a Data Source View; right click Data Source Views in the Solution Explorer, then select New Data Source View from the context menu. Select the AdventureWorksDW Data Source from the Select a Data Source dialog then select the following tables from the Select Tables and Views dialog:
The Data Source View allows you to focus on just the tables and views from the Data Source that you need to build the cube. You can edit the Data Source View later if necessary to add tables or views, create named calculations, create named queries, or add primary key / foreign key relationships.
Step 4: Launch the cube wizard; right click Cubes in the Solution Explorer, then select New Cube from the context menu. Click the radio button to build the cube using a data source; click the auto build check box to create attributes and hierarchies:
Step 5: Select the Data Source View for the cube; select the AdventureWorksDW Data Source View that we defined earlier:
Step 6: Identify facts and dimensions. The wizard reviews the tables (and views) selected and takes a stab at which ones are facts and dimensions. The wizard does not automatically pick the Time dimension; select DimTime from the drop down list. If necessary change the fact or dimension settings.
Step 7: If you specify a Time dimension, you need to map the Time table properties to the appropriate columns in the table. With intuitively named columns this should be a simple task. For additional details on the Time dimension see our earlier tip Using the Built-In Time Dimension and Time Intelligence in SQL Server Analysis Server.
Step 8: Select Measures. The wizard will select every numeric column as a measure. Uncheck things like key columns.
Step 9: Review the New Dimensions. You can drill down and check or uncheck attributes in the dimensions.
Step 10: Complete the wizard. Specify a name for the cube.
Step 11: Process the cube. Right click the AdventureWorksDW cube under the Cubes node in the Solution Explorer then select Process from the context menu. Click Run on the Process dialog. At this point you have built and populated your first cube.
Step 12: Query the cube. Please refer to our earlier tip How To Build a Cube Without a Data Source Using SQL Server Analysis Services to review the steps to view the cube in the Browser tab in BIDS. In this tip we’ll view the cube with Excel 2007 which has a nice, intuitive user interface for querying a cube. Open Excel 2007 and click Data from the menu, click From Other Sources, then click From Analysis Services:
Step 13: After selecting From Analysis Services the Excel Data Connection Wizard is launched. Complete the following dialogs to connect to the cube:
Step 14: After completing the Excel Data Connection Wizard, the PivotTable Field List will be available with placeholders for the areas in the Pivot table where you can display fields. Drag and drop fields from the fact and dimension tables onto the Report Filter, Column Labels, Row Labels and Values boxes as shown below (measures go in the Values box, dimension attributes go in the other boxes):
You will now see a pivot table that looks like this (click the glyph next to Order Date to select the year 2004):
Next Steps
- Download the sample SSAS project here to review the SSAS project. It requires the AdventureWorksDW database that comes with SQL Server on the local machine.
- Stay tuned for additional tips on building and working with SSAS cubes.
