Creating Backup, Restore, attach and detach cube in SSAS

In general term, a backup, or the process of backing up, refers to the copying and archiving of computer data so it may be used to restore the original after a data loss event.

Backups have two distinct purposes.

Ø  The primary purpose is to recover data after its loss, be it by data deletion or corruption.  Data loss can be a common experience of computer users.

Ø  The secondary purpose of backups is to recover data from an earlier time, according to a user-defined data retention policy, typically configured within a backup application for how long copies of data are required. Though backups popularly represent a simple form of disaster recovery, and should be part of a disaster recovery plan, by themselves, backups should not alone be considered disaster recovery.

Backup is very important activities of the databases. It is also very important in cube also. In this article I am going to explain step by step to creating the cube backup.

                       

Open SQL server Management studio.

Connect with Analysis services

Explore Database. You will get the list of cube which has been deployed on this Analysis services.

Right click on the cube for which you want to create a back up.

Click on backup.

A: - Click on browse where you want to store the backup of the cube. Select the paths where you want to keep the cube back up and write the cube backup name. Click ok.

B: - There are three type of option while creating the back up of the cube.

                               

Ø  Allow  file overwrite ( if you check this option it will be overwrite if any cube back up if exist on the same location).

Ø  Apply compression ( if you check this option it will be compress the cube back up file)

Ø  Encrypt back up file (if you check this option it will protect you back up with password protection).

C: - If you have checked encrypt back up file option then it will be enabling. You need to provide password. Whenever you restore this back up you need to provide that password that time.

D: - If you want to generate the script of the back file then click on the script you will get the following script.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">   <Object>     <DatabaseID>AdvenchaeWarks</DatabaseID>   </Object>   <File>E:\Cube BackUp\BackUpCube.abf</File>   <Password>****</Password> </Backup>

E: - Click OK.

Now back up has been created successfully.

Restore the backup cube file on the Analysis services

Right click on database

Click on Restore

A: - Click on the browser button and select the cube back up file. Click ok.

B: - Write Restore database name and select the path where you want to store .db file. Click OK.

C: - select the option if you want to over write the existing database the select it .

D: - while creating back up you used password for encrypt the backup file that password you need to provide here. If you did not provide password at backup creation type then keep it blank.

E: - If you want to generate script of restoring database click on script. You will get following script.

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">   <File>E:\Cube BackUp\BackUpCube.abf</File>   <DatabaseName>TestCubeBackUp</DatabaseName>   <AllowOverwrite>true</AllowOverwrite>   <Password>****</Password>   <DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"> E:\Cube BackUp\</DbStorageLocation> </Restore>

F: - Click Ok .Now back up has been restored.

Refresh the database you will get the new database.

Detach the cube

Right click on the cube database which one you want to detach.

Provide the password and click ok.

Cube database has been detached.  Refresh the database.

                                

Attached the cube database

Right click on the database and select attach

Select the .db file

Click ok and write the password.

If you want to generate the script then click on the script

<Attach xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">   <Folder>E:\Cube BackUp\TestCubeBackUp.7.db\</Folder>   <ReadWriteMode xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100"> ReadWrite</ReadWriteMode>   <Password>****</Password> </Attach>

Click ok. Now cube database has been attached. Refresh the database you will that cube database.

                             

     

Hope this article is helping you. If you have any query  feel free to disturb me.

Translation in SSAS

A translation is a representation of the names of Analysis Services objects in a specific language. Objects include measure groups, measures, dimensions, attributes, hierarchies, KPIs, actions, and calculated members. Translations provide server support for client applications that can support multiple languages.

Step by step applying translation on cube

I have created a cube.

Go to the translation section

Click on the new Translation.

Select the language and click ok.

Here you should need the write the measure mane in Hindi (I have selected as Hindi language).

Save it and close the windows. Build the project and deploy it. Now open the cube and go to the browse.

Similar we can apply translation on attributes. But one thing we need to store that language value in table. Here I am taking example of Adventure Works database. I am using product table for demo.

Click on Dimension and select Dim Product

Click on the translation section.

Click on the new translation.

Select the language. Here I am selecting language as Arabic and click OK.

Click on that cell you will get the Attribute Data Translation screen

Select translation column

Click ok

Save and close the window.

Build the project and deploy the project.

Now open the cube and go to the browse section select the language as Arabic. Select the measure and dimensions you will get the following type of output.

Perspectives in SSAS

A perspective is a defined subset of a cube, and is used to reduce the perceived complexity of a cube to the business user. It defines viewable subsets of a data model that provide focused, business-specific, or application-specific viewpoints of the model. Perspectives are available in both Multidimensional and Tabular versions of Analysis Services. It is an excellent option to reduce the complexity of a cube. It has some similarities to SQL Server Views which gives us the ability apply abstraction over available SSAS objects (measures and dimensions, KPI and named sets) available in an OLAP or Tabular cube.  It does not require any additional storage beyond their definition and has no effect on processing times of a cube.

A perspective enables administrators to create views of a cube, helping users to focus on the most relevant data for them. A perspective contains subsets of all objects from a cube. A perspective cannot include elements that are not defined in the parent cube.

Cubes can be very complex objects for users to explore in Microsoft SQL Server Analysis Services. A single cube can represent the contents of a complete data warehouse, with multiple measure groups in a cube representing multiple fact tables, and multiple dimensions based on multiple dimension tables. Such a cube can be very complex and powerful, but daunting to users who may only need to interact with a small part of the cube in order to satisfy their business intelligence and reporting requirements.

Step by step creating first perspective

Already I have created a cube (using Adventure Works database)

Open the cube you will getting the perspective tab on the editor

Click on the perspective tab

Click on the new perspective

New Perspective has been created. Write new perspective name.

Select the Measure and dimension according to your business.

Save that and close it.

Build the project after that deploy the project.

Click on the cube and go the browser tab.

Select the perspective which is created.

You will see the measure and dimension

Now you can generate the report according to your requirement.

Rebuild Index Task

The Rebuild Index task rebuilds indexes in SQL Server database tables and views. By using the Rebuild Index task, a package can rebuild indexes in a single database or multiple databases. If the task rebuilds only the indexes in a single database, you can choose the views and tables whose indexes the task rebuilds.

This task encapsulates an ALTER INDEX REBUILD statement with the following index rebuild options:

Ø  Specify a FILLFACTOR percentage or use the original FILLFACTOR amount.

Ø  Set PAD_INDEX = ON to allocate the free space specified by FILLFACTOR to the intermediate-level pages of the index.

Ø  Set SORT_IN_TEMPDB = ON to store the intermediate sort result used to rebuild the index in tempdb. When the intermediate sort result is set to OFF, the result is stored in the same database as the index.

Ø  Set IGNORE_DUP_KEY = ON to allow a multi row insert operation that includes records that violate unique constraints to insert the records that do not violate the unique constraints.

Ø  Set ONLINE = ON to not hold table locks so that queries or updates to the underlying table can proceed during re-indexing.

When and how often should you Rebuild Indexes?

The performance of your indexes, and therefore your database queries, will degrade as you indexes become fragmented. The Rebuild Index task does a very good job of rebuilding indexes to remove logical fragmentation and empty space, and updating statistics. As such, it is very important that you schedule this task to run regularly.

Ø  Nightly, if required. If your indexes fragment rapidly, and you have a nightly maintenance window that allows you to run the Rebuild Index task, along with all the other maintenance tasks; then do so. Index fragmentation will degrade the performance of your indexes. Assuming that you have a maintenance window, rebuilding every night can't do any harm, and can very well boost the performance of your server.

Ø  Weekly, at minimum. If you can't perform this task nightly, then, at a minimum, it should be run once a week, during a maintenance window. If you wait much longer than a week, you risk hurting your SQL Server's performance due to the negative impact of wasted empty space and logical fragmentation.

Ø  Consider alternatives, otherwise. If you don't have a maintenance window long enough to run this task at least once a week, then you need to consider the following alternatives:

Ø  Use the online version of the Rebuild Index task – available only with the Enterprise Edition of SQL Server.

Ø  Use the Reorganize Index task followed by the Update Statistics task – if you're using the Standard Edition of SQL Server. This is your only real alternative when using the Maintenance Plan Wizard if want to avoid the Rebuide Index task.

Ø  Avoid the Maintenance Plan Wizard – T-SQL or Power Shell scripts offer greater control and flexibility over the exact nature and duration of this task.

Step by step creating Rebuild Index Task

       Take Rebuild Index task

Double click on Rebuild Index task.

Create new connection and write the connection name, server name and select windows authentication.

Click OK.

Select the database.

Click OK.

Select Object Type.

View T-sql

Click OK.

Now execute the package.

Package executed successfully.