Big questions on data warehouse architecture - urgent help needed!

Hi,
I am a relative newcomer to data warehousing and OLAP. We are in the process
of planning a general way forward and an architecture for our warehouse, and
I am seeking advice as to best practice ways forward. We are using SQL
Server 2000, and will extend this with Analysis Services as and when
necessary. The ultimate goal of the project is to provide online reporting
capabilities using the new SQL Server Reporting Services, early next year.
My biggest headache is in designing our dimensions. I have ploughed through
books by Ralph Kimball ( => the authority on dimensional analysis, and
denormalised data warehouse structure with multiple marts making one big
whole) and Claudia Imhoff ( => advocate of a relational 'enterprise' data
warehouse, with dimensional data marts hanging off this). We are likely to
take the Imhoff route and have a relational DW, largely because we want to
have a place to keep historical data in a strcuture similar to the structure
it originated from. But we will use a lot of ideas from Kimball to construct
our data marts.
To the main headache...We have a number of dimensions that originate from
recursive relational tables (namely 'geographic region', 'market sector' and
'business division'). They all have unpredictable numbers of levels in their
hierarchy, and they are type 2 (slowly changing) dimensions - so it's
important to preserve historical relationships. These recursive tables also
are snowflaked - i.e. they form many/many relationships with other dimension
tables.
My question is how we should collapse/explode/denormalize these dimensions?
For our first stage of work we don't really need to do OLAp style analysis -
we can just report on our data as-is - OLAP analysis will come later.
Kimball suggests using 'bridge' tables to descibe the parent/child
hierarchies - these bridge tables are built at the DW load stage - does
anyone have experience doing this?
OR, could we use Analysis Services to handle the recursion and snowflaking -
AS makes big claims about how efficiently it can do this - and then create
cubes from our fact/dimensiosn and report from this?
Any suggestions or criticisms or anything else very gratefully received. If
I am barking up the wrong tree, then please let me know - as I said, I am
new to this.
Thanks, in anticipation.
Chris LewisHi
You may want to ask people of OLAP newsgroup.
"chrislewis@.etsolutions.com" <chrislewis@.etnospamsolutions.com> wrote in
message news:3fe6e368$0$45676$65c69314@.mercury.nildram.net...
> Hi,
> I am a relative newcomer to data warehousing and OLAP. We are in the
process
> of planning a general way forward and an architecture for our warehouse,
and
> I am seeking advice as to best practice ways forward. We are using SQL
> Server 2000, and will extend this with Analysis Services as and when
> necessary. The ultimate goal of the project is to provide online reporting
> capabilities using the new SQL Server Reporting Services, early next year.
> My biggest headache is in designing our dimensions. I have ploughed
through
> books by Ralph Kimball ( => the authority on dimensional analysis, and
> denormalised data warehouse structure with multiple marts making one big
> whole) and Claudia Imhoff ( => advocate of a relational 'enterprise' data
> warehouse, with dimensional data marts hanging off this). We are likely to
> take the Imhoff route and have a relational DW, largely because we want to
> have a place to keep historical data in a strcuture similar to the
structure
> it originated from. But we will use a lot of ideas from Kimball to
construct
> our data marts.
> To the main headache...We have a number of dimensions that originate from
> recursive relational tables (namely 'geographic region', 'market sector'
and
> 'business division'). They all have unpredictable numbers of levels in
their
> hierarchy, and they are type 2 (slowly changing) dimensions - so it's
> important to preserve historical relationships. These recursive tables
also
> are snowflaked - i.e. they form many/many relationships with other
dimension
> tables.
> My question is how we should collapse/explode/denormalize these
dimensions?
> For our first stage of work we don't really need to do OLAp style
analysis -
> we can just report on our data as-is - OLAP analysis will come later.
> Kimball suggests using 'bridge' tables to descibe the parent/child
> hierarchies - these bridge tables are built at the DW load stage - does
> anyone have experience doing this?
> OR, could we use Analysis Services to handle the recursion and
snowflaking -
> AS makes big claims about how efficiently it can do this - and then create
> cubes from our fact/dimensiosn and report from this?
> Any suggestions or criticisms or anything else very gratefully received.
If
> I am barking up the wrong tree, then please let me know - as I said, I am
> new to this.
> Thanks, in anticipation.
> Chris Lewis
>
>

BI Vendors

My company is looking at several vendors of BI software for our data
warehouse. We have never had a data warehouse before and are learning
everything from scratch. Does anyone have any opinions on the
following vendors...
Panorama
Proclarity
Cognos
Business Objects
Information Builders
We are hoping to bring in someone who has lots of DW experience to
help us decide on one of these vendors. But, we are interested in
opinions of people who have experience with these vendors.
Thanks for the help!!
--Lori
Hi Lori,
ProClarity & Panorama are very similar products but given the 2 I would
always go with Panorama. I prefer the look-and-feel and has some nice
functionality (e.g. Bubble-Up exceptions) that ProClarity doesn't.
Did you get all the information you wanted from your same post on
sqlservercentral.com?
cheers
Jamie
"LoriB" wrote:

> My company is looking at several vendors of BI software for our data
> warehouse. We have never had a data warehouse before and are learning
> everything from scratch. Does anyone have any opinions on the
> following vendors...
> Panorama
> Proclarity
> Cognos
> Business Objects
> Information Builders
> We are hoping to bring in someone who has lots of DW experience to
> help us decide on one of these vendors. But, we are interested in
> opinions of people who have experience with these vendors.
> Thanks for the help!!
> --Lori
>
|||Jamie,
Thanks for the info. And, I did get a lot of good opinions in my
other post. I wanted to try to get to as many people as I could so I
posted in several different places.
--Lori
"Jamie" <Jamie@.discussions.microsoft.com> wrote in message news:<1F3F614A-8EEF-4061-9159-2BA25277C12D@.microsoft.com>...
> Hi Lori,
> ProClarity & Panorama are very similar products but given the 2 I would
> always go with Panorama. I prefer the look-and-feel and has some nice
> functionality (e.g. Bubble-Up exceptions) that ProClarity doesn't.
> Did you get all the information you wanted from your same post on
> sqlservercentral.com?
> cheers
> Jamie
|||the answer is never clear, this depend on budget, functionalities required,
number of users...
if you have 100 users and 2 analysts the choice is different from 98
analysts and 2 viewers!
do you use Analysis Services cubes? (so focus on AS compatible applications)
or there is only an SQL Server database for the moment?
AS is required for Proclarity and Panorama. and optional for Cognos, BO and
Info Builders.
do you need to support AS features like actions, drill through, writeback?
* focus on Proclarity, panorama, and some of the AS compatible applications
what type of calculation do you need?
* distinct count are difficult to support outside AS and Cognos Cubes.
* complex calculations required (like: average of the parent level if the
user look at th day, but sum at the year level...) -> AS or Microstrategy
what is the expertise of your users?
* Very low : Info. Builder, Microstrategy (viewer) and Cognos Reportnet
(viewer only) (maybe BO, but the price is high for simply accessing a
report)
* Average : Proclarity / panorama / Cognos Powerplay
* High: Microstrategy / Proclarity / panorama / Bo (WebIntelligence) /
Cognos reportnet (designer role)
Do you need to create high quality and printable reports?
* Microstrategy / BO / Cognos. BO is very good for this, other the web or in
client/server.
The other tools are more analysis oriented than reporting oriented
what is the level of security required?
* Microstrategy and AS cubes are great, so its very easy to open the doors
to your users. Personally, I've never open the doors of BO and Cognos
because the data security is hard to setup. (except in simple viewer
accesses)
There is other interesting tools:
Reportportal; winsight (http://www.winsight.fr/en/index.htm); Arcplan
dynasight; Hyperion etc...
Test reportportal online to see what an AS tool can do. you'll find some of
these features in proclarity and panorama.
I've found the Microstratgy administration tools very good. deploying a
project, or comparing 2 projects is easy.
Cognos reportnet do a great job too.
The BO repository is bad. the organization of the reports and the management
of the published reports is not easy. all the other tools works like a
secure shared folder, where each item published can be secured.
other way to analyse: SDK and integration.
do you need to integrate your data warehouse in home made application?
calculate the price of the SDK if you need it ;-)
Cognos Powerplay cubes cannot be accessed by 3rd party tools.
Microstrategy provides an OLEDB for Olap driver which allow an Excel (or
procalrity, panorama...) users to access it.
Now I prefer to start a project with Excel as the end user tool. and after
I'll work with the users to find the right tool.
Because my users says : I need to analyse, to report to do everything!!! but
after they do nothing because its too complex!
Also each tool is very different from the concurrent, specially between BO
(ad-hoc reporting, scorecard with application fundation)/ Cognos (pushing,
web access, olap access) / Microstrategy (high performance, data security,
analysis capabilities), because each company provides its own OLAP/reporting
engine.
Also, think SQL 2005, the OLAP capabilites are great, so I recommand to
focus on a tools which support AS2005 in the future.
so... good luck in your job! :-)
and welcome in the DW world!
(its late, and my english starts to become very bad... so... ;-) )
"LoriB" <loribrown48@.hotmail.com> a crit dans le message de news:
848869d7.0408131126.46fce96c@.posting.google.com...
> My company is looking at several vendors of BI software for our data
> warehouse. We have never had a data warehouse before and are learning
> everything from scratch. Does anyone have any opinions on the
> following vendors...
> Panorama
> Proclarity
> Cognos
> Business Objects
> Information Builders
> We are hoping to bring in someone who has lots of DW experience to
> help us decide on one of these vendors. But, we are interested in
> opinions of people who have experience with these vendors.
> Thanks for the help!!
> --Lori

BI Vendors

My company is looking at several vendors of BI software for our data
warehouse. We have never had a data warehouse before and are learning
everything from scratch. Does anyone have any opinions on the
following vendors...
Panorama
Proclarity
Cognos
Business Objects
Information Builders
We are hoping to bring in someone who has lots of DW experience to
help us decide on one of these vendors. But, we are interested in
opinions of people who have experience with these vendors.
Thanks for the help!!
--LoriHi Lori,
ProClarity & Panorama are very similar products but given the 2 I would
always go with Panorama. I prefer the look-and-feel and has some nice
functionality (e.g. Bubble-Up exceptions) that ProClarity doesn't.
Did you get all the information you wanted from your same post on
sqlservercentral.com?
cheers
Jamie
"LoriB" wrote:

> My company is looking at several vendors of BI software for our data
> warehouse. We have never had a data warehouse before and are learning
> everything from scratch. Does anyone have any opinions on the
> following vendors...
> Panorama
> Proclarity
> Cognos
> Business Objects
> Information Builders
> We are hoping to bring in someone who has lots of DW experience to
> help us decide on one of these vendors. But, we are interested in
> opinions of people who have experience with these vendors.
> Thanks for the help!!
> --Lori
>|||Jamie,
Thanks for the info. And, I did get a lot of good opinions in my
other post. I wanted to try to get to as many people as I could so I
posted in several different places.
--Lori
"Jamie" <Jamie@.discussions.microsoft.com> wrote in message news:<1F3F614A-8EEF-4061-9159-2BA
25277C12D@.microsoft.com>...
> Hi Lori,
> ProClarity & Panorama are very similar products but given the 2 I would
> always go with Panorama. I prefer the look-and-feel and has some nice
> functionality (e.g. Bubble-Up exceptions) that ProClarity doesn't.
> Did you get all the information you wanted from your same post on
> sqlservercentral.com?
> cheers
> Jamie|||the answer is never clear, this depend on budget, functionalities required,
number of users...
if you have 100 users and 2 analysts the choice is different from 98
analysts and 2 viewers!
do you use Analysis Services cubes? (so focus on AS compatible applications)
or there is only an SQL Server database for the moment?
AS is required for Proclarity and Panorama. and optional for Cognos, BO and
Info Builders.
do you need to support AS features like actions, drill through, writeback?
* focus on Proclarity, panorama, and some of the AS compatible applications
what type of calculation do you need?
* distinct count are difficult to support outside AS and Cognos Cubes.
* complex calculations required (like: average of the parent level if the
user look at th day, but sum at the year level...) -> AS or Microstrategy
what is the expertise of your users?
* Very low : Info. Builder, Microstrategy (viewer) and Cognos Reportnet
(viewer only) (maybe BO, but the price is high for simply accessing a
report)
* Average : Proclarity / panorama / Cognos Powerplay
* High: Microstrategy / Proclarity / panorama / Bo (WebIntelligence) /
Cognos reportnet (designer role)
Do you need to create high quality and printable reports?
* Microstrategy / BO / Cognos. BO is very good for this, other the web or in
client/server.
The other tools are more analysis oriented than reporting oriented
what is the level of security required?
* Microstrategy and AS cubes are great, so its very easy to open the doors
to your users. Personally, I've never open the doors of BO and Cognos
because the data security is hard to setup. (except in simple viewer
accesses)
There is other interesting tools:
Reportportal; winsight (http://www.winsight.fr/en/index.htm); Arcplan
dynasight; Hyperion etc...
Test reportportal online to see what an AS tool can do. you'll find some of
these features in proclarity and panorama.
I've found the Microstratgy administration tools very good. deploying a
project, or comparing 2 projects is easy.
Cognos reportnet do a great job too.
The BO repository is bad. the organization of the reports and the management
of the published reports is not easy. all the other tools works like a
secure shared folder, where each item published can be secured.
other way to analyse: SDK and integration.
do you need to integrate your data warehouse in home made application?
calculate the price of the SDK if you need it ;-)
Cognos Powerplay cubes cannot be accessed by 3rd party tools.
Microstrategy provides an OLEDB for Olap driver which allow an Excel (or
procalrity, panorama...) users to access it.
Now I prefer to start a project with Excel as the end user tool. and after
I'll work with the users to find the right tool.
Because my users says : I need to analyse, to report to do everything!!! but
after they do nothing because its too complex!
Also each tool is very different from the concurrent, specially between BO
(ad-hoc reporting, scorecard with application fundation)/ Cognos (pushing,
web access, olap access) / Microstrategy (high performance, data security,
analysis capabilities), because each company provides its own OLAP/reporting
engine.
Also, think SQL 2005, the OLAP capabilites are great, so I recommand to
focus on a tools which support AS2005 in the future.
so... good luck in your job! :-)
and welcome in the DW world!
(its late, and my english starts to become very bad... so... ;-) )
"LoriB" <loribrown48@.hotmail.com> a crit dans le message de news:
848869d7.0408131126.46fce96c@.posting.google.com...
> My company is looking at several vendors of BI software for our data
> warehouse. We have never had a data warehouse before and are learning
> everything from scratch. Does anyone have any opinions on the
> following vendors...
> Panorama
> Proclarity
> Cognos
> Business Objects
> Information Builders
> We are hoping to bring in someone who has lots of DW experience to
> help us decide on one of these vendors. But, we are interested in
> opinions of people who have experience with these vendors.
> Thanks for the help!!
> --Lori

BI Deployment Experiences

I'm trying to get some info on how other companies are handling BI
implementations. We have a data warehouse project that we expect will have
continuos changes. That is, new data sources coming in, more reports, etc.
That implies new Integration Services packages, new Reporting Services
reports, changes in tables, view, stored procedures, etc. We also have a 3
environment installation: Development, QA, Production.
I would appreciate any info on how other people are handling this. Can you
manage with only Visual Studio? Is there any third vendor tool anybody would
recommend? The issue is that we could potentially have the 3 environments
with different versions and we might want to move objects from development t
o
QA, for example, without just duplicating the 2 environments (we could have
different stages of development and only want to move finished objects).
Any help is appreciated,
Carmen.We have pretty much the exact same setup you do. We use Visual Studio and
Visual Source Safe to manage all our code. Using the new features for
managing environments within Visual Studio - makes promoting SSIS packages a
snap. Using DTS was more challenging when you for example moved from DEV to
TEST you had to change all your data sources to point to new places - but
SSIS makes that part easy.
If you tie your solutions into VSS and have your coders do all their work
inside of VSS - that will ensure it's managed correctly. Where I work won't
spring for the new Visual Studio Team Edition for DB Professionals - but
that def makes things easier in managing your DB code within the same
solution as your SSIS packages. You can still add the DB into your
solution, but not have ALL the cool features Team Edition offers.
So our developers, by using the VSS integration inside VS, it auto checks
out all the code, whether it be packages or stored procedures. Your
promoting to new environments from Visual Source Safe will endure you are
promoting the right stuff.
It's not that hard - once you get it sorted out and documented and follow
the process - you will be good.
"Carmen" <Carmen@.discussions.microsoft.com> wrote in message
news:B9EAA9CD-B73C-43D3-A314-5E99EE3DC694@.microsoft.com...
> I'm trying to get some info on how other companies are handling BI
> implementations. We have a data warehouse project that we expect will have
> continuos changes. That is, new data sources coming in, more reports, etc.
> That implies new Integration Services packages, new Reporting Services
> reports, changes in tables, view, stored procedures, etc. We also have a 3
> environment installation: Development, QA, Production.
> I would appreciate any info on how other people are handling this. Can you
> manage with only Visual Studio? Is there any third vendor tool anybody
> would
> recommend? The issue is that we could potentially have the 3 environments
> with different versions and we might want to move objects from development
> to
> QA, for example, without just duplicating the 2 environments (we could
> have
> different stages of development and only want to move finished objects).
> Any help is appreciated,
> Carmen.|||Our approach is to use XMLA scripts (using ascmd utility from SP1) and the
AS syncronize feature (again via XMLA scripts). Works fine - and no
Integration Services required.
Chris
www.activeinterface.com
"Carmen" <Carmen@.discussions.microsoft.com> wrote in message
news:B9EAA9CD-B73C-43D3-A314-5E99EE3DC694@.microsoft.com...
> I'm trying to get some info on how other companies are handling BI
> implementations. We have a data warehouse project that we expect will have
> continuos changes. That is, new data sources coming in, more reports, etc.
> That implies new Integration Services packages, new Reporting Services
> reports, changes in tables, view, stored procedures, etc. We also have a 3
> environment installation: Development, QA, Production.
> I would appreciate any info on how other people are handling this. Can you
> manage with only Visual Studio? Is there any third vendor tool anybody
> would
> recommend? The issue is that we could potentially have the 3 environments
> with different versions and we might want to move objects from development
> to
> QA, for example, without just duplicating the 2 environments (we could
> have
> different stages of development and only want to move finished objects).
> Any help is appreciated,
> Carmen.|||Chris,
Thanks for the info!
Carmen.
"ChrisHarrington" wrote:

> Our approach is to use XMLA scripts (using ascmd utility from SP1) and the
> AS syncronize feature (again via XMLA scripts). Works fine - and no
> Integration Services required.
> Chris
> www.activeinterface.com
> "Carmen" <Carmen@.discussions.microsoft.com> wrote in message
> news:B9EAA9CD-B73C-43D3-A314-5E99EE3DC694@.microsoft.com...
>
>|||Joe,
Thanks for the info. I'll definitely investigate the options you mentioned.
Carmen.
"Joe" wrote:

