May 24, 2011 at 5:42 am
Hi everybody-
I'm trying to set up a many to many relationship within SSAS, but am coming up stuck. Basically, I have a FactFacilityStatistics table, and I want to be able to show Facility measures by Organizations that are impacted. One Organization can be housed at many Facilities, and one Facility can house many Organizations, so many to many seemed natural.
My bridge table is called FactPersonStatistics - it links Person, Organization, and Facility, and does have one measure of PersonCount. Basically, I'm trying to go from FactFacilityStatistics to a dimension called DimFacility, then through the bridge table FactPersonStatistics, and then to a dimension called DimOrganization. I've tried to follow along the basic MSDN article on the topic, and everything looks set up correctly; however, when I try and show a Facility measure sliced by an Organization, nothing comes up.
Does anybody have any ideas, or has anyone dealt with this before?
May 26, 2011 at 7:40 am
So a few days later, I ended up solving my own problem, but I wanted to post the answer in case somebody finds this later. My issue was that the granularity in the intermediate fact table was wrong. I ended up putting a view on top of the FactPersonStatistics table of the DISTINCT Organization to Facility mapping, and then everything worked as expected. I guess I gave SSAS too much credit to figure out what I wanted, but in the end, it worked.
May 27, 2011 at 12:19 am
Glad that you could solve your issue and thanks for posting the solution!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply