http://www.blrf.net/blog/74/microsoft/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.
Visual Studio 2008: New project: Analysis services project
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.
Visual studio 2008: Project solution explorer
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.
Visual studio 2008: Connection Manager: Creating new connection
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.
Visual Studio 2008: Creating new data source with wizard
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:
Visual Studio 2008: Data source view: Design view
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.
Visual Studio 2008: Dimension Wizard: Create DateTime dimension
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.
Visual Studio 2008: Dimension Wizard: Select dimension attributes
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 to
Hierarchy 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.
Visual Studio 2008: Dimension Wizard: Specify Source Information for Customers dimension
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.
Visual Studio 2008: Dimension structure view for new Customers dimension
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.
Visual Studio 2008: Dimension wizard: Select dimension attributes for Products dimension
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.
Visual Studio 2008: Cube Wizard: Select Creation Method
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.
Visual Studio 2008: Select measure group tables
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.
Visual Studio 2008: Cube Wizard: Select measures
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.
Visual Studio 2008: Cube Wizard: Select existing dimensions
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.
Visual Studio 2008: Cube wizard: Completing the wizard
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.
Visual Studio 2008: Process cube
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.
Visual Studio 2008: Process progress
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.
Visual Studio 2008: Cube browsing
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.
Microsoft Excel: Data menu
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.
Microsoft Excel: Data connection wizard
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.
Microsoft Excel: Data connection wizard: Select database and table
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.
Microsoft Excel: Pivot table with OLAP data from analysis services
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.
