|
|
The BI tool will utilise a reporting database for all its operations. The following diagram represents the proposed solution:
The Transactional database will remain as is. No changes will be made on this database. The user will still be able to run SSRS and Analyser reports from this database. The Schema on the Transactional database contains all the database tables (dbo tables) and all the views (vw tables)
SQL Replication will be used to populate the Reporting database. SQL Replication is part of the SQL stack of technologies designed to copy and distribute data between two databases and then synchronise them to maintain consistency. Replication follows a publisher/subscriber model, i.e., the changes are sent out by one database server ("publisher") and are received by others ("subscribers"). The Publisher part will reside on the Transactional database. The Distributor and Subscriber will reside on the Reporting database. The Type of Replication that will be used is Transactional Replication.
The Reporting database will have two schemas – On Key Schema and Reporting Schema. The On Key Schema will contain a subset of On Key dbo tables. This will be populated via SQL Replication and will be an exact copy of the tables within the On Key database. The Reporting Schema caters for external data required for some of the standard KPI reports. External data can be populated in the rpt tables in the Reporting schema. Views can be set up within the Reporting Schema to specifically address all the BI Model requirements. These views can be set up to query On Key and/or external data within the reporting database.
Default BI models exist which supports the standard set of KPI reports.
The On Key server will host On Key, the On Key transactional database and the Publisher part of the SQL Replication solution.
The Reporting server will host the Reporting database, the Distributor and Publisher part of the SQL Replication solution and the BI Server. One of the main reasons for having a reporting database is to gain a performance improvement on the Transactional database due to the fact that large reports will now run off the Reporting database. This entails that the Reporting database and BI server should run on its own server.
SSRS reports can now be configured to run or off the transactional database or off the reporting database.