Currently I am working on a project with the following requirements to design the universe…!
TABLE --A called TRANSACTION
Transaction_ID
Level_Of_Transaction
Customer_id
TABLE - B called: Customer
Customer_id
name
Address
ph#
The above releation between two tables ( TABLE-A called fact table & TABLE - B is dimension table)
Customer table has one to zero/one/many relation ship to transaction table so. The Level_Of_Transaction ( 1 or 2 or 3 or 4) show the same transaction at different levels_codes. In the transaction table my have different types of processing so each level of transaction may be filled with different type of information in the transaction table.
The same transaction with level 1 code may not have customer information. however level 2 code of the same transaction my have customer information. How can I design a universe to grab the customer name which is not available in the level 1 transaction but customer information ( CUST ID) is avaialble in the level 2 for the same transaction.
if I am not clear, please let me know, I can explain you better.
Seems like you need a right outer join on your join so that regardless of the level 1 not having any information, your will still get all the level 2’s.
If you have not set an outer join before, this is done on the actual join between your two tables. You will see two outer join check boxes, you’ll need to check the left had one.
Run your query, check the results and check the SQL to ensure it says right outer join.
Customer_id
name
Address
ph#
CUSTOMER TABLE:
CUSTID NAME ADDRESS PH#
100 MC DONALD 1200 E ALNG
200 JACKSON 200 JACKSON BLVD
In the above scenario users expects to display all level 1 transactions for TRANSID=001, should display the customer information by considering the customer id as 200 from the other levels.
I am doubt outer join would solve this problem.?
If you apply an outer join to the query your result would return all records where the tranid is 1, it will return all the information selected where customer id is 200 and for the Null record, you would only see the tranid.
You might be better flattening your structure with a materialised view by taking each transaction type and creating a union with four separate columns for each of the meanings. What type of database are you using (Oracle, SQL Server, etc.)?
Wat about creating a Derived Table say trans_cust in The Universe.
Select distinct transid,customer_id from Transaction where customer_id is not Null
This table will act as an intermediate table between the fact table & the Dimension table.
TRANSACTION<—Derived Table------CUSTOMER
I hope this helps.
[/quote]
Performance. In effect you’re adding a view between a fact table and a dimension table. If volumes are anything like significant, you’re going to end up with a real bottleneck in all queries using the dimension and the fact.
[quote:70df14c6e2=“ranjul.gupta”]I agree with the point that the performance can be an issue, but it was just a suggestion.
Moreover if we talk about standards, this is not the way the Fact table is designed as well in which at one level the Customer Information is missing.
[/quote]
You’re right about the fact table design - fact table rows should all have the same level of granularity and what has been described by the OP is not a proper fact table based on that. Hence my earlier suggestion for creating a new fact table sourced from the one that exists earlier.
(BOB member since 2004-03-22)
(BOB member since 2008-10-16)
(BOB member since 2005-01-06)