I'm creating a data model following a specs document that says I need to stretch a single table to a fact table with multiple dimensions. So I now I have a fact table with of course multiple dimensions, each dimension has an ID. Now I need to connect these dimensions to the fact table.
What I originally had in mind was the following: -CREATE DIMENSIONS (With fields: Id,DimensionName)- -CREATE FACT TABLE (With every field from the single table; Fields: FactName, Dimension)- - TempTable: LOAD Id as DimensionId Resident Dimension; - (create a temporary table to store the ID of the dimension) Concatenate Load * Resident FactTable WHERE Dimension = DimensionName-
To explain further what I'm trying to do is: to add the ID of the dimension to the FactTable with a where clause that compares the non-id-field of the Dimension with the field in the FactTable and then replace that field in the FactTable with the corresponding ID that references to the ID in the Dimension table.
When I try to load the data the output shows: "Table: 'FactTable' Not found: Concatenate Load * Resident FactTable WHERE Dimension = DimensionName"
Does anyone know how I can solve this problem better? Thank you!