How to start organise the following scenario?

[josephinetedesc]

1. There is a physical Machine - known by a number of different names - it also has a unique number.

2.     The physical Machine - exists in a physical location - known by a number of different names: for example: city, EM, Melb, East Melb,E Melb

3.      The physical machine - has a number of modes of working. Mode1, Mode2, Mode3, Mode4 - and those modes can be categorised as SuperMode1, which cosists of Mode1 and Mode2 and SuperMode 2 which consists of Mode3 and Mode4.

The system I am trying to implement will take values from:

Database1 - this only has the physical location and the superMode and the date and a couple of values.

Database2 - this has the physical Machine, with Mode1, Mode2, Mode3 - and the date and a couple of values.

Spreadsheet 1 - this has location and one mode only and a date and a couple of values.

So do I create a spreadsheet -with all machines, modes, alternative names etc?

Do I create 3 separate spreadsheets - a.machines and all alternative names b. location will all alternative names, c. modes with all althernative names

Tried Trial and error - almost worked.

Looked up incremental loads - that worked well

tried to understand the Date/Data Island - but I think I need to fix the spreadsheet/spreadsheets first.

Ideally I see it as - values from the Databases and spreadsheet - being able to be diced by location, Machine, Mode.

Thank you

Jo

[swuehl]

I think you would need to create a consolidated dimension for phys.machine as well as for phys. location. i.e. fields that translate all different names to one 'common' name per phys. entity, might also be just a technical name not exposed to the user (who will select from the 'different names' field).

I believe someone needs to define this translation table.

Your tables are then linked by the 'common' name field (or maybe a composite key made of 'common' name, date, ...).

I haven't really understand your data model, so if you need more help, I think some sample lines of data would really help to understand.

[josephinetedesc]

so you would create 3 spreadsheets or 1 spreadsheet?

[swuehl]

Not sure, since I haven't fully understood your requirements and data.

Could you upload a small sample or sample data?

[josephinetedesc]

OK so the following works but!  I now need all the dates in the 3 tables, ACHSDateFull, ArchiveDate, DateFull to respond to the Calendar - If I call them all the same name then it causes problems and spaghetti links.  What can I do?  Hardcode the monthname?

[swuehl]

Are you sure you want only a single calendar?

Why You sometimes should Load a Master Table several times

Canonical Date

Tutorial - Using Common Date Dimensions and Shared Calendars

[josephinetedesc]

yes I do not need separate calendars ...

I do not know how I would begin to do a bridging table ... or adata date island ...

[josephinetedesc]

ok this is what i did - I learnt about Resident loads!

but: this leads to synthetic keys ...however figures seem to be in the right range. 

Should I live with the synthetic keys?  How can I get rid of them?

Jo

[josephinetedesc]

I have this: which makes logical sense

but if I try to do this - ie the machine table is joined to the calendar table I get a synthetic key.  I want to  delete the synthetic key how do I do the resident load when information needs to come from 2 tables?

Thank you

Jo

[swuehl · Accepted solution]

You first need to decide if the date field in both tables should really be a key between the two tables, or is it something different.

If it's a key, you can maybe live the synthetic key or create your own concatenated key.

Synthetic Keys

You may also consider changing the data model, for example concatenating the two tables ACHS and machines.

See also my previous link about multiple or single calendar tables.