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 Wednesday, April 10, 2013 8:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:59 PM
Points: 13, Visits: 212
For the most part, yes. It is geared to L&D rather than clinical systems.
Post #1440822
Posted Wednesday, April 10, 2013 8:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
Can you attach your target schema? That is, the dimensions surrounding the fact table you are loading along with the fact table itself?

Also, think about what actual "events" you are measuring and the core business process(es) surrounding the event. More often than not, the "verbs" of your business represent the facts, and the dimensions represent the "nouns" or the "group bys" of your business.

More often than not, if you have to "group by" it, it is going into a dimension--a dimension that is usually denormalized. If you have to aggregate it, chances are it is a fact. While there are exceptions and advanced concepts where these rules do not apply, try to uses these rules of thumb when mapping your schema back to your business requirements.

Post #1440855
Posted Wednesday, April 10, 2013 9:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 2:32 PM
Points: 535, Visits: 1,630
I am struggling to understand how your diagram shows any relationships.

Is it simply a statement of facts about people?
Does the presence of a row in the HandHygiene table mean that the person either did or did not comply with hand hygiene rules?

Would your fact table look something like this?

DelegateID INT,
ReportDate DATE,
HasConflictResolutionEvent TINYINT,-- (1 - YES, 0 = NO)
CompliesWithHandWashing TINYINT,
CompliesWithInfectionControl TINYINT,
CompliesWithViolenceAggression TINYINT,
HasMentalCapacityTraining TINYINT,
etc.

With Dimensions:
Date
Division/Department/Role/Delegate
Post #1440903
Posted Wednesday, April 10, 2013 9:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:59 PM
Points: 13, Visits: 212
the diagram is not of the dimensions and fact table but of the OLTP at the second stage.
Stage 1. RawData into generic table
Stage 2. Data cleaned and loaded which should transform to dimensions.

The fact table data is has also been loaded into a table with a uniquie key as well.

It is a statement of facts about people.
The Fact Table will be exactly that.

However, I will also need to aggregate on Department, StaffGroup etc.
There will be KPIs and Trends for each of the topics. But that is the easy/ fun part (I hope )

Hope this helps.
Post #1440907
Posted Wednesday, April 10, 2013 9:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
What are the top 10 - 20 business questions you are attempting to answer?
Post #1440912
Posted Wednesday, April 10, 2013 10:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:59 PM
Points: 13, Visits: 212
The top 10 are:
1. What training has each staff member done or need to do
2. Is each staff member compliant
3. When next do they need to do their training
4. Have each department, staff group and organsation as whole met the KPI set out by the organisation
5. Can we monitor the percentage compliance by department and topic
6. What is the trend monthly, quarterly, yearly etc
7. Can we compare present data to historical data
8. Can we make predictions based on the data

These are the main questions for the moment.
Post #1440921
Posted Wednesday, April 10, 2013 12:06 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
Attendence/Course work is usually modeled by a methodology called a "Factless Fact Table". It may be appropriate for what you are trying to do.

Here is an article/design tip by the Kimball Group on how to model this as such. While it is not EXACTLY the same veritcal industry, it is a similar concept. Read this and see if stimulates some ideas to help you connect the dots.

http://www.kimballgroup.com/1996/09/02/factless-fact-tables/

Also, it sounds to be as though there is an opportunity to create an Employee dimension with a hierarchy to enable you to roll up totals by department. Let me know if you'd like the design tips for modeling department hierarchies for employees.

Additionally, there are tools out there for creating a more robust date dimension. The Kimball Group also has an excel spreadsheet that you can load in order to do this.
Post #1440970
Posted Wednesday, April 10, 2013 6:59 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:42 PM
Points: 176, Visits: 528
Very Good point - what are the top questions.

Starting at question 1:

1. What training has each staff member done or need to do

This implies the following objects:

Some kind of Staff Member dimension
Some kind of Certification dimension
As usual, there is a Time dimension involved here
Some kind of fact to describe the historical and possibly planned events and metrics around Staff and Certification

