2

I am building an OLAP database and am running into some difficulty. I have already setup a fact table that includes columns for sales data, like quantity, sales, cost, profit, etc. The current dimensions I have are Date, Location, and Product. This means I have the foreign key columns for these dimension tables included in the fact table as well. I have loaded the fact table with this data.

I am now trying to add a dimension for salesperson. I have created the dimension, which has the salesperson's ID and their name and location. However, I can't edit the fact table to add the new column that will act as a foreign key to the salesperson dimension.

I want to use SSIS to do this, by using a look up on the sales database which the fact table is based on, and the salesperson ID, but I first need to add the Salesperson column to my fact table. When I try to do it, I get an error saying that it can't create a new column because it will be populated with NULLs.

AHiggins
  • 7,029
  • 6
  • 36
  • 54
Bix Shmix
  • 89
  • 1
  • 6
  • Is it just an order of operations issue? Can you create the column, then populate it, then make it a foreign key and change it to not allow NULLs? – Tab Alleman Aug 27 '14 at 17:42

2 Answers2

2

I'm going to take a guess as to the problem you're having, but this is just a guess: your question is a little difficult to understand.

I'm going to make the assumption that you have created a Fact table with x columns, including links to the Date, Location, and Product dimensions. You have then loaded that fact table with data.

You are now trying to add a new column, SalesPerson_SK (or ID), to that table. You do not wish to allow NULL values in the database, so you clear the 'allow NULL' checkbox. However, when you attempt to save your work, the table errors out with the objection that it cannot insert NULL into the SalesPerson_SK column.

There are a few ways around this limitation. One, which is probably the best if you are still in the development stage, is to issue the following command:

TRUNCATE TABLE dbo.FactMyFact

which will remove all data from the table, allowing you to make your changes and reload the table with the new column included.

If, for some reason, you cannot do so, you can alter the table to add the column but include a default constraint that will put a default value into your fact table, essentially a dummy record that says, "I don't know what this is" 

ALTER TABLE FactMyFact
ADD Salesperson_SK INT NOT NULL 
CONSTRAINT DF_FactMyFact_SalesPersonSK DEFAULT 0

If you do not wish to put a default value into the table, simply create the column and allow NULL values, either by checking the box on the design page or by issuing the following command:

ALTER TABLE FactMyFact
ADD Salesperson_SK INT NULL

This answer has been given based on what I think your problem is: let me know if it helps.

Community
  • 1
  • 1
AHiggins
  • 7,029
  • 6
  • 36
  • 54
  • Wow, that is exactly my problem. Sorry, I couldn't explain it very well. Your first suggestion is also what my first idea was, but I am working with ~20 million rows, so reloading all of the data each time a new dimension is added will probably not work out once this goes live. Your other suggestions make sense, but would I still run into problems when loading data in SSIS? If there is a value of 0 for each row, will it overwrite it with the actual lookup value? – Bix Shmix Aug 28 '14 at 19:48
  • Also, is truncating the fact table and reloading it with the new columns a fairly standard practice? I've been trying to solve this problem and haven't seen anything explaining the best practice for adding additional dimensions after creating the fact table. – Bix Shmix Aug 28 '14 at 19:51
  • 1
    @BixShmix, you would have to configure SSIS to check for updates to the column, or else run a one-time, manual update statement to go through and assign a value for each of the already existing rows ([read point #3](http://www.kimballgroup.com/2001/10/design-tip-29-graceful-modifications-to-existing-fact-and-dimension-tables/)). I was hoping this was still in dev, so truncate/reload would be easy. If you have any non-recreatable data (such as an SCD2 column with history), then truncate is off the table. – AHiggins Aug 28 '14 at 19:55
  • Worst-case, you can create a dummy record in the SalesPerson dimension with an ID of 0 that says "Unknown" - best case, you can configure your SSIS package to check for changes and overwrite the dummy data in the fact table – AHiggins Aug 28 '14 at 19:56
  • Okay, this is great. Thanks for taking the time to answer. – Bix Shmix Aug 28 '14 at 20:08
  • More than happy to help! I've suggested an edit to your original post that I feel might reword it to point toward the actual problem. From reading the original, it sounded like your problem was configuring SSIS to load the new column, and it wasn't clear that your issue was actually with adding the column to the SQL table. Including the SSIS info was great, but in this question (and future ones as well) you want to be extra clear on where exactly you are having a problem. Posting exact steps taken and error messages received would be even better. Welcome to Stack Overflow, and have fun! – AHiggins Aug 29 '14 at 12:47
-2

Dimension inner join with fact table, get the values from dimensions and insert into fact...

or else create the fact less fact way

James Morris
  • 4,867
  • 3
  • 32
  • 51
Adi
  • 232
  • 1
  • 9