How To Build a Cube Without a Data Source Using SQL Server Analysis Services

By: Ray Barley   |   Updated: 2008-06-27   |   Comments (3)   |   Related: > Analysis Services Development

Problem

We are new to SSAS and are struggling with how to get started.  We don't yet have a relational data warehouse that we can use to populate a cube.  When you go to build a cube there is an option to build the cube without a data source.  Can you provide us with the details on how to go about doing this?

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 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.

In this tip we will walk through the steps to build a cube from scratch without using an existing relational data source.  You will find that this approach is often convenient because to build a cube you really should have dimension and fact tables in an existing relational data warehouse as your starting point.  Having a relational data source that supports your business operations as your starting point just isn't a great idea since the operational database schema is designed to facilitate transaction processing as opposed to querying.  You can jump start your effort to create a cube by designing the cube first, rather than designing the data warehouse then creating a cube from it.

Background

Before we create the cube, let's provide a high level overview of the key concepts:

• 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 - a group of dimension and fact tables designed for analyzing a business process

We join fact tables to dimension tables to slice and dice the data, typically filtering on columns in the dimension tables such as a date range, product category, customer type, etc.  In SSAS a fact table is called a measure group.

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.  Let's walk through the steps to build a cube from scratch.

Step 1: Create a new project by clicking File, New, Project from the menu then fill in the dialog as follows:

Step 2: 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 without a data source:

Step 3: Define the measures.  Measures are the numeric values you want to analyze.  Note that a Measure Group is represented as a single table in the relational schema that populates the cube.  You can have multiple measure groups; we will just use one.

Step 4: Define the cube dimensions.  Click the checkbox next to Time to add a dimension that will contain dates and various attributes such as year, quarter and month.  I like to call this dimension Calendar.  Add other dimensions such as Customer and Product.

Step 5: Define time periods.  The Calendar dimension will be populated automatically; select the time periods that you want to use to analyze your data.  The date range chosen matches the order date range in the AdventureWorksDW database, which we will use later to populate the cube with some sample data.  This dimension is also used to perform detailed analysis using all sorts of criteria such as previous year, previous quarter, same period previous year, etc.  For additional details see our earlier tip Using the Built-In Time Dimension and Time Intelligence in SQL Server Analysis Server.

Step 6: Specify additional calendars.  We are skipping this step for our example.  If you want to perform analysis based on a fiscal year calendar or some other calendar, you would set that up here.

Step 7: Specify the relationships between the measure group and dimensions; in our case the single measure group uses each dimension.  In the generated relational schema, the fact_Order table will have a foreign key to each of the dimension tables checked.

Step 8: Complete the wizard.  Enter the Cube name and click the Generate schema now checkbox; this will launch the Schema Generation Wizard immediately to walk through generating the relation schema that will be used to populate the cube.  You could leave the Generate schema now checkbox unchecked and add some attributes to the dimensions; you can still generate the relational schema from the Database menu in the SSAS project. 

Step 9: Create the Data Source View (DSV).  The cube is populated from a DSV which is a logical view on top of one or more data sources.  Click the New button (to the right of the Data Source drop down, not shown) to define a data source.  The data source is the database where the generated relational schema will be added.

Step 10: Fill in the Connection Manager dialog; the database you enter or select must already exist.

Step 11: Choose how SSAS will connect to the data source.  SSAS needs to query the data source to populate the cube.

Step 12: Provide a name for the new data source.

Step 13: Specify the schema generation options (just accept the defaults).  Select Populate from the Populate time table(s) drop down list to automatically populate the Calendar dimension based on the options specified in Step 5 above.

Step 14: Specify the naming conventions (just accept the defaults).

At this point you will see the following Data Source View in BIDS that was generated based on the options selected in the preceding steps:

Step 15: Populate the data source with sample data from the AdventureWorksDW database that comes with SQL Server 2005.  The purpose of this step is to put some data in the data source so we can use it to populate our cube.  Run the script below:

USE MSSQLTipsDW
GO
-- populate the Customer dimension
INSERT INTO MSSQLTIPS_SSAS_1.Customer (
PK_Customer, Customer_Name
)
SELECT CustomerKey, LastName 
FROM AdventureWorksDW.dbo.DimCustomer
GO
-- populate the product dimension
INSERT INTO MSSQLTIPS_SSAS_1.Product (
PK_Product, Product_Name
)
SELECT ProductKey, EnglishProductName 
FROM AdventureWorksDW.dbo.DimProduct
GO
-- populate fact_Order
INSERT INTO MSSQLTIPS_SSAS_1.fact_Order (
FK_Calendar, FK_Customer, FK_Product,
QuantitySold, UnitPrice, SaleAmount
)
SELECT 
t.FullDateAlternateKey,
f.CustomerKey,
f.ProductKey,
f.OrderQuantity,
f.UnitPrice,
f.ExtendedAmount
FROM AdventureWorksDW.dbo.FactInternetSales f
JOIN AdventureWorksDW.dbo.DimTime t 
  ON t.TimeKey = f.OrderDateKey
GO

Step 16: Process the cube.  This step extracts data from the DSV and populates the cube.  Right click MSSQLTIPS_SSAS_1.cube under Cubes in the Solution Explorer and select Process from the context menu.  Accept all defaults and click Run on the Process Cube dialog.

Step 17: View the cube.  Click the Browser tab in BIDS and you will see the dialog below with the hints to drag and drop measures and dimensions.  Expand the measures and dimensions then drag and drop as follows:

• Measures.fact_Order.SaleAmount onto Drop Totals or Detail Fields Here (to the right of Drop Row Fields Here and underneath Drop Column Fields Here, not shown below)
• Calendar.Year onto Drop Column Fields Here
• Product.Product onto Drop Row Fields Here

You will see the following, showing how the cube allows you to quickly pick the data elements of interest (i.e. measures) then slice and dice them by the various dimension attributes.

Next Steps

• As you begin to include SSAS in your business intelligence solutions, remember that you can easily design and create a cube before you actually have a relational data source to populate the cube.  It's often a good approach to focus on designing the cube and then just generate the relational data source to populate it.
• Download the sample SSAS project here to review the cube.  It requires the AdventureWorksDW database that comes with SQL Server 2005 and an additional database named MSSQLTipsDW, both on the local machine.
• Stay tuned for additional tips on building and working with SSAS cubes.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips