I am designing a universe I am facing a issue where for one record I am fetching 7 records and getting huge values , I should get one record. I have checked the all the joins but could not solve the issue. In my date table I have dates from day,week and month is this some thing to do with it needful advice is helpful
thanks in advance
bobtom (BOB member since 2008-01-17)
Hi,
This can be a result of a fan trap or of a chasm trap or of incorrectly defined joins or … different things. It’s really hard to debug this problem without knowing your actual data model, your data and the requirements based on which the universe was built.
Marek Chladny 
(BOB member since 2003-11-27)
thanks Marek , I have a A1 summary table and A2 detail tabel , I have connected both with common key and in both the tables I have date keyID. And A2 detail table joined with Date dim table and A1 summary table joined to alias date table which i have created. In A2 Detail table I have only hierarchy sim objects, key IDs there are no measure values. I have not used any contexts.
In A2 detail table I have Coloumns like KeyID,product1 Dim Obj,category1 Dim Obj,category2 Dim Obj,category3 Dim Obj,Category4 Dim Obj,subcategory Dim Obj and Date KeyID
In A1 table I have columns like subcategory Dim Obj,total products measuere,completed producted measuere,DatekeyID
Date Dimension table three columns Datekeyid,datefrom,todate. And Date formate we have is “mm/dd/yy hh.mm.ss am”
I have created a hierarchy from A2 detail table like
product1 Dim Obj,
category1 Dim Obj,
category2 Dim Obj,
category3 Dim Obj
Category4 Dim Obj,
subcategory Dim Obj
Please give me some advice .
thanks
bobtom (BOB member since 2008-01-17)
Hi,
If the summary table contains the same information as the detailed table but data is aggregated on a higher level then it means that for one row in the summary table there is one or more rows in the detailed table. So if you join the tables then in the result you will get multiple rows for each row in the summary table. So the results (measures) from the summary table are inflated. So it’s not a good idea to join the summary table with the detailed table even if you think you used correct table column(s) for the join.
Look at this simple example:
Summary table has 1 row:
dimension1…measure
A…100
Detailed table has 3 rows:
dimension1…dimenstion2…measure
A…A1…10
A…A2…40
A…A3…50
As you can see, the summary table has the same information as the detailed table but aggregated on a dimension1 level only.
Now when you join both table together via dimension1 dimension, you get this result:
dimension1…measure(from detailed table)…measure(from summary table)
A…10…100
A…40…100
A…50…100
As you can see, now the measure from the summary table is inflated 3 times - this is because the 1 row from the summary table has 3 corresponding rows in the detailed table.
So despite of the fact that the summary table and the detailed table were joined using a correct column (dimension1 in this case), logically it’s not correct to create this join between the 2 tables.
Marek Chladny (BOB member since 2003-11-27)
Thanks Marek, Tomorrow morning i will test as u described I will remove the join between summary table and detail table and will see the output.
thanks
bobtom (BOB member since 2008-01-17)
Moderator Note: Please do not use Instant Message abbreviations on BOB. We want to keep BOB easy to understand for our International community. Thanks.
Jansi 
(BOB member since 2008-05-12)
hi Marek ,
As you mentioned I have removed the join between summary fact table and detail dim table but in that case all the records i am fetching same values like rollup from lower to higher hierarchy with same values in huge numbers,
so what I did was I have joined the summary table keyID and detail table keyID and
In both summary and detail tables i have date keyID I have joined them and summary datekryID again joined with date dim table datekeyID in this case I am fetching correct values for all records but when Iam creating a section I am getting a record of section values in body due to which my rollup getting doubled. can you please through some light I think some small mistake I am doing here but I dont know where. I tried all combinations of outer joins but could not resolved and tried to resolve with context too but could not solve it.
Example
…c1 c2 c3
Product section record A 50 70 50 – Actual value should be 25 35 25
category body records B 10 10 10
A 25 35 25 – this record should be in section but displaying in body
c 15 25 15
I have doubled checked the hierarchy too but could not find where the mistake is going on. I have double clarified with DBA to make sure if there was any data issue but that was not the case.
Please give me some advice.
thanks
bobtom (BOB member since 2008-01-17)
Please see the snapshot and give me your opinion, my issue still not resolved
bobtom (BOB member since 2008-01-17)
Hi,
Can you set cardinalities of all joins? It’s important to understand whether the problem is caused by a fan/chasm trap.
And also, can you describe which table is the summary table and which one is the detailed table? And also which dimensions and measure are based on each table?
Marek Chladny (BOB member since 2003-11-27)
all the cardinatilities for the jons are set and Mr_program_score is summary table and Mr_resource is detail table.
Measures are taken from summary table starting from all_programs,completed… all the measure values are aggregated at database level itself.
take the measure values from summarry table.
All the values are comming from summary table. In detail table you see only Hierarchy and keyobjects.
bobtom (BOB member since 2008-01-17)
Hi,
If I understand it all correctly, the main problem with your data model is that you don’t have data on the level of details that you need in the report. You have only summarized data in the summary table. There is no detailed data so it can’t be reported.
If possible, add columns that represents measures also into the detailed table so you have details needed for the report.
Marek Chladny (BOB member since 2003-11-27)
Thanks Marek
bobtom (BOB member since 2008-01-17)