When you’re using a data warehouse, some actions get repeated over and over. We will take a look at four common algorithms used to deal with these situations.Most DWH (data warehouse) systems today are on a RDBMS (relational database management system) platform. These databases (Oracle, DB2, or Microsoft SQL Server) are widely used, easy to work with, and mature – a very important thing to bear in mind when choosing a platform. Although they include special features for DWH configuration (like bitmap indexes, partitioning, and materialized views) we still have to take care of data transformation ourselves.
Mastering SQL analytical functions is a necessity for an aspiring BI/DWH professional. In this article, we’ll explore the history of SQL in a BI environment. We’ll also introduce some of the more common SQL analytical functions, including RANK, LEAD, LAG, SUM, and others.SQL can be used for business intelligence (BI) applications that combine data retrieval, analysis, and computation. These computations are more complex than what we see in OLTP systems. If you’re interested in getting into data warehousing (DWH) or business intelligence, you need to go beyond plain SQL and start digging into the analytical functions (also known as window functions, windowing functions, or OVER clauses).
When designing your dimensional model, it is worthwhile to watch out for mistakes that commonly occur during the process. Specifically, they can occur in the relationships between tables, both in fact-to-dimension and dimension-to-dimension relationships. In this post, we’re going to take a closer look at five common modeling mistakes and what you can do about them.As you start a BI-related project, bulletproof dimensional design is hugely important. What makes a design bulletproof is the
When we start a data warehousing project, the first thing we do is define the dimensional tables. Dimensional tables are the interesting bits, the framework around which we build our measurements. They come in many shapes and sizes. In this article, we are going to take a closer look at each type of dimensional table.Dimensional tables provide context to the business processes we wish to measure. They tell us all we need to know about an event. Since they give substance to the measurements (i.e. fact tables) of the data warehouse (DWH) system, we spend more time on their definition and identification than on any other aspect of the project. Fact tables
The process of defining your data warehousing system (DWH) has started. You’ve outlined the relevant dimension tables, which tie to the business requirements. These tables definewhatwe weigh, observe and scale. Now we need to definehowwe measure.Fact tables are where we store these measurements. They hold business data that can be aggregated across dimension combinations. But the fact is that fact tables are not so easily described – they have flavors of their own. In this article, we’ll answer some basic questions about fact tables, and examine the pros and cons of each type.