I wanted to become a little more familiar with SQL Server Analysis Services, so I’ve posted the steps I used to create a simple cube. This is based off a simple database populated by this Test Database Script. Create the database first then apply the script.

I used SQL Server 2008 with 2008 Analysis services installed, as well as BIDS (Business Intelligence Development Studio) with Visual Studio 2008. I also used Excel 2010 for the last example.

— Create Cube

1) Open BIDS and create a new project – Project types ‘Business Intelligence Projects’ – ‘Analysis Services Project’
2) Create data source – Database connection –
a-Right click on ‘Data Sources’ – ‘New Data Source’ – ‘Create a data source based on an existing or new connection’ – ‘New’ button
b-select an option for connecting to DB – at first I selected ‘Use the credentials of the current user’, but then I got authentication errors when deploying the cube. Once I went back to the data source and submitted my user name and password, I was able to deploy.
3) Create a data source view – Use data source – Add tables
a Right click ‘Data Source Views’ – ‘New Data Source View’
b – Select data source
c – Select tables for the view
4) Create cube
a- Right click ‘Cubes’ – ‘New Cubes’
b- Use existing tables
c- Select measure group tables – Fact tables
d- Select Measures
e- Select new Dimensions
f – Name cube

— Dimensions

By default, only the primary key of each dimension table is brought in. We want to have each name available, instead of an ID number.
1) In solution explorer, open the ‘Sales Person’ dimension.
2) From the Data Source View pane, drag the ‘SalesPersonName’ column over to the ‘Attributes’ pane and Save.
3) Repeat with ‘Product Name’ with ‘Product’ and with ‘Customer’ (Name, State and Country) and DateDim (DateDay, DateMonth, DateMonthName, DateQuarter, DateYear).

— Deploy Cube

1) Setup
a – Right click on project – Properties
b – Under configuration properties select ‘Deployment’
c- Under ‘Target’, specify server and Database name.
2) Deploy
a – Right click project – ‘Process’

— Browse Results in AS

Build a pivot table showing which customers bought which products.
1) Open cube and go to ‘Browser’ tab.
2) Select a measure (Number Sold) and drag to ‘Detail Fields’ on browser.
3) Under the ‘Product’ dimension, drag ‘Product Id’ to the ‘Column Fields’ section of the browser.
4) Under the ‘Customer’ dimension, drag ‘Customer ID’ to the ‘Row Fields’ section of the browser.

— Data in Excel

If you have Excel and installed the PowerPivot tools when installing Analysis Services, data can be manipulated in a spreadsheet, much like in the Browser.

1) Open Excel, go to ‘Data’ tab.
2) ‘From Other Sources’, ‘From Analysis Services’
3) Provide server name and credentials
4) Select database and cube
5) Save connection file and Finish
6) Import data – select ‘PivotTable Report’ and ‘Existing Worksheet’.
7) Once the wizard has completed, you’ll see a PivotTable field list on the right. The dimensions can be selected and drug below to the ‘Column Labels’, ‘Row Labels’ and the measure to ‘Values’ to create the pivot table.