Circular Reference?

[#1]

Is it okay to have circular reference in this case? I've three dimensions and one fact table. Client Dimension: It can have multiple instance of Client_GID but unique within a SalesForce_ID. Similarly, SalesForce dimension can have multiple instance of same zipcode, but unique within a SalesForce. In the front end I am planning to have always one SalesForce_ID selected. Is it going to create a problems in Fact report?

[Oleg_Troyansky]

No, it's not OK. QlikView won't allow any circular references, no matter what...

In your case you should give up on one of the links. I'd consider dropping Zip Code from the Sales Force table, since you already have Zio Code in the Fact Table. This will resolve the "loop".

cheers,

Oleg

[#3]

If I drop Zipcode from the SalesForce table and I select District  = 'Dist Wilkes Barre, PA'  in  SalesFiorce_ID = Diabetes, how would it work? Since this district is made up of 50 zips how would it get to the Fact table to report only those 50 zips?

[Oleg_Troyansky]

Based on your diagram, Sales Force Data is associated with Clients based on SalesForce ID, and Clients are linked to the Fact table. When you select a District, it will select all associated Clients and therefore all the associated Zip codes. I don't see a problem there.

[#5]

If I select a District in Salesforce(After removing Zip from SF) It will select all associated clients for a Particular SalesForce and not clients within a District of a SalesForce. So, as soon as I remove ZipCode from SF, and select a District within a SalesForce, my Client table will Give me list of all clients present in a SalesForce and not within a District of that particular SF. Isn't that correct? I guess the best way for me is to try your suggestion and then let you know what happens. Thanks!

[#6]

Dropping a Zipcode field from SalesForce table doesnt work. As I said if a User picks a District;  instead of gettting only Zipcodes specific to only that District, QV returns all Zipcodes from entire SalesForce to which that District belongs. Any other Solution to avoid Circular reference?

[swuehl]

You could probably join the geography table with the salesforce table. Then rename %zipcode, city and state of the joined table to e.g. SalesforceCity, SalesforceZipCode and SalesforceState.

edit: I mean, join one copy of the geography table. Another geography table could still be linked to the fact table as is.

edited by swuehl

[swuehl]

Or without joining, have two copies of the geography table, one for the fact table, one for the salesforce table with different fieldnames.

Both soultions should work, if the descriptive data for salesforce and client (geographical descriptions) could be viewed as separate data, e.g. like Names (Salesforce Name and Client Name for example) and won't be selected in the same place (like you probably select Client Name and Sales Name separately, same for Client Zip and Salesforce Zip).