(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.

(Cancel this) Can’t paste into XML field in Management Studio.

Hi Folks,

It looks like Management Studio prevents pasting XML directly into a field of the type xml. Sometimes, it might be best to stay with varchar(max).

Original post below: >>

I want to paste some XML into an SQL Server 2005 xml type field, but the null is grayed out and paste simply does not work. What is going on?

Thanks,

Rob

Rob:

I see the same behavior. It appears to me that the COPY function is not active for the XML field and that you cannot directly modify it. You can copy from the XML field, but you cannot paste into it. I suggest that you use an XML query to update your field?

"Parameters" approach to fill report header with source data doesn't work

It's well known issue, that one can't use any dataset fields in a

report header/footer directly. One of the approach is to create

query-based parameter that basically equals

=First(Fields!@.FieldName@..Value, "@.DataSetName@.") and use that

parameter value instead. But it doesn't work in my case!

My report displays some entity description and is parametrized with

EntityID param. Its header contains entity name that, according to the

approach, is queried from the data source through the EntityName

report parameter. There's important issue: the report is displayed in

ReportViewer control, that is embedded into my application and entity

ID parameter isn't ser by user in ReportViewer parameters area. Its

default value is changed by the application with SetReportParameters()

web method every time a user wants to view the report according to the

entity the user is exploring in the application. But after the report

has been rendered, its header always contains not actual (outdated)

entity name. Nevertheless, the report body contains actual data

(including entity name). If I alter entity ID parameter in ReportViewer

or in web-based Report Manager and refresh report, header displays

correct entity name.

What's wrong in the workflow described?Up! SSRS can't deal with data in hearders, can it?|||

There is a way:

You can make the header fields refere to databound fields which are location within the body of the reports- to get your results ie You can put a hidden text field in the body and reference that field!

|||

Hi Viral,

If we put the hidden textbox in the body and use those textbox value it works perfectly here the problem is if our result in 5 pages when we export this result to PDF , the problem here is for the first page only the textbox value in the header is displaying from next page onwards it is displaying null values eventhough we made the property of textbox "Repeat With" :table1(result set) set.

Thanks