asked
How I create a cube using three fact table while in one fact record is present but in another are record is not present?
Hi All, I am facing a problem with one SSAS Cube processing. I have done as: I have Fact 1, Fact 2 dim : Customer :customerid 1, 2, 3, 4, 5 Fact 1 : 1,2,3,4 (customerid are present) Fact 2: 1,2,3,4 (customerid are present) so Cube process successfully. Then Next I have added Fact 3 So, In Fact 3 : 1,2,3,4,5 ( i.e. All Customer Ids are Present) So I gives error. Cube goes failure. Please let let me know I solve this.. How I create a cube using three fact table while in one fact record is present but in another are record is not present? ------------------------------------------------------------------------------------------ In Detail:--- Select CUSTOMER_ID from CUSTOMER where CUSTOMER_ID = '5' Result Customer_Id 5 Select Fact1.CUSTOMER_ID from Fact1 where CUSTOMER_ID = '5' Result Customer_Id (Blank) Select Fact2.CUSTOMER_ID from Fact3 where CUSTOMER_ID = '5' Result Customer_Id (Blank) Select Fact3.CUSTOMER_ID from Fact3 where CUSTOMER_ID = '5' Result Customer_Id 5 Required Code SELECT Customer.Customer_ID as Customer_ID FROM CUSTOMER INNER JOIN Fact1 ON Customer.Customer_ID = Fact1.Customer_ID INNER JOIN Fact2 ON Customer.Customer_ID = Fact2.Customer_ID INNER JOIN Fact3 ON Customer.Customer_ID = Fact3.Customer_ID where Customer.Customer_ID = '5' Result Customer_Id (blank) Because of Black Record SSAS Cube goes fail. How I handle this exeption in SSAS. To show successfull Cubbe if result is blank? BUT when I use Fact 1 and Fact 2 joined with customer table it doesnt gives error because of in both facts record is not present when I join to fact3 it give error because Fact 3 record is present. singly join with Customer anf Fact3 then it works fine but by using Fact1 & 2 with 3 it goes fail.. to handle this exception is there any option in SSAS. please give any suggestion --Parikshit
It is not necessary to have all Dimension IDs in the Fact Table to successfully process that particualar Measure Group. On the other side it is necessary that all IDs refered in Fact Table needs to be present in the Dimension. If there is ID in the Fact Table, which is not in the dimension table, then the processing of such measure group will fail. (There is possibility to ignore such records (fact rows) when processing a Measure Group, but in that case you will have not correct results. Other option is to convert such refered keys to "Unknown" and then such aggregation will be covered by "Unknown" member of the dimension (if the Unknown member is allowed) otherwise again will fail. If you use a mentioned query with the joins of all fact tables as source for the third fact table and the CustomerID is than NULL, it is the case where again a NULL key was not found in the dimension table. There is a possibility to setup custom Error configuration and set it to convert null keys to unknown so the processing will succeed however again the final results can be wrong - Depends on your reporting needs etc. On the other side I don't understand, why you are joining the fact tables. You are joining the fact tables to create a single measure group? Probably better will be create a separate measure group for each fact table. In that case you do not need to handle NULL and unknown keys and will receive correct results.
8 People are following this question.
