Most of the guidance in the Kimball method for designing, developing, and deploying a DW/BI system is just that: guidance. There are hundreds or thousands of rules in the Kimball Group’s many books, and I confess to having bent many of those rules over the decades, when faced with conflicting goals or unpleasant political realities. […]

An ever-growing set of data storage technologies offer advantages to the data warehouse architect that look pretty magical. How can you figure out what to do? Should you stick to the tried-and-true relational databases to host your data warehouse? Should you use cubes? Or should you move to the latest and greatest data management solutions […]

We characterize the ETL system as a back room activity that users should never see nor touch. Even so, the ETL system design must be driven from user requirements. This Design Tip looks at the design of one bit of ETL plumbing – the fact table surrogate key pipeline – from the perspective of business […]

It seems like a small thing, but names are important. Good names for tables and columns are particularly important for ad hoc users of the DW/BI system who need to find the objects they’re looking for. Object names should be oriented to the business users, not the technical staff. As much as possible, strive to […]

The increasingly popular data visualization tools deliver an environment that business analysts love. They provide the ability to define calculations, and more importantly, to explore and experiment with the data. The products have finally innovated away from the old standbys of tables, bar charts, and pie charts, making it easier for users to draw visual […]

One of the key components of the business intelligence (BI) architecture is a semantic layer. The semantic layer provides a translation of the underlying database structures into business user oriented terms and constructs. It is usually part and parcel of the query and reporting tool. OLAP or cube databases also include a BI semantic layer. […]

In Design Tip #140, I discussed the challenges of designing dimensional schemas for processes of indeterminate length such as a sales pipeline or insurance claims processing. We concluded they are best represented as accumulating snapshot fact tables characterized by one row per pipeline occurrence where each row is updated multiple times over its lifetime. However, […]

For most subject areas, it’s pretty easy to identify the major dimensions: Product, Customer Account, Student, Employee, and Organization are all easily understood as descriptive dimensions. A store’s sales, a telecommunication company’s phone calls, and a college’s course registrations are all clearly facts. However, for some subject areas, it can be challenging – especially for […]

A data warehouse / business intelligence system is challenging to test. Standard testing methodology tests one little thing at a time, but a DW/BI system is all about integration and complexity, not to mention large data volumes. Here are my top five recommendations for building and executing a testing environment for your DW/BI project. 1. […]