Migrating SSAS Multidimensional cube between Azure VM

Author- Shruti Srivastava (Data Engineer)

In this article, it is explained how to build a SSAS Multidimensional cube, deploy it in one Azure VM  and then copy and restore it into a second Azure VM. For this purpose, we have to create two Azure Virtual machines named shrutiVM main(in which SSAS cube is deployed) and shrutiVM(in which SSAS cube is restored). Also two analysis services SHRUTIAS for VM shrutiVMmain and SHRUTIGS for VM shrutiVM.

Prerequisites

1. SSMS.
2. Install SQL Server and Analysis service on both the Azure VM.
3. Install SSDT on your main server where you will deploy your cube.
4. Visual Studio.
5. Download AdventureWorksDW2012 as a sample database.
6. Server Admin user for both Azure VMs.

Connect SQL Database and Analysis service and restore sample database

Connect the Database Engine and Analysis service in SSMS for shrutiVMmain.

 

 Restore a database named AdventureWorksDW2012.

 

 

Steps to make a SSAS Multidimensional cube:

      A: Creating an Analysis Services Project:

1. Open SQL Server Data Tools.
2. Create a new Analysis Services Multidimensional project. Choose the Analysis Services  Multidimensional and Data Mining Project template.
3. Change the project name to Multidimensional SSAS project which also changes the Solution name box, and then click OK.
   

   B: Defining a Data Source:

1. In Solution Explorer (on the right of the Microsoft Visual Studio window), right-click Data Sources, and then click New Data Source

2. On the Welcome to the Data Source Wizard page of the Data Source Wizard, click Next to open the Select how to define the connection page

3. Select “Create a data source based on an existing or new connection” and click New.
4. In the Provider list box, verify that Native OLE DB\SQL Server Native Client 11.0 is selected. Enter the Server name and select Windows Authentication. In the Select or enter a database name list, select AdventureWorksDW2012. Click Test Connection to test the connection to the database, click OK and then click Next.
5. Click on Use a specific Windows username and password and provide credentials. Click on Next.
6. Click on Finish.

 

7. A Data source named Adventure Works DW2012.ds will be created under the Data source section.

     C: Defining a Data Source View:

1. In Solution Explorer (on the right of the Microsoft Visual Studio window), right-click Data Sources Views, and then click New Data Source View

2. On the Welcome to the Data Source View Wizard page, click Next.

 

3. Under Relational data sources, the Adventure Works DW 2012data source is selected. Click Next.
4. On the Select Tables and Views page, select tables and views from the list of objects that are available from the selected data source. In the Available objects list, select the following objects. You can select multiple tables by clicking each while holding down the CTRL-key. Click > to add the selected tables to the Included objects

• DimCustomer (dbo)
• DimDate (dbo)
• DimGeography (dbo)
• DimProduct (dbo)
• FactInternetSales (dbo)

Click Next

5. In the Name field, make sure Adventure Works DW 2012displays, and then click Finish.

 

6. A Data source view named Adventure Works DW2012.dsv will be created under the Data source views section.

     D: Defining a Dimension:

1. In Solution Explorer (on the right side of Microsoft Visual Studio), right-click Dimensions, and then click New Dimension. The Dimension Wizard appears.

2. On the Welcome to the Dimension Wizard page, click Next.

 

3. On the Select Creation Method page, verify that the “Use an existing table” option is selected, and then click Next.

 

4. On the Specify Source Information page, verify that the Adventure Works DW 2012 data source view is selected. In the Main table list, Select Date, Click Next.

 

5. On the Select Dimension Attributes page, select the checkboxes next to the following attributes:

• Date Key
• Full Date Alternate Key
• English Month Name
• Calendar Quarter
• Calendar Year
• Calendar Semester

Change the setting of attribute’s Attribute Type column

• Full Date Alternate Key to Date
• English Month Name to Month
• Calendar Quarter to Quarter
• Calendar Year to Year
• Calendar Semester to Half Year

Click Next

 

6. On the Completing the Wizard page, in the Preview pane, you can see the Date dimension and its attributes. Click Finish.

 

7. In Solution Explorer, the Date dimension appears in the Dimensions folder. On the File menu, click Save All.

E: Defining a Cube:

1. In Solution Explorer, right-click Cubes, and then click New Cube. The Cube Wizard appears.
2. On the Welcome to the Cube Wizard page, click Next.

 

   3. On the Select Creation Method page, verify that the Use existing tables option is selected, and then click Next.

 

     4. On the Select Measure Group Tables page, verify that the Adventure Works DW 2012 data source view is selected. Click Suggest button so the cube wizard suggests tables to use to create measure groups. The wizard examines the tables and suggests InternetSales as a measure group table. Click Next.

 

