Dimensional Modeling for WorkGroup Management in Electrical Utility space.

View previous topic View next topic Go down

Dimensional Modeling for WorkGroup Management in Electrical Utility space.

Post  Lceeba on Fri Sep 30, 2011 11:31 pm

Hello There,
Here is a typical scenario where hierarchical multi-project, multi-groups, multi-activities, multi-tasks done by several departments, multiple resource types, multi-skill types, multi Organizations in a large Electrical Generation/TRansmission industry.

How would you approach and where would you start to keep it simple/sensible (KISS) and flexible.

Projects, Project Activity, Work Order, Work Tasks, Task Types, Departments, Groups, Resources, est efforts, actual efforts, est cost, actual cost etc., etc.,

There are estimated start and end, actual start and end dates at each level (Project/Activity/Work Order/Work Task)
It should be simple enough to Roll up and Roll down at any level.

Thx
Lokesh

Lceeba

Posts : 7
Join date : 2011-09-30

View user profile

Back to top Go down

Re: Dimensional Modeling for WorkGroup Management in Electrical Utility space.

Post  ngalemmo on Sat Oct 01, 2011 2:25 am

If you want simple, do it dimensionally.

Anyway, done it before. I've also did a workforce management subject area for HR. They were integrated so they can analyze workloads during severe weather events, staffing levels, use of contractors, hiring pipeline, retirement/turnover, etc...
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Bridge tables that would be effective in design

Post  Lceeba on Sat Oct 01, 2011 9:29 am

It has to address following business needs

1) Resourcing pinch points/Availability by Skill type, Resource type in that area
2) External Vs. Internal employees
3) Field Force productivity
4) Schedule Deviation
5) Efforts Deviation
6) Materials lead time (Material Availability)
7) Actuals Vs. Estimated Budget
Actuals Vs. Estimated Cost
9) Actuals Vs. Estimated Efforts
10) All Planned vs. Unplanned Activities.
11) Historical Analysis for Unplanned activities.

Some of core dim tables I thought was Project, Work Order, Work task, Cost Center, Location Hierarchy, Resources, Skills, Status, Electrical Asset (blown up dim)

Every Project Activity, Work Tasks has Actuals vs. estimates captured (time, labour, cost, budget etc.,)
It will have a future In_service_Date like 10 weeks or 53 weeks future depending up on the scale of the project. Then the Application tool builds all Backward Schedules.

The biggest problem is there is no single unified work force management built. It is all in Silos and spread across different applications or spread sheets for that Business Unit.


Thx
Lokesh

Lceeba

Posts : 7
Join date : 2011-09-30

View user profile

Back to top Go down

Re: Dimensional Modeling for WorkGroup Management in Electrical Utility space.

Post  ngalemmo on Sat Oct 01, 2011 11:03 pm

Not sure you need a work order dimension, probably just a degnerate dimension would do. Your also best off storeing estimates and actuals in different facts.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

What is a Degenerate Dimension? Have sketched out a sample Dim and Fact table.. please comment..

Post  Lceeba on Sun Oct 02, 2011 12:01 am

Can you please give me some examples of a degenerate dimension?

Need to have the flexibility to roll down from highest level project to the lowest level tasks across all business groups and departments.

We have work order levels hence had sketched out wo_dim like this ? Any comments ?

Work Orde Dim

WO_SK
WO_Number
WO_Desc
WO_Created_Date
WO_Type
WO_Priority
Parent_WO_Id
Parent_WO_Desc
Maint_Activity_Type
Cause
Component
Corrective_Action
Outcome_Cd
Outcome_Desc
WO_Lvl_No
Parent_WO_Lvl_No
Lvl5_WO_No
Lvl4_WO_No
Lvl3_WO_No
Lvl2_WO_No
Lvl1_WO_No

Work Order Efforts

WO_SK
Location_SK
Target_St_Date_SK
Target_End_Date_SK
Actual_St_Date_SK
Actual_St_Time_SK
Actual_End_Date_SK
Actual_End_Time_SK
Status_SK
Ticket_SK
Project_SK
Outage_SK
Maint_Plan_Id
Planner_Grp_SK
Work_Centre_SK
Cost_Centre_SK
Est_Mat_Qty
Act_Mat_Qty
Est_Lab_Effort
Act_Lab_Effort
Est_Lab_Cost
Est_Mat_Cost
Est_Tool_Cost
Est_Service_Cost
Act_Lab_Cost
Act_Mat_Cost
Act_Tool_Cost
Act_Service_Cost
Other_Est_Cost
Other_Act_Cost
Tot_Est_Cost
Tot_Act_Cost

Thx
Lokesh

Lceeba

Posts : 7
Join date : 2011-09-30

View user profile

Back to top Go down

Re: Dimensional Modeling for WorkGroup Management in Electrical Utility space.

Post  hang on Thu Mar 22, 2012 9:01 am

Now I have seen the real beast!

I know it's an old topic, but recently I have happened to have a chance to look at some DW models in electric utility industry with almost exactly the same set of data elements mentioned in this topic. In addition, we also have work request and asset inspection as parts of the picture.

I guess the most difficult but critical aspect in modeling this business case is to identify the fact correctly. Kimball pointed out in his book that fact table should be far bigger than dimension tables, generally with difference in orders of magnitude. I wonder how many models have been riddled with off-balanced fact-dimenson stars.

However, I have seen in our model, work order, work request, estimate, outage are all modelled as dimension tables of massive size as they all contain rich set of attributes that need to be referenced by other fact tables in the name of dimension conformance.

I agree with ngalemmo that work order should be treated as a fact, so should other factual events listed above.

Anyway, if we model work order as fact table (maybe accumulating snapshot), what about all the attributes related to work order? They are dimensions surrounding work order fact. What is work order ID then? A degenerate dimension (DD) in the work order fact table.

What if other fact tables need to reference work order and it's relevant attributes (dimensions)? I would say put the work order ID in those fact tables directly as the connection (conformance point) to a work order star rather than a single work order dimension table with denormalised attributes.

Remember what Kimball said, the fact table should be 3NF and dimension table 2NF. So work order fact table should be normalised with all the relevant repeating FKs instead of textual attributes. Why? Because fact tables are big and it is far more significant to save space there than in dimensions, and that's also why dimension tables are 2NF, or denormalised per se.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Dimensional Modeling for WorkGroup Management in Electrical Utility space.

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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