Create OLAP cube for SQL Server 2005 Analysis Services

A complete tutorial on how to create an OLAP cube,  with sample sales data, that you can browse in Microsoft Excel. I’ll walk you through the whole process of defining fact table, dimensions and how to configure OLAP cube in Microsoft Visual Studio 2008 and publish it on SQL Server 2005 Analysis Services. You should already have SQL Server 2005 with Analysis services and Microsoft Visual Studio 2008 installed, as installing the software is not a part of this article.

Creating sample data

For this article, we need some sample data to work with. I’ve compiled a sample sales data with standard tables that you might already have. Of course, this is just a sample – OLAP cubes can be used for much more than just sales reports and sales analysis. Here’s a list of tables we’ll create in next steps:

• customers- table with all available customers
• products- table with all available products
• agents- table with all the sales agents
• sales_data- All sales performed (our fact table)

Before we start making tables, we need database. Use Microsoft SQL Server Management studio to connect to your MSSQL server and create a new database called mySalesData.

Creating customers table

I’ve created a minimum customer table for this example. Customer will have Id, Company and Country fields. Here’s the SQL code to create the table:

USE [mySalesData]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[customers](
	[id] [int] NOT NULL,
	[company] [nchar](30) NOT NULL,
	[country] [nchar](30) NOT NULL,
 CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

If everything went well, go on and add some sample data to that table. I’ve added these:

insert into customers (id,company,country) values(1,'Company A', 'USA')
insert into customers (id,company,country) values(2,'Company B', 'USA')
insert into customers (id,company,country) values(3,'Company C', 'Canada')
insert into customers (id,company,country) values(4,'Company D', 'Denmark')
insert into customers (id,company,country) values(5,'Company E', 'Egypt')
insert into customers (id,company,country) values(6,'Company F', 'France')

As you can see, I’ve created 6 different customers from 5 different countries. For simplicity, customers are called Company A-F.

Creating products table

Another simple table for products data. Products table will have 4 fields: Id, Code, Category and Name. Here’s my SQL create code:

USE [mySalesData]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[products](
	[id] [int] NOT NULL,
        code [nchar](10) NULL,
        [category] [nchar](15) NULL,
        [name] [nchar](30) NULL,
 CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
 ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now we also need to add some sample data to that table. I’ll create a simple set of computer store products.

insert into products (id, code, category, name) values(1, 'PROC01', 'Processors', 'Processor A')
insert into products (id, code, category, name) values(2, 'PROC02', 'Processors', 'Processor B')
insert into products (id, code, category, name) values(3, 'PROC03', 'Processors', 'Processor C')
insert into products (id, code, category, name) values(4, 'PROC04', 'Processors', 'Processor D')
insert into products (id, code, category, name) values(5, 'PROC05', 'Processors', 'Processor E')

insert into products (id, code, category, name) values(6, 'MEMO01', 'Memory', 'Memory 64MB')
insert into products (id, code, category, name) values(7, 'MEMO02', 'Memory', 'Memory 128MB')
insert into products (id, code, category, name) values(8, 'MEMO03', 'Memory', 'Memory 265MB')
insert into products (id, code, category, name) values(9, 'MEMO04', 'Memory', 'Memory 512MB')
insert into products (id, code, category, name) values(10, 'MEMO05', 'Memory', 'Memory 1GB')

insert into products (id, code, category, name) values(11, 'MTBD01', 'MotherBoards', 'MotherBoard A')
insert into products (id, code, category, name) values(12, 'MTBD02', 'MotherBoards', 'MotherBoard B')
insert into products (id, code, category, name) values(13, 'MTBD03', 'MotherBoards', 'MotherBoard C')
insert into products (id, code, category, name) values(14, 'MTBD04', 'MotherBoards', 'MotherBoard D')
insert into products (id, code, category, name) values(15, 'MTBD05', 'MotherBoards', 'MotherBoard E')

insert into products (id, code, category, name) values(16, 'DISK01', 'HardDisks', 'Hard disk 100G')
insert into products (id, code, category, name) values(17, 'DISK02', 'HardDisks', 'Hard disk 200G')
insert into products (id, code, category, name) values(18, 'DISK03', 'HardDisks', 'Hard disk 300G')
insert into products (id, code, category, name) values(19, 'DISK04', 'HardDisks', 'Hard disk 400G')
insert into products (id, code, category, name) values(20, 'DISK05', 'HardDisks', 'Hard disk 500G')

As you can see, we have 4 product categories. Each category has five different products inside. As a good computer store, we sell Processors, Memory, Mother boards and Hard disks. Note that price is not included in this table. The price is probably stored in your billing/accounting software and it might change from time to time. We’ll add price to fact table later on.

Creating agents table

Agents table is basically a list of sales agents that sell our products. By defining agents, we’ll get a very useful dimension in OLAP with which we can measure a performance of a sales person. Our table is simple and only holds two columns: Id and name. SQL create code looks like this:

USE [mySalesData]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[agents](
	[id] [int] NOT NULL,
	[name] [nchar](30) NOT NULL,
 CONSTRAINT [PK_agents] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Let’s add some sales guys:

insert into agents(id, name) values(1, 'Jordan Knight')
insert into agents(id, name) values(2, 'Jonathan Knight')
insert into agents(id, name) values(3, 'Joey McIntyre')
insert into agents(id, name) values(4, 'Donnie Wahlberg')
insert into agents(id, name) values(5, 'Danny Wood')

You probably don’t know these guys – if you do, I’m sorry 🙂 anyway, let’s go on …

Creating sales data table – our fact table

Sales data is a fact table, holding real sales data information with associated information like agent, product and customer to which the sale was made. I’ve tried to narrow this table down as low as possible. Normal sales data would have way more information available (which is great for analysis). Here’s a brief description of fields in the table:

• t- Date and time of sale
• bill_number- Unique bill number. Many products can be bought with one bill.
• customer_id- Customer to which the sale was made
• agent_id- Agent who sold the product
• product_id- Product that was sold
• quantity- Number of products sold
• price- Total price (product_price * quantity)

Following is the SQL code used to create the data table:

USE [mySalesData]
GO

/****** Object:  Table [dbo].[sales_data] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[sales_data](
	[t] [datetime] NOT NULL,
	[bill_number] [nchar](10) NOT NULL,
	[customer_id] [int] NOT NULL,
	[agent_id] [int] NOT NULL,
	[product_id] [int] NOT NULL,
	[quanity] [int] NOT NULL,
	[price] [float] NOT NULL
) ON [PRIMARY]

GO

Now we need to create a sample sale transactions. Here’s a short example sales data to insert:

/**
 * Customer A from USA bought 5 items with bill number 0001,
 * sold by Jordan Knight on 1st of January 2010:
 * - Processor A, Memory 64MB, MotherBoard A and 2x HardDisk 100G
 * - Total amount: 270EUR
 */
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
 values('2010-01-01 00:00:00', '0001', 1, 1, 1, 1, 50)
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
 values('2010-01-01 00:00:00', '0001', 1, 1, 6, 1, 20)
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
 values('2010-01-01 00:00:00', '0001', 1, 1, 11, 1, 100)
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
 values('2010-01-01 00:00:00', '0001', 1, 1, 16, 1, 50)
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
 values('2010-01-01 00:00:00', '0001', 1, 1, 16, 1, 50)
/**
 * Customer B from USA bought 2 items with bill number 0002,
 * sold by Jordan Knight on 1st of February 2010:
 * - HardDisk 300G, Memory 128MB
 * - Total amount: 110EUR
 */
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
values('2010-02-01 00:00:00', '0002', 2, 1, 18, 1, 80)
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
values('2010-02-01 00:00:00', '0002', 2, 1, 7, 1, 30)
/**
 * Customer D from Denmark bought 5 items with bill number 0003,
 * sold by Donnie Wahlberg on 1st of March 2010
 * - Processor E, Memory 1GB, MotherBoard E, Disk 500GB
 * - Total amount: 650EUR
 */
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
 values('2010-03-01 00:00:00', '0003', 4, 4, 5, 1, 100)
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
 values('2010-03-01 00:00:00', '0003', 4, 4, 10, 1, 200)
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
 values('2010-03-01 00:00:00', '0003', 4, 4, 15, 1, 200)
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
 values('2010-03-01 00:00:00', '0003', 4, 4, 20, 1, 150)
/**
 * Customer F from France bought 3 items with billing number 0004,
 * sold by Danny Wood on 15th of Februrary, 2010
 * - Processor C, Memory 512GB, MotherBoard E
 * - Total amount: 430EUR
 */
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
 values('2010-02-15 00:00:00', '0004', 6, 5, 3, 1, 80)
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
 values('2010-02-15 00:00:00', '0004', 6, 5, 9, 1, 150)
insert into sales_data(t, bill_number, customer_id, agent_id, product_id, quanity, price)
 values('2010-02-15 00:00:00', '0004', 6, 5, 16, 1, 200)

Sample tables and sample data inserted. We have created a simple computer store with 4 product categories, 20 products, 5 sales persons and 4 sales transactions. In next step, we’ll take a look at how we create OLAP cubes.

Creating Analysis Services Project

OLAP cube and all of it’s properties are defined in Visual Studio 2008. We have to create a correct project in Visual Studio. To do that, go to File->New->Project… . New window will popup, where you need to choose the template Analysis Services Project and name your project. If you have followed this tutorial so far, name your project: mySalesOLAP.

Once you setup everything and click OK, the new project will open up. Now we’ll use Solution Explorer to manage our project’s properties.

Defining data source

Every project must have at least one data source. Data source, as name implies, defines the source of data for cube(s). Since our data is available in Microsoft SQL server, we’ll use Native OLE DBSQL Server Native Client to access our data in sample tables we’ve created before.

Go to Data Sources in Solution Explorer, right-click on Data Sources and choose New Data Source…. New window will open “Welcome to the Data Source Wizard” and you should simply click Next. On next window click New…, to define new Data source. Connection Manager window will open and you need to specify SQL Server name and your database name –  in my case, server name is OLAP and database name is mySalesData. Click OK to return to previous window.

Make sure your new data source is selected in Data connections and click Next. On next window, it will ask you about Impersonation Information. I use Use the service account since both Microsoft SQL and Microsoft Analysis Services run under the same domain based user account. Once you define this, click Next and finish with defining Data source name as My Sales data.

Defining data source view

Data source view organizes our data sources. Dimensions and cubes are directly connected to data source view, so what ever you do here, it affects your OLAP cube(s).
Right-click on Data Source Views in projects Solution Explorer and click New data source view…. Again, a wizard window will open on which you simply click Next. First, it will ask you which data source you want to use for this view. Select previously created data source named My Sales Data and click Next.
On next window unclick Create logical relationships by matching columns, since we did not use any of the provided options to name our foreign columns. Once unchecked, click Next. On next view, we should see all four tables we created in our SQL Server (agents, customers, products and sales_data). Add all tables into Included objects and click Next. The wizard sums up what we did and asks for a name of the data source view. Use My Sales Data.

Now we get a design view of our data source view. Here we define how our data is interconnected. You define this by simple drag&drop, so it’s fairly easy. I ussualy position my fact table (sales_data) in the middle and all other tables around it.
Now, simply pull customer_id column from sales_data to customers table id column. This will create a relationship between those two tables. Do same for other two tables … so, pull agent_id from sales_data to agents table id column and product_id column from sales_data to products table id column.
For our DataTime dimension needs, we’ll replace our fact table sales_data with a named query. Named query actually acts the same as table and it’s quite a powerful tool. Simply right-click on sales_data table and click Replace Table->With New Named Query. This will open a new window, where you’ll see the complete query on our table. Simply replace this query with a new one:

SELECT t, YEAR(t) AS Year, MONTH(t) AS Month, DAY(t) AS Day,
bill_number, customer_id, agent_id, product_id, quanity, price
FROM         dbo.sales_data

With this named query, we get nicer date&time output. You’ll see that when we’ll create a DateTime dimension.
Click OK and the table will be replaced. Now click on sales_data table on column t and set it as Logical Primary Key. At the end, design view of our new data source view should look like this:

If you see something like this, you’ve succeeded!

Defining cube dimensions

Dimension in cube is like a search/grouping criteria for cubes data. We group and display our data by dimensions. You’ll see that later in this article, when we’ll browse the cube. In our cube, we’ll define five dimensions:

• DateTime
• Customers
• Products
• Product categories
• Agents

Let’s go on and create those dimensions.

Creating DateTime dimension

Go to Dimensions in project’s Solution Explorer. Right-click on Dimensions and click New dimension …. Again, wizard opens, so simply click Next. Choose Use an existing table option and click Next. On Specify Source Information window, select Data source view: My Sales Data, Main table: sales_data, Key columns t and Name columns t.

Click Next and on this window, un-check all Related tables offered to you and click Next again.
On Select Dimension Attributes only Attribute Names T, Year, Month and Day should be Enabled.

Click next and on the next window, name this dimension as DateTime and click Finish!
Every dimension has it’s own Attributes and those attributes can be assigned into Hierarchies, which define how different attributes are related to each other.
Let’s create DateTime Hierarchy. Simply drag&drop Year attribute from Attributes window toHierarchy window. This will create a new Hierarchy. After that, drag&drop Month from Attributes window to Hierarchy window into new level , right under Year attribute. Do the same for Day attribute, putting it right below Month attribute in Hierarchy window.
DateTime dimension is created. NOTE: This may not be the best practice on how to create datetime dimensions in analysis services. It serves as an example, easier to explain.

Creating customers dimension

Customers dimension is very easy to define. Right-click on Dimensions in projects Solution Explorer and click New Dimension…. When new wizard window opens click Next. On Select Creation Method window, click Next (Use an existing table should be selected). On Specify Source Information step, choose Data source view: My Sales Data, Main table:customers, Key columns: id and on Name column select company. Click next.

On Select Dimension Attributes window make sure all available attributes are enabled (both Id and Country) and click next. On next step, define dimension name as Customers and click Finish.
To create Customer Hierarchy in Dimension design tool, first drag company field from Data Source View panel to Customers attributes in Attributes panel. Next, drag Company attribute from Attributes panel to Hierarchy panel in the middle – that will create a new hierarchy. To rename the hierarchy, right-click on hierarchy and choose Rename. Rename the hierarchy to Customers Hierarchy. Now also drag Country from Attributes panel to Hierarchies panel right under the Company to new level.

Don’t forget to save dimension!

Creating products dimension

We create products dimension in the same way as Customers dimension. Go to Solution Explorer, right-click on Dimensions and choose New Dimension…. On first step, click Next. On second step, make sure Use an existing table is selected and click Next. On Specify Source Information select Data source view: My Sales Data, Main table: products, Key columns: id, Name column: name. and click next. On Select Dimension Attributes make sure all attributes are enabled.

Click Next and then make sure you specify name as Products and click Finish.
To create products hierarchy, first drag&drop name column from Data Source View panel to Attributes panel under Products in Attributes panel. To create a new hierarchy, drag&drop Name attribute from Attributes panel to the middle panel called Hierarchies. Now rename the newly created hierarchy, by right-clicking on it and selecting Rename, to Products Hierarchy. Drag&drop Category attribute to the new hierarchy to new level and do the same for Code hierarchy. Click save and you’re done.

Creating agents dimension

Same method applies for the last dimension we’re going to create for this cube. On Solution Explorer right-click on Dimensions folder and choose New Dimension…. On wizard’s welcome screen click Next. On this next step, make sure Use an existing table is selected and click Next. On Specify Source Information step, make sure Data source view selected is My Sales Data, Main table: agents, Key columns: id and Name column: name. Click Next. Click Next again, and on last page, make sure the name of the dimension is set to Agents and click Finish.
To create agents hierarchy, first drag name column from Data Source View panel and drop it to Attributes panel to Agents dimension. Now drag newly created attribute Name from Attributes panel to Hierarchy panel. This will create a new dimension, which we rename by right-clicking on it and choosing Rename. Name this new hierarchy to Agents Hierarchy. Save and you’re done!

Creating, deploying, processing and browsing the cube

Creating the cube with Cube Wizard

Now that we have all sample data, data sources, data source views and dimensions defined, we can create the cube. On project’s Solution Explorer right-click on Cubes folder and click New Cube. Wizard welcome screen opens – click Next. On Select Creation Method step select Use existing tables and click Next.

For the next step, we have to select our Measure Group table. This is usually our fact table, where we have quantities and prices, discounts, etc defined. In our case, we select sales_data table. Once selected, click Next.

On step Select Measures we select columns which define our measure data. Wizard will give some suggestions, but select only Quanity and Price and click Next.

Upon clicking next, we get to Select Existing Dimensions wizard.  Here we should see all four dimensions we’ve created earlier. Make sure they are all selected and click Next.

We have reached our final step. Make sure the cube’s name is My Sales Data and you see the same as on following screen-shot in Preview. If everything is OK, click Finish.

Deploying the cube

Once the cube is created in Visual Studio 2008, we can start deploying it. By deploying, we are basically sending it to the Analysis services database. Let’s begin by selecting Build in the upper menu of Visual Studio 2008 and select Deploy mySalesOLAP. If everything is configured as it should be, there should be no problem deploying the cube. You will see the deployment process progression in the right bottom corner.

Processing the cube

To load the data from database into our Dimensions and Cube we’ve created, we need to process them. To do that, go to project’s Solution Explorer and right-click on My Sales Data.cube in Cubes folder and click Process…. The following window will open, where you can select additional options.

Without changing any options, simply click Run and Process Progress window will open. When you see Process succeeded in Status – we’re done! Cube was successfully processed and you can now browse it. Click Close. The image below shows the succeeded process.

 Browsing the cube in Microsoft Visual Studio

You can browse the cube in Microsoft Visual Studio (not very nice, but you can). On Solution Explorer find your cube in Cubes folder and right-click on it. Select Browse and Browser will open in Cube’s design view. To create a view, we’ll select Price from Measures->Sales Data and drag it to Drop Total or Details Fields Here. You’ll notice that query is executed immediately and if you used same example data as me, you should see 1460. That’s our total sale price (total sales made). Now, to make things interesting, let’s add Month property from the DateTime dimension to Row Fields and we’ll see sales by month.

Connecting to Analysis Services from Excel

The real power (not full of course) of OLAP cubes can be shown in Microsoft Excel with Pivot tables. It’s quite easy to connect to Analysis Services from Excel, provided that you have all needed software and access rights in shape. To connect to Analysis services and begin browsing the cube, open new blank workbook and in topmenu go to Data and select From Other Sources->From Analysis Services.

Data Connection Wizard will open where you specify the server name. In my case, my server name is OLAP, so I write that. I use Windows authentication, but Analysis Services also support their own authentication, which can be quite useful in some situations.

Once you click next and, if your server is accessable and username and/or password correct, you should see the cubes we created earlier. Select My Sales Data cube and click Next.

On next step, rename your newly created data source or leave it as it is and click Finish. Afterwards Excel will ask you where you want to put your OLAP data (pivot table). Existing worksheet is OK. If you click on newly created Pivot Table, Pivot Table Field List window will open where you’ll see all the dimensions and data available. To create a sales report per month in product categories do the following:
Check on Sales Data->Price, which will put it into Values panel. From fields to add to report find Products, click on More fields and check Category. It will automatically go in to Row Labels. Drag&Drop Category from Row Labels to Column Labels. Now find the DateTime dimension and click on More fields and check Month. Data in Pivot table changes as you change the pivot table field list. Report is ready and you can see sales per category per month.

And that’s it. You can now test the Excel’s pivot table and rotate data as you wish. I hope this tutorial helped you in anyway to get a better insight into business intelligence and data analysis. If you have any suggestions and questions, don’t hesitate to leave a comment.

Tags: Analysis, BI, browse, create, cube, data, deploy, Excel, Microsoft, OLAP, pivot, process, services, table, Visual Studio

This entry was posted on June 15th, 2011 and is filed under Microsoft. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or Trackback from your own site.