June 2, 2011 at 5:08 pm
Hi All,
i will describe my question by using the object of the AdventureWork sample OLAP project:
let's say i have on Fact Table with all sales, the key is order number.
i have two dimension : DimCustomerOrders and DimResellerOrders. each one of the dimension has different order numbers
(there cannot be the same order number in both dimensions)
when i'm trying to coonect the mesure group with one of the dimension, i'm getting processing error - since there is an order in the fact table that doesn't exist in the dimension. example - in the fact table i have order SO123 which is a reseller order. this order dosn'e exist on the
DimCustomerOrders since it is a reseller order.
currently we populate both dimension with all orders which cause redundant data.
the client does not want to use two measure group (FactReseller and fact Internet).
i also tried to create a dimension from the fact table - Orders, and then use it as a referenced dimension between the FactOrders and the DimCustomerOrders. and i'm still getting error about not existing members.
any ideas how to solve this issu?
thank you,
Liran
June 3, 2011 at 7:04 am
First, if you are not using Surrogate keys as your dimension primary keys, you should be. The natural keys can still exist in the dimension, but they should not be the key that links to other tables.
Add a record to each dimension that represents N/A or something to indicate that the fact does not apply to that dimension.
When the fact table should have 2 FK columns, 1 for each of the dimensions linking to the SK of each dimension. Your measures, etc. can all filter out the N/A data.
June 5, 2011 at 8:23 am
great solution. it did solve the problem.
i do have another question:
like you suggested i added a record for each dimension. i wasn't sure if you meant to put the NA for each column in that record.
is there a way to i can specify that the NA member should not be visible?
currently i did it manually by using scope for each NA member of the dimensions attribute:
Scope
(
[Dim Customer].[Age].&[NA]
) ;
This = null ;
End Scope ;
so my question is: s there another way to indicate that this record not related to the fact?
thank you!
June 6, 2011 at 3:00 am
is there a way to i can specify that the NA member should not be visible?
Do this at the client, not at SSAS. If Excel is your client, N/A is easily filtered out using the drop down.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply