Build a Cube From an Existing Data Source Using SQL Server Analysis Services

By:   |   Comments (26)   |   Related: > Analysis Services Development


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:

dim promotion

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:

new project

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.

solution explorer

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):

connection manager

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:

data source wizard

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:

completing the wizard

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:

tables and views

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:

cube wizard

Step 5: Select the Data Source View for the cube; select the AdventureWorksDW Data Source View that we defined earlier:

cube wizard

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.

dim time

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.

select time periods

Step 8: Select Measures.  The wizard will select every numeric column as a measure.  Uncheck things like key columns.

select measures

Step 9: Review the New Dimensions.  You can drill down and check or uncheck attributes in the dimensions.

cube wizard

Step 10: Complete the wizard.  Specify a name for the cube. 

adventure works

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:

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:   

database server
data connection wizard
connection file
import data

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):

pivot table

You will now see a pivot table that looks like this (click the glyph next to Order Date to select the year 2004):

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley 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


Comments For This Article




Saturday, April 4, 2020 - 6:25:36 AM - Ray Barley Back To Top (85276)

Here are the details on setting up the Data Source in SQL Server ANalysis Services: https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/create-a-data-source-ssas-multidimensional?view=asallproducts-allversions

The above link will explain what you need as far as the options for connecting to the data source. Essentially you specify the account that SSAS will use to connect to the data source to retrieve the data for preossing the cube. Whatever account you use, that account needs the appropriate permissions on the SQL Server database(s) that SSAS needs to access. Assuming to you want to allow the minimum permission necessary, the account should be added to the db_datareader fixed database role for each database that it needs to access. This allows the account to read all data from all user tables.

You can find the details on db_datareader here: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15

Friday, April 3, 2020 - 3:39:24 PM - Navjot Back To Top (85270)

I am unabel to make connection with the data source...I have tried all the options for selecting Impersonation Account information...What should i do now??? How can i find the correct option so that i can connect to my data source

Wednesday, February 1, 2017 - 1:24:21 PM - Ray Barley Back To Top (45786)

 

I think the error "A connection cannot be made" means 1 of 2 things: either SSAS is installed but the service isn't running or SSIS is not installed,  If it is installed you need to start the service; otherwise install it or change your server in the deployment to the name of a server where SSAS is installed.

Wednesday, February 1, 2017 - 10:06:09 AM - Dave Back To Top (45782)

Hi.. thanks for the example.

Am not able to deploy the cube, below error is coming and also when i checked the services (sql server analysis services not visible there).

Error5The project could not be deployed to the 'localhost' server because of the following connectivity problems :  A connection cannot be made. Ensure that the server is running.  To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.00

 

thnaks

Wednesday, November 26, 2014 - 12:23:54 PM - Raymond Barley Back To Top (35417)

The answer to your questions is it depends.

Creating a cube; aka deploying the SSAS project without processing the cube takes very little time; generally a couple of minutes at most.  It's like creating a database; all you are doing is creating a schema more or less.

For the rest of your questions you have to adopt best practices to optimize cube processing and availability.

Here are some articles for you to review to get started:

SQL Server 2008 Analysis Services Performance Guide

http://technet.microsoft.com/en-us/library/dd542635(v=sql.100).aspx

Scale-Out Querying for Analysis Services with Read-Only Databases

http://technet.microsoft.com/en-us/library/ff795582(v=sql.100).aspx

Analysis Services Processing Best Practices

http://technet.microsoft.com/en-us/library/cc966525.aspx

 

 

Wednesday, November 26, 2014 - 3:35:07 AM - vanmathi Back To Top (35410)

 

Hi,

Thank you Raymond.  And please help me out the below quries. I may be update the lakhs of records. In these, large amount of data has been processed, what will be factors of following quries.Cube creation and updation done by relational database.  Please help me the answer.

 

1. How long it takes to create cube

2. How long it takes time to update the cube in real time oftenly

3. What are the other factors that affects when create/update the cube .

4. When cube is in update process, whether we can able to use the cube values or not.

 

Thanks,

Vanmathi N.A

