Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
slihp
Yak Posting Veteran
61 Posts |
 Posted - 2013-12-05 : 12:10:52
|
| Hi allIm 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-05 : 13:59:25
|
| Ideally you would require creating a OLAP system for reporting as the OLTP system will be optimized for data storage whereas it may not perform well for data retrievals. Table design itself would be different like applying normlization principles to avoid redundancy. So any summarized reports etc would require aggregating huge amount of data from multiple tables using join operations which would affect performance of reports. Also if you use same system, reporting will have an impact on transactions simultaneously happening on it. So better approach would be to design a separate OLAP system where tables will be denormalised and designed upto grain in which reports need to show the data. You may also choose to maintain history in OLAP system for time based analysis of attribute value.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
slihp
Yak Posting Veteran
61 Posts |
Posted - 2013-12-05 : 17:14:05
|
| Thanks.I anticipated the latency issues, so was considering a nightly etl process into a staging db and then into a reporting db.My issue is i have come mainly from a background of building ssrs reports ontop of relational databases. So going down the OLAP ssas route would be an additional learning curve, plus is OLAP not more for forcasting. i see the reporting need of my company being more like How is one product performing against another. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-06 : 05:05:33
|
| i didnt mean OLAP ssas route ...you can design relational database itself in such a way as to optimize OLAP operations ie make tables denormalized by flattening out hierachies, rollup tables to highr level grain tables as per reporting needs etc. Make SSRS feed on this relational system and performance would be much better compared to your existing OLTP. You can also chose to store history (Type 2) data if you want.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
slihp
Yak Posting Veteran
61 Posts |
Posted - 2013-12-06 : 09:48:16
|
| Ah i see, so in effect having an ETL process feeding into a star type schema relational database?thanks again visakh for your input. |
|
|
|
|
|
|
Using OLTP as datasource or move to OLAP