Connecting cube to excel step by step

Open the excel file

Click on the data section and select from other source. You will get different data source. Select From Analysis service.

Click on that

Write the server Name and select the windows Authentication.

Click on next button.

Select the cube which one you want export in excel.

Click next

Click Finish

Click ok.

You will get the pivot Excel

You will get the desirer reports

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.

Storage Mode in cube SSAS 2008

There are following storage modes in OLAP applications which affect the performance of OLAP queries and cube processing, storage requirements and also determine storage locations.

Ø  MOLAP

Ø  Scheduled MOLAP

Ø  Automatic MOLAP

Ø  Medium-latency MOLAP

Ø  Low-latency MOLAP

Ø  Real time HOLAP

Ø  Real time ROLAP

MOLAP Storage Mode

This is the default and most frequently used storage mode. In this mode when you process the cube, the source data is pulled from the relational store, the required aggregation is then performed and finally the data is stored in the Analysis Services server in a compressed and optimized multidimensional format.

After processing, once the data from the underlying relational database is retrieved there exists no connection to the relational data stores. So if there is any subsequent changes in the relational data after processing that will not reflect in the cube unless the cube is reprocessed and hence it is called offline data-set mode.

Advantage

Ø  Stores the detail and aggregate data in the OLAP server in a compressed multidimensional format; as a result the cube browsing is fastest in this mode.

Ø  Provides maximum query performance, because all the required data (a copy of the detail data and calculated aggregate data) are stored in the OLAP server itself and there is no need to refer to the underlying relational database.

Ø  All the calculations are pre-generated when the cube is processed and stored locally on the OLAP server hence even the complex calculations, as a part the query result, will be performed quickly.

Ø  MOLAP does not need to have a permanent connection to the underlying relational database (only at the time of processing) as it stores the detail and aggregate data in the OLAP server so the data can be viewed even when there is connection to the relational database.

Ø  MOLAP uses compression to store the data on the OLAP server and so has less storage requirements than relational databases for same amount of data. (Note however, that beginning with SQL Server 2008 you can use data compression at relational database level as well).

Disadvantage

Ø  With MOLAP mode, you need frequent processing to pull refreshed data after last processing resulting in drain on system resources.

Ø   Latency just after the processing if there is any changes in the relational database it will not be reflected on the OLAP server unless   re-processing is performed.

Ø  MOLAP stores a copy of the relational data at OLAP server and so requires additional investment for storage.

Ø  If the data volume is high, the cube processing can take longer, though you can use incremental processing to overcome this

Scheduled MOLAP

Ø  Measure group data and aggregations are stored in multidimensional format.

Ø  Notifications are not received when data changes.

Ø  Processing is automatically performed every 24 hours.

Automatic MOLAP

Ø  Measure group data and aggregations are stored in multidimensional format.

Ø  The server will listen for notifications when data changes.

Ø  Processing is performed automatically with no restriction on latency.

Medium-latency MOLAP

Ø  Measure group data and aggregations are stored in multidimensional format.

Ø  The server will listen for notifications when data changes.

Ø  Processing is performed automatically with a target latency of four hours.

Low-latency MOLAP

Ø  Measure group data and aggregations are stored in multidimensional format.

Ø  The server will listen for notifications when data changes.

Ø  Processing is performed automatically with a target latency of 30 minutes.

Real-time HOLAP

Ø  Measure group data is maintained in a relational format, and aggregations are stored in multidimensional format.

Ø  The server will listen for notifications when data changes.

Ø  All Queries reflect the current state of data.

Real-time ROLAP

In comparison with MOLAP, ROLAP does not pull data from the underlying relational database source to the OLAP server but rather both cube detail data and aggregation stay at relational database source. In order to store the calculated aggregation the database server creates additional database objects (indexed views). In other words, the ROLAP mode does not copy the detail data to the OLAP server, and when a query result cannot be obtained from the query cache the created indexed views are accessed to provide the results.

Advantage

Ø   Ability to view the data in near real-time.

Ø   Since ROLAP does not make another copy of data as in case of MOLAP, it has less storage requirements. This is very advantageous for large datasets which are queried infrequently such as historical data.

Ø   In ROLAP mode, the detail data is stored on the underlying relational database, so there is no limitation on data size that ROLAP can support or limited by the data size of relational database. In nutshell, it can even handle huge volumes of data.

Disadvantage

Ø  Compared to MOLAP or HOLAP the query response is generally slower because everything is stored on relational database and not locally on the OLAP server.

Ø   A permanent connection to the underlying database must be maintained to view the cube data.

Different Parameters and their explanation

Cache Settings:

1. Silence Interval

2. Silence Override Interval

3. Latency

4. Rebuild Interval 

Options:

1. Bring Online Immediately

2. Enable ROLAP Aggregation

3. Apply Settings to Dimension

For example, consider different parameter settings of Low Latency MOLAP option mentioned in below screen shot:

Silence Interval: For "Low Latency MOLAP", Silence Interval has been set 10 seconds. Whenever relational database change happens and notification comes to Analysis Server. Silence Interval start its stopwatch and if no other Database changes comes before 10 seconds then it will start reprocessing cube with new changes. If new database changes comes <10 seconds then Silence interval Stopwatch will be reset to zero so if frequent changes are coming then analysis services will wait till all the changes complete.
Silence Override Interval: As soon as first database change comes after last reprocessing of cube then analysis services start one more stopwatch "Silence Override Interval". It basically overrides "Silence Interval" for reprocessing cube. For the same example, if frequent database changes are happening and silence interval is getting reset each time then SSAS will forcibly process cube after it passes "Silence Override interval" so in this example, cube will be processed after 10 minutes.
Latency: Old MOLAP cache will be dropped after time interval specified in Latency. Latency ensures the time interval after which data will not be old. If new cache is not available after Latency period then queries will be addressed by relational database and user will see significant performance drop in query response time.

Update the cache frequently(Rebuild Interval): MOLAP cache will be rebuild after specified "Rebuild interval" irrespective of database changes. It means if database changes doesn't occur then also MOLAP cache will be rebuild.

Bring Online Immediately: If you select this option then queries will be addressed by relational DB when new cache is not up and old cache has been dropped.

Enable ROLAP Aggregation: If you select this option then Aggregation objects will be created in relational database.
a) Indexed Views in SQL Server 2005 and SQL Server 2008
b) Materialized Views in Oracle

 Apply Settings to Dimension: If you select this option then same cache settings will be applied to all related dimensions from partition. This will not be available for HOLAP standard setting.

Here is the chart which compares different Standard Storage Settings in SSAS. You can customize and create your own settings by clicking on "Custom Settings" and then specifying different parameters.

	Storage Mode	Enable Proactive Caching	Silence Interval	Silence Override Interval	Latency	Rebuild Interval	Bring Online Immediately
MOLAP	MOLAP	No
Scheduled MOLAP	MOLAP	Yes				1 Day
Automatic MOLAP	MOLAP	Yes	10 Sec	10 Min
Medium Latency MOLAP	MOLAP	Yes	10 Sec	10 Min	4 hours		Selected
Low Latency MOLAP	MOLAP	Yes	10 Sec	10 Min	30 Min		Selected
Real Time HOLAP	HOLAP	Yes	0 Sec		0 Sec		Selected
Real Time ROLAP	ROLAP	Yes	0 Sec				Selected