> We have pretty much the exact same setup you do. We use Visual Studio and
> Visual Source Safe to manage all our code. Using the new features for
> managing environments within Visual Studio - makes promoting SSIS packages
a
> snap. Using DTS was more challenging when you for example moved from DEV
to
> TEST you had to change all your data sources to point to new places - but
> SSIS makes that part easy.
> If you tie your solutions into VSS and have your coders do all their work
> inside of VSS - that will ensure it's managed correctly. Where I work won
't
> spring for the new Visual Studio Team Edition for DB Professionals - but
> that def makes things easier in managing your DB code within the same
> solution as your SSIS packages. You can still add the DB into your
> solution, but not have ALL the cool features Team Edition offers.
> So our developers, by using the VSS integration inside VS, it auto checks
> out all the code, whether it be packages or stored procedures. Your
> promoting to new environments from Visual Source Safe will endure you are
> promoting the right stuff.
> It's not that hard - once you get it sorted out and documented and follow
> the process - you will be good.
> "Carmen" <Carmen@.discussions.microsoft.com> wrote in message
> news:B9EAA9CD-B73C-43D3-A314-5E99EE3DC694@.microsoft.com...
>

BI Deployment Experiences

I'm trying to get some info on how other companies are handling BI
implementations. We have a data warehouse project that we expect will have
continuos changes. That is, new data sources coming in, more reports, etc.
That implies new Integration Services packages, new Reporting Services
reports, changes in tables, view, stored procedures, etc. We also have a 3
environment installation: Development, QA, Production.
I would appreciate any info on how other people are handling this. Can you
manage with only Visual Studio? Is there any third vendor tool anybody would
recommend? The issue is that we could potentially have the 3 environments
with different versions and we might want to move objects from development to
QA, for example, without just duplicating the 2 environments (we could have
different stages of development and only want to move finished objects).
Any help is appreciated,
Carmen.
We have pretty much the exact same setup you do. We use Visual Studio and
Visual Source Safe to manage all our code. Using the new features for
managing environments within Visual Studio - makes promoting SSIS packages a
snap. Using DTS was more challenging when you for example moved from DEV to
TEST you had to change all your data sources to point to new places - but
SSIS makes that part easy.
If you tie your solutions into VSS and have your coders do all their work
inside of VSS - that will ensure it's managed correctly. Where I work won't
spring for the new Visual Studio Team Edition for DB Professionals - but
that def makes things easier in managing your DB code within the same
solution as your SSIS packages. You can still add the DB into your
solution, but not have ALL the cool features Team Edition offers.
So our developers, by using the VSS integration inside VS, it auto checks
out all the code, whether it be packages or stored procedures. Your
promoting to new environments from Visual Source Safe will endure you are
promoting the right stuff.
It's not that hard - once you get it sorted out and documented and follow
the process - you will be good.
"Carmen" <Carmen@.discussions.microsoft.com> wrote in message
news:B9EAA9CD-B73C-43D3-A314-5E99EE3DC694@.microsoft.com...
> I'm trying to get some info on how other companies are handling BI
> implementations. We have a data warehouse project that we expect will have
> continuos changes. That is, new data sources coming in, more reports, etc.
> That implies new Integration Services packages, new Reporting Services
> reports, changes in tables, view, stored procedures, etc. We also have a 3
> environment installation: Development, QA, Production.
> I would appreciate any info on how other people are handling this. Can you
> manage with only Visual Studio? Is there any third vendor tool anybody
> would
> recommend? The issue is that we could potentially have the 3 environments
> with different versions and we might want to move objects from development
> to
> QA, for example, without just duplicating the 2 environments (we could
> have
> different stages of development and only want to move finished objects).
> Any help is appreciated,
> Carmen.
|||Our approach is to use XMLA scripts (using ascmd utility from SP1) and the
AS syncronize feature (again via XMLA scripts). Works fine - and no
Integration Services required.
Chris
www.activeinterface.com
"Carmen" <Carmen@.discussions.microsoft.com> wrote in message
news:B9EAA9CD-B73C-43D3-A314-5E99EE3DC694@.microsoft.com...
> I'm trying to get some info on how other companies are handling BI
> implementations. We have a data warehouse project that we expect will have
> continuos changes. That is, new data sources coming in, more reports, etc.
> That implies new Integration Services packages, new Reporting Services
> reports, changes in tables, view, stored procedures, etc. We also have a 3
> environment installation: Development, QA, Production.
> I would appreciate any info on how other people are handling this. Can you
> manage with only Visual Studio? Is there any third vendor tool anybody
> would
> recommend? The issue is that we could potentially have the 3 environments
> with different versions and we might want to move objects from development
> to
> QA, for example, without just duplicating the 2 environments (we could
> have
> different stages of development and only want to move finished objects).
> Any help is appreciated,
> Carmen.
|||Chris,
Thanks for the info!
Carmen.
"ChrisHarrington" wrote:

> Our approach is to use XMLA scripts (using ascmd utility from SP1) and the
> AS syncronize feature (again via XMLA scripts). Works fine - and no
> Integration Services required.
> Chris
> www.activeinterface.com
> "Carmen" <Carmen@.discussions.microsoft.com> wrote in message
> news:B9EAA9CD-B73C-43D3-A314-5E99EE3DC694@.microsoft.com...
>
>
|||Joe,
Thanks for the info. I'll definitely investigate the options you mentioned.
Carmen.
"Joe" wrote:

> We have pretty much the exact same setup you do. We use Visual Studio and
> Visual Source Safe to manage all our code. Using the new features for
> managing environments within Visual Studio - makes promoting SSIS packages a
> snap. Using DTS was more challenging when you for example moved from DEV to
> TEST you had to change all your data sources to point to new places - but
> SSIS makes that part easy.
> If you tie your solutions into VSS and have your coders do all their work
> inside of VSS - that will ensure it's managed correctly. Where I work won't
> spring for the new Visual Studio Team Edition for DB Professionals - but
> that def makes things easier in managing your DB code within the same
> solution as your SSIS packages. You can still add the DB into your
> solution, but not have ALL the cool features Team Edition offers.
> So our developers, by using the VSS integration inside VS, it auto checks
> out all the code, whether it be packages or stored procedures. Your
> promoting to new environments from Visual Source Safe will endure you are
> promoting the right stuff.
> It's not that hard - once you get it sorted out and documented and follow
> the process - you will be good.
> "Carmen" <Carmen@.discussions.microsoft.com> wrote in message
> news:B9EAA9CD-B73C-43D3-A314-5E99EE3DC694@.microsoft.com...
>

BI and Reporting Tools used with Reporting Services

What tools (companies) have you found to work well with your Data Warehouse,
easy to use by non-developers and also interface with MS Reporting Services?
JBH1
> What tools (companies) have you found to work well with your Data
> Warehouse,
> easy to use by non-developers and also interface with MS Reporting
> Services?
*sigh* i have a thread wondering this same thing. i can't find anything. And
getting information from the actual companies is like pulling teeth.
|||> What tools (companies) have you found to work well with your Data
> Warehouse,
> easy to use by non-developers and also interface with MS Reporting
> Services?
Oh, and btw, a guy sent me a link to a site where: if you're willing to pay,
he'll let you read his reviews of various data warehouse access tools.
That's how much of a secret this stuff is.
|||I have some links related to reporting services and 3rd party companies on
Reporting Services.
You might want to have a look at the SQL 2005 Report Builder as well.
Cizer.net has some nice tools too.
Links are provided on my website .
www.dandyman.net/sql
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Jeff H." <JeffH@.discussions.microsoft.com> wrote in message
news:E5BFF01A-13CF-494A-9D90-AED28D7EC64B@.microsoft.com...
> What tools (companies) have you found to work well with your Data
> Warehouse,
> easy to use by non-developers and also interface with MS Reporting
> Services?
> --
> JBH1
|||This is sort of a broad question.
How isl the data being accessed by the end users stored?
"Jeff H." <JeffH@.discussions.microsoft.com> wrote in message
news:E5BFF01A-13CF-494A-9D90-AED28D7EC64B@.microsoft.com...
> What tools (companies) have you found to work well with your Data
> Warehouse,
> easy to use by non-developers and also interface with MS Reporting
> Services?
> --
> JBH1
|||Ian,
I am not sure many of the major BI vendors are keen to work closely
with RS...after all MSFT has now announced it's intention of 'eating
their lunch'....remember how low key the first release of RS was? And
no MSFT is 'talking it up' as a real alternative to Crystal Reports now
owned by Business Objects.
I am certainly aware that Business Objects is training up staff to
compete with RS2005.
When an 800 pound gorilla like MSFT steps into the BI marketplace all
vendors are going to do whatever they can to maintain their
clients....
All those vendors (BO, COGN, MSTR etc) will all work with SQL Server
and position their tools as somehow 'better' than Report Services....
For the last 7-8 years people have talked about integration of the
development environment between vendors, usually via some form of
metadata hub (for example Ascentials MetaStage and the MetaData
Coalition etc.). However, this has been a pretty fruitless exercise.
The BI vendors are not interested in making it easier to share their
metadata with other BI vendors. Whoever owns 'what is presented on the
screen' owns the client and all of them are trying to protect their
position from MSFTs RS efforts.
Ufortunately IT shops and business users have remained subbornly
ignorant of the fact that 80% of the work is in database design and ETL
and 20% in the presentation layer...and people buy the presentation
layer and not the infrastructure/architecture. This make 'what is on
the screen' even more important to protect.
Best Regards
Peter Nolan
www.peternolan.com
|||What do you mean by interfacing with Reporting Services? Microsoft itself
provides some good BI front tend tools for 2005.
"Jeff H." <JeffH@.discussions.microsoft.com> wrote in message
news:E5BFF01A-13CF-494A-9D90-AED28D7EC64B@.microsoft.com...
> What tools (companies) have you found to work well with your Data
> Warehouse,
> easy to use by non-developers and also interface with MS Reporting
> Services?
> --
> JBH1
|||In a data warehouse. In fact tables, surrounded by dimension tables.
"Jesse O" <jesperzz@.hotmail.com> wrote in message
news:ee5Z7SYuFHA.596@.TK2MSFTNGP12.phx.gbl...
> This is sort of a broad question.
> How isl the data being accessed by the end users stored?
>
>
> "Jeff H." <JeffH@.discussions.microsoft.com> wrote in message
> news:E5BFF01A-13CF-494A-9D90-AED28D7EC64B@.microsoft.com...
>
|||Hi JT,
which ones? I have read zip about any good tools that integrate RS into
them...we are doing this work for ourselves...
We have been surprised there is no good browser front end for RS and
that the answer we have gotten back so far is that we must build one
for ourselves...?
Thanks
Peter Nolan
www.peternolan.com
|||Depending on what you are looking for. Cizer has an interesting
web-based report builder environment. My favorite though is
SoftArtisans OfficeWriter -
http://officewriter.softartisans.com...riter-250.aspx
Because RS has a pretty complete array of rendering mechanisms, there
hasn't been a real need (at least ont he dozen or so projects I have
worked on) for a 3rd party add-on.
If you want to be a bit more specific about what problems you are
having, that you are looking for a solution too, I'll see if I can find
something.
Steve Muise
neudesic LLC

BI and Reporting Tools used with Reporting Services

