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’
https://api.kontext.tech/resource/45d23ec9-5af3-5f01-aca3-9004aa237d77
2) Create data source ‘HiSqlServer-Sample’
Connect to database using SQL Server authentication.
https://api.kontext.tech/resource/3e6e04d9-be8d-579f-861b-ceebb9dd22e0
Use the credentials of the current user for Analysis Services to connect to the data source
https://api.kontext.tech/resource/f07d7d96-a833-52e2-a652-3860b7f5ce27
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.
https://api.kontext.tech/resource/9abf72bd-c8b6-5cbf-b6ae-9e7f40881d25
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:
https://api.kontext.tech/resource/0daba586-1e3f-5bea-a743-8513792cc7eb
Select Dimension Attributes:
https://api.kontext.tech/resource/86323888-9173-5474-8d10-1abd6c007c4a
Completing the Wizard:
https://api.kontext.tech/resource/3011692b-b7f8-5e55-890a-48cd3dc06c7f
The dimension now looks like the following screenshot:
https://api.kontext.tech/resource/4ebf72c4-cb71-5643-b7f8-e15ada2a5e9d
5) Create dimension DimProduct
Use same way to create another dimension Products.
https://api.kontext.tech/resource/d15314ee-0b35-5692-9037-2e10827a5bc1
https://api.kontext.tech/resource/00fb5416-4e5f-54a1-a9bf-eb5fc167d0cd
Once the dimension is created, create a hierarchy named ‘Product Category’ using attributes Product Name and Color.
https://api.kontext.tech/resource/cd294b63-4b59-592c-b0a2-646e455ff1c6
6) Create a cube SalesCube
Create the cube by using Cube Wizard.
Select Creation Method: Use an existing table.
Select Measure Group Tables:
https://api.kontext.tech/resource/2534006a-2fa0-52dc-8579-779a5dcacb2a
Select Measures:
https://api.kontext.tech/resource/fa0eb9a2-99a2-54ad-a699-65c93cc02de2
Select Existing Dimensions:
https://api.kontext.tech/resource/b77f49da-426f-5423-b840-10591b2f8d83
Input cube name as ‘SalesCube’ and save the dialogue.
https://api.kontext.tech/resource/7800af4d-0b0e-540d-94a2-dc8ef83dd0e0
7) Deploy the project
Use the deployment function in Visual Studio to deploy the project.
https://api.kontext.tech/resource/ab329f66-781a-516d-afad-3dd0e0d9c4d3
Once it is deployed, you can also query the multidimensional database through SSMS.
https://api.kontext.tech/resource/b5e62d32-922e-538e-b1f7-845bd4478da5
8) Browse the cube
In Visual Studio or SSMS, you can generate all kinds of reports by simply dragging measures and dimension attributes.
https://api.kontext.tech/resource/493f56a2-96c7-5de0-a56e-cb870295a20a
9) OLAP through Excel
Open Excel and create data source by using ‘From Analysis Services’.
https://api.kontext.tech/resource/b1c4ae42-8eb9-58ea-ad34-d780d36b6af1
Input the credential:
https://api.kontext.tech/resource/38cf1066-23a9-59f0-b6ca-479b75bfdb09
Choose the database and then cube.
https://api.kontext.tech/resource/22aa372e-f571-541e-ac51-98b66ae5678b
Save the connection file and finish.
https://api.kontext.tech/resource/384648c5-a34a-5815-a6db-b91533cf1b66
Import the data using PivotTable Report.
https://api.kontext.tech/resource/b142f9b6-02b9-50eb-b6c2-e70f7ede4a90
Now, you are able to do the data analysis through Excel very easily.
