Having manually inserted 'Default' rows in DIM tables

Can anyone tell me if a fact table row should have at least one dimensional row for it if the logical relationship between the tables calls for it?

I am working on a commercial insurance project and our approach is for the Fact table to contain values that do not match to a row in the dimension tables, unless we force rows into it. The dimensial tables are for Insurance Agency and Insurance Company. We are not assigning a value for everyone one of these Agencies and Insurancy company columns in the Fact table. These non-assignments are built into Fact tables as 0's for the surrogate keys. During the dimensional table creation, no record for these 0's have been created. The called for fix is to manually add a 'Default' row to the dimension for these to match to.

Does this sound like sound data modeling practices based on Dimensional modeling practices?

If not where can I find literture stating this.

Is there a fundamental issue with building facts that do not get a defined dimension built for it when dimensions are built? How many 'unknows' can you have before the facts fall apart?

Thank you!

Every FK in a fact table should reference a dimension row. A fact table should never have a null FK. Manually creating default dimension rows using a known PK value is one way to handle this.

It is my opinion that a fact load process should be able to accommodate new dimensional references. In other words, if the fact load process encounters a natural key that cannot be found in the dimension, it should create a row in the dimension using that natural key (exception is a null value, you would need to substitute that with a known value (such as 'null') for the natural key. Such a process would atomically create rows as unknown natural keys are encountered.

Fact and Dimensions shares 1-n relationship, that means for each dim row there could be multiple rows in the fact and for each fact row there MUST be one and only one row qualifying for that.

You don't need to enter every now and then NULL rows to the dimension, add a surrogate keys or natural key with value 999 and its description will be N/A or NOT APPLICABLE or whatever you want.
And use this 999 whereever you find it is applicable in fact.

Vishy wrote:
You don't need to enter every now and then NULL rows to the dimension, add a surrogate keys or natural key with value 999 and its description will be N/A or NOT APPLICABLE or whatever you want.
And use this 999 whereever you find it is applicable in fact.

The approach of dealing with unknown natural keys does not result in multiple NULL rows every now and then. It only results in one 'null' row that would be used for all future null rows. What is also does is allow creation of other new dimension rows for natural keys that are not in the existing dimension table. Such situations can occur when timing errors occur between the arrival of the fact and the dimension update (either operationally or user error).

If you handle such situations with a single row with a made-up key you will lose facts in situations when the natural key was valid, but the dimension row wasn't added yet. If you create a row with that natural key and assign the correct FK to the fact, subsequent dimension updates will properly populate the dimension row and the fact will be properly recognized in future queries.

As far as NK values that are actually NULL, you do need to substitute the NK with an actual value, such as "NULL", so that a NK based lookup works properly.