enterprise data warehouse design best practices

The goal of the Business Intelligence Team inside this Bank – a top 10 in Italy by market capitalization – was to lead the IT side of the company and all the BI suppliers, in order to enhance Enterprise Data Warehouse design best practices and then standards.

They did some training on Conceptual Modeling and on the Dimensional Fact Model, and started using indyco Builder as a standard in a new projects. This was an important step and a prerequisite for the Top Manager to authorise new projects and new initiatives: in fact, in the past they used to spend a lot and obtain poor results mainly because of a lack of governance and management.

What happened is that indyco, on top of becoming a new Data Warehouse modeling standard, became a communication standard. When you have to share Data Models in such a big environment, you need a methodology and a tool: it helps if you want to centralize, for example, customer records or analysis dimensions.

With indyco, they were able to specify Conformed Hierarchies: hierarchies that are shared among two or more fact schemata in a Data Mart or in different Data Marts. Creating conformed hierarchies is a powerful way of ensuring that the information stored in different facts can be consistently integrated. In particular, conformed hierarchies have a primary role in Data Mart Bus Architectures because they enable an enterprise view of the data warehouse even if the different Data Marts are separately designed and built.

Also, the team was able to leverage the automatic Data Warehouse documentation capabilities: with just a click, they could, at any given moment, generate a comprehensive set of documents or just copy / paste what was needed.


indyco Explorer was the communication tool for Business Users, too: they all were able to interact with a site (available also for tablets) that is a sort of Google Maps of their data structure.

Thanks to an intuitive graphical interface, to a great User Experience and a powerful Business Glossary (indyco Explorer), the bank reached both its goals in one shot: setting up a modeling and a communication standard.

explorer1For instance, take a quick look at the aside snapshot of a Sales area. The two things that every business user will immediately understand are:

  • I clearly get how we navigate the Product hierarchy starting from a single Department. Every Department has many Categories; every Category has many Types and finally every Type has many Products;
  • Let’s suppose that a report shows a table with Brand and Number of Units. If I double click on Brand everybody would expect to drill down to the product level and not for example to the Department.

After the first wave of adoption, they were able to quickly move to new areas, such as client multichannel behaviours or credit analysis. As both IT and Biz Users were more used to Conceptual Model Design and the DFM, they started co-designing the new Data Model together, directly using indyco Builder: they needed just a little help to cope with the validation engine. The validation engine is an extremely important part of the tool that makes sure you are following the correct Data Warehouse best practices: it runs in the background and constantly checks the ‘validity’ of your model. When everything is green and the tool says the model is correct, this means you are on the right path: you will just need to check your Project Metrics to be sure your design is at the quality level you need (look at the post: DFM and DW Testing – A Winning Combination).

This means that after training IT and Key Biz Users for overall less than 3 days, they were able to run core projects in parallel, and each project was estimated to last 50% less than before, mainly due to the lack of misunderstanding, over-recycling of documentation and more agreement on Biz topics.