What tools (companies) have you found to work well with your Data Warehouse,
easy to use by non-developers and also interface with MS Reporting Services?
--
JBH1> What tools (companies) have you found to work well with your Data
> Warehouse,
> easy to use by non-developers and also interface with MS Reporting
> Services?
*sigh* i have a thread wondering this same thing. i can't find anything. And
getting information from the actual companies is like pulling teeth.|||> What tools (companies) have you found to work well with your Data
> Warehouse,
> easy to use by non-developers and also interface with MS Reporting
> Services?
Oh, and btw, a guy sent me a link to a site where: if you're willing to pay,
he'll let you read his reviews of various data warehouse access tools.
That's how much of a secret this stuff is.|||I have some links related to reporting services and 3rd party companies on
Reporting Services.
You might want to have a look at the SQL 2005 Report Builder as well.
Cizer.net has some nice tools too.
Links are provided on my website .
www.dandyman.net/sql
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Jeff H." <JeffH@.discussions.microsoft.com> wrote in message
news:E5BFF01A-13CF-494A-9D90-AED28D7EC64B@.microsoft.com...
> What tools (companies) have you found to work well with your Data
> Warehouse,
> easy to use by non-developers and also interface with MS Reporting
> Services?
> --
> JBH1|||This is sort of a broad question.
How isl the data being accessed by the end users stored?
"Jeff H." <JeffH@.discussions.microsoft.com> wrote in message
news:E5BFF01A-13CF-494A-9D90-AED28D7EC64B@.microsoft.com...
> What tools (companies) have you found to work well with your Data
> Warehouse,
> easy to use by non-developers and also interface with MS Reporting
> Services?
> --
> JBH1|||Ian,
I am not sure many of the major BI vendors are keen to work closely
with RS...after all MSFT has now announced it's intention of 'eating
their lunch'....remember how low key the first release of RS was? And
no MSFT is 'talking it up' as a real alternative to Crystal Reports now
owned by Business Objects.
I am certainly aware that Business Objects is training up staff to
compete with RS2005.
When an 800 pound gorilla like MSFT steps into the BI marketplace all
vendors are going to do whatever they can to maintain their
clients....
All those vendors (BO, COGN, MSTR etc) will all work with SQL Server
and position their tools as somehow 'better' than Report Services....
For the last 7-8 years people have talked about integration of the
development environment between vendors, usually via some form of
metadata hub (for example Ascentials MetaStage and the MetaData
Coalition etc.). However, this has been a pretty fruitless exercise.
The BI vendors are not interested in making it easier to share their
metadata with other BI vendors. Whoever owns 'what is presented on the
screen' owns the client and all of them are trying to protect their
position from MSFTs RS efforts.
Ufortunately IT shops and business users have remained subbornly
ignorant of the fact that 80% of the work is in database design and ETL
and 20% in the presentation layer...and people buy the presentation
layer and not the infrastructure/architecture. This make 'what is on
the screen' even more important to protect.
Best Regards
Peter Nolan
www.peternolan.com|||What do you mean by interfacing with Reporting Services? Microsoft itself
provides some good BI front tend tools for 2005.
"Jeff H." <JeffH@.discussions.microsoft.com> wrote in message
news:E5BFF01A-13CF-494A-9D90-AED28D7EC64B@.microsoft.com...
> What tools (companies) have you found to work well with your Data
> Warehouse,
> easy to use by non-developers and also interface with MS Reporting
> Services?
> --
> JBH1|||In a data warehouse. In fact tables, surrounded by dimension tables.
"Jesse O" <jesperzz@.hotmail.com> wrote in message
news:ee5Z7SYuFHA.596@.TK2MSFTNGP12.phx.gbl...
> This is sort of a broad question.
> How isl the data being accessed by the end users stored?
>
>
> "Jeff H." <JeffH@.discussions.microsoft.com> wrote in message
> news:E5BFF01A-13CF-494A-9D90-AED28D7EC64B@.microsoft.com...
>|||Hi JT,
which ones? I have read zip about any good tools that integrate RS into
them...we are doing this work for ourselves...
We have been surprised there is no good browser front end for RS and
that the answer we have gotten back so far is that we must build one
for ourselves...'
Thanks
Peter Nolan
www.peternolan.com|||Depending on what you are looking for. Cizer has an interesting
web-based report builder environment. My favorite though is
SoftArtisans OfficeWriter -
http://officewriter.softartisans.co...writer-250.aspx
Because RS has a pretty complete array of rendering mechanisms, there
hasn't been a real need (at least ont he dozen or so projects I have
worked on) for a 3rd party add-on.
If you want to be a bit more specific about what problems you are
having, that you are looking for a solution too, I'll see if I can find
something.
Steve Muise
neudesic LLC

BI Accelerator Cube Reprocess Problems

We are currently trying to implement a BI solution for a data warehouse,
but can't seem to get the last few kinks worked out.
I have a cube made by BI that has monthly partitions. The problem is
that whenever I add new dimention information (which should be an
incremental update), I have to reprocess the entire cube. This is not
really an option, since it takes 57 hours to do that. So, does anyone
have a suggestion why BI is not doing an incremental update?
Thanks,
Chris
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Two comments:
1) Re: "The problem is that whenever I add new dimention information (which
should be an incremental update), I have to reprocess the entire cube." Can
you be more specific what you are using? To "add" this new information are
you doing a full process of the dimension? Or an incremental process of the
dimension? I am a bit confused because you said "which should be an
incremental update" -- is it or not?
One of the things that you need to remember is that when you do a full
process of a non-changing dimension (regardless if the data changed or not),
then a full process of the cube is always required. This is because a full
process of the dimension could have caused changes in the hierarchy, which
would have likewise changed all of the aggregates -- which is a full
reprocess.
2) Re: "This is not really an option, since it takes 57 hours to do that."
You should be looking at improving that time. The general rule of thumb that
I use is that a server-class machine processing a typical partition should
be able to process about 1 million rows per minute. If your system is not
getting performance close to that then you should be looking at the general
best practices outlined in the SSAS Performance Guide at:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
see the section titled: "Optimizing Analysis Services to Improve Processing
Performance"
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris Timko" <ctimko@.hersheys.com> wrote in message
news:eP1Nk8lzEHA.2316@.TK2MSFTNGP15.phx.gbl...
> We are currently trying to implement a BI solution for a data warehouse,
> but can't seem to get the last few kinks worked out.
> I have a cube made by BI that has monthly partitions. The problem is
> that whenever I add new dimention information (which should be an
> incremental update), I have to reprocess the entire cube. This is not
> really an option, since it takes 57 hours to do that. So, does anyone
> have a suggestion why BI is not doing an incremental update?
> Thanks,
> Chris
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||It is simply adding new members to the lowest level of the hierarchy,
there were no changes being made to the rest of the structure. I guess
I am going to have to look at some of the other ways BI processes
dimentions.
And yes, we would like to get our cube running a little (actually, a
lot) faster. Right now, though, we need to make sure a full reprocess
isn't needed every time we add items.
Chris
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Adding new members should *not* force a full reprocess.
Look in the documentation, I believe there is a way to force it do an
incremental only.
As a worst-case, you can modify the packages by-hand for only do an
incremental.
However I had thought that we had this worked out correctly so that we
detected if an incremental or full was required, e.g. deleting a member
forces a full.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris Timko" <ctimko@.hersheys.com> wrote in message
news:eAe%233UL0EHA.3820@.TK2MSFTNGP11.phx.gbl...
> It is simply adding new members to the lowest level of the hierarchy,
> there were no changes being made to the rest of the structure. I guess
> I am going to have to look at some of the other ways BI processes
> dimentions.
> And yes, we would like to get our cube running a little (actually, a
> lot) faster. Right now, though, we need to make sure a full reprocess
> isn't needed every time we add items.
> Chris
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||I made the change to the only incremental update for the dimentions, and
it seemed to update just fine. If a full process needed to be run
instead of the incremental, would the BI dts update package crash? If
so, then it seems like there may exist a bug in the auto update of
dimentions. I plan to at some point (after some data massaging) use the
flagged update algorithm, so some of the dimentions can be changed.
thanks for the help,
Chris
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

