Shalini's Knowledge Share

We do a few mistakes when designing a system. When I say we, customer, BA, Consultants included;

    1. Not using the optimal design patterns and technologies

    2. Customer not highlighting some requirements because he/she thinks it is a minor requirement although that minor requirement can become a big issue if we identify it later as it requires a design change that will result in a big impact to the implementation if already halfway through.

Coming from a software engineering background and changing over to BI and also in project management, I have seen this, experienced this and felt the pain of it.

Dimension types 1 and 2 is one such thing. seen, experienced and felt. This is something I experienced on my second BI project which was time critical few years ago.

For anyone reading this who is new to this area, most dimensions change over time. So they are called Slowly Changing Dimensions.

Will start with Dimension type 0.

Type 0: these are the ones that never change. For example Colour dimension. Blue will always remain blue.

1	Blue
2	Green

Type 1: replacing the existing value. For example Customer dimension. John Doe\’s telephone number is changing. With a type 1 dimension, we simply replace the existing telephone number

1

John

Doe

333-222

This example might sound like why do we need to keep all the telephone numbers, as we need only the current telephone number in use.

But in the business world, these things are actually needed.

On the project I explained above this was an issue. We had to create clinical reports for an emergency department based on each month. At this point in time, I had very little experience, so somebody else designed the project. Reading later, I understood it was designed as Type 1 dimensions for all. This was okay as how it was documented was we needed to show the latest summaries for all reports.

With the requirement discussions for other non BI areas still going but the reporting designs complete and implementation done halfway, I grabbed a sentence from the customer that we needed to show the snapshot of the report for that point in time.

Lets say we are living in June 2020. A hospital (site A) has 20 encounters attached to it by March 2020. The site name changed to site B after March and now has 40 encounters attached to it. If you generate the report for March which your sitting in June, you need to display Site A as 20 and if you generate the report for June, it needs to display Site B as 40. However, we were displaying Site B as 20 when generating the report for March.

There were 5 massive reports, with 20-30 KPIs in each report, each having many aggregations or showing the trends. Almost all the KPIs had to do this change. Which means, we had to change the design, and more than 50% of the implementation; lot of rework, lot of extra hours spent, lot of money wasted!