Dimensional modelling advice

I am a former DBA that has moved into a totally new role and
environment, as a data architect on an enterprise data warehousing
project for the national statistics office. I've started reading the
Kimball Data Warehouse Toolkit (2nd Edition) and now have a feel for
the approach that I'd like to follow with the data modelling, ie.
using the data warehouse bus architecture approach that is advocated
by Kimball.
However, the main concern that I have with this approach is that we
have hundreds of different data sources in the form of surveys that we
conduct as well as data that we share from other govt and non-govt
agencies. So the data that we will be loading into our DW is not
sourced from our OLTP systems but from a multitude of other businesses
and households. If we were to model the data on a business process
basis there is the potential to end up with dozens/hundreds of star
schemas, as well as the ongoing maintenance of systems development for
when new surveys are created. Internally there is some support for
developing a generic dimensional model that has the flexibility to
accept any type of data (be it of an economic, social or environmental
nature). I could see that this would work as far as loading data into
the warehouse goes, but think that it would introduce a layer of
abstraction that would make it difficult for the business users to
understand and query the data (or do BI tools get around this). So
the first question is, how suitable is generic modelling for a data
warehouse implementation and if not, how big an issue is deploying a
large number of star schemas for an integrated data warehouse?
Secondly, a question around granularity. We have many situations
where different surveys request similar data but with slight
variations. eg. We could ask about a businesses export totals in a
number of different ways in several surveys - total exports of all
products for 2003, exports of fruit and veges for April, exports of
apples for Q3 2004, exports of apples for June in US$, total exports
to Europe etc. How is this handled in a dimensional model? Is it
possible to model this in a single star schema when the granularity
appears to be different. NB. Is may not be possible to derive
aggregate totals from data with a finer grain because a different
subset of businesses will have been asked a different set of
questions, so aggregate totals might not include the full subset of
data that is needed for an accurate total.
Lastly, does anybody know of an example of a dimensional model that
could be used as a starting point for survey collection and analysis
data warehouse?
TIA,
Keith.Keith -
I would suggest that you also purchase another Kimball book entitled
"Data Warehouse ETL Toolkit"; they should really be sold as a pair.
Approaching a data warehouse with a strong relational 3nf
bakcground can be difficult to understand and implement the
denormalizing structures needed for a successful dw.
I've implemented large dw systems with numerous external sources of
data, and I've found that if one really boils the inbound information
the complexity is not what it seems at first.
FWIW.
\
b.
Keith Chung wrote:

> I am a former DBA that has moved into a totally new role and
> environment, as a data architect on an enterprise data warehousing
> project for the national statistics office. I've started reading the
> Kimball Data Warehouse Toolkit (2nd Edition) and now have a feel for
> the approach that I'd like to follow with the data modelling, ie.
> using the data warehouse bus architecture approach that is advocated
> by Kimball.
> However, the main concern that I have with this approach is that we
> have hundreds of different data sources in the form of surveys that we
> conduct as well as data that we share from other govt and non-govt
> agencies. So the data that we will be loading into our DW is not
> sourced from our OLTP systems but from a multitude of other businesses
> and households. If we were to model the data on a business process
> basis there is the potential to end up with dozens/hundreds of star
> schemas, as well as the ongoing maintenance of systems development for
> when new surveys are created. Internally there is some support for
> developing a generic dimensional model that has the flexibility to
> accept any type of data (be it of an economic, social or environmental
> nature). I could see that this would work as far as loading data into
> the warehouse goes, but think that it would introduce a layer of
> abstraction that would make it difficult for the business users to
> understand and query the data (or do BI tools get around this). So
> the first question is, how suitable is generic modelling for a data
> warehouse implementation and if not, how big an issue is deploying a
> large number of star schemas for an integrated data warehouse?
> Secondly, a question around granularity. We have many situations
> where different surveys request similar data but with slight
> variations. eg. We could ask about a businesses export totals in a
> number of different ways in several surveys - total exports of all
> products for 2003, exports of fruit and veges for April, exports of
> apples for Q3 2004, exports of apples for June in US$, total exports
> to Europe etc. How is this handled in a dimensional model? Is it
> possible to model this in a single star schema when the granularity
> appears to be different. NB. Is may not be possible to derive
> aggregate totals from data with a finer grain because a different
> subset of businesses will have been asked a different set of
> questions, so aggregate totals might not include the full subset of
> data that is needed for an accurate total.
> Lastly, does anybody know of an example of a dimensional model that
> could be used as a starting point for survey collection and analysis
> data warehouse?
> TIA,
> Keith.

Dimensional modelling advice

I am a former DBA that has moved into a totally new role and
environment, as a data architect on an enterprise data warehousing
project for the national statistics office. I've started reading the
Kimball Data Warehouse Toolkit (2nd Edition) and now have a feel for
the approach that I'd like to follow with the data modelling, ie.
using the data warehouse bus architecture approach that is advocated
by Kimball.
However, the main concern that I have with this approach is that we
have hundreds of different data sources in the form of surveys that we
conduct as well as data that we share from other govt and non-govt
agencies. So the data that we will be loading into our DW is not
sourced from our OLTP systems but from a multitude of other businesses
and households. If we were to model the data on a business process
basis there is the potential to end up with dozens/hundreds of star
schemas, as well as the ongoing maintenance of systems development for
when new surveys are created. Internally there is some support for
developing a generic dimensional model that has the flexibility to
accept any type of data (be it of an economic, social or environmental
nature). I could see that this would work as far as loading data into
the warehouse goes, but think that it would introduce a layer of
abstraction that would make it difficult for the business users to
understand and query the data (or do BI tools get around this). So
the first question is, how suitable is generic modelling for a data
warehouse implementation and if not, how big an issue is deploying a
large number of star schemas for an integrated data warehouse?
Secondly, a question around granularity. We have many situations
where different surveys request similar data but with slight
variations. eg. We could ask about a businesses export totals in a
number of different ways in several surveys - total exports of all
products for 2003, exports of fruit and veges for April, exports of
apples for Q3 2004, exports of apples for June in US$, total exports
to Europe etc. How is this handled in a dimensional model? Is it
possible to model this in a single star schema when the granularity
appears to be different. NB. Is may not be possible to derive
aggregate totals from data with a finer grain because a different
subset of businesses will have been asked a different set of
questions, so aggregate totals might not include the full subset of
data that is needed for an accurate total.
Lastly, does anybody know of an example of a dimensional model that
could be used as a starting point for survey collection and analysis
data warehouse?
TIA,
Keith.
Keith -
I would suggest that you also purchase another Kimball book entitled
"Data Warehouse ETL Toolkit"; they should really be sold as a pair.
Approaching a data warehouse with a strong relational 3nf
bakcground can be difficult to understand and implement the
denormalizing structures needed for a successful dw.
I've implemented large dw systems with numerous external sources of
data, and I've found that if one really boils the inbound information
the complexity is not what it seems at first.
FWIW.
\
b.
Keith Chung wrote:

> I am a former DBA that has moved into a totally new role and
> environment, as a data architect on an enterprise data warehousing
> project for the national statistics office. I've started reading the
> Kimball Data Warehouse Toolkit (2nd Edition) and now have a feel for
> the approach that I'd like to follow with the data modelling, ie.
> using the data warehouse bus architecture approach that is advocated
> by Kimball.
> However, the main concern that I have with this approach is that we
> have hundreds of different data sources in the form of surveys that we
> conduct as well as data that we share from other govt and non-govt
> agencies. So the data that we will be loading into our DW is not
> sourced from our OLTP systems but from a multitude of other businesses
> and households. If we were to model the data on a business process
> basis there is the potential to end up with dozens/hundreds of star
> schemas, as well as the ongoing maintenance of systems development for
> when new surveys are created. Internally there is some support for
> developing a generic dimensional model that has the flexibility to
> accept any type of data (be it of an economic, social or environmental
> nature). I could see that this would work as far as loading data into
> the warehouse goes, but think that it would introduce a layer of
> abstraction that would make it difficult for the business users to
> understand and query the data (or do BI tools get around this). So
> the first question is, how suitable is generic modelling for a data
> warehouse implementation and if not, how big an issue is deploying a
> large number of star schemas for an integrated data warehouse?
> Secondly, a question around granularity. We have many situations
> where different surveys request similar data but with slight
> variations. eg. We could ask about a businesses export totals in a
> number of different ways in several surveys - total exports of all
> products for 2003, exports of fruit and veges for April, exports of
> apples for Q3 2004, exports of apples for June in US$, total exports
> to Europe etc. How is this handled in a dimensional model? Is it
> possible to model this in a single star schema when the granularity
> appears to be different. NB. Is may not be possible to derive
> aggregate totals from data with a finer grain because a different
> subset of businesses will have been asked a different set of
> questions, so aggregate totals might not include the full subset of
> data that is needed for an accurate total.
> Lastly, does anybody know of an example of a dimensional model that
> could be used as a starting point for survey collection and analysis
> data warehouse?
> TIA,
> Keith.

differential backup 2005 advice

Morning.
Im running a FULL BACKUP in overwrite mode daily at 3am.
Im running a DIFFERENTAL BACKUP in overwrite mode hourly.
I cannot lose more than an hours data.
SQL 2005 Standard.
Backup Timeline:
3am Full
4am Differential
5am Overwrites 4am Differental.
etc ..
So if i restore the 5am Differential have i lost the 3am to 4am data ? (does
it let me do this ?)
If this is the case should I create a Differential overwrite as a separeate
file every hour ? (cannot append as data too large).
Example Timeline:
3am full
4am Differental4am.bak
5am Differental5am.bak
So i need to restore to 5am i need to
1. restore 3am full
2. restore Differential4am.bak
3. restore Differantal5am.bak
Is this correct ?
Thanks for any advice
Scott
Scott
BOL says
"A differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data."
Test your DDR
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'c:\db.bak' WITH INIT
insert test..test default values
backup database test to disk = 'c:\db_diff1.bak' WITH DIFFERENTIAL
insert test..test default values
backup database test to disk = 'c:\db_diff2.bak' WITH DIFFERENTIAL
GO
RESTORE DATABASE test FROM disk = 'C:\db.bak' WITH FILE = 1, norecovery
--RESTORE DATABASE test FROM disk = 'c:\db_diff1.bak' WITH FILE = 1,
recovery
RESTORE DATABASE test FROM disk = 'c:\db_diff2.bak' WITH FILE = 1, recovery
select * from test..test
DROP Database test
"Scott" <scott_lotus@.yahoo.co.uk> wrote in message
news:extosaChIHA.748@.TK2MSFTNGP04.phx.gbl...
> Morning.
> Im running a FULL BACKUP in overwrite mode daily at 3am.
> Im running a DIFFERENTAL BACKUP in overwrite mode hourly.
> I cannot lose more than an hours data.
> SQL 2005 Standard.
>
> Backup Timeline:
> 3am Full
> 4am Differential
> 5am Overwrites 4am Differental.
> etc ..
> So if i restore the 5am Differential have i lost the 3am to 4am data ?
> (does it let me do this ?)
> If this is the case should I create a Differential overwrite as a
> separeate file every hour ? (cannot append as data too large).
> Example Timeline:
> 3am full
> 4am Differental4am.bak
> 5am Differental5am.bak
> So i need to restore to 5am i need to
> 1. restore 3am full
> 2. restore Differential4am.bak
> 3. restore Differantal5am.bak
> Is this correct ?
> Thanks for any advice
> Scott
>
|||ah i see ! : )
many thanks for the reply and the rename tip.
so thats the key, differential is changes since last full ... didnt know
that. All makes sense now.
Whats "BOL" ?
all the best
scott
|||: ) brillant , thank you very much everyone.
Scott
|||I just thought that you might be interested in a stored procedure for doing
backups that supports creation of backup files with date and time in the file
name, verification of backups as well as deletion of old backup files. It is
available on http://ola.hallengren.com.
Ola Hallengren
"Scott" wrote:

> : ) brillant , thank you very much everyone.
> Scott
>
>

differential backup 2005 advice

Morning.
Im running a FULL BACKUP in overwrite mode daily at 3am.
Im running a DIFFERENTAL BACKUP in overwrite mode hourly.
I cannot lose more than an hours data.
SQL 2005 Standard.
Backup Timeline:
3am Full
4am Differential
5am Overwrites 4am Differental.
etc ..
So if i restore the 5am Differential have i lost the 3am to 4am data ? (does
it let me do this ?)
If this is the case should I create a Differential overwrite as a separeate
file every hour ? (cannot append as data too large).
Example Timeline:
3am full
4am Differental4am.bak
5am Differental5am.bak
So i need to restore to 5am i need to
1. restore 3am full
2. restore Differential4am.bak
3. restore Differantal5am.bak
Is this correct ?
Thanks for any advice
Scottdifferential backup backups all changes since last full backup,
therefore in your scenario you don't lose any data.
If you would restore the database, the procedure is:
1- backup the tail of the log (if it's not corrupted)
2- rstore full
3- restore last diff
4- restore backuped log (from 1)
please read more in BOL
it wouldn't hurt to move/rename old diff when doing new one - instead of
overwrite. Consider the case when diff backup doesn't finish OK. then
you don't have any diff backup 'cause:
1- old file was overwritten
2- new one wasn't finished
Scott wrote:
> Morning.
> Im running a FULL BACKUP in overwrite mode daily at 3am.
> Im running a DIFFERENTAL BACKUP in overwrite mode hourly.
> I cannot lose more than an hours data.
> SQL 2005 Standard.
>
> Backup Timeline:
> 3am Full
> 4am Differential
> 5am Overwrites 4am Differental.
> etc ..
> So if i restore the 5am Differential have i lost the 3am to 4am data ? (does
> it let me do this ?)
> If this is the case should I create a Differential overwrite as a separeate
> file every hour ? (cannot append as data too large).
> Example Timeline:
> 3am full
> 4am Differental4am.bak
> 5am Differental5am.bak
> So i need to restore to 5am i need to
> 1. restore 3am full
> 2. restore Differential4am.bak
> 3. restore Differantal5am.bak
> Is this correct ?
> Thanks for any advice
> Scott
>|||Scott
BOL says
"A differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data."
Test your DDR
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'c:\db.bak' WITH INIT
insert test..test default values
backup database test to disk = 'c:\db_diff1.bak' WITH DIFFERENTIAL
insert test..test default values
backup database test to disk = 'c:\db_diff2.bak' WITH DIFFERENTIAL
GO
RESTORE DATABASE test FROM disk = 'C:\db.bak' WITH FILE = 1, norecovery
--RESTORE DATABASE test FROM disk = 'c:\db_diff1.bak' WITH FILE = 1,
recovery
RESTORE DATABASE test FROM disk = 'c:\db_diff2.bak' WITH FILE = 1, recovery
select * from test..test
DROP Database test
"Scott" <scott_lotus@.yahoo.co.uk> wrote in message
news:extosaChIHA.748@.TK2MSFTNGP04.phx.gbl...
> Morning.
> Im running a FULL BACKUP in overwrite mode daily at 3am.
> Im running a DIFFERENTAL BACKUP in overwrite mode hourly.
> I cannot lose more than an hours data.
> SQL 2005 Standard.
>
> Backup Timeline:
> 3am Full
> 4am Differential
> 5am Overwrites 4am Differental.
> etc ..
> So if i restore the 5am Differential have i lost the 3am to 4am data ?
> (does it let me do this ?)
> If this is the case should I create a Differential overwrite as a
> separeate file every hour ? (cannot append as data too large).
> Example Timeline:
> 3am full
> 4am Differental4am.bak
> 5am Differental5am.bak
> So i need to restore to 5am i need to
> 1. restore 3am full
> 2. restore Differential4am.bak
> 3. restore Differantal5am.bak
> Is this correct ?
> Thanks for any advice
> Scott
>|||ah i see ! : )
many thanks for the reply and the rename tip.
so thats the key, differential is changes since last full ... didnt know
that. All makes sense now.
Whats "BOL" ?
all the best
scott|||Scott wrote:
> ah i see ! : )
> many thanks for the reply and the rename tip.
> so thats the key, differential is changes since last full ... didnt know
> that. All makes sense now.
> Whats "BOL" ?
> all the best
> scott
>
>
BOL is Books On Line - Microsoft SQL Server Help, available with F1 key
(no wonder) :-)|||Hi,
A DIFF backup contains all changes since the last FULL backup, so you
won't loose anything by skipping a DIFF backup file. Actually, in a
restore scenario, you will restore the latest FULL backup and the latest
DIFF backup - you don't need the DIFF backups in between.
Personally I prefer to backup up to seperate files rather than
overwriting existing files. By backing up to seperate files, I'll always
have the older files available if one of the files suddenly becomes
corrupt. If you keep overwriting and only have one file, then you've
lost everything if this file is damaged.
I know there might be cases where you can get older files from a
filebackup, but most companies only run file backups nightly. This means
that if the file is being corrupted in evening, then you've lost the
whole days backup.
You might also want to read up on BACKUP/RESTORE in BOL - that will
explain the different options you have.
Regards
Steen Schlüter Persson
CRM System Specialist / DBA
Scott wrote:
> Morning.
> Im running a FULL BACKUP in overwrite mode daily at 3am.
> Im running a DIFFERENTAL BACKUP in overwrite mode hourly.
> I cannot lose more than an hours data.
> SQL 2005 Standard.
>
> Backup Timeline:
> 3am Full
> 4am Differential
> 5am Overwrites 4am Differental.
> etc ..
> So if i restore the 5am Differential have i lost the 3am to 4am data ? (does
> it let me do this ?)
> If this is the case should I create a Differential overwrite as a separeate
> file every hour ? (cannot append as data too large).
> Example Timeline:
> 3am full
> 4am Differental4am.bak
> 5am Differental5am.bak
> So i need to restore to 5am i need to
> 1. restore 3am full
> 2. restore Differential4am.bak
> 3. restore Differantal5am.bak
> Is this correct ?
> Thanks for any advice
> Scott
>|||: ) brillant , thank you very much everyone.
Scott|||I just thought that you might be interested in a stored procedure for doing
backups that supports creation of backup files with date and time in the file
name, verification of backups as well as deletion of old backup files. It is
available on http://ola.hallengren.com.
Ola Hallengren
"Scott" wrote:
> : ) brillant , thank you very much everyone.
> Scott
>
>