Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

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

MSSQLTips author Ray Barley By:   |   Read Comments (16)   |   Related Tips: > 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:

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

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 2005 on the local machine.
  • Stay tuned for additional tips on building and working with SSAS cubes.


Last Update: 7/2/2008


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, June 12, 2014 - 10:46:08 AM - Evette Read The Tip

This was a really good article.  Thanks


Wednesday, October 09, 2013 - 6:19:27 AM - Gunjan Patel Read The Tip


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 04, 2013 - 2:01:33 PM - Ray Barley Read The Tip

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 04, 2013 - 9:27:46 AM - Amir Read The Tip

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


Sunday, March 03, 2013 - 8:02:09 AM - Ray Barley Read The Tip

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 03, 2013 - 5:25:17 AM - amit Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Great ideas, Ray. Thanks again!


Friday, July 18, 2008 - 6:58:24 AM - raybarley Read The Tip

 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 Read The Tip

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 Read The Tip

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 Read The Tip

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




 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.