access_time 6 years ago visibility1165 comment 0 languageEnglish
more_vert

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

info Last modified by Raymond at 6 years ago * This page is subject to Site terms.

More from Kontext

visibility 14052
thumb_up 0
access_time 6 years ago

SQL Server Compact 4.0 (CE 4.0) is a free SQL Server embedded database ideal for building standalone and occasionally connected applications for mobile devices, desktops, Web clients and others. In one of my projects, I used it as the database for logging errors, which assumes the errors will onl...

open_in_new View open_in_new SQL Server

visibility 688
thumb_up 0
access_time 6 years ago

SQL Server provides a batch of great features to build robust, high-performance and scalable data solutions. ...

open_in_new View open_in_new SQL Server

visibility 4339
thumb_up 1
access_time 6 years ago

Serial: An Introduction to SQL Server Features Case Scenario For this ETL project, the requirements are listed below: Sales data will be pushed to specified shared fold...

open_in_new View open_in_new SQL Server

Create Business Reports using SSRS

local_offer plot

visibility 877
thumb_up 0
access_time 6 years ago

Serial: An Introduction to SQL Server Features Case Scenario After the data is loaded into the database, reports can be built using SSRS (SQL Server Reporting Service). The g...

open_in_new View open_in_new SQL Server

info About author

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.


Learn more arrow_forward