2

I have an Analysis services database with several cubes in it. We have a nightly job that extracts data from our source database to some staging tables, and another that performs a "Process Full" of the entire SSAS database. After these jobs are complete, one particular cube has a dimension that contains 6 elements, all of which are incorrectly labeled. (It's supposed to have values "M1 - name1" through "M6 - name6", but instead it gets "M1 - " (code, but no name value) and then 5 " - " (neither code nor name).
I can fix this by doing a "Process Update" of just that dimension, followed by a "Process full" of the cube that uses the dimension; the dimension will then contain all the expected labels.

What I don't understand is why the database-wide "Process Full" is mucking up the dimension in the first place and why a "Process Update" gives me a different result.

I read on a Microsoft SQL BI blog that a ProcessUpdate

does not discard the dimension storage contents. It applies the changes in a "smart" manner that preserves the fact data in dependent partitions.

but since these processes are being run in sequence with no changes to the cube and no changes to the underlying data in between, I still don't get how the Update can produce changes to the dimension.

This process is completely repeatable: any time that I just process-full the cube, the dimension gets mucked up. Process-update the dimension and then process-full the cube, the dimension is good.

So,
(a) How can the Process-full produce different results than the Process-Update on the dimension when they are both operating on the same data?
(b) Is there a better way to get to my desired cube state than tacking on steps to my nightly job to do the "process-update dim/process-full cube"?

Here are a couple of excerpts from the processing logs, where you can see that the queries are identical but the results are not...

from the Update:

    Processing Dimension Attribute 'Facility' completed. **7 rows have been read.**  
            Start time: 5/31/2017 9:11:19 AM; End time: 5/31/2017 9:11:19 AM; Duration: 0:00:00  
            SQL queries   
                SELECT DISTINCT 
 [dbo_Dim_WorkCenter].[Facility] AS [dbo_Dim_WorkCenterFacility0_0],[dbo_Dim_WorkCenter].[Facility id name] AS [dbo_Dim_WorkCenterFacility_x0020_id_x0020_name0_1]
  FROM [dbo].[Dim_WorkCenter] AS [dbo_Dim_WorkCenter]

from the Full:

            Processing Dimension Attribute 'Facility' completed. **18 rows have been read.**  
            Start time: 5/31/2017 9:14:56 AM; End time: 5/31/2017 9:14:56 AM; Duration: 0:00:00  
            SQL queries   
                SELECT DISTINCT 
 [dbo_Dim_WorkCenter].[Facility] AS [dbo_Dim_WorkCenterFacility0_0],[dbo_Dim_WorkCenter].[Facility id name] AS [dbo_Dim_WorkCenterFacility_x0020_id_x0020_name0_1]
  FROM [dbo].[Dim_WorkCenter] AS [dbo_Dim_WorkCenter]
  • Have you captured the query that SSAS is using to process the dimension? If yes, what data do you get when you run the query yourself? Are you processing through views or tables? Are you viewing the dimension by itself or related to a fact record? – Wes H May 30 '17 at 22:37
  • Hi @WesH, I have captured the complete results (including actual SQL queries) from the update vs. the full, and the queries used to select from the datamart tables are identical, but they produce different numbers of rows. – Hellion May 31 '17 at 14:32
  • When you run the query, do your results match the full or update? Is there any other ETL happening on the system that may be changing the number of rows in the table? – Wes H May 31 '17 at 16:14
  • @WesH when I run the query manually, my results match the full. There is no other ETL happening (ETL only runs overnight, the samples above were three minutes apart this morning). – Hellion May 31 '17 at 16:21
  • Process update should only find the new rows. Process full clears and reloads everything. Try running a process clear, then a process update on that dimension. Does the row count match a full process? – Wes H May 31 '17 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.