Fact slowly changed issue

View previous topic View next topic Go down

Fact slowly changed issue

Post  mek on Wed Feb 13, 2013 6:19 am

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

Posts : 1
Join date : 2013-02-13

View user profile

Back to top Go down

Re: Fact slowly changed issue

Post  chade25 on Sat Feb 16, 2013 5:23 pm

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

chade25

Posts : 29
Join date : 2012-04-12
Age : 37
Location : Oregon

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum