Fact slowly changed issue

hello,

i'm designing Data mart for the billing system the FactInvoices contains columns related to the invoices and it's invoices items sample of the columns as the below :

FactID
InvoiceNumber
InvoiceIssueDate
PorductID
Amount
PaidStautsID
.
....

now some of the columns changes over time for example the invoice issued at 1-1-2013 and paid at 3-1-2013 so i need to make some kind of slowly change type 2 at the fact table to keep that historical changes so i added 3 columns at the fact ValidTill , Validform and Iscurrent

my questions are :

1- is that valid solution or i will face trouble at the SSAS ?
2- i need to know the best practice to trace changes over time at business process (billing ,ordering fulfillment ) like invoice , work orders and trouble shooting tickets which had daily changes in its fields like Group ,status,handling agent , priority ,..... ?


thanks
Mohamed

mek wrote:hello,

i'm designing Data mart for the billing system the FactInvoices contains columns related to the invoices and it's invoices items sample of the columns as the below :

FactID
InvoiceNumber
InvoiceIssueDate
PorductID
Amount
PaidStautsID
.
....

now some of the columns changes over time for example the invoice issued at 1-1-2013 and paid at 3-1-2013 so i need to make some kind of slowly change type 2 at the fact table to keep that historical changes so i added 3 columns at the fact ValidTill , Validform and Iscurrent

my questions are :

1- is that valid solution or i will face trouble at the SSAS ?
2- i need to know the best practice to trace changes over time at business process (billing ,ordering fulfillment ) like invoice , work orders and trouble shooting tickets which had daily changes in its fields like Group ,status,handling agent , priority ,..... ?


thanks
Mohamed

I dont fully get the issue. You can have a time dimension with dates and a lot more to better describe the dates, so if you had bill date and paid date, you would have 2 foreign keys when they change it will insert a new record and keep the history as you want. So, if a customer makes multiple payments that date is recorded on the fact table with the amount, and since that date is a foreign key to the dimension, it will insert a new record. But be cautious, if you are recording two business events in one fact table, then you want two seperate fact tables maybe with common dimensions between them.

-Nathan