Sql-server – Processing Dimension completed but error occurs

sql serverssas

I have problem while processing cube in SSAS, everytime I have an error:

I tried to process every dimension separately and it looks like something bad was going on after processing 'Date' dimension.

Any ideas why?

Best regards,
Ana

Best Answer

If you take a look at your dimension table and run a query like the following:

Select count(suspect_column) as count_suspect_column, suspect_column
from Dimension_Date
GROUP BY suspect_Column
HAVING COUNT(suspect_column) > 1

It is likely that there is a duplicate value causing the process to fail. These values in that column need to be unique for the process to work. Also worth noting is that SSAS treats nulls as empty strings according to this excellent article: https://www.sqlservercentral.com/articles/ssas-error-duplicate-attribute-key-found-when-processing

Related Solutions

Sql-server – Need to filter dimension where two attributes are the same

I got it!

Basically the filter expression was correct, but I needed to Crossjoin the set what I wanted to filter against...

FILTER (
  CROSSJOIN([Student].[Major].[Major], [Student].[Minor].[Minor]),
  [Student].[Major].Properties("Key") = [Student].[Minor].Properties("Key")
)

I know I answered this quick but I've spent the best part of 10 hours on this today :P

Sql-server – Cube processing either takes too long or fails

Oh my dear..

First of all. In your case I would like to suggest you to choose the "Process Default" option. If you are using Process Full always you are dropping all data and objects and recreating all of your stuff. You don't need them. Very often the proposal option is to use ProcessData followed by ProcessIndex.

I didn't proces the cube from within the SSIS, but probably SSIS is checking something and locking something on the database. The best option is to use XMLA. If you want to process your cube from within the SSIS you can write a script task to perform the processing - you can find a scripts on the net.

You need to also check if you have a clean data. Check twice in your project and in your data if you have all the keys and so on. If you are processing separately dimension the data is not comparing between measures and dimensions. I am pretty sure that you have some keys in fact tables that have no corresponding values in your dimension. Make an order with this. If you have no values in dimension and it's correct (doesn't exist in the source tables) you can add to each dimension one extra value with i.e. key "-1" and all values like "N/A" (not applicable) and point the not founded values to this record in the dimensions.

And at the end. If you have a big SSAS database please think about a partitioning.

It's a lot to talk about. If you have some specific questions feel free to ask.

Best Answer

Related Solutions

Sql-server – Need to filter dimension where two attributes are the same

Sql-server – Cube processing either takes too long or fails

Related Question