This post has already been read 226 times!

Objective: To setup sample databases that we would use.

We would be exploring MDX on SQL Server Analysis Service platform. For queering we would use AdventureWorks Analysis Service database, therefore we will start by setting up AdventureWorks sample database. There are two ways to setup the AdventureWorks Cube.

  1. Restoring from an existing backup
  2. Deploying from Sql Server Analysis Service project

Restoring from an existing backup

To restore from an existing cube backup, you first have to download the backup(.abf) file. Either you can download it from https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks-analysis-services  or you can download it from the downloads section. Once you have the .abf file, you need to copy it in the Analysis Services backup directory.

Typically this directory would be within OLAP folder, which in turn would be within the Analysis Service folder, which inturn would be within the Sql Server installation folder. So for my system it looks like
“d:\Program Files\Microsoft SQL Server14\MSAS12.MSSQL14\OLAP\Backup” where “Microsoft SQL Server14” is my Sql Server installation folder,
“MSAS12.MSSQL14” is my Analysis Service subfolder

You can also check your backup directory in the management studio. Simply login to your Analysis Service instance, in the Object Explorer right click your Analysis Service instance, from the drop down click properties. On the Left hand panel, click General. This will show your Analysis Services current settings. You need to look for “BackupDir“.

Once you have copied the backup .abf file in your backup folder, go to Object Explorer, right click Databases, from the drop down click Restore. This will open the Restore Database page, in front of Backup file click Browse. Now expand your backup folder and select the backup file.In the Restore database drop down either you can give it a name or you can leave it blank Incase if you leave it blank, it will restore with its defaulf name. The Adventure Works backup has no password, so leave the Password blank and Click ok. Now refresh your Databases, in the Object Explorer. You should be able to see the backup restored

Typically this directory would be within OLAP folder, which in turn would be within the Analysis Service folder, which inturn would be within the Sql Server installation folder. So for my system it looks like
“d:\Program Files\Microsoft SQL Server14\MSAS12.MSSQL14\OLAP\Backup” where “Microsoft SQL Server14” is my Sql Server installation folder,
“MSAS12.MSSQL14” is my Analysis Service subfolder

You can also check your backup directory in the management studio. Simply login to your Analysis Service instance, in the Object Explorer right click your Analysis Service instance, from the drop down click properties. On the Left hand panel, click General. This will show your Analysis Services current settings. You need to look for “BackupDir“.

Once you have copied the backup .abf file in your backup folder, go to Object Explorer, right click Databases, from the drop down click Restore. This will open the Restore Database page, in front of Backup file click Browse. Now expand your backup folder and select the backup file.In the Restore database drop down either you can give it a name or you can leave it blank Incase if you leave it blank, it will restore with its defaulf name. The Adventure Works backup has no password, so leave the Password blank and Click ok. Now refresh your Databases, in the Object Explorer. You should be able to see the backup restored

Deploying from Sql Server Analysis Service project

To deploy a cube from an Analysis Service project, you need

  1. Database containing the dimensional model(star schema) for cube
  2. The Analysis Service project for the cube

For AdventureWorksDW, the database containing the dimensional model can be downloaded from either github https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW-data-warehouse-install-script.zip or you can download it from the downloads section . Once you have the install-script, unzip the file and then follow the instructions in instawdb.sql. Once the database is created, next comes the Analysis Service Project.

The Adventure works Analysis Services project can be downloaded from github https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks-analysis-services/adventure-works-multidimensional-model-project.zip or you can download it from the downloads section .

Once you have the project based on your Sql Server Analysis Server edition, open the relevent project. Go to the Solution Explorer. Under Data Sources, open the AdventureWorksDW data source. This will open Data Source Designer, Click Edit, this will open the Connection Manager. In the Server name, provide the name of the databse server where you created your AdventureWorksDW, now select your AdventureWorksDW database in the Select or enter a database name drop down, now click Test Connection, then click OK.
Now back in the Data Source Designer, select the Impersonation Information tab and provide the User Name and Password of the windows user that is Analysis Service administrator. Incase if you have logged in with the same user you can select Use the service account option. Next in Solution Explorer right click the project and click Properties. Now Click Deployment on your lefthand side. Now provide your Analysis Service server name in Server and the name of your Analysis Service database in Database. I would recommd you to leave it as it is, but make sure you dont have any database on your Analysis Service server with this name. Now back in the Solution Explorer, right click the project and click Process. It will prompt you to build the project first, click Yes, next click Run. Your cube has started processing and will take a few minutes.

code source of article : http://www.learningmdx.com/setting-up/

Leave a Reply

Post Navigation