What training do they need to do? This would appear to boil down to an actual vs target situation. If they are at the same granularity then it might make sense to put the 'actual' and 'target' (and many other targets otherwise) into the same fact.

But it appears you are already some way down the design path. and your current issue is loading the fact.

Do you want to explain your issue in more detail and we can help.


Post #1441079
Posted Thursday, April 11, 2013 3:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:59 PM
Points: 13, Visits: 212
I have been able to upload the dimensions that I will be using (most of them covers the suggestions given).

My process. I have several source data tables.

I create a table called Delegate which has as the source key of employee number.
I then create a surrogate key called DelegateID to use in my Delegate dimension
In all my other tables I create I am injecting the surrogate key DelegateID.
e.g.
SELECT
[Staff Group]
,DelegateID
,GroupTypeID
FROM
(
SELECT DISTINCT
[Staff Group]
,del.DelegateID
,ROW_NUMBER() OVER(PARTITION BY esr.Employee ORDER BY DelegateID) AS RowNum
,CASE WHEN [Staff Group] IN ('Administrative and Clerical', 'Estates and Ancillary') THEN 2
WHEN [Staff Group] IN ('Allied Health Professionals'
,'Add Prof Scientific and Technic'
,'Nursing and Midwifery Registered'
,'Additional Clinical Services'
,'Healthcare Scientists') THEN 1
WHEN [Staff Group] = 'Medical and Dental' THEN 3
ELSE 0
END AS GroupTypeID

FROM ESR esr JOIN Delegate del
ON esr.Employee = del.Employee) x
WHERE RowNum = 1

The surrogate key for Delegate dimension is in the StaffGroup query. When I create the StaffGroup dimension, the StaffGroupID surrogate key will be created by IDENTITY.
My dimension will look like:
StaffGroupID
DelegateID
GroupTypeID
StaffGroup

Hope that is clear.

Now, in creating the Fact Table, I am using the DelegateID as the anchor for the composite set of surrogate keys for for my fact
e.g. of what should happen when the fact table is loaded is (made up data) :

DelegateID, StaffGroupID, ConsentID, SafeguardingChildrenID, DateID, Status (fact)
1 , 1, 4, 0, 1200, 1
1, 1, 0, 5, 14, 1
0 meaning NA.
Hope this too is understandable.

The problem I am facing is that when I try to load the fact table by using a SQL join I am getting data that doesis not correct. An example would be:

Delegate 1 has done Consent and SafeguardingChildren and so on the JOIN, I will get four records
Consent and the ConsentDate
Consent and the SafeguardingDate
Safeguarding and the SafeguardingDate
Safeguarding and the ConsentDate
Two of these records are incorrect. Perhaps my JOINS are not correct

I tried using the LookUp function in SSIS but am running in to problems with the LookUps as it only loads some of the data. I am also not sure what to look up against. I am assuming it is the Delegate Table.

This is where I come to a standstill.

I hope this is clear and makes sense to others apart from myself.

I would like to thank you all again for taking time to help me. I really do appreaciate and I am reading a lot and learning a lot as well.

Regards,

Marvin.
Post #1441155
Posted Thursday, April 11, 2013 9:04 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 2:32 PM
Points: 535, Visits: 1,630
Delegate 1 has done Consent and SafeguardingChildren and so on the JOIN, I will get four records
Consent and the ConsentDate
Consent and the SafeguardingDate
Safeguarding and the SafeguardingDate
Safeguarding and the ConsentDate
Two of these records are incorrect. Perhaps my JOINS are not correct



For Delegate1, how many rows do you want to insert?

1 rows with two measure values and two measure dates.
2 rows each with a measure and a date and a measure type?

If 1 row, then you need some kind of pivot, perhaps insert the delegateID and update Consent columns, then the Safeguarding columns (in a staging table).

If 2 rows then maybe you need separate inserts. Join Delegate to Consent and insert. Join Delegate to Safeguarding and insert.

Post #1441308
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse