I'm creating a warehouse using SQL Server 2008 and Analysis Services. I've managed to create and populate the dimension tables, but I'm having a lot of trouble writing the SQL for loading the fact table. For one thing, I'm not sure how to load the keys of the fact table with the PKs from the dimension table. I tried writing a query that had a series of JOINs to get the keys and the measures I want, but the statement got so complicated that I got lost.
This is the star schema that I have to work from:
What am I doing wrong? I have a feeling that I'm missing something pretty basic, but I'm fairly new to this and most of the information I found online seemed to deal with using SSIS, which I don't have installed.
Any help would be appreciated.