Dimension or Fact?

View previous topic View next topic Go down

Dimension or Fact?

Post  ryno1234 on Wed Jan 07, 2015 10:50 am

I'm new to dimensional modeling and am currently working my way through "The Data Warehouse Toolkit". I'm having trouble wrapping my head around whether or not the concept I'm trying to model is a dimension or fact. The scenario is below:

"Staffing Company" receives a "requisition" which is nothing more than an order from "Company A" for "Staffing Company" to find x number of candidates to place at "Company A". These requisitions can be open for quite a while as it takes a long time to completely place candidates at "Company A" (could takes months or even longer).

A requisition has the following attributes:

  • The number of open positions
  • The title of the position
  • The client / company that the requisition belongs to
  • Date Received
  • Date Filled
  • Pay Rate
  • Bill Rate
  • The location that the candidates will work at (City / State / ZIP)
  • Current status (Open, Filled, Cancelled, etc)
  • etc.


I can see this being both a fact (as a pipeline) or a dimension. I can see it being a dimension because there are other facts that would relate to it, for example: A candidate can apply to a requisition.

This makes it difficult for me to determine the "what" of what this actually is that I'm trying to model.

As a side note, if this is a dimension, what would I do with the fact that the dimension would carry attributes for my Client (client name, client address, client type, etc. etc. etc) which are all attributes of a client dimension I already have (not to mention location attributes, time attributes, etc). Given that I don't want a snowflake schema here, I need to figure out what the best course of action is with these attributes.
avatar
ryno1234

Posts : 33
Join date : 2015-01-07

View user profile

Back to top Go down

Re: Dimension or Fact?

Post  BoxesAndLines on Thu Jan 08, 2015 1:26 pm

Yep, Requisition is just another name for Order. Put the requisition id as a degenerate dimension on your fact table. Take the other requisition attributes and allocate to other dimensions. What type of fact you choose is dependent on what you're trying to measure.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimension or Fact?

Post  ryno1234 on Thu Jan 08, 2015 1:34 pm

Thank you for the reply! It's much appreciated, especially given I'm trying to cut my teeth on this.

This begs the question then: If the "requisition" is considered a fact, and I need to break out the idea of a requisition into many different dimensions (req status dimension, hiring manager dimension, etc), that means I will need many different dimensions together to identify the complete view of a "requisition". Given this, that means if I have other fact tables which all relate to a requisitions, I will need to include each of these dimensions there as well. I can see this becoming very overwhelming to have to continuously "reassemble" the idea of a requisition over and over again for various different fact tables. Not only would I have to continuously model this, I would have to maintain changes to dimension ids, etc. across these many different fact tables. It seems my management just went through the roof.

Am I correct in my perception of this? Is this just the nature of the beat? Perhaps I do not need to reassemble the full concept of a requisition in every fact table, maybe I just need to reassemble part of it.

Any help would be appreciated.
avatar
ryno1234

Posts : 33
Join date : 2015-01-07

View user profile

Back to top Go down

Re: Dimension or Fact?

Post  BoxesAndLines on Thu Jan 08, 2015 1:42 pm

That's the nature of the beast. I only see 3 or 4 dimensions from what you listed. Think of the how easy it is to drill into your metrics by hiring manager now using your role named Employee dimension or calculating average rate at a given client. Easy as pie!
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimension or Fact?

Post  ryno1234 on Thu Jan 08, 2015 2:20 pm

Thanks BoxesAndLines!
avatar
ryno1234

Posts : 33
Join date : 2015-01-07

View user profile

Back to top Go down

Re: Dimension or Fact?

Post  ryno1234 on Tue Jan 13, 2015 2:29 pm

What would be the method of tracking the a "status" which changes over time?

I'd have a dim_requisition_status table which would hold the statuses possible and I would stamp the id into the fact table for my requisition, however as the status changes over time, I'd want to reflect that. How would one go about doing that in this situation?
avatar
ryno1234

Posts : 33
Join date : 2015-01-07

View user profile

Back to top Go down

Re: Dimension or Fact?

Post  ron.dunn on Tue Jan 13, 2015 2:38 pm

Get a copy of 'Star Schema: The Complete Reference' by Christopher Adamson.

Chapter 11 has a worked example of an Accumulating Snapshot Fact Table, which I think is almost identical to your scenario.

This book, and other good dimensional modelling references, can be linked through my Dimensional Modelling Bookshelf.

ron.dunn

Posts : 55
Join date : 2015-01-06
Location : Australia

View user profile http://ajilius.com

Back to top Go down

Re: Dimension or Fact?

Post  ryno1234 on Tue Jan 13, 2015 6:29 pm

Thank you Ron for your response.
--------------
After more thought, I'm feeling increasingly opposed to the concept of this Requisition being a Fact - not because I feel I know better, but because it just feels wrong everytime I try to design it as a fact and deal with the extenuating circumstances and management points around that.

The Requisition is a long running process, true, but many of the things associated with the requisition do not have their own master tables in the source system making it difficult to understand how changes should be reflected in the DW for that field's equivalent dimension table due to a lack of a natural key.

From the source systems perspective:
* There is not master "Hiring Manager" table in the source system, this is merely free text directly associated with the Requisition so it's impossible for me to tell if a change in the "hiring manager" should trigger an update in the DW's dim_hiring_manager tables or if a new key should be generated with a new entry.

This is just one example, but pretty much almost all my attributes are highly correlated to the requisition itself. In addition, given that fact's aren't supposed to be updated, I'm struggling with the highly updateable nature of a requisition, again making it feel like a dimension to me. It nearly all cases it feels "right" for me to say a Requisition is a dimension, or at least both a fact and a dimension. Is this even possible? I've been racking my brain for days on end over this issue and am in paralysis with my design

Any help would be *greatly* appreciated.
avatar
ryno1234

Posts : 33
Join date : 2015-01-07

View user profile

Back to top Go down

Re: Dimension or Fact?

Post  nick_white on Wed Jan 14, 2015 8:47 am

In my opinion, your starting point should be what your reporting requirements are, rather than how to model your source system as a dimensional model.

Once you've understood how your business want to report on/analyse their data, you can design a dimensional model that best supports this and once you have your dimensional model you can worry about how you will populate it from your source data.

If you start with the approach of "I have a transactional data model that I need to convert into a dimensional model", which it sounds like what you are doing, then when you encounter the sort of issues that you mention that are then very difficult to solve - as you are not focussing on the right aspect of the issue.

So when you look at the requisition process, what are you trying to measure? What are the values you want to count, sum, aggregate, etc.
Once you've identified these measures then what are the attributes that you want to be able to filter or aggregate these measures by? When you group these attributes by the common key(s) that identify them, that will give you an initial cut of what your dimensions should be.

Hope this helps?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Dimension or Fact?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum