We have a requirement to load full table data and get only last 7 days data from OLTP to OLAP. My approach is as follows: 1. Connect to OLTP using SSIS and load as a dimension table to store full table records or except last 7 days 2. Have 2 connections (dsn) in SSAS so that we query the Full table from DWH db, and using OLTP DB (dsn) connection we could create a named query for last 7 days which would give me the latest. Is there any better approach to handle this requirement?

What is your requirement exactly? That you have a fact table with all historical data and a fact table with only data for the last 7 days?MCTS, MCITP - Please mark posts as answered where appropriate.

Yes. To handle this whether above said approach will do or is there any better option?

Hi ananthasa, Creating and rebuilding indexes is an essential part of the data loading process, but can be time-consuming for large tables. However, there are ways to create indexes on the freshly loaded data without impacting the data in the rest of the table. SQL Server 2005 provides partitioned tables, and while loading data into a partitioned table, you can have several options. Each option provides its own distinct advantages, but the option you choose depends upon the requirements of your application, such as performance, concurrency, temporary storage, and so on. This document presents the data loading options on a partitioned table and compares them in terms of performance, concurrency and temporary storage requirements: http://technet.microsoft.com/en-us/library/cc966380.aspx Please feel free to ask if you have any question. Thanks, Eileen

Hi Eileen, I wanted to know which is the best approach We have a requirement to load full table data and get only last 7 days data from OLTP to OLAP. My approach is as follows: 1. Connect to OLTP using SSIS and load as a dimension table to store full table records or except last 7 days 2. Have 2 connections (dsn) in SSAS so that we query the Full table from DWH db, and using OLTP DB (dsn) connection we could create a named query for last 7 days which would give me the latest. Is there any better approach to handle this requirement?

depending on the amount of records, having 7 days data in some storage would be better instead of quering everytime for those 7 days data. other queries on that 7 days data would save more prosessing time than querying everytime full table with check. again, it depends your server setup(memory, space, back up settings, recovery model, transaction settings etc). regards joon