Re: Intermediate Fact Table
- From: Marco Russo <marco.russo@xxxxxxxxx>
- Date: Tue, 20 May 2008 00:42:25 -0700 (PDT)
On May 19, 5:44 pm, JordieWilli...@xxxxxxxxxxxxxx wrote:
Hi,
I am writing a BI solution for a recruitment company. In their
business, the can be n number of participants from different
dimensions linked to the same fact record. For example, a client can
be sent the CV of 50 candidates. That's my first problem. My second
problem is the variety of dimension participant types for a given fact
record. This results in the need for nullable dimension FK's - which
I'm trying to avoid. For example, consider the following two business
events. In the first one, a candidate fills a job. Easy, we have a
record in the fact table where the fact table has the following
columns: DateKey, EventType, CandidateKey, VacancyKey. No nullable
columns, great. But there are other events that I want to store in
the fact table too. Let's go back to my first example: The client is
sent CV's of 50 candidates in one transaction. So there is one client
linked to the fact, but 50 candidates. So now I need to extend the
fact table and add another column: CandidateGroupKey (which links to
and Intermediate Fact Table). But in this case there was no vacancy
involved. So do I now have to make the VacancyKey column nullable?
That doesn't seem like a good idea...
Or o I have to go for a completely different approach and have
different fact tables instead of just one?
Anyone have any suggestions?
Yes, you should consider different fact tables for different events
with different granularity.
However, it is not clear from your description what is the object of
the analysis.
What are the measures of the fact? First of all, think about the
business requirements. Modeling a one (or more) star schema(s) is a
consequence of this, and not the starting point. Another common
mistake is starting from the data you have, trying to model a star
schema putting everything into it, without considering the real
requirements.
Thus, what is the event you want to measure? What are the measures of
this event?
A candidate fills a job - this is a fact, may be without specific
measures.
A client sent 50 CV - I don't understand what the client is in this
scenario, but if you don't have the concept of "client" into your
dimensions, then these are simply 50 new rows in the previous fact
table - right?
NULLs: you have to create surrogate keys for your dimensions. This
allows to create some "dummy" member that represents the lack of a
member for a dimension. I typically create a special row in each
dimension that has the value "<unkonwn>" for each dimension attribute
(using "?" or "<?>" for the application keys). This makes the query on
the fact table easy even with a query builder.
Marco Russo
http://www.sqlbi.eu
http://sqlblog.com/blogs/marco_russo
.
- References:
- Intermediate Fact Table
- From: JordieWilliams
- Intermediate Fact Table
- Prev by Date: Strange behavior of Parent-Child Dimension in SSAS 2005
- Next by Date: Re: Strange behavior of Parent-Child Dimension in SSAS 2005
- Previous by thread: Intermediate Fact Table
- Next by thread: Strange behavior of Parent-Child Dimension in SSAS 2005
- Index(es):
Relevant Pages
|