Tuesday, November 25, 2014 - 12:03:02 PM - Raymond Barley Back To Top (35400)

If you want to update a cube in real time you probably want to take a look at proactive caching: http://www.mssqltips.com/sqlservertip/1563/how-to-implement-proactive-caching-in-sql-server-analysis-services-ssas/

However without really giving careful consideration to your requirements and time window for updating the cube this is at best a guess.

 

Tuesday, November 25, 2014 - 10:55:25 AM - vanmathi Back To Top (35399)

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting

Hi

Thanks all for previous answers. Now I need another help.

I created the cube from relational data. How to update the cube data in real time process when made changes in relational data that should be updated in cube. 

 

Tuesday, November 25, 2014 - 10:05:43 AM - Raymond Barley Back To Top (35398)

The key relationship is necessary in order to join the measure groups to the dimensions.  For instance if you have a sales measure, you want to be able to relate it to customer, date, product, geography, etc.  Specifying the primary key / foreign key relationship in the underlying relational database or in the data source view is necessary.

Tuesday, November 25, 2014 - 12:35:37 AM - Vanmathi Back To Top (35391)

 

Key relationship is mantatory while creating the cube?? Pls tell me the idea.

Thursday, June 12, 2014 - 10:46:08 AM - Evette Back To Top (32215)

This was a really good article.  Thanks

Wednesday, October 9, 2013 - 6:19:27 AM - Gunjan Patel Back To Top (27087)


Hello Sir,
I create a cube in VS 2012 [Bussiness Intelligence -> Analysis Service--->
Analysis Service Multi Dimensional Data mining Project],

I deploy it successfully and also browse cube,


now i want to use Deployed Cube in my project, How it is possible ,
please Help me...

Thank you....

Thursday, July 4, 2013 - 2:01:33 PM - Ray Barley Back To Top (25716)

I'm assuming you have an ADO.NET dataset and you would like to use it as the data source to populate a cube.  I believe it is possible to do this programmatically using Analysis Services Management Objects (AMO); I've never done it and I think it would be quite a bit of work versus using a project in Business Intelligence Development Studio (BIDS) or SQL Server Data Tools (SSDT)..  I think this is the starting point for your research on how to do it: http://msdn.microsoft.com/en-us/library/ms345091.aspx

 

Thursday, July 4, 2013 - 9:27:46 AM - Amir Back To Top (25710)

I needto create cube by using data set, can you help me with the process?

Sunday, March 3, 2013 - 8:02:09 AM - Ray Barley Back To Top (22534)

Could be any of a number of things.  Do you have Analysis Services installed?  Was it installed as the default instance?  Based on the error message your project's target server property is localhost.  Did you install as a named instance?  If so then your project's target server property should be localhost\instancename.  Is the instance of Analysis Services running?  Is it set to startup automatically?  Open SQL Server Configuration Manager and check the SQL Server Services - this will tell you if you have Analysis Services installed, if it's state is running and what its start mode is.  You can right click on the Analysis Services line, select Properties and make any changes you need.

 

Sunday, March 3, 2013 - 5:25:17 AM - amit Back To Top (22533)

Error2The project could not be deployed to the 'localhost' server because of the following connectivity problems :  A connection cannot be made. Ensure that the server is running.  To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.00

 
 
plz help..

Thursday, October 25, 2012 - 10:46:01 AM - DAVE Back To Top (20089)

Thanks Ray!  I tried the impersonation tab settings, then I had a "doh" moment and 1) ran SSAS as Administrator and 2) went under Tools->Options and increased the timeouts in there from 15 secs to 30-60 secs.  I also changed the verbosity of output to Detailed.  For whatever reason, that let it process.  When I ran as Administrator, it let me get to the Process screen but I got more "username unknown" or "logon failed" errors until I adjusted the timeouts.  Thanks for the help, you're awesome

Wednesday, October 24, 2012 - 12:16:17 PM - Ray Barley Back To Top (20072)

