Few words about: Unknown member

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:

  • FactResellerSales
  • DimProduct

After build Multidimensional project we should see something like this:

Simple schema of two tables

Simple dimension

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]

      ,[OrderDateKey]

      ,[DueDateKey]

      ,[ShipDateKey]

      ,[ResellerKey]

      ,[EmployeeKey]

      ,[PromotionKey]

      ,[CurrencyKey]

      ,[SalesTerritoryKey]

      ,@SalesOrderNumer

      ,[SalesOrderLineNumber]

      ,[RevisionNumber]

      ,[OrderQuantity]

      ,[UnitPrice]

      ,[ExtendedAmount]

      ,[UnitPriceDiscountPct]

      ,[DiscountAmount]

      ,[ProductStandardCost]

      ,[TotalProductCost]

      ,[SalesAmount]

      ,[TaxAmt]

      ,[Freight]

      ,[CarrierTrackingNumber]

      ,[CustomerPONumber]

      ,[OrderDate]

      ,[DueDate]

      ,[ShipDate]

  FROM [AdventureWorksDW2012].[dbo].[FactResellerSales]

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.

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.

properties of dimension

After all we should see something like below.

members of dimension

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.

process cube

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.

dimension key errors

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.

result with unknwon member

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s