Database capacity

Hi,

I’m working in a DWH project the source system have data from passed 30 years and the total number of records from the source system is 10 million.

I’m need to do the estimation for the data base for the up coming 10 years of data.

As I can predict that the 30 year of data is 10 million then expected data for 10 years would not be more then 3 million.

The ETL tool I’m using is SQL Server Integration services and the reporting tool is Business Objects.

Can any one guide me on the implementation plan, meanmemory taken for ETL jobs (As SSIS use large amount of memory) database and business objects.

Sheikh

A good place to start is this link http://www.dmreview.com/article_sub.cfm?articleId=1048082

In talks in general about Capacity Planning.

Some basic things I would say -- almost a must have for a Datawarehouse

1) 64 Bit Environment -- you can use more than 4 GB of Memory in here (well you can use more than 4 GB in a 32 bit -- but 64 bit is better, especially if you can afford it).

2) Min of 8 GB of RAM. (considering you do not have more than 15 Million Rows).

3) Obviously -- enough Hard DIsk space for your DW.

4) A Good Disk Subsytem.

But a lot depends on your usage pattern and what you are trying to achieve. Ultimately it will all come down to cost vs benefit.

|||Thank you for the helpfull post.

Database automated part-archiving

Hi all,
i am a student doing my final year project and i have the duty to develop a automated part-archiving for our MsSql Server 2000 Database, but its not so simple :eek: .

the environment:
- MsSql Server 2000
- Suns App-Server Glasfish
- Java (J2EE)
- Hibernate

the requirements:
1.)
Some tables should be synchronized and redundant (the same data in both databases)

2.)
If the maindatabase is changing the schema the archivedatabse must do the same changes.

3.)
The datas in some tables (the ones that aren't synchronized) which are older then 2 Years should be moving (copy, paste) to the archivedatabase every 24 hours.

4.)
If there are is a access for a data which are not in the maindatabase, it is necassary to get it from the archivedatabase.

I don't find a way to realise this ...
can some one give me a hint?

... Sorry for my bad english, i hope i explained my problem good enough.

Thanks for ur answersyou have been sitting in class all semester and you have not a clue where to begin? Do you have any thoughts about how to do this?

This message board thing works a lot better if you have a solution in mind and you want our opinions. it does not work so well if you want someone to do your homework for you.

we have a team lead at my job that gets his\her work done by tasking it out to other people. everyone knows that person is worthless.

gee, its the middle of April. I guess you have 2 or 3 weeks to knock this out. Good luck.|||Offtopic:
yes u are right, there are always people who want that others do there work and people who ask questions (most of the time these people are comming to me), wait for answers and didn't do a littlebit one her own.
If u think i am worthless because i ask a question (for a not trival problem i think), it's fine but please don't spam the forum.
And no, i still have 3-4 month.
And yes, i have somethink in my mind, but i want to hear other opinions bevor i talk about it, so that it would not affect the answers.

Back to topic:

1 and 2)
Maybe i will try to make replication for the schema and the synchronized tables or read out the meta-data and use them to synchronize the schema and the tables.

3)
A simple thread can read the old datas from the maindatabase and write them in the archive database. But this is not ACID save. So, i musst find a save way to log the actions and if something went wrong i must undo the work.

