Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Loading Fact Tables - Step by Step Instructions Challenge Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 9:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
Simply put, your measurement events appear to be:

1. Getting consent (possibly)
2. Taking a course (definitely)

Those events give context to the employee (working in a dept that is part of an ogranization) via a status (that can change based on the number and type of courses taken)? Can the status change and move along finite milestones such as "Not Certified", Certification in Process", "Partially Certified", "Fully Certified"?

The end goal is compliance, which is based on a status Fully Certified in different areas of study such as "Slip and Fall"?

Post #1441329
Posted Thursday, April 11, 2013 9:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:59 PM
Points: 13, Visits: 212
This is correct. My compliance can move through finite steps, however it has to be based on a set of courses having a 'Completed/Passed' status.
Post #1441344
Posted Thursday, April 11, 2013 9:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
Is it important to go back in time and ask:

"What was the compliance status of the department back on 1/1/2009?"

Or, do your end users merely care about there "here and now" state of an employee's status?
Post #1441347
Posted Thursday, April 11, 2013 9:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:59 PM
Points: 13, Visits: 212
Yes, it is important to know previous years deparment's compliance for comparison and trending and later on predictions.
Post #1441348
Posted Thursday, April 11, 2013 10:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
Would you say the trending is fixed (as in merely year to year)? Or, do the end users need to be able to compare any periods to any periods (i.e. this quarter to last year, this month to last month, etc)?

Also, is there a percentage compliance or a Key Performance Metric you must meet (say 80% Cardiac Nurses must be compliant by 2014--or "employee X must be 100% compliant by the end of the quarter or they're outta here!")? Are those metrics published at all?

There are some designers that would put all the employee's compliance buckets into columns in the employee dimension (if those buckets were fixed) and, as part of the ETL process, update those buckets based upon the courses taken (courses stored in the fact table) or not taken (courses required but not existing in the fact table). It's called the "When A Dimension Becomes A Fact" concept.

Joy Mundy of the Kimball Group wrote a nice concise article. In relating to this article, think of your employees as the entity and the courses taken as the measurement event.

http://www.kimballgroup.com/2011/11/01/design-tip-140-is-it-a-dimension-a-fact-or-both/
Post #1441356
Posted Monday, May 13, 2013 6:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 820, Visits: 2,117
tinyint is one byte, smallint is 2 bytes. Review how many values you can have for each (256 and 65K+ respectively) and use the right one for every dimension.


I support this concept, but at least in SSAS 2005 the tinyint can't be made into a connection in the DSV. So I avoid it even if it would have been a good choice. For those, the smallint isn't much of an increase and allows you to use negative numbers for the Unknown and (if needed) Not Applicable Bucket.



Post #1452076
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse