Advice for modelling planned relational data

View previous topic View next topic Go down

Advice for modelling planned relational data

Post  colabus on Wed Jan 23, 2013 4:31 am

Hi All,
I知 hoping someone might be able to offer a little advice for a KPI/measure I知 looking at modelling in a dimension model. The data I want to provide to the business is around operational planning. Periodically through the day our planning team will submit plans out of our plan software which is passed via BizTalk into our operational (execution software). These plans can be sent often throughout the day for a customisable horizon window. I want to provide them that information along with the actual data as completed.

i.e. Planners will tend to submit plan now to weeks into the future, the operational guys who work within a 24 hour window will only go for a day or 2.

If I break down the plan message (being mindful I only want to look at accepted plans), I have something of this schema.
Plan (PlanID, UserID, PlanDate, PlanHorizon)
Plan.Train (TrainNumber, StationPlatform, ETA, ETD)
Plan.Train.Rake (RakeNo)
Plan.Train.Rake.Task (TaskID, StartTime, EndTime, Tonnes)
Plan.DeletedTasks (TaskID)

So for example, a plan is made up of trains that are made up of rakes (or packages) and for each package (as they are of different product) there are tasks associated with them.

So how do I model this?

I知 really at a loss at this stage on the best design and heck the dimensions to use. The data isn稚 really additive or semi-additive.

So I致e decided to work from my immediate need/goal as a proof of concept to see how it goes.
factPlanAnalysis (PlanID, UserID, PlanDate, PlanHorizon, TrainNumberID (FK), StationPlatform, RakeNo, TaskID, StartTime, EndTime, Tonnes, DeletedTaskID)
dimTrain (TrainNumberID (PK), TrainNumber, StationPlatform , ATA, ATD)

My concern with this is that as Train and DeletedTask data aren稚 dependant in the plan I end up with lots of rows in the fact table.

i.e
Plan Data columns.., Train Data columns.., DeletedTasks columns..
1.., Train1, NULL
1.., Train2, NULL
1.., Train3, NULL
1.., NULL , 1000
1.., NULL , 1001
2.., Train1, NULL
2.., Train2, NULL
2.., Train3, NULL
2.., Train4, NULL
2.., NULL , 1002
2.., NULL , 1003

Also, when it comes to the analysis it痴 across the fact AND the dimension so not really a 壮lice-and-dice design.

Has anyone come up against this type of data? Maybe I need to simplify it further and create more fact tables factPlanAnalysis_Trains, factPlanAnalysis_DeletedTasks.

I致e been working through the Kimball modelling book but the KPI/measures are very basic.

Maybe I知 tackling something too complex as a start?

Any help would be great!

colabus

Posts : 1
Join date : 2013-01-23

View user profile

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