let's say tasks are in the past, a little in the future, and all opps are in the future. this data cannot be shoved into the same table, task and opps are on differently levels of granularity. now let's say i want to graph hours by day. i'm using a stacked column chart that is graphing hours by month. there is a task stack and on top of it there is an opp stack. no problems with that so far.
*then* i add an office field to each fact table, as well as a manager field. i want the user to be able to filter by office. if i name one officetask and the other officeopp then i would need 2 selectors in the front end to pick office - no go. however if i name them both office, they create a loop i don't like and the data seems to come out incorrect. please advise.
sickmint79 wrote:should i be making the effort/is it normally achievable to remove all synthetic keys from a qv model?
If the synthetic keys make sense to you, I wouldn't think so. In practice, I almost never see synthetic keys in my modeling, though, perhaps because my data sources are highly normalized. I'd have to go to some effort to get duplicate information on multiple tables. So in my case, if synthetic keys appear, it's usually because I did something wrong. But it's just a hint that maybe there's something wrong, not strictly an error.
sickmint79 wrote:why is this synthetic key being made here, and how would you remove it?
Pretty much what you said - QlikView recognized that I used the same two fields both the task fact and opportunity fact tables. It probably figured that rather than redundantly store the data, it should store it in a separate table with an autogenerated ID. As for how to remove it, one simple approach is to do exactly what QlikView did. Store these two fields in a separate table, give the combinations an ID, and use that ID in the fact and opportunity tables. I believe there would be no advantage to that, however, since QlikView has done it for you. I could, however, be deeply wrong, as I haven't studied what's going on behind the scenes. This issue just doesn't surface for me.
sickmint79 wrote: my original one was similar to this, although rather than office being on the fact table, i had office as an attribute of both opportunity and of task. if you name them task_office and opp_office you will have no problem, however if you name them both office you will see the loop i was talking about. i see how pushing it down to the fact layer actually closes that loop in your example, kind of like making an office dimension. this is the correct way to resolve this loop?
OK, making the change you mentioned, I now see the loop you were talking about. I'm not certain that pushing the office to the fact dimension is the correct solution, as I don't fully understand your data. However, it would seem to me on the surface at least that we could work on a single task in more than one office. If so, it makes no sense to put the office on the task, and seems to make sense to put the office on the fact table.
sickmint79 wrote:my model has however gone from 1 previously existing synthetic key to now 3. perhaps related to other parts i am working on as well.
Might or might not be a problem. If they all make sense to you, it might be OK.
