Something is Missing from the Table or View

With our prospect plan stage durations example, a data warehouse query can be built which is very similar to the OLTP version. But some of the columns available in the OLTP database are not available in the data warehouse database. For example, the OLTP INTERACTION table has columns for actual start datetime and actual end datetime. But the fact and dimension tables only have one datetime column. That column corresponds to a column in the OLTP database which coalesces the actual start datetime and the expected start datetime. So that leaves us with some choices to make. Here are some options.

Use what is there: The only end datetime needed is the end datetime for the last step in the plan. And the interactions only have a day associated with them. So a useful metric can still be created. But it will not convey hours. In this case, we could adapt the query to use the same date for the one place where end datetime is used. But it would probably be better to rewrite the query altogether since the time parts are considered throughout. The metric would show zero days rather than one day in many situations.

Create a new table to extend the fact and a view to join the fact to the new table: This requires extending the data warehouse. But it is a fairly simple extension since we can base it on the existing table revision and SSIS package. It would allow us to model our data warehouse query on the OLTP query we already have. Unfortunately, the data is spread across a fact table and a dimension table. So we need to create an extra join to make this work. We can leave this to whoever uses the table or create a view to join the fact to the new table. We will see the benefit of placing the reporting burden on the data warehouse rather than the transactional database. But the query won't be more efficient.

Note: If we were extending the OLAP cube, we could create a fact extension to accomplish this.

Create new tables and views tailored to the reporting needs: This requires extending the data warehouse. It also requires thoughtful data modeling. And within this option, one needs to consider whether the extension will only support the specific reporting needs at hand or if there will be other reporting needs down the road. For example, with the prospect plan stage durations example, it is necessary to create sequences and many rows are eliminated in the course of the query. The extension could reflect this to make the report more efficient. But is would limit the functionality of the extension to support other reports.