(Design) Production db used as the data warehouse?

We're designing our first bi suite and we're considering not using a data warehouse at all but connecting directly to the production database via the DS. I have a nagging feeling that inherently this is not a good idea but would welcome pros and cons.

Pros
- real time data updates as fast as we can Process
- no need for ETL
- we can write named queries for use in the DSV to satisfy our data requirements

Cons
- performance hit to non BI users when BI users report if using ROLAP partitions
- potential table locking during Processing, again affecting non BI users

My gut feel is that instead we should be keeping a synchronized copy of Production as a DW to report off, but I will need stronger Cons that those above to convince my boss.

Be gentle please - my first post and I completed my fist SSAS course only yesterday. Thanks.

Are your production db home made or purchased solution from sombody?

Is the data quality 100% so you don't need data clearing and validation?

Are there any updates/upgrades of production db structure?

Are you sure, that you production db is only source of your data warehouse and there will be no another sources in next years?

How large is the volume of your production db?

How large it will be in 3-5 years?

Do you plan to archive some old transaction data from your production db?

|||

Thanks Vlad, some good points here:

Are your production db home made or purchased solution from sombody?

- The production database is also ours.

Is the data quality 100% so you don't need data clearing and validation?

- Yes, and any changes that need to be made through the interfaces would be made in the source (production) database (which would then flow through).

Are there any updates/upgrades of production db structure?

Cheers I hadn't considered this - if we change the DDL of the production database during version upgrades then this will potentially have an affect on the DSV's.

Are you sure, that you production db is only source of your data warehouse and there will be no another sources in next years?

At this stage yes - although if it's very successful I could see clients wanting to report on other external sources like their upstream ERP's. I'm not sure how having the production database as the data warehouse could relate to this though...

How large is the volume of your production db?

Our largest production databases are about 5GB.

How large it will be in 3-5 years?

Hmm, hard to say but would guess 10GB?

Do you plan to archive some old transaction data from your production db?

Very good point - for customers who have been using the software for some time and have a large amount of history they may want to use the DW as an archive and remove data from production - the approach I outlined about does not allow this easily (we would have to write a number of date queries restricting the data.

Considering the lukewarm response I got to this thread perhaps what is being proposed is not such a big deal/poor option - if I consider the cons above it sounds like we may be able to implement it in such a way. I must admit I still have a bad get feel about it but can't really pin down exactly why...

Has anyone seen cubes based directly off their production database (i.e. not a copy of it) in a real world environment? Would love some more feedback...

|||

Hi,

I have seen many SSAS solutions, and a some of them direct connected to production db. but no one of direct connected was ERP db.

I depends on your businnes. Is you production db a ERP like db, or what else? If ERP like, then you must have DWH, if you what to sleep relaxed :-) another way you get enough headache

|||It's an OLTP database but not high volume, only dozens of transactions an hour. But we're thinking of only processing nightly, and setting an expectation with the users that this will be the case.

# of Tables in a DB

If I want to build an architected data warehouse, otherwise known as a star
constellation of multiple facts and conformed dimensions, I'm trying to figu
re out how to separate the physical tables by database. Think about modelin
g the constellation in ERwi
n. You want to have mutiple fact tables with foreign key relationships to s
hared conformed dimensions. The question is - AS A GENERAL RULE, when does
a single database have too many tables? Does each star in the constellation
(for each business process
) merit its own database in the SQL Server instance? If so, how do you set
foreign key relationships across database boundaries, since not having the r
elationships will cause performance to suffer? I have found no way to set f
oreign key relationships ac
ross database boundaries in ERwin.I'm not sure why you would want to have multiple databases. You can already
use filegroups to isolate certain tables or non-clustered indexes across var
ious spindles. Regardless, you cannot have FK constraints that span database
s.

# of Tables in a DB

If I want to build an architected data warehouse, otherwise known as a star constellation of multiple facts and conformed dimensions, I'm trying to figure out how to separate the physical tables by database. Think about modeling the constellation in ERwi
n. You want to have mutiple fact tables with foreign key relationships to shared conformed dimensions. The question is - AS A GENERAL RULE, when does a single database have too many tables? Does each star in the constellation (for each business process
) merit its own database in the SQL Server instance? If so, how do you set foreign key relationships across database boundaries, since not having the relationships will cause performance to suffer? I have found no way to set foreign key relationships ac
ross database boundaries in ERwin.
I'm not sure why you would want to have multiple databases. You can already use filegroups to isolate certain tables or non-clustered indexes across various spindles. Regardless, you cannot have FK constraints that span databases.