Loops in dimensions

[#1]

Hello everyone,

I am facing a problem to resolve a loop. I have created two qvw to explain my problem. I have following entities in my database:

Hospital

Brand: Which are known as drugs.

TA: Therapeutic Area, One therapeutic Area can have multiple drugs but one drug have only one TA

Access: Users have access to limited hospitals

Direct Sales: Sales of a Drug (Brand) through a hospital

MAT: Moving anual total for a drug and a hospital

Donation: A hospital is given some amount of donation for a TA

Now in first qvw, a synthetic table is being created which i have removed in second qvw by manually creating link table between brand and hospital.

The only problem which I was not able to solve is the loop which is getting created between hospital, TA, Donation and Brand as I have to analyze Donation amount by Hospital and TA. Currently the chart it is giving correct result but I don't know when loosly coupled table will start giving wrong results.

I have also attached the table viewer images for those who cannot open the qvw.

Hope I am able to explain my problem.

Anosh Nathaniel

[#2 · Accepted solution]

I consulted with my friend and found a solution for the above loop issue in which we have created Hospital-Brand-TA link table and all other table are joined through this link table. Attached is the solution qvw.

But now the problem is if I don’t get HospBrandLink table from db I need to generate it manually so that I can create ‘Link’ table. Hospital brand relationship is present in multiple tables like Direct Sales, MAT etc… I need to take all these table into consideration and need to find all possible combination of hospital – brand ids present in db.

Am I thinking in correct direction and Is this solution correct?

Thanks in advance,

Anosh Nathaniel