I don't understand enough about your technical issue with lookups, but for starters be aware that under default settings it is case sensitive.

You generally would not load a source system key into a fact table. Source system keys usually go into the dimensions (or a prior merge/mapping table before the dimension).

What you should be doing is loading dimensions (which often will not necessarily map 1-1 with lookup tables in the source system), and saving the source system key in here, and generating a new 'surrogate key' in the process.

That's your first step.

Next step is to load transactions from the source, then work out what dimensions these transactions relate to and then load that into the fact table.

The result is a fact table that only has surrogate keys in it. All of the complications around changing attributes, multiple source systems, data cleansing, multi column keys, strange data models etc. are handled by the ETL and the dimension table. All of these complications are boiled down to the single surrogate key that joins neatly to your fact.

Hopefully the schema you posted is the source system, not the data warehouse? While you are waiting for your data warehouse book, there are plenty of resources online about star schemas and Kimball.

When building your star schema you need to think about the business process, i.e. what events happen over time and in what way are they measured? - that's your fact(s).

If your source system records these events in multiple tables in a complicated data model, your challenge is to boil this down to a nice fact/dimension representation.