SharePoint development Training

Importing SQL Server Analysis Services (SSAS) Sample CUBE and Processing the CUBEs

sql server analysis services cube backup

Objective:
In my previous article I have explained how to install and configure the SQL Server Analysis Services (SSAS). It is time to extend our knowledge and environment by implementing and processing data.

Again I would like to emphasize more for our SharePoint techies as it is a primary and important step to configure SharePoint Business Intelligence (BI) capably in your DEV / PROD environment.


What is CUBE?
In SQL Server we can store data in multiple databases and tables, when user need a report from various tables created in separate Databases SQL architect propose the powerful option called CUBE.

A cube is a multidimensional structure that contains information for analytical purposes; the main constituents of a cube are dimensions and measures. Dimensions define the structure of the cube that you use to slice and dice over, and measures provide aggregated numerical values of interest to the end user.

For more in detail on cubes:
https://technet.microsoft.com/en-us/library/cc952924(v=sql.100).aspx

In this article I would like to refer the standard and free Database implemented by CODEPLEX team.
This Database is a focal point for most of the developers for their initial development.

Let me layout the approach and steps for consuming the sample Database and executing cubes.

1. Ensure that SSAS is configured properly with the help of my previous article
Configuring SSAS in SQL Server – 2012

2. Once it is configured download the sample report by clicking on the below link
Download AdventureWorksDW2012.bak

3. Download the c# code to process the cubes
Download C#.Net Solution File

4. Execute the below commands in SQL Management studio to extract the .mdf and .ldf file from .Bak file.
RESTORE DATABASE AdventureWorksDW2012
FROM DISK = ‘S:\PS Scripts\AdventureWorksDW2012.bak’
WITH MOVE ‘AdventureWorksDW2012’ TO ‘S:\Program Files\Microsoft SQL Server\MSSQL11.MSTECHSQL12\MSSQL\DATA\AdventureWorksDW2012.mdf’,
MOVE ‘AdventureWorksDW2012_log’ TO ‘S:\Program Files\Microsoft SQL Server\MSSQL11.MSTECHSQL12\MSSQL\DATA\AdventureWorksDW2012_log.ldf’
Note: Change the paths as per your local instance.


sql cube
sql cube

5. Once it is extracted attach the Database
a. Open SQL Management studio
b. Connect to SQL Database Engine
c. Connect to Database Engine Server
d. Ensure the right account to server
e. Click on Connect button

sql cube example
sql cube example

6. In Step 4 we extracted database now we need to attach the same in Analysis Server. Right click on Database option and click on “Attach”.

sql cube tutorial
sql cube tutorial
sql cube rollback
sql cube rollback

7. Double click on the .sln file which we have downloaded as part of Step-3 and select Business Intelligence option.

sql cube data
sql cube data

8. Double click on “Adventure Works DW” in visual studio solution
a. Double click on “Adventure Works DW.ds”
b. Click on Edit on button
c. Ensure the server name
d. Double check the DB Name
e. Click on “OK” for connection manager
f. Click on “OK” on data source designer popup also

sql cube by
sql cube by

9. Process the solution to create the cubes

What is the cube in SQL
What is the cube in SQL

10. Click on “Yes” button in the below popup

What is the data cube
What is the data cube

11. Click on “Run” to process the cubes

sql server analysis services cube
sql server analysis services cube

12. Once the cube is processed properly it will show as below


sql server analysis services cube examples
sql server analysis services cube examples

13. You can test cube quickly by following the below set of screens

sql server analysis services cube backup
sql server analysis services cube backup
sql server analysis services cube design
sql server analysis services cube design

Select Measures->Sales Summary->drag and drop Sales Amount on to right
Select product in the list down and drag and drop the product & Category on to right hand side.
It will look like as below:

sql server analysis services cube performance
sql server analysis services cube performance

With this we confirm that cubes got created successfully!!!
Hope this helps ….

Check out Best Alternative to InfoPath -> Try Now

free sharepoint training

SharePoint Online FREE Training

JOIN a FREE SharePoint Video Course (3 Part Video Series)

envelope
envelope

About Krishna Vandanapu

I am Krishna.Vandanapu a SharePoint architect working in IT from last 13+ years, I worked in SharePoint 2007, 2010, 2013, 2016 and Office 365. I have extensive hands on experience in customizing SharePoint sites from end to end. Expertise in SharePoint migration tools like Sharegate, Doc Ave and Metalogix. Migrated SharePoint sites from SharePoint 2007 to 2010 and 2010 to 2013 several times seamlessly. Implementing CSOM with Microsoft best practices. Spent quality time in configuring SharePoint application services like User Profile, Search, Managed Meta data services etc. Now exploring SharePoint Framework and SharePoint 2019

View all posts by Krishna Vandanapu →