My source is single table from a database having multiple columns with millions of records, data will be increase on weekly basis.
can anyone please suggest me the best data model to handle this data, is it fine to split the table and create linked table, will this approach be correct??
If you don't have key fields then don't split the table maintain it in a single table.
If you have
CountryID, Country, Date, Sales then split Country dimension table, if your data not in this format then do it with a single table. Don't complicate the model.
I can implement the incremental load later but now while starting the data model how should I approach, is it fine to divide 1 source table into multiple dimension and fact tables and then using link table, can you please suggest me on data model
If u hav the all flag(Insert and update) and Datemodified fields in ur fact table u can use the incremental load.
otherwise split the fact table in to several qvd using Year week, and update the last week qvd alone and concatenate the all qvd into one and use this qvd into ur document.
So that: Split into qvd like.
and update the last week qvd,
Load * from Fact_Table where Yearweek(Date_field)=Yearweek(Today())
Store tab into Fact_Table_2014-03.qvd.
And Concate the all qvd into one.
Load * from Fact_Table_*.qvd