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’
/project/allsqlserver/resources/45D23EC9-5AF3-5F01-ACA3-9004AA237D77.webp
2) Create data source ‘HiSqlServer-Sample’
Connect to database using SQL Server authentication.
/project/allsqlserver/resources/3E6E04D9-BE8D-579F-861B-CEEBB9DD22E0.webp
Use the credentials of the current user for Analysis Services to connect to the data source
/project/allsqlserver/resources/F07D7D96-A833-52E2-A652-3860B7F5CE27.webp
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.
/project/allsqlserver/resources/9ABF72BD-C8B6-5CBF-B6AE-9E7F40881D25.webp
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:
/project/allsqlserver/resources/0DABA586-1E3F-5BEA-A743-8513792CC7EB.webp
Select Dimension Attributes:
/project/allsqlserver/resources/86323888-9173-5474-8D10-1ABD6C007C4A.webp
Completing the Wizard:
/project/allsqlserver/resources/3011692B-B7F8-5E55-890A-48CD3DC06C7F.webp
The dimension now looks like the following screenshot:
/project/allsqlserver/resources/4EBF72C4-CB71-5643-B7F8-E15ADA2A5E9D.webp
5) Create dimension DimProduct
Use same way to create another dimension Products.
/project/allsqlserver/resources/D15314EE-0B35-5692-9037-2E10827A5BC1.webp
/project/allsqlserver/resources/00FB5416-4E5F-54A1-A9BF-EB5FC167D0CD.webp
Once the dimension is created, create a hierarchy named ‘Product Category’ using attributes Product Name and Color.
/project/allsqlserver/resources/CD294B63-4B59-592C-B0A2-646E455FF1C6.webp
6) Create a cube SalesCube
Create the cube by using Cube Wizard.
Select Creation Method: Use an existing table.
Select Measure Group Tables:
/project/allsqlserver/resources/2534006A-2FA0-52DC-8579-779A5DCACB2A.webp
Select Measures:
/project/allsqlserver/resources/FA0EB9A2-99A2-54AD-A699-65C93CC02DE2.webp
Select Existing Dimensions:
/project/allsqlserver/resources/B77F49DA-426F-5423-B840-10591B2F8D83.webp
Input cube name as ‘SalesCube’ and save the dialogue.
/project/allsqlserver/resources/7800AF4D-0B0E-540D-94A2-DC8EF83DD0E0.webp
7) Deploy the project
Use the deployment function in Visual Studio to deploy the project.
/project/allsqlserver/resources/AB329F66-781A-516D-AFAD-3DD0E0D9C4D3.webp
Once it is deployed, you can also query the multidimensional database through SSMS.
/project/allsqlserver/resources/B5E62D32-922E-538E-B1F7-845BD4478DA5.webp
8) Browse the cube
In Visual Studio or SSMS, you can generate all kinds of reports by simply dragging measures and dimension attributes.
/project/allsqlserver/resources/493F56A2-96C7-5DE0-A56E-CB870295A20A.webp
9) OLAP through Excel
Open Excel and create data source by using ‘From Analysis Services’.
/project/allsqlserver/resources/B1C4AE42-8EB9-58EA-AD34-D780D36B6AF1.webp
Input the credential:
/project/allsqlserver/resources/38CF1066-23A9-59F0-B6CA-479B75BFDB09.webp
Choose the database and then cube.
/project/allsqlserver/resources/22AA372E-F571-541E-AC51-98B66AE5678B.webp
Save the connection file and finish.
/project/allsqlserver/resources/384648C5-A34A-5815-A6DB-B91533CF1B66.webp
Import the data using PivotTable Report.
/project/allsqlserver/resources/B142F9B6-02B9-50EB-B6C2-E70F7EDE4A90.webp
Now, you are able to do the data analysis through Excel very easily.
