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:
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
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.
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
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”.
7. Double click on the .sln file which we have downloaded as part of Step-3 and select Business Intelligence option.
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
9. Process the solution to create the cubes
10. Click on “Yes” button in the below popup
11. Click on “Run” to process the cubes
12. Once the cube is processed properly it will show as below
13. You can test cube quickly by following the below set of screens
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:
With this, we confirm that cubes got created successfully!!! Hope this helps ….
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