Check the impersonation Information tab on your data source.  In order to access data on another machine you generally need a domain account.  You can select Windows user name and password,Use the credentials of the current user, if they are domain accounts.  By default Use the service account would probably be a local windows account that won't work when connection to a database on another machine.

Wednesday, October 24, 2012 - 11:32:52 AM - DAVE Back To Top (20071)

I realize your example is single-server.  But using perhaps a more work-related example with SSAS 2005, what if you have your data on one Database server, and you have BIDS and Analysis Services installed on another Dev server.  I assume you should be able to design your cube in BIDS on the Dev server and pull data from the Database server to build a cube. 

Unfortunately, I am getting constant errors doing this.  The Build always fails saying something akin to "Error 1 Either the '\' user does not have permission to create a new object in 'Dev', or the object does not exist."  I am using an Admin user with full auth on my Dev box and I have checked folder properties showing that my user has Full Control all the way to the \bin directory in which it is purportedly trying to create my cube.  BIDS works fine to let me reference my tables on my DB server and design my datasource, view and cube but apparently will just not let me pull that data to build the cube object.   So...I am wondering, do I have to have the actual database on my Dev server, or do I need a Full install of SS in order to make this work? Any ideas? Is there another, more detailed log that would have more detail than the Error List in BIDS?

Thanks in advance. 

Saturday, February 27, 2010 - 10:25:20 AM - raybarley Back To Top (4977)

I just tried it with SSAS 2008 and I got the same result as you.  I can't think of a good reason why it should work this way. 

At the time I did the tip I thought this approach was good but I think it's actually better to just use the dimension wizard to create each dimension one at a time (i.e. right click on Dimensions in the solution explorer then select New Dimension). After you have all of your dimensions created and fine-tuned (e.g. naming, attribute properties set, hierarchies, etc.) then use the cube wizard to build your cube.  You can then just select which dimensions you want to include in the cube.

Friday, February 26, 2010 - 9:44:10 AM - Mrnosh Back To Top (4975)

This is all good and Cube worked just fine for 2005. I tried it with 2008 and is not the same, I do not get a chance to define the DimTime table as time dimensional and its been defined as a regulare dimensional. Has anyone tried it with 2008? Thx    

Friday, July 18, 2008 - 7:09:02 AM - bluedogs Back To Top (1453)

Great ideas, Ray. Thanks again!

Friday, July 18, 2008 - 6:58:24 AM - raybarley Back To Top (1452)

 I'm not sure how to do it in SSAS but you could:

- Do it in the Data Source View using a named query; e.g. add in a CASE statement to map the ranges to text columns like '0 - 20'

- Create a view in the Data Source and use CASE

- Do it as part of your ETL process

 

 

Friday, July 18, 2008 - 6:32:11 AM - bluedogs Back To Top (1450)

Thanks for the advice Ray...I appreciate it.

 I thought about using the discretization buckets, but I was not able to find a way to specify custom buckets. My understanding is that SSAS decides how to divide the data depending on the bucket count you choose...I need to be able to specify 0-20, 20-350, 350-10000, for example.

 Do you know any way to do this? Do I need to get out my MDX book? :)

Thursday, July 17, 2008 - 6:24:32 PM - raybarley Back To Top (1445)

I have a suggestion that could help out.  You can group members in a dimension so that instead of having 10,000 discrete values you have a much smaller number of values which are actually ranges of values.  

There are 2 attribute properties that you use to set this up: DiscretizationBucketCount (i.e. how many) and DiscretizationMethod (Automatic, EqualAreas, Clusters).

You can find an example in AdventureWorks  - take a look at the YearlyIncome attribute in the Customer dimension. It has values like 10000 - 30000, 40000 - 70000, 80000 - 90000, etc.

Thursday, July 17, 2008 - 6:55:06 AM - bluedogs Back To Top (1443)

I am having some annoying issues with Excel 2007...the main one is that I am able to do a "between" filter on an attribute, but when I move that attribute to the "Report Filter" area, I am only able to filter by checkbox, which doesnt work really well when you have 10,000 values :)

Has anyone else seen this issue and found a resolution for it? Thanks in advance














get free sql tips
agree to terms