Slowly Changing Dimension table

Hello,

I have scenario where we are creating a Dimension Table and Fact Table from the same Source Table which contains
both descriptive and numeric data

I want to model the Dimension table as a Slowly changing Dimension Type 2 (create new records)

I am creating a new record in Dimension table marking the previous one as 0 and new one as 1
my question is when I create a new record in the Dimension table should I also create a new record in the fact table even though
no numeric data has changed

How is this kind of a scenario ideally handled

Example

Source Table
==========
AssetId Assettag Price DateModified
1 ABCD 500 01/01/2010

Dimension Table
=============
DimAstSK AssetIdNatural AssetTag Current_Flag TimeId
888 1 ABCD 1 111

Fact Table
=========
DimAstSK Price
888 500

=====================

Source record changes descriptive information

Source Table
==========
AssetId Assettag Price DateModified
1 XYZ 500 31/01/2010

Dimension Table
=============
DimAstSK AssetIdNatural AssetTag Current_Flag TimeId
888 1 ABCD 0 111
999 1 XYZ 1 222

Fact Table
=========
DimAstSK Price
888 500
999 500

Please advise
Thanks
Kenny

No. A dimensional change does not trigger a new fact row.

What do the facts represent and what are the implications of a change in an assets attributes?

The facts represent the Price for the Asset, Count of CPUs on the Asset,

Asset attributes like AssetTag could be changed as in Retagged

so if I do not create a new record in the Fact table what Facts will my new Dimension record point to

what happens when we report

Type 2 is used to provide dimensional context at the point in time of the fact.

To get the current attribute values from a type 2, you need to self-join on the natural key of the dimension and locate the current row.

There are alternate techniques to avoid the self-join discussed in this forum. One is to maintain a type 1 (i.e. non-changing surrogate key) as an alternate key to the dimension and storing both FKs in the fact.

To get the current attribute values from a type 2, you need to self-join on the natural key of the dimension and locate the current row

Can you please elaborate a little bit, give an example

It's been discussed at length before, but I can't find the link...

Anyway, getting the current row in a type 2 using a self join is as follows:

SELECT ...
FROM yourFact f, yourDimension d1, yourDimension d2
WHERE f.dimKey = d1.dimKey
AND d1.naturalKey = d2.naturalKey
AND d2.currentFlag = true

The d2 row will contain current attributes while the d1 row will contain point-in-time attributes.

Thanks very much sir,

I have one more question, I am trying to make enhancements to an existing datamart design

It is designed in a following manner, for both the Dimension and Fact the same source table is used

Asset Dimension is Type 1 Overwrite, Attributes are (AssetTag, AssetSerialNo)
Asset Fact Table stores Historic changes via 3 columns (CurrentRecord,StartDate and EndDate) other Attributes are Price, CPU_Count

My company wants to change the Dimension to store History as well (Type 2) which means many records on the Dimension,
and Many Records in the Fact table for each record in the Dimension Table

Is this very complex to implement, is there a way I can achieve this, please advise