BI Accelerator Cube Reprocess Problems

We are currently trying to implement a BI solution for a data warehouse,
but can't seem to get the last few kinks worked out.
I have a cube made by BI that has monthly partitions. The problem is
that whenever I add new dimention information (which should be an
incremental update), I have to reprocess the entire cube. This is not
really an option, since it takes 57 hours to do that. So, does anyone
have a suggestion why BI is not doing an incremental update?
Thanks,
Chris
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Two comments:
1) Re: "The problem is that whenever I add new dimention information (which
should be an incremental update), I have to reprocess the entire cube." Can
you be more specific what you are using? To "add" this new information are
you doing a full process of the dimension? Or an incremental process of the
dimension? I am a bit confused because you said "which should be an
incremental update" -- is it or not?
One of the things that you need to remember is that when you do a full
process of a non-changing dimension (regardless if the data changed or not),
then a full process of the cube is always required. This is because a full
process of the dimension could have caused changes in the hierarchy, which
would have likewise changed all of the aggregates -- which is a full
reprocess.
2) Re: "This is not really an option, since it takes 57 hours to do that."
You should be looking at improving that time. The general rule of thumb that
I use is that a server-class machine processing a typical partition should
be able to process about 1 million rows per minute. If your system is not
getting performance close to that then you should be looking at the general
best practices outlined in the SSAS Performance Guide at:
http://www.microsoft.com/technet/pr...n/ansvcspg.mspx
see the section titled: "Optimizing Analysis Services to Improve Processing
Performance"
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris Timko" <ctimko@.hersheys.com> wrote in message
news:eP1Nk8lzEHA.2316@.TK2MSFTNGP15.phx.gbl...
> We are currently trying to implement a BI solution for a data warehouse,
> but can't seem to get the last few kinks worked out.
> I have a cube made by BI that has monthly partitions. The problem is
> that whenever I add new dimention information (which should be an
> incremental update), I have to reprocess the entire cube. This is not
> really an option, since it takes 57 hours to do that. So, does anyone
> have a suggestion why BI is not doing an incremental update?
> Thanks,
> Chris
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||It is simply adding new members to the lowest level of the hierarchy,
there were no changes being made to the rest of the structure. I guess
I am going to have to look at some of the other ways BI processes
dimentions.
And yes, we would like to get our cube running a little (actually, a
lot) faster. Right now, though, we need to make sure a full reprocess
isn't needed every time we add items.
Chris
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Adding new members should *not* force a full reprocess.
Look in the documentation, I believe there is a way to force it do an
incremental only.
As a worst-case, you can modify the packages by-hand for only do an
incremental.
However I had thought that we had this worked out correctly so that we
detected if an incremental or full was required, e.g. deleting a member
forces a full.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris Timko" <ctimko@.hersheys.com> wrote in message
news:eAe%233UL0EHA.3820@.TK2MSFTNGP11.phx.gbl...
> It is simply adding new members to the lowest level of the hierarchy,
> there were no changes being made to the rest of the structure. I guess
> I am going to have to look at some of the other ways BI processes
> dimentions.
> And yes, we would like to get our cube running a little (actually, a
> lot) faster. Right now, though, we need to make sure a full reprocess
> isn't needed every time we add items.
> Chris
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||I made the change to the only incremental update for the dimentions, and
it seemed to update just fine. If a full process needed to be run
instead of the incremental, would the BI dts update package crash? If
so, then it seems like there may exist a bug in the auto update of
dimentions. I plan to at some point (after some data massaging) use the
flagged update algorithm, so some of the dimentions can be changed.
thanks for the help,
Chris
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Best way to replicate huge transactions

We do nightly processing for our warehouse data on Server A and we replicate
the data after all the processing to Server B.
What is the fastest way to push this data across ? Right now it seems like
it takes more than an hour to push all the changes across.i.e an hour
latency. We are using trans replication. Any parameters we can tweak to push
the data faster or are there any other techniques.
Please advice.. Thanks
Hassan,
this article quantifies the effects of using most of the available
parameters:
http://www.microsoft.com/technet/pro.../tranrepl.mspx
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||replicate the execution of stored procedures if
1) your stored procedures modify more than one row at a time
2) the bulk of your batch operations use procs
consider using merge replication if
1) your batch operations modify the same row many times, ie a stock market
application.
2) use download only exchangetype
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Best way to archive data?

