Suggested Model Changes

Thu, 16 Jun 2011 07:28:06 GMT

Hi All,

This is my first model that I have built using Qlikview and I have posted several times regarding issues faced - all of which the Community has helped me to overcome. I am 20% through reading "The Data Warehouse Toolkit" by Ralph Kimball and that is helping my understanding of dimensional modelling. But I am still a little stuck on the best route for redesigning (if I have to, but suspect I will) my current model.

It is in a warehousing environment and is related to warehouse operational efficiency. As such the following dimensions are at play:

1. There are sites which contain locations (for pallets / products)

2. There are principals who are serviced by departments (a department can service multiple locations)

3. There are jobs performed by departments for principles by acting on products in locations (importing into the w/house or exporting largely)

4. There are hours worked by department

5. There are storage snapshots which look at the volume of products across location for each principal

6. I need to work out efficiencies such as:

a) Physical Space Efficiencies

i) Location Utilisation

ii) Volume or Space Utilisation

b) Manhour Utilisation

i) Various measures as ratios of normal vs overtime

ii) Permanent vs Casual Labour

iii) etc

c) Storage Snapshots

i) Highest Hourly Snapshot vs Last Day Snapshot (CBM comparison)

d) Overall Departmental Efficiencies

i) Manhours per CBM

There are also the normal date issues to contend with such as job dates, timesheet hour dates and storage dates. I can see my model has multiple fact (potentially linked) tables with some clear dimensions such as site, location, principal, department.

I have pretty much all the data I require, but because of the poor (inexperience) design, I am struggling to link the reported timesheet hours by department to the volume processed by principal in the jobs. This is crucial tio be able to do simply to allow for the various departmental efficiencies.

I would truly appreciate any constructive input of my problem. Should I have multiple fact tables? Should I try and force the existing information into a single fact table with an indicator as to what the row represents? I have seen a chapter later in the book talking about a data warehouse bus architecture . . . I haven't fully read and understood it yet, but having scanned through it, it seems that another valid suggestion might be multiple warehouses for separate processes? So should I in fact have two or three separate applications, each of which handles a separate process?

Really interested to see what the community can offer on this.

Regards

Ian

topic Suggested Model Changes in QlikView

Suggested Model Changes