x

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
more ▼

asked Jul 23 '12 at 06:59 AM in Default

parikshitssas gravatar image

parikshitssas
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.
more ▼

answered Jul 24 '12 at 06:13 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x49

asked: Jul 23 '12 at 06:59 AM

Seen: 1129 times

Last Updated: Jul 24 '12 at 06:13 AM