4)
Catch every query and check if the result is valid, if not query the archivdatabase.
(But at witch point i should catch the query. At my company(yes i do my final year projekt in a company) we are using Hibernate and i don't know where i should catch the query, after hibernate i think its not possible, in hibernate ... i will not change the hibernate framework, befor hibernate it's not allowed because the application-code should not affect from my changes.

I am thank full for all ur opinions.|||I never said you are worthless and if you did not launch a personal attack on me I would have helped you out today because things are a little light on my end today.

I was simply helping you by telling you how to get a better response here and you did that, but since you do not appreciate my advice you can go some where else.

(_X_)|||Hmm, i didn't mean to launch an attack at u.
I am sorry if it looks like that.
The truth is, that i think that u launched an attack at me and didn't want to help me ;).

So, thanks for ur advice and a nice finishing time.|||Good lord...

In any case, you looking to automate Structure changes in SQL 2k?

Are you sure it's not SQL 2k5?

So you are asking for opinions, what have you come up with so far?|||Jeez, I would never automate cascading schematic changes between databases. There is an all-important phase called "testing" which is impossible to include in automation. Was this thesis your idea, or was it assigned to you?|||@.blindman:
It was assigned to me and wasn't my idear.
The reason for this solution is, that they have some tables that are realy large and grow every Day. To tune-up the access time they want to split the tables in old and new datas. But they want that the work after this is nearly like they have only one Database. So if they make schematic changes to the database (maindatabase) it should cascade to the other database (archivdatabase).

@.Brett Kaiser
2k5 is on the roadmap but at the moment it is still Sql-Server 2k.
If it is only possible with 2k5 than there is no other way then using 2k5.
I am still in the designing-phase.
I think i should try to use transaktion-replikation to synchronize the tables which should be synchronized (like Employees or some think like this) but i still don't know if the table schema will be replikated too and if i could only replikate selected tables, that is somethink i will glean today.
For the tables where the data shouldn't be synchronized (new data in the maindatabase, old data in the archivdatabase) i don't know how to cascade the schema from the tables. So i think i will write a program which willl read the meta-data form the tables (maindatabase) and update the tables in the archivdatabase. Then i have a delay between the struktur change in the main- and the update in the archivdatabase, but that shouldn't make a problem if i did this every time befor i move data from the maindb to the archivdb. In a thread i will move every 24hours (for example) data older then 2 years from the maindb to the archivdb but this is not transaktionsave (ACID) so i will log the status in a file (or maybe in the maindb) so that it will be possible to make somethink like a rollback if the transaktion fails (like a loose of power).

Thanks 4 ur help and opinions|||My buddy at work bought a zero-gravity pen last week. It was developed by NASA for astronauts to use in outer space. It contains a tiny CO2 cartridge that pressurizes the ink cylinder enabling it to write upside-down, or at any angle.
When faced with the same challenge the Russians used a pencil.

If you want to synchronize schematic changes between databases, then script all your modifications and run them on both databases. What you are endeavoring to do violates the KISS principle and my crystal ball tells me you are going to have more down time from process failures and corrupted data than it would take to to proper testing of modifications.

Thus sayeth the blindman.|||Thank u, i think i will do how u say and don't automate the schema synchronisation to "Keep It Simple, Stupid".

For the synchronisation of the datas i will write a program, that will compensate
the datas in the archivdb.

For the moving of the datas i will write a program, that will looking if the data in the maindb should move to the archivdb. The problem is, how to find all the datas which are depending on the moving data, because they all must move to. But i think i can find it out with the Meta-Informations from the tables ... that shouldn't be a problem.

Thank you for your help, opinion, and advice.|||It is absolutely appropriate to automate the update and transfer of the data. It is just modifications to the schema which you should not be automating.

Database and Log maintenance

Running SQL Server 2000 Enterprise Edition SP3. The database is also
used by Microsoft Project Server 2002 and also has OLAP views, so the
database is being used to view/run cubes in the Analysis Manager.

What is the best way of shrinking the database size and its log file too?

Is there an automatic way to do this with a maintenance plan or i have
to manually run a SQL statement periodically?

What are the best practices?

I have a database that the size of it is 260megs and now the log file is
over 800megs...
Everytime i run the following:

BACKUP LOG DBName WITH TRUNCATE ONLY
DBCC SHRINKDATABASE (DBName, 10, TRUNCATEONLY)

When running it, i get the following 2 records returned:

DbId FieldId CurrentSize MinimumSize UsedPages EstimatedPages
7 1 32304 128 28152
28152
7 2 160 128 160
128

I don't know what the above 2 records mean. I am also concerned if i should
be running that statement or not.

Also concerned as to why the Windows Server 2003 (Enterprise Edition)
always creates a CRITICAL error in the Application event viewer with EventID
number 17055, source being MSSQLSERVER and the description of the event:

18278:
Database log truncated: Database: DBName.

If it's a critical error message, then what i am doing is bad? Am i damaging
my database?

Within like 7-10 days of running the above statement, the log file becomes
close
to 1 GB again! How can i find out what causes the log file to grow that big?
Is it being
caused by running some OLAP views or what? The db has about 20 users
connected to it
using an ASP web aplication as the front-end, and MSP Professional 2002 also
to connect
to the Project Server 2002 (which uses the same database as the above
DBName).

I would appreciate any help.

Thank you very much"serge" <sergea@.nospam.ehmail.com> wrote in message
news:UTiZc.17326$CG3.1077546@.news20.bellglobal.com ...
> Running SQL Server 2000 Enterprise Edition SP3. The database is also
> used by Microsoft Project Server 2002 and also has OLAP views, so the
> database is being used to view/run cubes in the Analysis Manager.
> What is the best way of shrinking the database size and its log file too?
> Is there an automatic way to do this with a maintenance plan or i have
> to manually run a SQL statement periodically?
> What are the best practices?
> I have a database that the size of it is 260megs and now the log file is
> over 800megs...
> Everytime i run the following:
> BACKUP LOG DBName WITH TRUNCATE ONLY
> DBCC SHRINKDATABASE (DBName, 10, TRUNCATEONLY)
> When running it, i get the following 2 records returned:
> DbId FieldId CurrentSize MinimumSize UsedPages EstimatedPages
> 7 1 32304 128 28152
> 28152
> 7 2 160 128 160
> 128
> I don't know what the above 2 records mean. I am also concerned if i
> should
> be running that statement or not.
> Also concerned as to why the Windows Server 2003 (Enterprise Edition)
> always creates a CRITICAL error in the Application event viewer with
> EventID
> number 17055, source being MSSQLSERVER and the description of the event:
> 18278:
> Database log truncated: Database: DBName.
>
> If it's a critical error message, then what i am doing is bad? Am i
> damaging
> my database?
>
> Within like 7-10 days of running the above statement, the log file becomes
> close
> to 1 GB again! How can i find out what causes the log file to grow that
> big?
> Is it being
> caused by running some OLAP views or what? The db has about 20 users
> connected to it
> using an ASP web aplication as the front-end, and MSP Professional 2002
> also
> to connect
> to the Project Server 2002 (which uses the same database as the above
> DBName).
>
> I would appreciate any help.
> Thank you very much

It sounds as if your database is in full recovery - all transactions are
logged, and the transaction log is not truncated unless you back it up; this
allows recovery to a specific point in time. Since your database is very
small, you might want to change it to simple recovery, where the log is
automatically truncated and the space reused. This means the only recovery
option you have is to restore your last full backup, so you would lose any
modifications since then. If you do need point in time recovery, then you
would need to schedule transaction log backups - backing up the log
truncates it.

The reason for the critical error (I assume) is that if the database is in
full recovery and you truncate the log, it is no longer possible to restore
any backups - you need to make a full backup immediately.

This KB article might also be helpful:

http://support.microsoft.com/defaul...8&Product=sql2k

Simon|||I will check the simple/full recovery thing you are talking about.

Thank you Simon

> It sounds as if your database is in full recovery - all transactions are
> logged, and the transaction log is not truncated unless you back it up;
this
> allows recovery to a specific point in time. Since your database is very
> small, you might want to change it to simple recovery, where the log is
> automatically truncated and the space reused. This means the only recovery
> option you have is to restore your last full backup, so you would lose any
> modifications since then. If you do need point in time recovery, then you
> would need to schedule transaction log backups - backing up the log
> truncates it.
> The reason for the critical error (I assume) is that if the database is in
> full recovery and you truncate the log, it is no longer possible to
restore
> any backups - you need to make a full backup immediately.
> This KB article might also be helpful:
>
http://support.microsoft.com/defaul...8&Product=sql2k|||serge (sergea@.nospam.ehmail.com) writes:
> Running SQL Server 2000 Enterprise Edition SP3. The database is also
> used by Microsoft Project Server 2002 and also has OLAP views, so the
> database is being used to view/run cubes in the Analysis Manager.
> What is the best way of shrinking the database size and its log file too?
> Is there an automatic way to do this with a maintenance plan or i have
> to manually run a SQL statement periodically?
> What are the best practices?

Depends on your requirement. If you want up-to-the point recovery, you
should backup your transaction log regularly, and never truncate it.
Notice that just backing up the database does not truncate the transaction
log.

If you don't care about up-to-the point recovery, the best practice is
to switch to simple recovery mode.

> Also concerned as to why the Windows Server 2003 (Enterprise Edition)
> always creates a CRITICAL error in the Application event viewer with
> EventID number 17055, source being MSSQLSERVER and the description of
> the event:
> 18278:
> Database log truncated: Database: DBName.
>
> If it's a critical error message, then what i am doing is bad? Am i
> damaging my database?

You are losing the ability to do up-to-the-point recovery, and for many
production sites, this is really bad.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you for your response Erland, I'll have to think over my backup
procedures
and learn more about simpre/advanced recover mode options.

> Depends on your requirement. If you want up-to-the point recovery, you
> should backup your transaction log regularly, and never truncate it.
> Notice that just backing up the database does not truncate the transaction
> log.
> If you don't care about up-to-the point recovery, the best practice is
> to switch to simple recovery mode.
> > Also concerned as to why the Windows Server 2003 (Enterprise Edition)
> > always creates a CRITICAL error in the Application event viewer with
> > EventID number 17055, source being MSSQLSERVER and the description of
> > the event:
> > 18278:
> > Database log truncated: Database: DBName.
> > If it's a critical error message, then what i am doing is bad? Am i
> > damaging my database?
> You are losing the ability to do up-to-the-point recovery, and for many
> production sites, this is really bad.

data warehouse truncate question

have an SSIS project and I have a package that drops and creates the
database, creates tables, then I have a package that loads the data into the
data warehouse. Now, I got the request in to only load the data and not drop
and create the database every time the package runs due to the package is
going to run every 5-10 minutes. Now I never created an SSIS package until
now, so how can I accomplish in only loading the tables every time its ran
instead of dropping and creating the database every time the package is ran?
so in a nutshell.
I need a package to load the data only if the data does not exsist and not
drop and create the database every time.
any suggestions on how this can be done?
Two methods come to mind.
1) Simple insert where not exists statements for each table. Can be VERY
inefficient, but easy to construct.
2) Build system that tracks the last-inserted key value for each table.
Reference the appropriate key in the where clause of each insert statement.
If you need to also track modified data, then it gets MUCH more complicated
because you need triggers on every base table to insert the modified row's
PKs into a refresh holding table which would also be reference for the
warehouse update statements.
TheSQLGuru
President
Indicium Resources, Inc.
"Mike" <igotyourdotnet@.gmail.com> wrote in message
news:%23%23HSmywaHHA.1300@.TK2MSFTNGP02.phx.gbl...
> have an SSIS project and I have a package that drops and creates the
> database, creates tables, then I have a package that loads the data into
> the
> data warehouse. Now, I got the request in to only load the data and not
> drop
> and create the database every time the package runs due to the package is
> going to run every 5-10 minutes. Now I never created an SSIS package until
> now, so how can I accomplish in only loading the tables every time its ran
> instead of dropping and creating the database every time the package is
> ran?
> so in a nutshell.
> I need a package to load the data only if the data does not exsist and not
> drop and create the database every time.
> any suggestions on how this can be done?
>
>

data warehouse truncate question

have an SSIS project and I have a package that drops and creates the
database, creates tables, then I have a package that loads the data into the
data warehouse. Now, I got the request in to only load the data and not drop
and create the database every time the package runs due to the package is
going to run every 5-10 minutes. Now I never created an SSIS package until
now, so how can I accomplish in only loading the tables every time its ran
instead of dropping and creating the database every time the package is ran?
so in a nutshell.
I need a package to load the data only if the data does not exsist and not
drop and create the database every time.
any suggestions on how this can be done?Two methods come to mind.
1) Simple insert where not exists statements for each table. Can be VERY
inefficient, but easy to construct.
2) Build system that tracks the last-inserted key value for each table.
Reference the appropriate key in the where clause of each insert statement.
If you need to also track modified data, then it gets MUCH more complicated
because you need triggers on every base table to insert the modified row's
PKs into a refresh holding table which would also be reference for the
warehouse update statements.
TheSQLGuru
President
Indicium Resources, Inc.
"Mike" <igotyourdotnet@.gmail.com> wrote in message
news:%23%23HSmywaHHA.1300@.TK2MSFTNGP02.phx.gbl...
> have an SSIS project and I have a package that drops and creates the
> database, creates tables, then I have a package that loads the data into
> the
> data warehouse. Now, I got the request in to only load the data and not
> drop
> and create the database every time the package runs due to the package is
> going to run every 5-10 minutes. Now I never created an SSIS package until
> now, so how can I accomplish in only loading the tables every time its ran
> instead of dropping and creating the database every time the package is
> ran?
> so in a nutshell.
> I need a package to load the data only if the data does not exsist and not
> drop and create the database every time.
> any suggestions on how this can be done?
>
>

