SSAS cube: Partition process successfully but measure group is not showing any data

Some days will bring you big surprise which you are not prepare for at all!! I am going to share one of them!!

One of the cube I was working with processed successfully but there is no data in the measures which was just there before last processed.

What can go wrong??

I have only  added one attribute to the existing dimension. And then processed default, processed full, processed partition by by partition full; all different cases processed successfully but no data under the measure group. When I drag dimensions under SSAS cube browser I am able to see the value but no rows are show while adding measures.

Was google helpful?

Through google search, some suggested to check if the source database connection is set correctly, Some talked about full process, some mentioned about adding slice to process the partition correct.

Nothing really helped me.

What steps resolved the issue?

step 1: Make sure you have right database connection and passed your credential as well as used correct impersonate  correct(sometime cube don't show you the correct error message)

step 2: Process all the dimensions one after another

step 3: Since it's huge data set so process one partition and checked the estimated size property of the specific partition that you just processed.

If estimated size of the partition property is not 0 MB, it means you should have the data.

Lesson learned: if you find the cube measure is not shown any data suddenly after adding an attribute to the existing dimension then do process all the dimensions (not only the changed dimension) and then process the cube. This step is always helpful if your cube behave strange sometime without noticing you :)

MS Analysis Services:How to sync SSAS Cube?

When business users accessing a SSAS cube by connecting via excel or any other front end tool and at the same time if you process the cube then user can be affected badly. User simply can't access the cube if you are processing the same.

To avoid the above scenario, we can have two different SSAS server, one for Processing and other for Query. Business user should connect the Query server. So while you processing the cube it will update the Processing server; so user  can't be affected while you are processing the cube since user will be connected with Query server.

Today's writing will cover how to sync cube from one server to other.

Step 1: Find the Query server where you want to sync.

Click at the database of the server and then click Synchronize as like below fig 1

Server->Database->Synchronize

Fig 1: Sync to the Query server

Step 2: Synchronize wizard

At this step it explains what do you with the wizard

Fig 2: synchronize wizard

Step 3:  Choose the server from where you would like to sync which can be your processing server and Destination server will be automatically chosen.

Fig 3: Choose source and Destination to Sync

Step 4: Specify location for the partition, you can leave as default

Fig 4: location for partition

Step 5: Choose security roles and members

You need to take this part as per your need, if you don't want to copy any role and security from the processing server then you can choose third option 'Ignore all'. And if you want role need to copied but not members then choose 2nd option.

Fig 5: Choose what to Copy from processing Server

Step 6: Save script or Synchronize

At this last step, you can either start synchronize or generate the script to save in a file for later uses.

Fig 6: Last step of synchronization

You may choose Synchronize now, however; you can copy the script so that you can add the script to a SQL Server Agent job. 

Why do you create SQL Server Agent job?

If you need to sync the cube from Processing server to Query server time to time then creating job will be the best solution. Hence, the script saved from step 6 can be used to create a job. And whenever you need to sync the cubes you can run the job.

Measures(Calculation) in SSAS Tabular

While you work with SSAS Tabular or OLAP you have option to create measures. As found calculation in SSAS OLAP is easier than Tabular. However, I have recently made SSAS Tabular cube and going to share how to create Measures/Calculation in SSAS Tabular model.

SSAS tabular project look like below, I have marked the area for the calculation/measures.

Fig 1: Measures/Calculation area.

How do we make Measures?

Select any cell from above fig 1, and then as like excel you  have option to put the calculation or formula at the formula bar as like below fig 2 (yellow shaded).

Fig 2: Calculation bar

Here, I am going to calculate how many product is sold in Canada region, So I have calculated number of rows where productno are not empty and filter the salesTerritory with Canada.

The measure name is: Prod_Cnt_Canada (product count in the canda region)

Prod_Cnt_Canada:=COUNTAX(FILTER(FactInternetSales;[SalesTerritoryKey]=6);[ProductKey])

The above example counts the number of nonblank rows in the column, ProductKey, using the table FactInternetSales that results from filtering the SalesTerritoryKey=6 (means canada).

COUNTAX function is to return the number of rows for non empty rows. And FILTER function is to do the filter.

Result after the calculation is show in the below figure: 3

Fig 3: Measure after the calcuation