Serial: An Introduction to SQL Server Features

Case Scenario

In the previous articles of this serial, I’ve introduced how to use SQL Server as database for online transaction processing, build ETL packages for data integration and create reports. SSAS is another important feature supporting online analytical processing.

Finally, we are going to build a simple multidimensional database to support OLAP in Excel or other tools.

Create SSAS Project

1) Create SSAS project ‘SSAS-Sample’

image

2) Create data source ‘HiSqlServer-Sample’

Connect to database using SQL Server authentication.

image

Use the credentials of the current user for Analysis Services to connect to the data source

image

Input the name as ‘HiSqlServer-Sample’ and then save.

3) Create data source view ‘SalesDSV’

Open the ‘New Data Source View’ dialogue and choose the data source we just created.

image

Name Matching: Same name as primary key.

Select Tables: Add all the three tables (Areas, Products, Sales).

Input the name as ‘SalesDSV’ and then save the dialogue.

4)Create Dimension DimArea

Create dimension Areas by using Dimension Wizard.

Select Creation Method: Use an existing table.

Specify Source Information:

image

Select Dimension Attributes:

image

Completing the Wizard:

image

The dimension now looks like the following screenshot:

image

5) Create dimension DimProduct

Use same way to create another dimension Products.

image

image

Once the dimension is created, create a hierarchy named ‘Product Category’ using attributes Product Name and Color.

image

6) Create a cube SalesCube

Create the cube by using Cube Wizard.

Select Creation Method: Use an existing table.

Select Measure Group Tables:

image

Select Measures:

image

Select Existing Dimensions:

image

Input cube name as ‘SalesCube’ and save the dialogue.

image

7) Deploy the project

Use the deployment function in Visual Studio to deploy the project.

image

Once it is deployed, you can also query the multidimensional database through SSMS.

image

8) Browse the cube

In Visual Studio or SSMS, you can generate all kinds of reports by simply dragging measures and dimension attributes.

image

9) OLAP through Excel

Open Excel and create data source by using ‘From Analysis Services’.

image

Input the credential:

image

Choose the database and then cube.

image

Save the connection file and finish.

image

Import the data using PivotTable Report.

image

Now, you are able to do the data analysis through Excel very easily.

image

About author
Comments
No comments.
Add comment
Title
Title is required.
Name
Name is required.
Email
Please input your personal email with valid format.
Comments
Please input comment content.
Captcha Refresh
Input captcha:

Subscription

Statistics

Locations of visitors to this page