Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using OLTP as datasource over creating an OLAP Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 10:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 4:12 AM
Points: 79, Visits: 461
Hi all

Im moving to a new company to be there BI specialist. doing some research on best practices and looking for some advice feedback on decisions to take.

The company has no BI just now and i belive they have a few OLTP database that there web sales system updates. Im havent seen the DB's yet but have been told they would be hard to report against. Im sure for the most part Day old data would be sufficient for reporting purposes.

My question is do i HAVE to create and OLAP database with dimensions and facts table i.e. star schema design even if the delivery method will only be ReportingServices. Im not sure if they will require the forecasting ability that SSAS can provide in the outset.

Im sure i will have to ETL the data from the production database nightly, but what are my options for this output if im only running ssrs reports off it?

thanks
Post #1520218
Posted Friday, December 6, 2013 8:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:07 AM
Points: 817, Visits: 2,061
If the only requirement is to run SSRS reports, the operational databases shouldn't be used and the data can be one day old, I would report off a restored backup of the OLTP servers. This seems to me the fastest way to get you going. I personally find SSRS difficult to use against MDX sources, but this isn't a problem with OLTP and standard T-SQL.

If a true BI solution were ever desired, however, this method hasn't taken any steps in that direction.



Post #1520600
Posted Friday, December 6, 2013 9:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 4:12 AM
Points: 79, Visits: 461
RonKyle (12/6/2013)
I personally find SSRS to use against MDX sources, but this isn't a problem with OLTP and standard T-SQL.


Hi thanks for the replay, is the word "difficult"missing from the above sentence, as i would agree if it is.

yes thats what i was thinking IN THE SHORT term, run reports off a restore of the current DB. I see this as giving some value to the bisness up front and would give me more time to get to grips of the business needs to provide medium & long term BI solutions weather that be Datawarehouse, Datamarts OLAP cubes.
Post #1520639
Posted Friday, December 6, 2013 9:36 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:07 AM
Points: 817, Visits: 2,061
I did mean to add the word difficult. Odd that I dropped an entire word. Letters I understand... I have altered the previous post.


Post #1520645
Posted Monday, January 6, 2014 11:43 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 6, 2014 12:07 PM
Points: 815, Visits: 563
You were hired as a BI specialist and sooner or later you will be asked for BI solutions. Therefore, I would start working on multidimensional or tabular models ASAP. At the same time you could provide reports from a copy of OLTP but that will not work in the long time. I know people will pressure you for results but well designed data warehouse will last for many years.
Post #1528211
Posted Monday, January 6, 2014 4:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 4:43 AM
Points: 5,401, Visits: 7,512
You can report off OLTP systems with a light touch as long as you're not looking for significant aggregations and the like. My guess is they're not, not yet.

What I'd recommend is the following: Work off their systems. Get a feel for the company and what they're hoping to get out of their data. Build them some reports, get them interested in the idea, let them start round tabling where they'd like to go with it. Then rein in the ones running for the horizon.

With that initial set of guidelines, you can get a much better idea of how current your data needs to be (usually there's two sets, immediate dashboards and historical reviews). From there, start your initial warehouse design, realizing it'll probably be iterative (and disposable) the first time or two while you actually settle out what the company really needs from a data perspective.

The heavier the aggregations and the more load you put against the OLTP systems, the faster you'll be advancing to the warehouse.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1528290
Posted Tuesday, January 7, 2014 7:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 4:12 AM
Points: 79, Visits: 461
Thanks guys, very helpful info.

one thing im not clear on. If have seen data warehouses being dropped and recreated then populated on a nightly basis. this obviously means the ETL has to be performed on all the data in the OLTP source to populate the warehouse. is this the norm? how would this scale up?

or would you be better to adopt a strategy of just updating the warehouse with only new data?
Post #1528497
Posted Tuesday, January 7, 2014 8:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:07 AM
Points: 817, Visits: 2,061
I don't ever recommend dropping and recreating the warehouse from scratch, although I've seen that as a solution from some consultants. It's faster to develop but ultimately (and as you correctly ask about) doesn't scale well. Incremental updates are the way to go. It will take longer to develop, but your nightly run times will be stable instead of incrementally increasing until they reach a point where it's unsustainable.


Post #1528508
Posted Tuesday, January 7, 2014 10:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 4:43 AM
Points: 5,401, Visits: 7,512
RonKyle (1/7/2014)
I don't ever recommend dropping and recreating the warehouse from scratch, although I've seen that as a solution from some consultants. It's faster to develop but ultimately (and as you correctly ask about) doesn't scale well. Incremental updates are the way to go. It will take longer to develop, but your nightly run times will be stable instead of incrementally increasing until they reach a point where it's unsustainable.


I won't disagree with you outright, Ron, for the simple fact that if you want a scalable solution you're dead on target. That said, most folks aren't working against huge data, and overdevelopment is overkill.

An example: A current warehouse I have starts with a Truncate/Reload component. This has a strict limit. Any table over 1,000,000 records OR longer than a 10 minute load time (they're concurrent) falls out to a delta component for the next sprint. That's where we decided to implement our sanity check.

You have to work against expectations of the developer vs. expectations of the development. Good enough is, in most cases, good enough. Smaller shops, like the one the OP is involved in, will probably only need SCD2 type work in very rare instances. In most cases, I find that using a delta for every component is indeed overdeveloping the expectations. I personally use a 10 year rule. If someone will have to go in and modify my code for data expansion expectations within the next 10 years to deal with volume vs. hardware upgrade expectations, I overdevelop to allow for the expansion. Otherwise: KISS. 10 years is (usually) more than enough to allow for business changes to already have redeveloped any particular component twice, and it can be dealt with then should optimization be considered more valuable than development time.

When in doubt, XKCD is my best friend for this: http://xkcd.com/1205/

As I said to client one day: "Sometimes, you don't hire more IT, you hire a secretary."



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1528756
Posted Wednesday, January 8, 2014 2:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 4:12 AM
Points: 79, Visits: 461
Both good points well made. first one answers my question on scaleability but also second one explains why the ETL's ive seen before do a drop of the DW first (the OLTP store was not massive).
Post #1528807
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse