It is not always necessary to be connected with OLAP cube to create reports in excel. There can be situations when you need to analyze the data when you are not connected to your network or you are out of office.
In those cases you can create an offline cube file (with .cub extension) which can store the measures and dimensions of the OLAP source and that file will be stored on your local computer hard drive.You can choose limited number of measures and dimensions in the offline cube file to save the local computer’s hard drive space.
Following are the steps which will elaborate how to create offline cube file with SSAS OLAP cube as a source.
1) You need to first create or open a PivotTable or PivotChart report which is connected to SSAS cube. To know more how to create a PivotTable using SSAS source you can go to https://ellarr.com/business-intelligence/self-service-reporting-in-excel-from-sql-server-analysis-services-ssas-using-pivot-table/
2) In PivotTable Tools section ,under Options click OLAP Tools dropdown and then Offline OLAP as shown in the image below.
3) Click on create offline data file button as shown in the image below.
4) Create Cube File wizard will open as shown in the image below which will create an offline cube file (.cub) which will store data of SSAS OLAP cube. You need to click next to go to next step.
5) Choose the dimension attributes that you want to store in .cub file as shown in the image below.
6) You can choose the members (or values) of the dimension attributes which you have chosen in the previous step as shown in the image below and also you can choose desired measures in the same step. It means that .cub file doesn’t have to store all the measures and dimensions of the OLAP source. By choosing limited number of facts and dimensions you can save the computer hard drive space.
7) In the next step browse to the hard disk location where you need to create the .cub file.
8) After that you can create a pivot report from the offline cube file as shown in the image below. Save the workbook for future use.
9) You can refresh the offline cube by clicking on the Refresh button as shown in the image below.
I am extending my discussion and write about how to convert an offline cube back to online cube.
- Again in the PivotTable Tools section ,under Options click OLAP Tools dropdown and then Offline OLAP as shown in the image below.
- Choose the option On-Line OLAP as shown in the image below.
Also you can modify offline cube file .For that in the PivotTable Tools section, under Options click OLAP Tools dropdown and then Offline OLAP. Then offline OLAP Settings window will appear click Edit Offline data file button to modify the offline cube as shown in the image below.