Data warehouse or data mart

Hi,
I'm working on a project at the moment where have an existing warehouse, and
need to bring in data from new source systems, which are from a similar (but
not quite the same) part of the business. These two parts of the business
merged recently, and they want to have a consolidated view of the data, as
well as replace some existing (Excel and Access based) reporting.
We are coming to the end of the analysis and design phase, and I want to get
some opinions that the approach I'm taking is valid.
I have tried to bring everything into the one database. I've added some new
fact tables for new source systems, as the original and new source systems
don't quite work the same. I've also tried to conform the dimensions as far
as possible, so that the data will aggregate consistently for the management
reporting.
This seems to fit pretty well, and I haven't had to alter the current data
model very much.
My question is basically: Is this the right way to go, or am I better off
breaking the warehouse into separate data marts?
A lot of the reporting will only apply to each part of the business, but
there will be some management reporting that wants a consolidated view of the
new merged business.
Any comments will be appreciated.
Thanks.
Hi,
Based on my scope, there is no necessary to break them into seperate data
marts for this situation because they have similar model and need to be
merged in the same analysis report. You may consider to create different
cube and you could at least share some common dimensions such as
time/location etc.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Data warehouse or data mart
>thread-index: AcXSOg1GLZ3yTwgwTGqbRewN1uy9bQ==
>X-WBNR-Posting-Host: 203.54.233.209
>From: "=?Utf-8?B?V3JlY2s=?=" <Wreck@.community.nospam>
>Subject: Data warehouse or data mart
>Date: Sun, 16 Oct 2005 03:12:02 -0700
>Lines: 31
>Message-ID: <EA2C0FDE-2A52-44DE-88F9-5BA83B6278BF@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.datawarehouse
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.datawarehouse:2317
>X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
>Hi,
>I'm working on a project at the moment where have an existing warehouse,
and
>need to bring in data from new source systems, which are from a similar
(but
>not quite the same) part of the business. These two parts of the business
>merged recently, and they want to have a consolidated view of the data, as
>well as replace some existing (Excel and Access based) reporting.
>We are coming to the end of the analysis and design phase, and I want to
get
>some opinions that the approach I'm taking is valid.
>I have tried to bring everything into the one database. I've added some
new
>fact tables for new source systems, as the original and new source systems
>don't quite work the same. I've also tried to conform the dimensions as
far
>as possible, so that the data will aggregate consistently for the
management
>reporting.
>This seems to fit pretty well, and I haven't had to alter the current data
>model very much.
>My question is basically: Is this the right way to go, or am I better off
>breaking the warehouse into separate data marts?
>A lot of the reporting will only apply to each part of the business, but
>there will be some management reporting that wants a consolidated view of
the
>new merged business.
>Any comments will be appreciated.
>Thanks.
>
|||Hi Peter,
That's pretty much the approach I'm taking. The new source systems will have
their own fact tables, with conformed measures, joining to conformed
dimensions.
I'll build separate cubes for the fact tables, and put the old and new cubes
in a single virtual cube, joined on the common dimensions.
Thanks,
Wreck.
"Peter Yang [MSFT]" wrote:

> Hi,
> Based on my scope, there is no necessary to break them into seperate data
> marts for this situation because they have similar model and need to be
> merged in the same analysis report. You may consider to create different
> cube and you could at least share some common dimensions such as
> time/location etc.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> and
> (but
> get
> new
> far
> management
> the
>

Data warehouse or data mart

Hi,
I'm working on a project at the moment where have an existing warehouse, and
need to bring in data from new source systems, which are from a similar (but
not quite the same) part of the business. These two parts of the business
merged recently, and they want to have a consolidated view of the data, as
well as replace some existing (Excel and Access based) reporting.
We are coming to the end of the analysis and design phase, and I want to get
some opinions that the approach I'm taking is valid.
I have tried to bring everything into the one database. I've added some new
fact tables for new source systems, as the original and new source systems
don't quite work the same. I've also tried to conform the dimensions as far
as possible, so that the data will aggregate consistently for the management
reporting.
This seems to fit pretty well, and I haven't had to alter the current data
model very much.
My question is basically: Is this the right way to go, or am I better off
breaking the warehouse into separate data marts?
A lot of the reporting will only apply to each part of the business, but
there will be some management reporting that wants a consolidated view of th
e
new merged business.
Any comments will be appreciated.
Thanks.Hi,
Based on my scope, there is no necessary to break them into seperate data
marts for this situation because they have similar model and need to be
merged in the same analysis report. You may consider to create different
cube and you could at least share some common dimensions such as
time/location etc.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Data warehouse or data mart
>thread-index: AcXSOg1GLZ3yTwgwTGqbRewN1uy9bQ==
>X-WBNR-Posting-Host: 203.54.233.209
>From: "examnotes" <Wreck@.community.nospam>
>Subject: Data warehouse or data mart
>Date: Sun, 16 Oct 2005 03:12:02 -0700
>Lines: 31
>Message-ID: <EA2C0FDE-2A52-44DE-88F9-5BA83B6278BF@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.datawarehouse
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.datawarehouse:2317
>X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
>Hi,
>I'm working on a project at the moment where have an existing warehouse,
and
>need to bring in data from new source systems, which are from a similar
(but
>not quite the same) part of the business. These two parts of the business
>merged recently, and they want to have a consolidated view of the data, as
>well as replace some existing (Excel and Access based) reporting.
>We are coming to the end of the analysis and design phase, and I want to
get
>some opinions that the approach I'm taking is valid.
>I have tried to bring everything into the one database. I've added some
new
>fact tables for new source systems, as the original and new source systems
>don't quite work the same. I've also tried to conform the dimensions as
far
>as possible, so that the data will aggregate consistently for the
management
>reporting.
>This seems to fit pretty well, and I haven't had to alter the current data
>model very much.
>My question is basically: Is this the right way to go, or am I better off
>breaking the warehouse into separate data marts?
>A lot of the reporting will only apply to each part of the business, but
>there will be some management reporting that wants a consolidated view of
the
>new merged business.
>Any comments will be appreciated.
>Thanks.
>|||Hi Peter,
That's pretty much the approach I'm taking. The new source systems will have
their own fact tables, with conformed measures, joining to conformed
dimensions.
I'll build separate cubes for the fact tables, and put the old and new cubes
in a single virtual cube, joined on the common dimensions.
Thanks,
Wreck.
"Peter Yang [MSFT]" wrote:

> Hi,
> Based on my scope, there is no necessary to break them into seperate data
> marts for this situation because they have similar model and need to be
> merged in the same analysis report. You may consider to create different
> cube and you could at least share some common dimensions such as
> time/location etc.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> --
> and
> (but
> get
> new
> far
> management
> the
>

Data warehouse data refresh/update

Hello,
Im a member of a team planning a data warehousing project. We have multiple data sources which are aggregated in a staging area. This is then denormalised and imported into the datawarehouse database.
I am looking at ideas for incremental data refresh, rather than a drop and re-import of all data. This would allow us to have historic data.
Does anyone have any tips that might be helpful for detecting changes in the source data for import? We have had some bad experiences with triggers on our source database in the past, so would rather not use these. I have considered replication and log sh
ipping, but these just give a replica of the source data and does not flag the updated/new data.
Any help would be greatly appreciated.
Thanks.
Ben.
Look in SQL Books Online for CHECKSUM_AGG to identify changes in a table. The difficulty then is in trying to identify what has changed. Another route is to try and identify fields within the production data that will identify when it was last changed. Ty
pically these tend to be datetime, timestamp or rowversion data types.
If you keep production keys in your fact table as additional attributes then this will give you another option for identifying new data.
"Ben" wrote:

> Hello,
> Im a member of a team planning a data warehousing project. We have multiple data sources which are aggregated in a staging area. This is then denormalised and imported into the datawarehouse database.
> I am looking at ideas for incremental data refresh, rather than a drop and re-import of all data. This would allow us to have historic data.
> Does anyone have any tips that might be helpful for detecting changes in the source data for import? We have had some bad experiences with triggers on our source database in the past, so would rather not use these. I have considered replication and log
shipping, but these just give a replica of the source data and does not flag the updated/new data.
> Any help would be greatly appreciated.
> Thanks.
> Ben.
|||Timestamp your source data when it gets changed. That is definately the best way.
Rgards
Jamie
"Ben" wrote:

> Hello,
> Im a member of a team planning a data warehousing project. We have multiple data sources which are aggregated in a staging area. This is then denormalised and imported into the datawarehouse database.
> I am looking at ideas for incremental data refresh, rather than a drop and re-import of all data. This would allow us to have historic data.
> Does anyone have any tips that might be helpful for detecting changes in the source data for import? We have had some bad experiences with triggers on our source database in the past, so would rather not use these. I have considered replication and log
shipping, but these just give a replica of the source data and does not flag the updated/new data.
> Any help would be greatly appreciated.
> Thanks.
> Ben.

Data warehouse data refresh/update

Hello,
Im a member of a team planning a data warehousing project. We have multiple
data sources which are aggregated in a staging area. This is then denormalis
ed and imported into the datawarehouse database.
I am looking at ideas for incremental data refresh, rather than a drop and r
e-import of all data. This would allow us to have historic data.
Does anyone have any tips that might be helpful for detecting changes in the
source data for import? We have had some bad experiences with triggers on o
ur source database in the past, so would rather not use these. I have consid
ered replication and log sh
ipping, but these just give a replica of the source data and does not flag t
he updated/new data.
Any help would be greatly appreciated.
Thanks.
Ben.Look in SQL Books Online for CHECKSUM_AGG to identify changes in a table. Th
e difficulty then is in trying to identify what has changed. Another route i
s to try and identify fields within the production data that will identify w
hen it was last changed. Ty
pically these tend to be datetime, timestamp or rowversion data types.
If you keep production keys in your fact table as additional attributes then
this will give you another option for identifying new data.
"Ben" wrote:

> Hello,
> Im a member of a team planning a data warehousing project. We have multipl
e data sources which are aggregated in a staging area. This is then denormal
ised and imported into the datawarehouse database.
> I am looking at ideas for incremental data refresh, rather than a drop and
re-import of all data. This would allow us to have historic data.
> Does anyone have any tips that might be helpful for detecting changes in the sourc
e data for import? We have had some bad experiences with triggers on our source data
base in the past, so would rather not use these. I have considered replication and l
og
shipping, but these just give a replica of the source data and does not flag the updated/new
data.
> Any help would be greatly appreciated.
> Thanks.
> Ben.|||Timestamp your source data when it gets changed. That is definately the best
way.
Rgards
Jamie
"Ben" wrote:

> Hello,
> Im a member of a team planning a data warehousing project. We have multipl
e data sources which are aggregated in a staging area. This is then denormal
ised and imported into the datawarehouse database.
> I am looking at ideas for incremental data refresh, rather than a drop and
re-import of all data. This would allow us to have historic data.
> Does anyone have any tips that might be helpful for detecting changes in the sourc
e data for import? We have had some bad experiences with triggers on our source data
base in the past, so would rather not use these. I have considered replication and l
og
shipping, but these just give a replica of the source data and does not flag the updated/new
data.
> Any help would be greatly appreciated.
> Thanks.
> Ben.

Data type for memo in Ms SQL 7

Hi, currently i am doing discussion forum for my project.
What I want to ask is what data type that I have to set for the message. Previously I am using nvarchar which I thought it would be ok, but when the time I save the message the ENTER command is become space in the database. For example I type

Message 1
Message 2

When I save into the database it become

Message 1 Message 2

I am new in using MS SQL server 7, so please give any suggestion about this.

Thanks,
REDit is not space, it is char(13) in the database.
you can replace it by whatever you want|||hi luber,

How do I replace it to make it saved in order. What data type do I have to set?

Thanks for the reply
RED|||it is ok for database.
If you want to put such a string on the asp.net page
and make new row each time when the user pressed enter

you can use something like this:

select replace(myfield, char(13), '<br>') as formatedfield
from mytable

data type DateTime

Hi all!!
I'm developing a project using VS 2005 (C# code) and SQL Server 2005.
I have some problems with the data type 'DateTime'.

In some parts of my project, I have used DataSets and DataAdapters and I can insert in a dataRow a data type 'DateTime' and it is inserted as dd/mm/yyyy hh:mm:ss. That's OK.

But in other parts I'm not using Datatables and DataAdapters and I insert new rows in the database using ExecuteNonQuery, but the data type 'DateTime' in the format dd/mm/yyyy hh:mm:ss is not valid. It only allows formats like mm/dd/yyyy hh:mm:ss, but I want the other format.

Why is dd/mm/yyyy hh:mm:ss working with DataAdapters? Why is dd/mm/yyyy hh:mm:ss not working with ExecuteNonQuery? Is there any way to specify in the Database that I want the format dd/mm/yyyy hh:mm:ss??

Thanks in advance folks,
Javier.

Try to add a setting for DATEFORMAT just before you insert statement, for example:

SET DATEFORMAT dmy; insert into ...

|||Thanks Iori_Jay,
But I've checked that it is necessary to write "SET DATEFORMAT dmy" every time I want to insert a new row in the table that has DateTime attribute. Is there another way of keeping this value within the DB's life??

thanks again|||Since the DATEFORMAT option is a session(means connection to SQL) option, you have to set it in each connection. And within the connection, the option is effective.

Data Type > Save various object types

Hello,

I am working on a .NET project and I would like to save some LOCALIZED content in an SQL 2005 database.

This content might be a string, a number or even a class with its properties values defined so I can retrieve it later.

I will not need search or any complex operation just the following:
1. Add content to the database
2. Remove content from the database by contentId
3. Get content from the database by contentId

Anyway, is this possible?

What SQL 2005 datatype should I use to accomplish this?

Thanks,
Miguel

I suppose the only one which will work for you isvarbinary(max) I suppose

Thanks

Data Transformation Project Template for SQL 2005

Does anyone know where I can download and install the Data Transformation Project template for SQL Server 2005 Integation Services?

I'm having the same issue. Did you ever find a solution?|||

Data tranformations are done through the dataflow controls, to start a project you will need to have the management tools loaded onto the machine and start the SQL Server business intelligence studio from the start menu. You can also access this through the visual studio system... Once the management tools are loaded onto the machine.

|||Try looking at the integration services template|||

I have the Business Intelligent Project but inside I don't have Data Transformation Project Template.

I have:
* Analisys Services Project
* Integration Services Project
* Report Model Project
* Import Analisys Services 9.0 Database
* Report Server Project Wizard
* Report Server Project

Like the question, I don't have the Data Transformation Project Template and I have SQL Server 2005 Client Tools instaled.

Maybe I have to have something else instaled.... what?

tkx in advance
Paulo Aboim Pinto
Odivelas - Portugal

|||The name "Data Transformation Project" was used by early betas of SQL 2005, it is now called Integration Services Project.

Data Transformation Project Template for SQL 2005

Does anyone know where I can download and install the Data Transformation Project template for SQL Server 2005 Integation Services?

I'm having the same issue. Did you ever find a solution?|||

Data tranformations are done through the dataflow controls, to start a project you will need to have the management tools loaded onto the machine and start the SQL Server business intelligence studio from the start menu. You can also access this through the visual studio system... Once the management tools are loaded onto the machine.

|||Try looking at the integration services template|||

I have the Business Intelligent Project but inside I don't have Data Transformation Project Template.

I have:
* Analisys Services Project
* Integration Services Project
* Report Model Project
* Import Analisys Services 9.0 Database
* Report Server Project Wizard
* Report Server Project

Like the question, I don't have the Data Transformation Project Template and I have SQL Server 2005 Client Tools instaled.

Maybe I have to have something else instaled.... what?

tkx in advance
Paulo Aboim Pinto
Odivelas - Portugal

|||The name "Data Transformation Project" was used by early betas of SQL 2005, it is now called Integration Services Project.

Data Transformation Project Template for SQL 2005

Does anyone know where I can download and install the Data Transformation Project template for SQL Server 2005 Integation Services?

I'm having the same issue. Did you ever find a solution?|||

Data tranformations are done through the dataflow controls, to start a project you will need to have the management tools loaded onto the machine and start the SQL Server business intelligence studio from the start menu. You can also access this through the visual studio system... Once the management tools are loaded onto the machine.

|||Try looking at the integration services template|||

I have the Business Intelligent Project but inside I don't have Data Transformation Project Template.

I have:
* Analisys Services Project
* Integration Services Project
* Report Model Project
* Import Analisys Services 9.0 Database
* Report Server Project Wizard
* Report Server Project

Like the question, I don't have the Data Transformation Project Template and I have SQL Server 2005 Client Tools instaled.

Maybe I have to have something else instaled.... what?

tkx in advance
Paulo Aboim Pinto
Odivelas - Portugal

|||The name "Data Transformation Project" was used by early betas of SQL 2005, it is now called Integration Services Project.

Data Transformation Project Template for SQL 2005

Does anyone know where I can download and install the Data Transformation Project template for SQL Server 2005 Integation Services?

I'm having the same issue. Did you ever find a solution?|||

Data tranformations are done through the dataflow controls, to start a project you will need to have the management tools loaded onto the machine and start the SQL Server business intelligence studio from the start menu. You can also access this through the visual studio system... Once the management tools are loaded onto the machine.

|||Try looking at the integration services template|||

I have the Business Intelligent Project but inside I don't have Data Transformation Project Template.

I have:
* Analisys Services Project
* Integration Services Project
* Report Model Project
* Import Analisys Services 9.0 Database
* Report Server Project Wizard
* Report Server Project

Like the question, I don't have the Data Transformation Project Template and I have SQL Server 2005 Client Tools instaled.

Maybe I have to have something else instaled.... what?

tkx in advance
Paulo Aboim Pinto
Odivelas - Portugal

|||The name "Data Transformation Project" was used by early betas of SQL 2005, it is now called Integration Services Project.

Data Transformation Project Template

Does anyone know how to install or make available the Data Transformation Project Template in SQL Server 2005? I can not find it using Integration Services.

Not quite sure what you are asking but this might answer your question: http://blogs.conchango.com/jamiethomson/archive/2005/11/12/2380.aspx

-Jamie