We have a data warehouse of approximately 200Gb. Our data is separated by
yearly quarters into separate tables and filegroups. Each quarter is in one
table and there is one table per filegroup. There is a view which unions all
the tables and makes the data accesible to the application. Currently we are
backing up the full database every night. I need to make a case to convice
management to archive the data and need to know what would be the best
approach. The main two factors to consider are speed and ease of recovering
the archived data if users need it. I am considering the options below. Which
one would you recommend?
1. BCP out data and shrink filegroups - to recover, expand filegroups & bcp
in.
2. Same as #1 but instead move the data to a separate database and then back
that up or restore it to recover the data and copy it across.
3. Place data in separate databases and backup/drop/restore as needed (The
view will need to changed each time. I also don't know how this will affect
select performance if the view is spread across the databases on the same
server.)
4. Back up the individual filegroups, empty them, and then restore them
individually if needed. (don't know if this is possible)I have a similar situation at my shop. What we did was create an Archive db
on a separate server that was a restore of our Live db. We then truncated
out the current year's data from the Archive db. Next we truncated all the
previous year's data from the Live db. We left the tables in place so we
didn't have to update any of our views. Needless to say the queries on the
Live db are much faster since there is a lot less data. Our Live db is
about 150 GB and our Archive db is about 100 GB. The other thing that was
helpful about this scenario is that we kept our support tables in tact in
our Archive db. Additionally, we replicate the support tables from Live to
Archive. So if a request comes in from a customer who wanted information
from a previous year, it's a piece of cake to run the report on the Archive
db since all support tables/parameters are in place.
HTH.
Andre
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:9CE1DDB3-DB6D-42C3-9187-64B8E51212CE@.microsoft.com...
> We have a data warehouse of approximately 200Gb. Our data is separated by
> yearly quarters into separate tables and filegroups. Each quarter is in
one
> table and there is one table per filegroup. There is a view which unions
all
> the tables and makes the data accesible to the application. Currently we
are
> backing up the full database every night. I need to make a case to convice
> management to archive the data and need to know what would be the best
> approach. The main two factors to consider are speed and ease of
recovering
> the archived data if users need it. I am considering the options below.
Which
> one would you recommend?
> 1. BCP out data and shrink filegroups - to recover, expand filegroups &
bcp
> in.
> 2. Same as #1 but instead move the data to a separate database and then
back
> that up or restore it to recover the data and copy it across.
> 3. Place data in separate databases and backup/drop/restore as needed (The
> view will need to changed each time. I also don't know how this will
affect
> select performance if the view is spread across the databases on the same
> server.)
> 4. Back up the individual filegroups, empty them, and then restore them
> individually if needed. (don't know if this is possible)

Best way to archive data?

We have a data warehouse of approximately 200Gb. Our data is separated by
yearly quarters into separate tables and filegroups. Each quarter is in one
table and there is one table per filegroup. There is a view which unions all
the tables and makes the data accesible to the application. Currently we are
backing up the full database every night. I need to make a case to convice
management to archive the data and need to know what would be the best
approach. The main two factors to consider are speed and ease of recovering
the archived data if users need it. I am considering the options below. Which
one would you recommend?
1. BCP out data and shrink filegroups - to recover, expand filegroups & bcp
in.
2. Same as #1 but instead move the data to a separate database and then back
that up or restore it to recover the data and copy it across.
3. Place data in separate databases and backup/drop/restore as needed (The
view will need to changed each time. I also don't know how this will affect
select performance if the view is spread across the databases on the same
server.)
4. Back up the individual filegroups, empty them, and then restore them
individually if needed. (don't know if this is possible)
I have a similar situation at my shop. What we did was create an Archive db
on a separate server that was a restore of our Live db. We then truncated
out the current year's data from the Archive db. Next we truncated all the
previous year's data from the Live db. We left the tables in place so we
didn't have to update any of our views. Needless to say the queries on the
Live db are much faster since there is a lot less data. Our Live db is
about 150 GB and our Archive db is about 100 GB. The other thing that was
helpful about this scenario is that we kept our support tables in tact in
our Archive db. Additionally, we replicate the support tables from Live to
Archive. So if a request comes in from a customer who wanted information
from a previous year, it's a piece of cake to run the report on the Archive
db since all support tables/parameters are in place.
HTH.
Andre
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:9CE1DDB3-DB6D-42C3-9187-64B8E51212CE@.microsoft.com...
> We have a data warehouse of approximately 200Gb. Our data is separated by
> yearly quarters into separate tables and filegroups. Each quarter is in
one
> table and there is one table per filegroup. There is a view which unions
all
> the tables and makes the data accesible to the application. Currently we
are
> backing up the full database every night. I need to make a case to convice
> management to archive the data and need to know what would be the best
> approach. The main two factors to consider are speed and ease of
recovering
> the archived data if users need it. I am considering the options below.
Which
> one would you recommend?
> 1. BCP out data and shrink filegroups - to recover, expand filegroups &
bcp
> in.
> 2. Same as #1 but instead move the data to a separate database and then
back
> that up or restore it to recover the data and copy it across.
> 3. Place data in separate databases and backup/drop/restore as needed (The
> view will need to changed each time. I also don't know how this will
affect
> select performance if the view is spread across the databases on the same
> server.)
> 4. Back up the individual filegroups, empty them, and then restore them
> individually if needed. (don't know if this is possible)

Best way to archive data?

We have a data warehouse of approximately 200Gb. Our data is separated by
yearly quarters into separate tables and filegroups. Each quarter is in one
table and there is one table per filegroup. There is a view which unions all
the tables and makes the data accesible to the application. Currently we are
backing up the full database every night. I need to make a case to convice
management to archive the data and need to know what would be the best
approach. The main two factors to consider are speed and ease of recovering
the archived data if users need it. I am considering the options below. Whic
h
one would you recommend?
1. BCP out data and shrink filegroups - to recover, expand filegroups & bcp
in.
2. Same as #1 but instead move the data to a separate database and then back
that up or restore it to recover the data and copy it across.
3. Place data in separate databases and backup/drop/restore as needed (The
view will need to changed each time. I also don't know how this will affect
select performance if the view is spread across the databases on the same
server.)
4. Back up the individual filegroups, empty them, and then restore them
individually if needed. (don't know if this is possible)I have a similar situation at my shop. What we did was create an Archive db
on a separate server that was a restore of our Live db. We then truncated
out the current year's data from the Archive db. Next we truncated all the
previous year's data from the Live db. We left the tables in place so we
didn't have to update any of our views. Needless to say the queries on the
Live db are much faster since there is a lot less data. Our Live db is
about 150 GB and our Archive db is about 100 GB. The other thing that was
helpful about this scenario is that we kept our support tables in tact in
our Archive db. Additionally, we replicate the support tables from Live to
Archive. So if a request comes in from a customer who wanted information
from a previous year, it's a piece of cake to run the report on the Archive
db since all support tables/parameters are in place.
HTH.
Andre
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:9CE1DDB3-DB6D-42C3-9187-64B8E51212CE@.microsoft.com...
> We have a data warehouse of approximately 200Gb. Our data is separated by
> yearly quarters into separate tables and filegroups. Each quarter is in
one
> table and there is one table per filegroup. There is a view which unions
all
> the tables and makes the data accesible to the application. Currently we
are
> backing up the full database every night. I need to make a case to convice
> management to archive the data and need to know what would be the best
> approach. The main two factors to consider are speed and ease of
recovering
> the archived data if users need it. I am considering the options below.
Which
> one would you recommend?
> 1. BCP out data and shrink filegroups - to recover, expand filegroups &
bcp
> in.
> 2. Same as #1 but instead move the data to a separate database and then
back
> that up or restore it to recover the data and copy it across.
> 3. Place data in separate databases and backup/drop/restore as needed (The
> view will need to changed each time. I also don't know how this will
affect
> select performance if the view is spread across the databases on the same
> server.)
> 4. Back up the individual filegroups, empty them, and then restore them
> individually if needed. (don't know if this is possible)