I have a table in my SQL database (Dimension) that I would like to populate with data, the table structure is as follows.
CREATE TABLE [dbo].[DIM_PRODUCT](
[product_id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[item_category] varchar(4) NULL,
[variety] varchar(2) NULL,
[variety_short] varchar(15) NULL,
[commodity] varchar(2) NULL,
[commodity_short] varchar(4) NULL,
[lot] varchar(3) NULL,
[deal] varchar(3) NULL,
[exporter] varchar(6) NULL,
From the Source system, we dump all the data into a large CSV File, about 6 million rows, using SSIS I am able to load this to a dump table.
Now From the dump table, I want to load my dimension above, the dump has over 100 columns with 6m+ rows of records, but the ones I am interested in are above and I want to get only the unique/distinct values from the dump, then I could later load my fact tables from the records in the dump file and use fk/sk keys to link it accordingly.
This is so that when I load my fact table below:
CREATE TABLE [dbo].[FACT_SALES](
[fk_product_id] int NOT NULL,
[2LYR_volume] money NULL,
[qe_fob] money NULL,
[oe_freight] money NOT NULL,
[oe_brokerage] money NOT NULL,
[oe_other] money NOT NULL,
[tt_fob] money NOT NULL,
[net_fob] money NOT NULL,
[average_gross] money NOT NULL,
[total_income] money NOT NULL,
[total_revenue] money NOT NULL,
[accrued_income] money NOT NULL,
ALTER TABLE FACT_SALES
ADD CONSTRAINT FK_DimProduct FOREIGN KEY (fk_product_id)
I can do a look-up on the foreign key in the fact table when inserting my fact data.
Hopefully someone can show me how to do this. If you are unclear of what I am trying to do please ask the question i will try to clarify as best I can.
I would like to learn a method so that I can pull different data into different dimensions from the dump file (Geographical Dimension, Sales Person/Office/Divison Dimension, etc.)