Today's BI Design - On Premises Enterprise BI Architecture & Design from a bird's-eye view.

Today's Enterprise BI Architecture

A good BI enterprise design must be done as though the application will be for sale. It should be able to accommodate any input data, or be as flexible as required for that particular BI business logic. The legacy data should depend on the Source DB because the Source DB has a closer design to the BI DW. 

See the overall objective and complete agenda here: Overall Objective and Agenda

Generally you will need the following databases to design an optimized BI environment:

1. DL DW
   A Data Lake data warehouse is known as the Source DB, it's the landing area of the data. Some designers like to call it a flat database because it dose not have any Foreign Keys (FK) and the tables likely have no more than 2-3 indexes.
2. BI DW
   This is the location where part of the business logic is applied. Other things like SCD Type2 using HASHBYTE also happens in this database. You must know the business to create optimized tables with the right Primary Keys (PK) and indexes. One of the things that I generally do is to create all the FK constraints and make sure that they are all disabled.
3. DM
   A DM is a database but not a SQL database and it's the second location where business logic is applied. When I am evaluating a DM I look for:
   
   1. How many measures are being designed.
   2. Check the hierarchy and cross join of the hierarchies
   3. Perspectives
   4. Partitioning
   5. Dynamic Processes
   6. Documentation
   7. See more
4. Config DB
   The configuration database controls the entire enterprise application. It contains lots of non-confidential information and other tools such as:
   
   1. BI Incremental load mechanism
   2. HASHBYTE Process
   3. Internal/External logs
   4. Auditing
   5. Report generator tools
   6. Multi-language support
   7. Documentation
   8. Date Dimension
   9. SQL and DM Partition
5. Other Databases
   Depending on your companies security/design policies you might need more databases/environments for example you will need SSISDB for SSIS Catalog deployment, or you might need [msdb] database because you are trying to fire off a SQL job using a front-end tool.

The best practice is a combination of the old approach and the new BI Architecture.