5. On the Select Measures page, review the selected measures in the Internet Sales measure group, and then clear the checkboxes for the following measures:

• Promotion Key
• Currency Key
• Sales Territory Key
• Revision Number

 

By default, the wizard selects as measures all numeric columns in the fact table that are not linked to dimensions. However, these four columns are not actual measures. The first three are key values that link the fact table with dimension tables that are not used in the initial version of this cube

Click Next

 

     6. On the Select Existing Dimensions page, make sure the Date dimension that you created earlier is selected, and then click Next.

 

       7. On the Select New Dimensions page, select the new dimensions to be created. To do this, verify that the Customer, Geography, and Product checkboxes are selected, and then clear the InternetSales check box. Click Next.

 

    8. Click Finish to complete the wizard

 

9. In Solution Explorer, Adventure Works DW2012.cube appears in the Cubes folder, and the Customer and Product database dimensions appear in the Dimensions folder. On the File menu, click Save All.

 

    F: Adding Attributes to Customer Dimensions:

1. Open Dimension Designer for the Customer dimension. To do this, double-click the Customer dimension in the Dimensions node of Solution Explorer.2
2. In the Attributes pane, notice the Customer Key and Geography Key attributes that were created by the Cube Wizard.

3. Drag the following columns from the Customertable in the Data Source View pane to the Attributes pane:

• Birth Date
• marital status
• Gender
• EmailAddress
• Yearly Income
• TotalChildren
• NumberChildrenAtHome
• English education
• English occupation
• HouseOwnerFlag
• NumberCarsOwned
• Phone
• DateFirstPurchase
• CommuteDistance

4. Drag the following columns from the Geographytable in the Data Source View pane to the Attributes pane:

• City
• StateProvinceName
• EnglishCountryRegionName
• PostalCode

On the File menu, Click Save All

     G: Adding Attributes to Product Dimensions:

1. Open Dimension Designer for the Product dimension. Double-click the Product dimension in Solution Explorer.2
2. In the Attributes pane, notice the Product Key attribute that was created by the Cube Wizard.

3. Drag the following columns from the Product table in the Data Source View pane to the Attributes pane:

• StandardCost
• Color
• SafetyStockLevel
• ReorderPoint
• ListPrice
• Size
• SizeRange
• Weight
• DaysToManufacture
• ProductLine
• DealerPrice
• Class
• Style
• ModelName
• StartDate
• EndDate
• Status

On the File menu, click Save All.

      H: Deploying an Analysis Services Project:

1. In Solution Explorer, right-click the MultiDimensionalSSAS Project, and then click Properties.
2. Provide the Target server name and database name should be MultiDimensionalSSAS
3. In Solution Explorer, right-click MultiDimensionalSSAS Project and then click Deploy.
4. A MultidimensionalSSASproject will appear in the Analysis Service Database.

      I : Backup of MultidimensionalSSAS Project:

1. In SSMS of shrutiVMmain, expand Analysis Server, right-click on MultidimensionalSSAS Project under databases and click on Backup.
2. Click on Browse.

 

3. Provide selected path and click OK.

 

4. Provide Encrypt backup file password and click OK.
5. Backup MultidimensionalSSASProject.abf will be created in Backup folder of C drive.

    J: Restore of MultidimensionalSSAS Project to shrutiVM:

1. Copy the Backup file from Backup folder of shrutiVM main to Backup folder of shrutiVM .
2. In SSMS of shrutiVM, expand Analysis Server, right click on databases and select restore.
3. Click on Browse and provide the selected path and file name and click OK.
4. Provide the password and click OK.
5. MultidimensionalSSASProject will be restored in databases.

 

 

    K: Check the Functioning of Cube in shrutiVM:

1. To process the cube in shrutiVM . Expand Analysis server databases, expand Multidimensional SSAS Project. In Data Source double click on Adventure Work DW 2012 .

 

2. Click on Connection String and change the Server name.

3. Click on Impersonate account and click on Use a specific Windows username and password and provide the credentials and click OK

4. Right-click on MultidimensionalSSASProject database and click on Process.
5. CLICK OK
6. You will get the project “succeeded message”.
   

This is how you can deploy an SSAS Multidimensional Cube from one Azure VM to another.