How to remove circular loop

[#1]

Hi All,

I am facing problem while creating datamodel in Qlikview. This is Clinical Trial Domain scenario. To explain my problem in details I have 5 entities in my datamodel.

1. Dim_Study: Represents a study which is getting conducted for testing a drug reliability.
2. Dim_Site: Represents the locations where these studies are getting conducted. One study can be conducted at multiple sites and one site can have more than one study running.
3. Dim_RSM:  A person, responsible for monitoring a study on a site.
4. Dim_Geo: Geography hierarchy  dimention table.
5. Fact_Country_Milestone: Represents a fact table containing some measures related to a country and a study.

I have attached a screen shot represents the relationship between first 4 entities. When I add the 5 entitiey (Fact_Country_Milestone) It creates a loop because I have to join it with Dim_Study and Dim_Geography.

My problem is that I can connect Fact_Country_Milestone to RSM table on Study and Geo Id but If for any reason, there is a country for which no sites present then I will not be able to see Country Milestone measures by that country, study.

Hope I am able to explain my problem.

Thanks & Regards,

Anosh Nathaniel

[swuehl]

I believe your problem is already starting with your fact table, from here your measures are linked to only GeoID and StudyID. You then detail your Studies into sites and then trying to link back your sites to countries via the sitemanager. This design doesn't seem appropriate to me (but of course I don't really know what you want to analyze).

Your dimensions, namely the study sites, are more detailed than your fact table, so you won't really get your measures per site. I assume that the GeoID and StudyID based measures are targets (you called them Milestone), so you are probably thinking of bringing in some more actual study results?

Maybe you could tell us a bit more about the big picture and your analysis requirements?

Regards,

Stefan

P.S: It's quite easy to break the loop by removing the GeoID form Dim_RSM, but I assume that's not what you want. I wouldn't expect to assign a GeoId to a RSM, but maybe to a site, why do you need this information at this place?

See attached.

[#3]

Hi Stefan,

Thanks for your quick reply.

The table structure which I have shared with you is a mockup of original datamodel where I have multiple attributes within each table.Also the fact milestone is having multiple dates as per the different milestone. The geography table contains a hierarchy which is Region-->Country-->Subregion.

I do not have Sub-region coming in Dim_Site from the source. It is only present in Dim RSM. I can manage to populate Sub-region at site level using the fact that only one RSM can be assigned to a Site for a study.

Doing this if I create Dim_Geography join with Dim_Site then also Fact_monitoring_milestone will create loop by joining with Study and Gegography table.

Thanks & Regards,

Anosh Nathaniel