Sometimes in my work i’ve got problems with integrity of data, especially when I need to build cube based on OLTP systems. The easiest way is to build multidimensional cubes on DW but sometimes there is no possibility to have DW. Poor situation but Analysis Services give us opportunity to deal with it.
Perfect situation is when our solution has all data that it needs – every fact table refers to existing dimension row. But what if something refers to non-existing ID of our dimension we can easily solve this problem by setting UnknownMember property. Below I will show small demo how to recognize this problem and solve it.
First of all we need to build small cube project based only on two tables from Adventure Works DW:
After build Multidimensional project we should see something like this:
We don’t need user defined hierarchies here. After all we have to add a row to fact table that refers to non-exiting id in Product dimension. First of all we have to turn off constraint of our table:
ALTER TABLE FactResellerSales NOCHECK CONSTRAINT ALL
After that we insert product orphan row that doesn’t have referenced row in dimension table.
declare @ProductKey int, @SalesOrderNumer varchar(10)
set @ProductKey =(select max(ProductKey) from FactResellerSales)
set @SalesOrderNumer = ‘testNumber’
Insert into FactResellerSales
SELECT @ProductKey+1 —-[ProductKey]
where ProductKey =@ProductKey
and ResellerKey =170
and OrderDateKey = 20080601
Now if we want to populate our cube we get error – there is violation of referential integrity.
To provide UnknownMember functionality we must set dimension UnknownMember property to Visible and optionally UnknownMemberName to set friendly name for our UnknownMember.
After all we should see something like below.
But if we want to use it with measures we have to set special properties in the processing cube dialog box. On the first screen we can click Change Settings.
In change settings dialog blox on the Dimension key errors tab we must set custom error configuration. There is Key error action that need to be set to Convert to unknown. Another important property is Ignore errors count – this option give us opportunity to ignore all errors that will show up when fact table find reference to non-exisiting row in dimension table.
Finally, we’ve got nice group of orphan rows in group “Undefined”. In the future I will post about other Custom Error Configuration. I hope you enjoy it.