New SQL Course!
Learn the basics of T-SQL for SQL Server, retrieve data from an SQL Server database and build simple reports.
View course
Discover our other courses:
In the previous two parts, we’ve presented the live database model for a subscription-based business and a data warehouse (DWH) we could use for reporting. While it’s obvious that they should work together, there was no connection between these two models. Today, we’ll take that next step and write the code to transfer data from the live database into our DWH.
Can you design an OLAP database model from an OLTP model? In this article, we’ll show you how!
Welcome to a new series that shows you the practical side of the data warehouse (DWH)! In the first article, we’ll tackle a data model for a subscription business.
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.
There’s a lot to keep in mind when you’re designing a database, and very few of us can remember every valuable tip and trick we’ve learned. So, let’s take a look at some online resources that feature database design tips and best practices. As we go, I’ll share my own opinions on the ideas presented, based on my experience in database design.
Introduced in SQL 2012, ColumnStore indexes differ greatly from standard row-based indexes. Intended for OLAP systems, these indexes store data in a highly compressed, segmented fashion with the column as the basis (rather than typical row-based indexes). This type of column-based index allows for great performance gains in data warehouses where table scans, rather than seeks, are performed.
In this article, I look at bridging the requirements between transactional and analytical processing and look particularly at the spider schema as one possible solution.
Working on a project to create an application that is focused on analytics, reporting, and especially forecasting, has forced me to recognize the different requirements between transactional and analytical database design.