Re: Design choice in the Use of SQl Server Views for ETL
- From: "Jeje" <willgart@xxxxxxxxxxx>
- Date: Mon, 20 Aug 2007 20:21:12 -0400
backup/restore is a good option.
you can also create simple SSIS packages to copy your new data between your servers. this method allows you to copy only required content and also give you the option to create different indexes on the MCopy server.
"RK" <RK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:C646D12B-2C7C-4AA4-8556-0F561F2C8337@xxxxxxxxxxxxxxxx
Hello jeje,.
The queries (or the views) that need to be built will be fairly complex and
will involve a large number of records. Now coming to moving data from M to
MCopy, it will not be done using views. Since M is in a SQl 2000 server and
MCopy will be in a SQl 2005 server, I cant use Log shipping to do this. I am
planning to do a full backup and then regular differential backups of the
database M. I shall then create some SQL jobs based on some T-SQl script
which will then do a full restore and a differential restore based on a set
schedule. This would keep my Mcopy as uptodate as I need to M.
All the views etc will then run off MCopy rather than M and will be the
source to populate my ODS.
Workable do you think and also doing you think?
Thanks,
RK
"Jeje" wrote:
well... the impact depends on the complexity of your queries.
if your queries do large and complex joins, then the impact can be important
on the M server. so going through MCopy is a good idea.
moving data from M to MCOPY or M to ODS (using simple views) should results
on the same performance, but the design is simplified.
compare the price of maintaining MCOPY versus adding memory and disks on the
M server. adding disks and memory can improve the end user access and your
design, so everybody is happy.
"RK" <RK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7884C0B1-CE23-441A-8805-D1DFEDA5B7F2@xxxxxxxxxxxxxxxx
> Hi Jeje,
>
> Wont the use of views based in the database M cause performance > problems.
> For instance if I were to use normal data views every time a view is > run
> as a
> part of the ETL, its going to run a query on the M database further
> constraining performance of the already constrained database M.
>
> Using indexed view directly on database M in that server would mean the
> overhead of having to maintain the data in the indexed views by the
> updates,
> inserts and deletes happening in the underlying tables in database M > means
> again potentially poor response times on the application runnng based > on
> database M.
>
> Hope you understand my concern.
>
> Thanks
>
> RK
>
> "Jeje" wrote:
>
>> your solution M --> MCOPY --> ODS appear to be a good one.
>> but have you try to do M --> ODS?
>> maybe you can upgrade the M server to support more queries, this >> solution
>> simplify the overall process.
>>
>> where do you want to use indexed views?
>> if it's on the MCOPY server, there is no advantage of these indexed
>> views.
>> but using views as the source of your ETL process is a good option.
>>
>
- References:
- Prev by Date: Re: version/source control in datawarehouse project
- Next by Date: UDM and Star Schema
- Previous by thread: Re: Design choice in the Use of SQl Server Views for ETL
- Next by thread: version/source control in datawarehouse project
- Index(es):
Relevant Pages
|
Loading
