Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jordie
Starting Member
1 Post |
 Posted - 2008-05-19 : 11:48:42
|
| Hi,I am writing a BI solution for a recruitment company. In theirbusiness, the can be n number of participants from differentdimensions linked to the same fact record. For example, a client canbe sent the CV of 50 candidates. That's my first problem. My secondproblem is the variety of dimension participant types for a given factrecord. This results in the need for nullable dimension FK's - whichI'm trying to avoid. For example, consider the following two businessevents. In the first one, a candidate fills a job. Easy, we have arecord in the fact table where the fact table has the followingcolumns: DateKey, EventType, CandidateKey, VacancyKey. No nullablecolumns, great. But there are other events that I want to store inthe fact table too. Let's go back to my first example: The client issent CV's of 50 candidates in one transaction. So there is one clientlinked to the fact, but 50 candidates. So now I need to extend thefact table and add another column: CandidateGroupKey (which links toand Intermediate Fact Table). But in this case there was no vacancyinvolved. So do I now have to make the VacancyKey column nullable?That doesn't seem like a good idea...Or do I have to go for a completely different approach and havedifferent fact tables instead of just one?Anyone have any suggestions? |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-05-19 : 12:17:43
|
| Outch! Not sure... Different fact tables with slightly different designs sounds tidier and would likely yield better performance because you have less unnecessary joins. So you could end up with lots of fact tables. One fact table would have (presuming you wanted to know who did it, I've added a consultant key column too) CandidateTransactions: DateKey, ConsultantKey, EventType, CandidateKey. Another tables called maybe ClientTransactions: DateKey, ConsultantKey, EventType, ClientKey. So those two tables would be for when there is always one dimension record linked to a fact Key column. And then another fact table for your sending CV's scenario CandidateTransactionsType2: DateKey, ConsultantKey, EventType, ClientKey, CandidateGroupKey. Potentially you'll probably end up with even more Transaction Fact tables for other business processes too. But if you ever had to report on joint data from different fact tables, I don't know how hard that would be. Has anyone else here done that before? |
 |
|
|
|
|
|
|
|
Intermediate Fact Table