Today, reports and analytics are almost as important as core business. Reports can be built out of your live data; often this approach will do the trick for small- and medium-sized companies without lots of data. But when things get bigger – or the amount of data starts increasing dramatically – it’s time to think about separating your operational and reporting systems. Before we tackle basic data modeling, we need some background on the systems involved.
With the release of SQL Server 2016’s Community Technology preview, we’re finally able to get an in-depth look at the exciting options SQL Server 2016 includes. Two of the most anticipated are Always Encrypted and In-memory OLTP. Always Encrypted is a new method of consistent encryption intended to greatly simplify data protection. In-memory tables, while technically included in SQL 2014, were painful to implement and had significant limitations; SQL Server 2016 removes many of these and makes in-memory OLTP a viable option.
Introduction As I mentioned in my article “OLAP for OLTP practitioners”, I am working on a project that needs to create an analytical database for on-line analytical processing (OLAP). I have mostly worked with on-line transaction processing (OLTP) with some limited reporting features. OLAP is a new area for me. In OLAP, the main focus of the database itself is simply to store data for analysis; there is limited maintenance of data.
I am currently working on a project where we need to create a database that will be primarily used to store data for reporting and forecasting. In the past, I have mostly worked with databases used for typical CRUD (create, retrieve, update, and delete) operations of data with some limited reporting features. When performing CRUD operations, normalization is important; while in analytics, a de-normalized structure is generally preferred.