Expired records breaking SSAS Cube if not present?

Hi,
We implemented change tracking - in other words - if a record in the source system is deleted - then in the data warehouse the corresponding dimension record is flagged as expired (RowIsCurrent = 'N').

Now when I filter out all expired records in the SSAS Cube - the cube processing fails since some fact records still need the expired dimension records.

So I tried to be clever and filtered out our dimension records as follows - but still it did not work:

SELECT * FROM DimAccount WITH(NOLOCK)
WHERE RowIsCurrent = 'Y'
OR AccountKey IN (SELECT DISTINCT AccountKey FROM FactGLJournalEntry WITH(NOLOCK))
OR AccountKey IN (SELECT DISTINCT AccountKey FROM FactBudget WITH(NOLOCK))

This was the error message - which i cannot understand why this calculated measure fails to process when i use this filter???
The field it mentions (profit)/loss ... is not even in the account dimension?

MdxScript(Finance) (46, 57) The level '[(Profit)/loss for the period]' object was not found in the cube when the string, [Account].[Account].[(Profit)/loss for the period], was parsed.

The END SCOPE statement does not match the opening SCOPE statement.

MdxScript(Finance) (48, 1) The END SCOPE statement does not match the opening SCOPE statement.

It seems like what you are trying to do should work. Does the cube process OK if you select all DimAccount records including the expired ones?

Do you have the dmension type in SSAS set to 'Regular' or 'Account'? I'm not sure that would change anything, just curious.

Is your DimAccount an SCD2 dimension? If you use RowIsCurrent for deletion, you may miss out previous dimension records due to SCD changes when filtering out deleted records. I would have an additional flag if I don't want deleted dimension to influence the aggregate values.

However in regards of cube processing, it is important to make sure the dimension key in fact table must have a unique entry in their respective dimension which is normally enforced by identity surrogate key. So to keep referential integrity and also exclude certain dimension records, you need to filter on the fact tables to exclude those dimension keys that point to the deleted records. To isolate the problem, process the cube by removing the MDX calculated measures first. You may add them back one by one, once successful. And also process dimensions before processing cube. Hope this will help.

Why would you exclude 'deleted' dimension rows? Historically, they did exist and you have facts associated with them. Not loading those facts would distort the historical record.

Hi

What happened was the business created two accounts called "Test1" and "Test2", then they deleted those.
So of course the ETL flagged those 2 as expired with the row indicator "N" indicating that they are expired (no longer present in the source system)

They then originally asked me to also make the expired accounts 'visible' when browsing the account dimension.

I then went ahead and changed the data source view on the cube to filter out those accounts where the indicator "N".

This worked at first because the two test accounts never had any actual transactions posted again them ...

The problem came in when the business deleted an actual account in the reporting source system.
the ETL of course again expired that specific account - and my filter excluded that account from being pulled through during dimension processing.

But then the fact (measure groups) failed to process since they have transactions in the fact tables with FK's pointing to the account in the account dimension which is not present in the cube dimension due to my filter - causing the processing to fail ...

what do i do now?

Ian,

The cube process failed because there is a referential violation in the cube when you filter out the dimension record but the dimension key still exists in the fact table.

If the delete operation on dimension is required by business, meaning the users don't want to see the deleted dimension records in the list event for PIT analysis, the best way I can think of is to add a deleted account entry in the dimension, say -1. Then whenever an account becomes deleted, update the fact table and set the corresponding dimension key to -1. Now your cube process should be successful and you will also see how many transactions point to deleted accounts. Hope this will help.

Would this not be negating compliance?

If a transaction is in doubt somewhere down the line ad we have to filter through the transactions we will no longer be able to find the original account to which this transaction belonged? even though both have to be expired (fact as well as dimension record) surely the transaction should refer back to the expired account dimension record - even if that is also expired?

In that case, you don't physically update the fact table. Create a fact table view instead, to reassign those deleted account key to -1, so you still have the referential integrity in the cube. Hope this is clear enough.

Hi Hang
Thank You, very very interresting idea!

I will probably just use the SSAS - DSV ( Data Source View) and change the fact table to a named query and make all the current fact records which reference an expired account to display the account key to -1.

If users do not want to see the test accounts in future - i will certainly make this kind of enhancement to the cube.
Then everyone is happy and from a compliance point of view the transactions in the fact table are original - and has not been tampered with.

Something else you could consider is having a more descriptive (Type 1) attribute to indicate whether a given account in the dimension has been deleted or not e.g. account_status_flag with values of "Deleted Account" vs. "Live Account".

That way you retain all the data about the account which may have been true at the time of the transaction in the fact, but give your users the visibility if they need it that an account has been deleted, and the ability to filter out deleted accounts.