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