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.
Showing posts with label dba. Show all posts
Showing posts with label dba. Show all posts
Tuesday, March 27, 2012
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.
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.
Sunday, March 11, 2012
Differential Backups
Hi Folks!
I did a few searches and found some interesting threads but was not able to understand much. :)
I recently jumped from support to a DBA position and am learning about SQL Server 2000.
My dilemma: We have a SQL 2000 server with about 500GB's worth of databases that has some jobs from the app locking up due to tape backups. I am performing Full nightly and 1/2hour transaction SQL backups. We run daily differential and weekly full tape backups. It is taking 14 hours to run tape backups daily, causing some of the application jobs to fail. So to test it, I stopped the tape job to run one night and none of the App jobs failed.
So, I am hoping that if I start running nightly differentials, weekly fulls, and 1/2 hour trans, it should work. However, I am not very familiar with Differential jobs as till today i have been using maintenance plans. :confused:
My ideal scenario is this: Differentials start on Sat morning and stop before the weekly backup runs on Friday and then delete the older file when starting a new one. Is this possible? how?
C= OS, D=Backup (Raid 0), G=Data (Raid 5), E=Logs(Raid5).
Any help would be appreciated, and obviously if I have missed some piece of information, please feel free to ask.You're plan seems reasonable. So what's the problem, can't you choose differential backups in your maintplan?
To give you an alternative...
We had similar problems as we're backing up several TB of databases each day. We eventually solved this by using multiple tape units and decreased the size of the backups by using a compression backup tool.|||Haha.. the problem is this:
I can setup a differential job to create a diff file and then append to it till Friday evening. But Sunday I would want a new one to start OR 'clean' the old one and recreate it OR delete the old one and start a new one. You know what i mean? like the maintenance plan does for tlogs/full backups..
Does that make sense? We do not have a third party compression tool and we're cheap .. heh.
oh btw, can't create a maintenance plan for differentials in SQL 2000. :)|||Do the backups need to be appended to an existing one? You can create seperate files for each full, diff and tx backup.
This also has the advantage that you can restore a smaller volume from tape when, say, you wanted to restore until a certain monday.|||Hi Lexiflex,
Please help my dumb ass understand this right.. Heh..
So, i will have 3 jobs right?
1. A weekly full backup that runs on sat and after done deletes the older file
2. Transaction logs that run every 1/2 hour and delete files from 24 before after done.
3. This is where the confusion is..
A differential job that creates a nightly file or appends to it and then when the new week starts, it starts from scratch...
So when it is time to restore, I restore the weekly full then the appended diff file or the diff files from each night after the weekly and then transaction logs upto the time of failure.. right?
I am short on space on the server so that's why i need to delete the older files..
Makes sense?|||Step 3 should be:
3. A differential job that runs nightly from sun - fri and deletes the previous diff backup
I get the feeling your confusion is how to tell the differential backup when to start anew. Don't worry about this because SQL remembers when the last full backup was made and starts from there.
As for the other question:
So when it is time to restore, I restore the weekly full then the appended diff file or the diff files from each night after the weekly and then transaction logs upto the time of failure.. right?
Not quite, a differential backup in SQL 2000 consists of the data that has changed since the last FULL database backup. So when restoring:
1. Restore the weekly full
2. Restore the diff closest to but before the failure
3. Resore the tx backups until just before the failure
But, as always, don't take my word for it but also look it up in the BOL. Search for differential backups, it also suggests some strategies.
Hope this helps.|||Sweet!
It makes sense now.
BACKUP DATABASE 'mydb' TO DISK = N'D:\MSSQL\Backups\mydb.bak' WITH INIT , NOUNLOAD , DIFFERENTIAL
This is it right? Then I scheduled it to run nightly.
Thanks for your help!|||This way you're effectively overwriting (WITH INIT) the previous backup. Make sure that one is securely on the tape!
We always put a date, time and backup type in the file name so we can tell them apart and order them easily. But that's a matter of choice.
Before committing to this solution I recommend doing some tests with backing up and restoring this way (not on you production environment!). You will get a feel for doing it and if you document along the way you'll have your emergeny backup plan 75% finished :cool:
Afterwards make it a point to test restoring (tape to disk to SQL Server) once every few months to check if everything is still in working order (believe me, I've experienced tape units merrily messing up backups for month without the slightest hickup).
Have fun! :D
BTW. NOUNLOAD works only for tape backups.
I did a few searches and found some interesting threads but was not able to understand much. :)
I recently jumped from support to a DBA position and am learning about SQL Server 2000.
My dilemma: We have a SQL 2000 server with about 500GB's worth of databases that has some jobs from the app locking up due to tape backups. I am performing Full nightly and 1/2hour transaction SQL backups. We run daily differential and weekly full tape backups. It is taking 14 hours to run tape backups daily, causing some of the application jobs to fail. So to test it, I stopped the tape job to run one night and none of the App jobs failed.
So, I am hoping that if I start running nightly differentials, weekly fulls, and 1/2 hour trans, it should work. However, I am not very familiar with Differential jobs as till today i have been using maintenance plans. :confused:
My ideal scenario is this: Differentials start on Sat morning and stop before the weekly backup runs on Friday and then delete the older file when starting a new one. Is this possible? how?
C= OS, D=Backup (Raid 0), G=Data (Raid 5), E=Logs(Raid5).
Any help would be appreciated, and obviously if I have missed some piece of information, please feel free to ask.You're plan seems reasonable. So what's the problem, can't you choose differential backups in your maintplan?
To give you an alternative...
We had similar problems as we're backing up several TB of databases each day. We eventually solved this by using multiple tape units and decreased the size of the backups by using a compression backup tool.|||Haha.. the problem is this:
I can setup a differential job to create a diff file and then append to it till Friday evening. But Sunday I would want a new one to start OR 'clean' the old one and recreate it OR delete the old one and start a new one. You know what i mean? like the maintenance plan does for tlogs/full backups..
Does that make sense? We do not have a third party compression tool and we're cheap .. heh.
oh btw, can't create a maintenance plan for differentials in SQL 2000. :)|||Do the backups need to be appended to an existing one? You can create seperate files for each full, diff and tx backup.
This also has the advantage that you can restore a smaller volume from tape when, say, you wanted to restore until a certain monday.|||Hi Lexiflex,
Please help my dumb ass understand this right.. Heh..
So, i will have 3 jobs right?
1. A weekly full backup that runs on sat and after done deletes the older file
2. Transaction logs that run every 1/2 hour and delete files from 24 before after done.
3. This is where the confusion is..
A differential job that creates a nightly file or appends to it and then when the new week starts, it starts from scratch...
So when it is time to restore, I restore the weekly full then the appended diff file or the diff files from each night after the weekly and then transaction logs upto the time of failure.. right?
I am short on space on the server so that's why i need to delete the older files..
Makes sense?|||Step 3 should be:
3. A differential job that runs nightly from sun - fri and deletes the previous diff backup
I get the feeling your confusion is how to tell the differential backup when to start anew. Don't worry about this because SQL remembers when the last full backup was made and starts from there.
As for the other question:
So when it is time to restore, I restore the weekly full then the appended diff file or the diff files from each night after the weekly and then transaction logs upto the time of failure.. right?
Not quite, a differential backup in SQL 2000 consists of the data that has changed since the last FULL database backup. So when restoring:
1. Restore the weekly full
2. Restore the diff closest to but before the failure
3. Resore the tx backups until just before the failure
But, as always, don't take my word for it but also look it up in the BOL. Search for differential backups, it also suggests some strategies.
Hope this helps.|||Sweet!
It makes sense now.
BACKUP DATABASE 'mydb' TO DISK = N'D:\MSSQL\Backups\mydb.bak' WITH INIT , NOUNLOAD , DIFFERENTIAL
This is it right? Then I scheduled it to run nightly.
Thanks for your help!|||This way you're effectively overwriting (WITH INIT) the previous backup. Make sure that one is securely on the tape!
We always put a date, time and backup type in the file name so we can tell them apart and order them easily. But that's a matter of choice.
Before committing to this solution I recommend doing some tests with backing up and restoring this way (not on you production environment!). You will get a feel for doing it and if you document along the way you'll have your emergeny backup plan 75% finished :cool:
Afterwards make it a point to test restoring (tape to disk to SQL Server) once every few months to check if everything is still in working order (believe me, I've experienced tape units merrily messing up backups for month without the slightest hickup).
Have fun! :D
BTW. NOUNLOAD works only for tape backups.
Subscribe to:
Posts (Atom)