Issue with Report Model on a Cube (multiple measure groups)
Hi All,
I have was asked to built a report model on the cube for some users and being new to report builder 3.0.
I am having a weird issue. Lets Say I have following DIMS and FACTS. I have a cube built on top this Dimensional model.
Two measure Groups/mearsures : ClassFact/ClassHours and FacilitatorFact/FacilitatorHours.
used Many to Many relationship for FacilitatorDIM and ClassFact measuregoup. I now browse the cube it works excellent.
The problem Starts
Now a report model is built on top of it, then in the query wizard when I pull both groups and then FName the Classhours(measure) Disappears.
Does Report Modes supports Many to Many or am I missing something? Any help will be appreciated.
Thanks
Dims
ClassDIM (ClassID, ClassName)
FacilitatorDIM ( FID, FName)
Facts
ClassFact( ClassID, Classhours)
FacilitatorFACT(FID, ClassID, FacilitatorHours)
Sample Data:
SELECT ClassID = 1 , ClassDurationHours = 100.0
FROM ClassFact
UNION
SELECT ClassID = 2 , ClassDurationHours = 200.0
FROM ClassFact
UNION
SELECT ClassID = 3 , ClassDurationHours = 0.0
FROM ClassFact
UNION
SELECT ClassID = 4 , ClassDurationHours = 1.0
FROM ClassFact
UNION
SELECT ClassID = 5 , ClassDurationHours = 50
FROM ClassFact
SELECT ClassID = 1, ClassName = 'ABC'
FROM ClassDIM
UNION
SELECT ClassID = 2, ClassName = 'DB'
FROM ClassDIM
UNION
SELECT ClassID = 3, ClassName = 'BC'
FROM ClassDIM
UNION
SELECT ClassID = 4, ClassName = 'ZYZ'
FROM ClassDIM
UNION
SELECT ClassID = 5, ClassName = 'Z'
FROM ClassDIM
SELECT ClassID = 1, FacilitatorID = 100, FacilitatorDurationHours = 20
FROM FacilitatorFACT
UNION
SELECT ClassID = 2, FacilitatorID = 200, FacilitatorDurationHours = 30
FROM FacilitatorFACT
UNION
SELECT ClassID = 3, FacilitatorID = 300, FacilitatorDurationHours = 50
FROM FacilitatorFACT
UNION
SELECT ClassID = 4, FacilitatorID = 400, FacilitatorDurationHours = 60
FROM FacilitatorFACT
UNION
SELECT ClassID = 5, FacilitatorID = 500, FacilitatorDurationHours = 70
FROM FacilitatorFACT
UNION
SELECT ClassID = 5, FacilitatorID = 600, FacilitatorDurationHours = 100
FROM FacilitatorFACT
SELECT FacilitatorID = 100 , FacilitatorFullName = 'F100'
FROM FacilitatorDIM
UNION
SELECT FacilitatorID = 200 , FacilitatorFullName = 'F200'
FROM FacilitatorDIM
UNION
SELECT FacilitatorID = 300 , FacilitatorFullName = 'F300'
FROM FacilitatorDIM
UNION
SELECT FacilitatorID = 400 , FacilitatorFullName = 'F400'
FROM FacilitatorDIM
UNION
SELECT FacilitatorID = 500 , FacilitatorFullName = 'F500'
FROM FacilitatorDIM
UNION
SELECT FacilitatorID = 600 , FacilitatorFullName = 'F600'
FROM FacilitatorDIM
May 20th, 2011 8:41pm