Is this Chasm Trap?

[Former Member]

Hi Experts,
I have to build an universe where there is one fact table and 5 dim table as mentioned in the diagram below. My report query
needs to have objects from DIM A, PT_DATE_DIM, EMP1_DIM AND measure from Fact1.
There seems to be 2 many to 1 to many joins. Fact (many) to Dimention (one) to Dimension (many). Is this a Chasm Trap? Does it needs to have two fact tables to have Chasm Trap? If I join these tables in Universe and build report query based of fact table and PT_DATE_DIM and EMP1_DIM (no objects from table at one end) will that be any problem? I think we cannot have many to 1 to many join in Universe. Please give me some
suggestions.

-->

[Former Member · Accepted solution]

Your first thoughts are correct. You don't need two fact tables to create a chasm trap, although in a correctly built dimensional model, it would be difficult to get into that situation. I think that before we can recommend an answer, we need you to explain what PT_DATE_DIM and EMP1_DIM do as well as how they are related to DATE_DIM and EMPLOYEE_DIM. Until you answer those, any other answers we give are purely best guesses and shouldn't be considered as the correct answer.

[Former Member · Accepted solution]

PT_DATE_DIM is a date table which have has dates for patient visits. This date table is joined to Main Date table DATE_DIM with 1 to M relationship as I have mentioned in the diagram above. Similar EMP1_DIM table contain employes of cetrain category and is joined to EMPLOYEE DIM table . There is 1 to M relationship between EMPLOYEE DIM and EMPL1_DIM as mentioned in diagram above. In my report I need to employee names from EMPL1_DIM table and dates from PT_DATE_DIM tables not from DATE_DIM and EMPLOYEE_DIM

[Former Member · Accepted solution]

OK, so what is Fact1?

PT_DATE_DIM sounds more like a fact table - it is recording patient visits. They are transactions that happened (facts) rather than further descriptions of the fact (dimensions).

However, I suspect your model at the universe layer may be wrong, depending upon what type of transactions Fact1 contains - I'm thinking that DATE_DIM may need aliasing because it may have different means to Fact1 and PT_DATE_DIM.

Question: Given "EMP1_DIM table contain employes of cetrain category", how can that have a 1 t- M relationship with EMPLOYEE DIM with EMP1_DIM on the many end? Is EMP1_DIM again a transactional table?

[Former Member · Accepted solution]

Fact1 has all the measures like Total Volume Counts. PT_DATE_DIM and EMP1_DIM are not a fact table. They were  view (May be I did not read the model correctly). The model shows relationship between the view PT_DATE_DIM and table DATE_DIM as M:1 and EMP1_DIM and EMPL_DIM as M:1. So, while building my universe do I need to consider these DIM tables and Views? Can I directly Join View with Fact tables with 1:M relatioships ? or I need to bring in DIM tables and Views also?

[Former Member · Accepted solution]

General concept of chasm traps

You cannot have tables going M:1:M in one context - that's a chasm trap. You have no way of knowing which record to relate from the first M end to the second M end, so BO will pair them all up, giving you the inflated results that you get in a standard chasm trap.

EMP1_DIM

The confusing bit is that you say is that EMP1_DIM is a subset of EMPL_DIM. What do the mulitple records in EMP1_DIM represent? Without knowing this, you cannot start to build your universe correctly.

FACT1

Fact1 has measures of what? What is the granularity of it? I'm not concerned about the facts in the table, more the dimensions that it is split by.

PT_DATE_DIM

What is the data in PT_DATE_DIM? You say it's a view but you also say that it has dates for patient visits. That is a fact table in a sense - it's a record of patient visits. If there is more than one occurence of any date in there then you cannot join it to FACT1.

Sorry for all the questions but I don't want to advise you incorrectly - without understanding the two views and the fact table more, I may do just that, as would anyone else.

[Former Member · Accepted solution]

Mark,

The model was confusing. I finally spoke to a modeler and got the clarification. We dont needs those middle table. I can directly join with view with 1:M from Dim views to Fact. Now, its good to go. I really appreicate taking out time for me and helping me out dig through the problems. Thanks a lot.

[Former Member · Accepted solution]

No problem. At least you've learned one thing - you need to understand the model before you can add best value to it. That's why I was asking the questions - because I didn't know the model well enough.

So, yes, you only need the one context now with the 1-to-many joins that you will have in place

[Former Member · Accepted solution]

"Chasm and Fan Trap - SAP BusinessObjects "